CentOS 7系統配置MySQL的主從複製模式 (Master-Slave Replication)

 MySQL的主從複製廣泛用於Database Backup、容錯移轉、資料分析等場合。
   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: YesSlave_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);
從伺服器
   在從伺服器上，你應該可以看到相同的變化。

拓展知識

GRANT REPLICATION SLAVE
   The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.

RULES FOR READ LOCK
The session that holds the lock can read the table (but not write it).

Multiple sessions can acquire a READ lock for the table at the same t  ime.

Other sessions can read the table without explicitly acquiring a READ lock.

RULES FOR WRITE LOCK
The session that holds the lock can read and write the table.

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

Lock requests for the table by other sessions block while the WRITE lock is held.