花了小一天的時間，終于實現了centos7 mariadb主從復制配置搭建，下面記錄一下過程
環境:
虛擬機:vm8; centos7 版本：7.2.1511; mariadb 版本：centos7.2內置的
主庫服務器： 10.69.5.200，CentOS 7，MariaDB 10已安裝，有數據。
從庫服務器1： 10.69.5.201，CentOS 7，MariaDB 10已安裝，無應用數據。
主服務器配置
以下操作在主服務器192.168.71.151的/etc/my.cnf上進行。
1.修改配置文件，命令：vim /etc/my.cnf，輸入下列代碼：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
[root@localhost ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock`# 新添加的部分# 配置主從時需要添加以下信息 startinnodb_file_per_table=NOlog-bin=/var/lib/mysql/master-bin #log-bin沒指定存儲目錄，則是默認datadir指向的目錄binlog_format=mixedserver-id=200 #每個服務器都需要添加server_id配置，各個服務器的server_id需要保證唯一性，實踐中通常設置為服務器IP地址的最后一位#配置主從時需要添加以下信息 end `# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d |
最后，:wq!保存退出
2.重啟mariadb服務，輸入命令
|
1
|
[root@localhost ~]# systemctl restart mariadb.service |
3.登錄mariadb
|
1
|
[root@localhost ~]# mysql -u root -padmin |
注：-p后是密碼，中間沒有空格
4.創建帳號并賦予replication的權限
從庫,從主庫復制數據時需要使用這個帳號進行
|
1
2
|
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin';Query OK, 0 rows affected (0.00 sec) |
5.備份數據庫數據，用于導入到從數據庫中
加鎖
實際工作中，備份的時候是不讓往庫中寫數據的，所以數據庫要加鎖，只能讀
|
1
2
|
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;Query OK, 0 rows affected (0.00 sec) |
記錄主庫log文件及其當前位置
|
1
2
3
4
5
6
|
MariaDB [(none)]> SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 694 | | |+------------------+----------+--------------+------------------+ |
記住File和Position的部分，從服務器會用到
備份數據，輸入命令：
|
1
|
[root@localhost ~]# mysqldump -uroot -p --all-databases > /root/db.sql |
解鎖 主庫
數據備份完成后，就可以釋放主庫上的鎖：
|
1
2
|
MariaDB [(none)]> UNLOCK TABLES;Query OK, 0 rows affected (0.00 sec) |
從服務器配置
以下在從服務器上的操作
1.導入主庫的數據
|
1
|
[root@localhost ~]# mysql -uroot -p < db.sql |
2.從服務器/etc/my.cnf配置,設置relay-log
my.cnf文件中添加一行relay_log=relay-bin
如果不設置，默認是按主機名 + “-relay-bin”生成relay log。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
[root@localhost ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0`#配置主從時需要添加以下信息 startinnodb_file_per_table=NOserver-id=201 #一般與服務器ip的最后數字一致relay-log=/var/lib/mysql/relay-bin#配置主從時需要添加以下信息 end `# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/var/log/mariadb/mariadb.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d |
3.重啟服務
|
1
|
[root@localhost ~]# systemctl restart mariadb.service |
4.登錄mariadb
|
1
|
[root@localhost ~]# mysql -u root -padmin |
5.設置主從復制
|
1
2
|
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694;Query OK, 0 rows affected (0.02 sec) |
這個命令完成以下幾個任務：
a.設置當前服務器為主服務器(10.69.5.200)的從庫
b.提供當前數據庫(從庫)從主庫復制數據時所需的用戶名和密碼，即上面的GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin';設置的
c.指定從庫開始復制主庫時需要使用的日志文件和文件位置，即上面主庫執行SHOW MASTER STATUS;顯示結果中的File和Position
6.開啟主從復制
|
1
2
|
MariaDB [(none)]> START SLAVE;Query OK, 0 rows affected (0.00 sec) |
7.查看從庫狀態
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
MariaDB [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.69.5.200 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 694 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 530 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 694 Relay_Log_Space: 818 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2001 row in set (0.00 sec) |
注意：結果中Slave_IO_Running和Slave_SQL_Running必須為Yes，如果不是，需要根據提示的錯誤修改。
驗證
主服務器：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || mytest || performance_schema || test |+--------------------+5 rows in set (0.04 sec)MariaDB [(none)]> use mytest;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 || 3 | t3 |+----+------+3 rows in set (0.00 sec)MariaDB [mytest]> insert into user(name) values('t4');Query OK, 1 row affected (0.01 sec)MariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 || 3 | t3 || 4 | t4 |+----+------+4 rows in set (0.00 sec) |
查看從服務器數據是否變化：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
MariaDB [(none)]> use mytest;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 |+----+------+2 rows in set (0.00 sec)MariaDB [mytest]> select * from user;+----+------+| id | name |+----+------+| 1 | t || 2 | t2 || 4 | t4 |+----+------+3 rows in set (0.00 sec) |
可以看到，從服務器更新了數據
搭建過程中遇到的問題及解決方法
問題1：從服務器設置主從復制出現錯誤:
|
1
2
|
MariaDB [mytest]> start slave;ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log |
發現
|
1
2
|
Slave_IO_Running: NoSlave_SQL_Running: No |
進一步發現我輸入的是：CHANGE MASTER TO MASTER_HOST='192.168.71.151',MASTER_USER='slave_user', MASTER_PASSWORD='bigs3cret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;
重新輸入：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;
報錯：ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log
于是看錯誤日志：/var/log/mariadb/mariadb.log
錯誤日志的位置在/etc/my.cnf中配置：log-error=/
|
1
2
3
|
[root@localhost ~]# cat /var/log/mariadb/mariadb.log160915 12:52:02 [ERROR] Failed to open the relay log './mariadb-relay-bin.000001' (relay_log_pos 4)160915 12:52:02 [ERROR] Could not find target log during relay log initialization |
通過查找答案： 刪除/var/lib/mysql/路徑下the ‘master.info' ‘mysqld-relay-bin.*' ‘relay-log.info' ‘relay-log-index.*'
運行命令：rm -rf master.info,rm -rf *relay*
重啟服務：[root@localhost mysql]# systemctl restart mariadb.service
進入mariadb:
|
1
2
3
4
5
6
7
|
[root@localhost mysql]# mysql -u root -padminMariaDB [(none)]> flush logs;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> reset slave;Query OK, 0 rows affected (0.00 sec) |
重新設置主從復制關系：
|
1
2
|
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694;Query OK, 0 rows affected (0.02 sec) |
這次成功了。
|
1
2
|
MariaDB [(none)]> start slave;Query OK, 0 rows affected (0.01 sec) |
查看從庫狀態：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
MariaDB [(none)]> show slave status\G*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.69.5.200 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 694 Relay_Log_File: relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes ··· ··· ··· Replicate_Ignore_Server_Ids: Master_Server_Id: 01 row in set (0.00 sec) |
發現問題2.Slave_IO_Running: Connecting
問題2.Slave_IO_Running: Connecting
查看錯誤日志
|
1
2
3
4
|
[root@localhost ~]# cat /var/log/mariadb/mariadb.log···160915 13:17:56 [Note] Slave SQL thread initialized, starting replication in log 'master-bin.000001' at position 694, relay log '/var/lib/mysql/relay-bin.000001' position: 4160915 13:17:56 [ERROR] Slave I/O: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.69.5.200' (113), Error_code: 2003 |
這時運行telnet命令
|
1
|
[root@localhost ~]# telnet 10.69.5.200 3306 |
-bash: telnet: 未找到命令
安裝telnet
|
1
|
[root@localhost ~]# yum -y install telnet-server.x86_64 |
安裝成功后重啟telnet服務
|
1
2
3
|
[root@localhost ~]# systemctl start telnet.socket[root@localhost ~]# systemctl enable telnet.socket[root@localhost ~]# telnet 10.69.5.200 3306 |
-bash: telnet: 未找到命令
還是不行
這回我reboot重啟虛擬機，運行命令
注意：這回不是"yum -y install telnet-server.x86_64"了，這回沒有telnet-server了
|
1
|
[root@localhost ~]# yum install telnet.x86_64 |
運行成功了
接著
|
1
2
3
4
5
6
|
[root@localhost ~]# systemctl enable telnet.socket[root@localhost ~]# systemctl start telnet.socket[root@localhost ~]# firewall-cmd --add-service=telnet --permanent success[root@localhost ~]# telnettelnet> |
telnet終于安裝成功了
從最新版本的centos7系統開始，默認的是 Mariadb而不是mysql！
使用系統自帶的repos安裝很簡單：
|
1
|
yum install mariadb mariadb-server |
- systemctl start mariadb ==> 啟動mariadb
- systemctl enable mariadb ==> 開機自啟動
- mysql_secure_installation ==> 設置 root密碼等相關
- mysql -u root -p 123456 ==> 測試登錄！
結束！
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://blog.csdn.net/mingliangniwo/article/details/54606894?locationNum=8&fps=1
