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

从服务器

在从服务器上，你应该可以看到相同的变化。

拓展知识

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.

References

[1] How to Setup MySQL (Master-Slave) Replication in RHEL, CentOS, Fedora
[2] LOCK TABLES and UNLOCK TABLES Syntax