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 statusG
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);