664
外匯
在CentOS 7上配置MySQL的主從複製模式 (Master-Slave Replication)
MySQL的主從複製廣泛用於數據庫備份、故障轉移、數據分析等場合。
MySQL主從複製基於主服務器在二進製日誌中跟蹤所有對數據庫的更改（更新、刪除等等）。因此，要進行複製，必須在主服務器上啟用二進製日誌。從服務器從主服務器接收已經記錄到其二進製日誌的更新，當一個從服務器連接主服務器時，主服務器從日誌中讀取最後一次成功更新的位置，從服務器接收從那時起發生的更新，並在本機上執行相同的更新，然後等待主服務器通知新的更新。從服務器執行備份不會幹擾主服務器，在備份過程中主服務器可以繼續處理更新。
測試環境
Master: 192.168.10.201
Slave: 192.168.10.202
端口： 3306
數據庫：test2
安裝MySQL
yum install mariadb mariadb-server
systemctl enable mariadb
service mariadb start
Reset root password
mysqladmin -u root password abc@DEF
主服務器配置
主服務器配置文件/etc/my.cnf
[mysqld]
server-id=1
binlog-do-db=test2
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin
重啟MySQL
service mariadb restart
賦予REPLICATION SLAVE權限
mysql -uroot -p
GRANT REPLICATION SLAVE ON . TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 469 | test2 | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
注意：記下紅色部分，稍後還會用到。
備份數據庫
為了備份數據庫，需要為數據庫中所有表叫上“隻讀鎖” (Read Lock)，再進行dump備份：
mysqldump -u root -p --all-databases --master-data > /root/dbdump.db
備份完成後，可以用以下命令解鎖：
mysql -uroot -p
UNLOCK TABLES;
從服務器配置
還原數據庫
mysql -u root -p < /root/dbdump.db
從服務器配置文件/etc/my.cnf
[mysqld]
server-id=2
replicate-do-db=test2
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin
重啟MySQL
service mariadb restart
連接主服務器
mysql -uroot -p
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.10.201', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=469;
start slave;
show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.201
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 469
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test2
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: 469
Relay_Log_Space: 1107
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: 0
Master_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: 1
驗證
主服務器
mysql -uroot -p
drop test2;
create database test2;
use test2;
create table emp (c int);
insert into emp (c) values (10);
最後更新：2017-04-27 11:31:02