2019-06-07 MySQL主从复制与应用实践（2）
1. MySQL主从复制实践
1. 主从复制实践准备
1.1 主从复制数据库实践环境准备
准备两台服务器，每台机器一个独立数据库的环境。本测试环境系统为CentOS 7.5。
1.2 定义主从复制需要的服务器角色
主库及从库IP信息
1.3 检查数据库的当前状态
[root@db01 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 26774 mysql 10u IPv6 39422 0t0 TCP *:mysql (LISTEN)
[root@db02 ~]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 24170 mysql 10u IPv6 35638 0t0 TCP *:mysql (LISTEN)
根据结果可以确定数据库已处于正常启动状态。
2. 在主库Master上执行操作配置
2.1 设置server-id的值并开启binlog功能参数
1）修改主库的配置文件。执行vi /etc/my.cnf，编辑MySQL的配置文：
[mysqld]
server_id = 1 ---用于同步的每台机器或实例server_id都不能相同
log_bin
---从MySQL 5.6起，参数中改用“_”代替“-”了，但是常规情况下后面这种写法依然兼容
2）检查配置参数之后的结果：
[root@db01 ~]# egrep "server_id|log_bin" /etc/my.cnf
server_id = 1
log_bin
3）重启主库MySQL服务：
[root@db01 data]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
4）登录数据库检查参数的更改情况：
[root@db01 data]# mysql -e "show variables like 'log_bin';" ---不登入数据库检查，因为密码已经写入配置文件中
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON | ---binlog功能已开启
+---------------+-------+
[root@db01 data]# mysql -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 | ---通过检查可以得知配置的server_id为1
+---------------+-------+
2.2 在主库上建立用于主从复制的帐号
根据主从复制的原理，从库要想与主库同步，必须要有一个可以连接主库的帐号，并且这个帐号是在主库上创建的，权限是允许从库连接主库并同步数据。
1）建立用于从库复制的帐号及对应的权限：
[root@db01 data]# mysql
mysql> grant replication slave on *.* to 'rep'@'192.168.150.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.01 sec)
---replication slave为允许MySQL Slave同步的必须权限，此处不要授权all权限
---“*.*”表示所有库的所有表，也可以指定具体的库和表进行复制。例如oldboy.test中，oldboy为库，test为表
---'rep'@'192.168.150.%'中的rep为同步帐号。192.168.150.%为授权主机网段，使用了“%”表示允许整个192.168.150.0网段以rep用户访问
mysql> flush privileges; ---刷新权限使得授权的权限生效
Query OK, 0 rows affected (0.00 sec)
2）检查主库创建的rep复制帐号：
mysql> select user,host from mysql.user;
+------+---------------+
| user | host |
+------+---------------+
| root | 127.0.0.1 |
| rep | 192.168.150.% | ---已经配置好了
| root | ::1 |
| | db01 |
| root | db01 |
| | localhost |
| root | localhost |
+------+---------------+
7 rows in set (0.00 sec)
mysql> show grants for rep@'192.168.150.%'; ---查看授权权限命令
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.150.% |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.150.%' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.3 对主数据库锁表只读后进行备份
1）主数据库锁表只读（当前窗口不要关掉）：
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
默认情况下，自动解锁的市场参数值设置如下：
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+-----------------------------+----------+
12 rows in set (0.00 sec)
---只截取部分关键内容
2）锁表后查看主库的状态：
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| db01-bin.000001 | 408 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
最关键的是前两项，由上述代码可以看到当前binlog日志文件名和二进制binlog日志偏移量的位置。
注意，“show master status;”命令显示的信息需要记录在案，将后面的从库导入全备后，继续对主库进行复制时就是要从这个文件以及对应的位置开始。
3）导出主库数据
锁表后，一定要单开一个新的SSH窗口（否则锁表会失效），导出数据库中的所有数据，如果数据量很大（30GB以上），并且允许停机，则也可以停库直接打包数据文件进行迁移，那样做速度更快。
[root@db01 ~]# mkdir /server/backup -p ---创建备份目录
[root@db01 ~]# mysqldump -A -B | gzip > /server/backup/bak_$(date +%F).sql.gz
[root@db01 ~]# ll /server/backup/bak_$(date +%F).sql.gz ---检查备份
-rw-r--r--. 1 root root 165980 Jun 7 18:21 /server/backup/bak_2019-06-07.sql.gz
为了确保导出数据期间，数据库没有数据插入，导库完毕后可以再检查下主库的状态信息：
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| db01-bin.000001 | 408 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
导出数据完毕后，解锁主库，恢复可写，因为主库还要对外提供服务，因此不能一直锁定不让用户访问：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
2.4 把从主库导出的MySQL数据迁移到从库
[root@db01 data]# scp -rp /server/backup/bak_$(date +%F).sql.gz root@192.168.150.131:/opt
The authenticity of host '192.168.150.131 (192.168.150.131)' can't be established.
RSA key fingerprint is 11:ca:fd:fc:9e:00:e9:c7:83:5b:ef:5e:03:95:41:1e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.150.131' (RSA) to the list of known hosts.
root@192.168.150.131's password:
bak_2019-06-07.sql.gz 100% 162KB 162.1KB/s 00:00
3. 在MySQL从库上执行的操作过程
3.1 设置server_id的值并关闭binlog功能参数
一般来说，数据库的server_id在一套主从复制体系内是唯一的，这里从库的server_id必须与主库及其他的从库不同，并且需要注释掉从库的binlog参数配置。如果不对从库进行级联复制，并且不作为备份使用，就不要开启binlog，开启了反而会增加从库磁盘I/O的压力。
但是，如下两种情况需要打开从库的binlog功能：
- 作为形如A->B->C的级联同步，中间的B数据库，需要开启binlog
- 在从库中做数据库备份时需要开启binlog记录功能。因为数据库备份必须要有全备和binlog日志，才是完整的备份
1）修改配置文件，配置从库（192.168.150.131）的相关参数
执行vi /etc/my.cnf，编辑my.cnf配置文件，按如下两个参数对内容进行修改：
[mysqld]
server_id = 2 ---调整等号后的数值，与任何一个数据库实例都不相同
#log_bin
2）检查配置参数后的结果：
[root@db02 ~]# egrep "server_id|log_bin" /etc/my.cnf
server_id = 2
# log_bin
3）重启从数据库：
[root@db02 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
4)登录数据库检查参数的改变情况：
[root@db02 ~]# mysql -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
[root@db02 ~]# mysql -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
3.2 将从库mysqldump中导出的数据恢复到从库
在做主从复制之前，我们需要让主库与从库的数据保持一致，因此需要将主库导出的数据全恢复到从库之后再设置主从复制。
[root@db02 ~]# zcat /opt/bak_2019-06-07.sql.gz | mysql ---直接读取压缩包恢复数据，不能有报错
提示：如果备份时使用了“-A”参数，则在还原数据到其他从库时，登录的root密码也会与主库一致，因为授权表也被主库数据覆盖了。
3.3 登录从库配置复制参数
1）MySQL从库连接主库的配置信息如下：
CHANGE MASTER TO
MASTER_HOST='192.168.150.130', ---主库的IP
MASTER_PORT=3306, ---主库的端口
MASTER_USER='rep', ---主库上建立的用于复制的用户rep
MASTER_PASSWORD='oldboy123', ---rep用户的密码
MASTER_LOG_FILE='db01-bin.000001', ---show master status时查看到的二进制日志文件名称，注意不能多出空格
MASTER_LOG_POS=408; ---showmaster status时查看到的二进制日志偏移量，注意不能多出空格
配置MySQL从库连接主库信息操作的整个过程如下：
[root@db02 ~]# mysql << EOF ---这里也可以直接登录进mysql进行操作，而不使用EOF输入
> CHANGE MASTER TO
> MASTER_HOST='192.168.150.130',
> MASTER_PORT=3306,
> MASTER_USER='rep',
> MASTER_PASSWORD='oldboy123',
> MASTER_LOG_FILE='db01-bin.000001',
> MASTER_LOG_POS=408;
> EOF
注意：主库需要关闭防火墙，或者放行3306端口，不然连不上。
上述操作过程的原理实际上是把用户密码等信息写入从库新的master.info文件中
[root@db02 ~]# ll /application/mysql/data/master.info
-rw-rw----. 1 mysql mysql 93 Jun 8 15:49 /application/mysql/data/master.info
[root@db02 ~]# cat /application/mysql/data/master.info
23
db01-bin.000001
408
192.168.150.130
rep
oldboy123
3306
---省略若干---
4. 启动从库同步开关并测试主从复制
1）启动从库主从复制开关，并查看复制状态：
[root@db02 ~]# mysql -e "start slave;"
[root@db02 ~]# mysql -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.150.130
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db01-bin.000001
Read_Master_Log_Pos: 408
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 282
Relay_Master_Log_File: db01-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 408
Relay_Log_Space: 454
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
Master_UUID: 61dca574-88f8-11e9-a004-000c299a6102
Master_Info_File: /application/mysql-5.6.44/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
主从复制是否配置成功，最关键的是查看下面3项状态参数：
[root@db02 ~]# mysql -e "show slave status\G" | egrep "IO_Running|SQL_Running:|_Behind_Master"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
- Slave_IO_Running：Yes，这个表示I/O的线程状态，I/O的线程状态，I/O线程负责从主库中读取Binlog日志，并将Binlog日志写入从库的中继日志中，状态为Yes表示I/O线程工作正常，否则异常。
- Slave_SQL_Running：Yes，这个表示SQL的线程状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库中，状态为Yes表示I/O线程工作正常，否则异常。
- Seconds_Behind_Master: 0，这个表示在复制过程中，从库比主库延迟的秒数，这个参数很重要，但企业里有更准确地判断主从延迟的方法：在主库中写时间戳，然后通过从库读取时间戳，与当前数据库时间进行比较，从而认定是否真的延迟。
2）测试主从复制结果。在主库中写入数据，然后观察从库的数据状况：
[root@db01 ~]# mysql -e "create database alex_python;" ---在主库上操作
[root@db02 ~]# mysql -e "show databases like 'alex%';" ---在从库上操作
+------------------+
| Database (alex%) |
+------------------+
| alex_python | ---从库上也创建了，说明是同步的
+------------------+
5. MySQL主从复制问题汇总
问题1：主库“show master status;”没有返回状态结果
mysql > show master status;
Empty set (0.00 sec)
解答：上述问题的原因是主库binlog功能开关没开或是没生效。
问题2：出现错误信息“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: Could not find first log file name in binary log index file'”。
解答：上面故障的原因是执行CHANGE MASTER命令时某一个参数的值多出了空格，因而产生了错误：
CHANGE MASTER TO
MASTER_HOST='192.168.150.130',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123',
MASTER_LOG_FILE=' db01-bin.000001 ', ---db01-bin.000001 文件两端不能有空格
MASTER_LOG_POS=408;
问题3：MySQL服务无法启动。
故障语句如下：
[root@db01 ~]# /etc/init.d/mysqld start
ERROR! MySQL server PID file could not be found!
解决：上述问题的原因有很多种可能，具体如下。
1）数据库目录权限和用户属主问题，数据库目录使用mysql用户
2）my.cnf配置出错，需要调整my.cnf中有问题的配置
3）数据库初始化时有问题，需要重新初始化数据库
6. MySQL主从复制配置步骤小结
1）准备两台数据库环境，关闭Selinux和Iptables防火墙，确定其能正常启动和登录。
2）配置my.cnf文件：主库配置log_bin和server_id参数；从库配置server_id参数，该值不能像主库及其他从库一样，从库一般不开启log_bin功能。配置参数后需要重启才能生效。
3）登录主库增加用于同步的账户，例如rep，并授权replication slave同步的权限。
4）登录主库，整库锁表flush table with read lock（窗口关闭后即失效，超时参数设置的时间到了，锁表也会消失），然后show master status查看binlog的位置状态。
5）新开窗口，在Linux命令行备份导出原有的数据库数据，并复制到从库所在的服务器目录。如果数据库数据量很大，则使用热备工具Xtrabackup或停机打包，而不用mysqldump。
6）导出主库中数据之后，即刻执行unlock tables解锁主库。
7）把从库中导出的数据恢复到从库。
8）根据从库中导出的show master status查看到的binlog的位置状态，在从库中执行“change master to...”语句。
9）从库开启复制开关，即执行“start slave;”。
10）从库“show slave status\G”，检查同步状态（两个Yes和一个延迟秒数是否为0），并在主库中进行更新，在从库中检查测试复制是否成功。
7. MySQL主从复制线程状态说明及用途
7.1 MySQL主从复制主库I/O线程状态说明
1）登录主数据库查看MySQL线程的同步状态：
mysql> show processlist\G ---查看主库所有线程状态
*************************** 1. row ***************************
Id: 9
User: rep
Host: 192.168.150.131:49325
db: NULL
Command: Binlog Dump ---用于复制的主库线程
Time: 9247
State: Master has sent all binlog to slave; waiting for binlog to be updated ---当前状态
Info: NULL
*************************** 2. row ***************************
Id: 12
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
2 rows in set (0.00 sec)
提示：上述状态的意思是线程已经从binlog日志中读取到了所有的更新，并已经将更新发送到了从数据库服务器。线程现在为空闲状态，等待主服务器上二进制日志中的新事件更新。
下表列出了主服务器的binlog Dump线程的State列的最常见状态。如果没有在主服务器上看见任何binlog Dump线程，则说明复制没有运行，二进制binlog日志由各种事件组成，一个事件通常会为一个更新添加一些其他的信息。
主库I/O线程工作状态
2）登录从数据库查看MySQL线程的工作状态，从库有两个线程，即I/O和SQL线程。
下面是从库I/O线程的状态
mysql> show processlist\G
*************************** 1. row ***************************
Id: 12
User: system user
Host:
db: NULL
Command: Connect
Time: 10553
State: Waiting for master to send event
Info: NULL
下表列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列，由SHOW SLAVE STATUS显示。
从库I/O线程的工作状态
下面是从库SQL线程的状态：
*************************** 2. row ***************************
Id: 13
User: system user
Host:
db: NULL
Command: Connect
Time: 24673
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
下表列出了从服务器的SQL线程其State列的最常见的状态
从库SQL线程状态及说明
7.2 查看MySQL线程同步状态的用途
通过MySQL线程同步状态可以看到同步是否正常进行，故障的位置是什么，另外还可以查看数据库同步是否完成，可用于主库宕机切换数据库或者人工数据库主从切换迁移等。
例如，主库宕机，要选择最快的从库将其提升为主库，就需要查看主从库的线程状态，如果是主从复制在正常情况下进行角色切换，也需要查看主从库的线程状态，根据复制状态确定数据库更新是否完成。
8. 生产场景中部署MySQL主从复制方案
8.1 快速配置MySQL主从复制
快速配置MySQL主从复制的步骤具体如下：
1）安装好要配置的从库的数据库，配置好log_bin和server_id参数
2）无须配置主库my.cnf文件，主库的log_bin和server_id参数默认就是配置好的
3）登录主库，增加从库连接主库同步的账户，例如rep，并授权replication slave同步的权限
4）使用曾经在半夜通过mysqldump带“-x”和“--master-data=1”的命令及参数定时备份的全备数据备份文件，把它恢复到从库
5）在从库中执行“change master to...”语句，无须binlog文件及对应位置点
6）从库开启同步开关，start slave
7）在从库中执行“show slave status\G”，检查同步状态，并在主库中进行更新测试
8.2 无须熬夜，轻松部署MySQL主从复制
1）在主库上通过定时任务使其半夜执行如下所示的命令，备份并导出主库数据：
mysqldump -A -B -x --master-data=1 | gzip > /opt/bak1_$(date +%F).sql.gz
“--master-data=1”参数会在备份数据里增加如下语句：
-- Position to start replication or point-in-time recovery from
CHANGE MASTER TO MASTER_LOG_FILE='oldboy-bin.000024', MASTER_LOG_POS=107;
2）白天找机会在需要做复制的从库上导入全备
zcat /opt/bak1_$(date +%F).sql.gz | mysql
mysql << EOF
CHANGE MASTER TO
MASTER_HOST='192.168.150.130',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123';
EOF
---这里忽略了两个参数MASTER_LOG_FILE和MASTER_LOG_POS，因为在备份的时候使用了“--master-data=1”，在导入数据库时，这两个参数已经自动设置好了。
最后在从库上开启复制开关，检验成果：
start slave;
show slave status\G
2. MySQL主从复制在企业中的故障案例
工作中MySQL从库停止复制的故障案例
下面进行模拟操作，先在从库中创建一个库，然后在主库中创建同名的库来模拟数据冲突。
show slave status\G
Slave_IO_Running：Yes
Slave_SQL_Running：NO
Seconds_Behind_Master：NULL
Last_Error：Error 'Can't create database 'xiaoliu'; database
exists' on query.Default database: 'xiaoliu'. Query: 'create database xiaoliu'
对于该冲突，解决方法1为：
stop slave; ---临时停止同步开关
set global sql_slave_skip_counter = 1; ---将同步指针向下移动一个，如果多次不同步，则可以重复操作
start slave; ---开启同步开关
对于普通的互联网业务，上述移动指针的命令操作所带来的问题不是很大。当然，要在确认不影响公司业务的前提下进行上述操作。
若是在企业场景下，对当前业务来说，解决主从同步问题比主从数据不一致问题更重要，如果主从数据一致也是很重要的，那么就再找个时间来恢复下这个从库。
主从数据不一致更重要还是保持主从同步持续状态更重要，则要根据具体业务进行选择。
这样Slave就会和Master同步了，主要关键点为：
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
Seconds_Behind_Master是否为0，0表示状态已经同步
提示：set global sql_slave_skip_counter=n; n取值大于0，表示忽略执行N个更新。
解决方法2：根据可以忽略的错误号事先在配置文件中进行配置，跳过指定的不能影响业务数据的错误，例如：
[root@MySQL ~]# grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032,1062,1007
提示：类似由于入库重复所导致的失败可以忽略，其他情况下是不是可以忽略则需要根据不同公司的具体业务来进行评估。
其他可能引起复制故障的问题具体如下：
- MySQL自身的原因及人为重复插入数据
- 不同的数据库版本会引起不同步，低版本到高版本可以，但是高版本不能往低版本同步
- MySQL的运行错误或者程序BUG
- binlog记录模式，例如row level模式就比默认的语句模式要好
