CentOS 7系統配置MySQL的主從復制模式 (Master

主從復制小編最多在 windows中配置過了，下文來為各位介紹一篇 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: 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.