MySQL主从复制详解
一、MySQL主从复制原理
随着网站访问量的增加，单台MySQL服务器的压力也不断增加。此时需要对MySQL进行优化。如果优化效果不明显，可以采用高可用、主从复制、读写分离、分库分表等方式进一步优化。
MySQL主从复制的主要目的是实现数据库冗余备份，将master数据库的数据定时同步到slave库中。一旦master数据库宕机，可以快速切换web应用的数据库配置到slave数据库，确保web应用的可用性。MySQL主从复制架构图如下：
MySQL主从复制至少需要两台数据库服务器，一台作为master，另一台作为slave。MySQL主从数据同步是一个异步复制的过程。要实现复制，首先需要在master上开启bin-log日志功能。bin-log日志用于记录在master库中执行的增、删、改SQL语句。整个过程需要开启三个线程：master上的I/O线程和slave上的I/O线程和SQL线程。具体主从同步原理如下：
- 在slave上执行
start slave，开启主从复制开关，生成I/O线程和SQL线程。slave I/O线程通过master创建的授权用户连接到master，并请求master从指定的文件和位置发送bin-log日志内容。 - master接收到slave I/O线程的请求后，先进行验证。验证通过后，master I/O线程根据slave I/O线程的请求读取指定bin-log日志文件的指定position点之后的bin-log内容，然后返回给slave的I/O线程。
- 返回的信息除了bin-log日志内容外，还包括master最新的bin-log文件名以及在bin-log中的下一个指定更新的position点。
- slave I/O线程接到信息后，将收到的日志内容依次添加到slave端的中继日志（relay-log）文件的最末端（如
MySQL-relay-bin.xxxxx），并将读取到的master端的bin-log文件名和position点记录到master.info文件中，以便下一次读取时能够告诉master从相应的bin-log文件名和最后一个position点开始发起请求。 - slave SQL线程检测到relay-log中有更新，会立刻解析relay-log日志中的内容，将解析后的SQL语句在slave中执行。执行成功后，slave库和master库的数据保持一致。
二、主从复制实战
1、环境说明
| HOST | OS | MySQL版本 | 端口 | 路径 | 主/从 |
|---|---|---|---|---|---|
| 192.168.56.11 | Centos 7.4 | MySQL 5.6.35 | 3306 | /usr/local/mysql/data/ | 主 |
| 192.168.56.12 | Centos 7.4 | MySQL 5.6.35 | 3306 | /usr/local/mysql/data | 从 |
2、配置Master和Slave的my.cnf
[root@master ~]# vim /etc/my.cnf
log-bin=/usr/local/mysql/data/mysql-bin # 开启Master的bin-log日志记录功能
server-id=1 # 配置Master的server-id，必须和slave不一样
[root@slave ~]# vim /etc/my.cnf
server-id=2 # 配置Slave的server-id
3、在Master上创建主从复制的用户rep并授权
[root@master ~]# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.56.%' IDENTIFIED BY '123456'; # 创建主从复制授权用户
Query OK, 0 rows affected (0.03 sec)
mysql> FLUSH PRIVILEGES; # 刷新权限
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT user, host FROM mysql.user; # 查看创建的rep用户
+-----------+--------------+
| user | host |
+-----------+--------------+
| root | % |
| wordpress | % |
| root | 127.0.0.1 |
| rep | 192.168.56.% |
| root | localhost |
+-----------+--------------+
5 rows in set (0.03 sec)
mysql> SHOW GRANTS FOR 'rep'@'192.168.56.%';
+---------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.56.% |
+---------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.56.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4、在Master上对数据库进行全备
mysql> FLUSH TABLES WITH READ LOCK; # 给主库加上读锁（备份结束前，该会话不能关闭，否则binlog的文件名或位置点会有变化）
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW MASTER STATUS; # 查看主库的binlog日志名称和POS值
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
********新开一个xshell窗口进行备份**********
[root@master ~]# mkdir -p /server/backup # 创建数据库备份目录
[root@master ~]# mysqldump -uroot -p123456 -A -B --events | gzip > /server/backup/rep_bak_$(date +%F).sql.gz # 全备Master的库
[root@master ~]# ls -lrt /server/backup/
total 320
-rw-r--r-- 1 root root 324968 Jul 25 20:53 rep_bak_2018-07-25.sql.gz
[root@master ~]# scp /server/backup/rep_bak_2018-07-25.sql.gz root@192.168.56.12:/tools/ # 将备份拷贝到Slave上
rep_bak_2018-07-25.sql.gz 100% 317KB 5.9MB/s 00:00
*********拷贝完成后，进行解锁**********
mysql> UNLOCK TABLES;
5、在Slave上恢复Master上的数据
[root@slave ~]# gzip -d /tools/rep_bak_2018-07-25.sql.gz
[root@slave ~]# mysql -uroot -p123456 < /tools/rep_bak_2018-07-25.sql
6、在Slave上配置master.info
[root@slave ~]# mysql -uroot -p
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.02 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.56.11', MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
7、在Slave上启动主从复制
mysql> START SLAVE; # 开启主从复制
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW SLAVE STATUS\G # 查看slave上的IO和SQL线程是否运行
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 120
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes # 表示Slave上的IO线程是否运行
Slave_SQL_Running: Yes # 表示Slave上的SQL线程是否运行
Replicate_Do_DB:
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: 120
Relay_Log_Space: 456
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的秒数，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
Master_UUID: 885e2479-89f6-11e8-a128-000c29ce31fd
Master_Info_File: /usr/local/mysql-5.6.35/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
[root@slave ~]# ll /usr/local/mysql/data/slave-relay-bin.*
-rw-rw---- 1 mysql mysql 173 Jul 25 21:25 /usr/local/mysql/data/slave-relay-bin.000001
-rw-rw---- 1 mysql mysql 383 Jul 25 21:26 /usr/local/mysql/data/slave-relay-bin.000002
-rw-rw---- 1 mysql mysql 50 Jul 25 21:25 /usr/local/mysql/data/slave-relay-bin.index
8、测试在Master上的操作是否能同步到Slave上
mysql> CREATE DATABASE repppp; # 在主上创建数据库repppp
Query OK, 1 row affected (0.02 sec)
mysql> SHOW DATABASES; # 在从上查看是否存在repppp数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| repppp |
| test |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)
9、总结
- 异步方式同步
- 逻辑同步模式（binlog有三种模式：SQL、混合、rowlevel），默认是通过SQL语句执行
- 主库通过记录binlog实现对从库的同步
- 主库1个线程（I/O线程），从库2个线程（I/O和SQL线程）来完成
- 从库的关键文件：
master.info、relay-log、relay-info - 如果从库还想级联从库，需打开
log-bin和log-slave-updates参数
10、生产场景快速配置MySQL主从复制的方案
- 安装好要配置从库的数据库，配置好
log-bin和server-id参数。 - 无需配置主库的
my.cnf文件，主库的log-bin和server-id参数默认就是配置好的。 - 登录主库增加用于从库连接的帐户，并授予
REPLICATION SLAVE权限。 - 半夜使用
mysqldump带--master-data=1参数全备主库，并在从库进行恢复。 - 在从库执行
CHANGE MASTER TO语句，无需binlog文件及对应位置点。 - 在从库开启同步开关
START SLAVE。 - 在从库
SHOW SLAVE STATUS\G，检查同步状态，并在主库进行更新测试。
11、遇到的问题
- 当执行
START SLAVE命令时，系统提示ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO，执行SHOW SLAVE STATUS;又提示Empty set (0.00 sec)
原因：slave已经默认开启，要先关闭再开启。执行STOP SLAVE;后再执行CHANGE MASTER TO语句。
- 出现错误提示
Slave_IO_Running: NO，mysql的error日志中信息：Slave I/O: error connecting to master 'replication@172.16.0.100:3306' - retry-time: 60 retries: 8640.
解决方法：
- 在主mysql中创建slave远程连接访问时候的登录密码一定要设置正确。
- 停止slave上的mysqld服务，从服务器上删除掉所有的二进制日志文件，包括数据目录下的
master.info文件和以hostname-relay-bin开头的文件，然后启动slave上的mysqld服务。
slave上Slave_SQL_Running: No，提示某个表定义不正确
解决方法： 清空并删除master和slave上的已存在所有表，然后从master库上导入新的数据即可。
