MySQL系列（六）：MySQL主从复制高级应用

废权 • 2018-05-18 • 数据库

回顾

mysql主从同步配置步骤

1）准备两台数据库环境，或者单台多实例环境，能否正常启动和登陆

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命令行备份或导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据量很大，并允许停机，可以停机打包，而不用mysqldump

6）解锁主库，unlock tables;

7）把主库导出的原有数据恢复到从库

8）根据主库的show master status查看binlog的位置状态，在从库执行change master to...语句

9）从库开启同步开关，start slave

10）从库show slave status\G ,检查同步状态，并在主库进行更新测试。

mysql主从复制原理要点

1）异步方式同步。

2）逻辑同步模式，多种模式，默认是通过SQL语句执行

3）主库通过记录binlog实现对从库的同步。binlog记录数据库的更新语句。

4）主库1个IO线程，从库由1个IO线程和一个SQL线程来完成的

5）从库关键文件master.info,relay-log,relay-info功能

6）如果从库还想级联从库，需要打开log-bin和log-slave-updates参数。

生产场景快速配置mysql主从复制方案

1）安装好要配置从库的数据库，配置好log-bin和server-id参数

2）无需配置主库my.cnf文件，主库的log-bin和server-id参数默认就是配置好的

3）登陆主库增加用于从库连接主库同步的账号例如：rep，并授权replication slave同步的权限

4）使用半夜mysqldump带--master-data=1备份的全备数据恢复到从库。

5）在从库执行change master to...语句，无需binlog文件及对应位置点。

6）从库开启同步开关，start slave。

7）从库show slave status\G ,检查同步状态，并在主库进行更新测试。

生产环境主从库同步配置注意事项

第一次做从库如何做？

1）申请设备资源，用来做从库服务器

2）撰写方案文档和实施步骤。

假如你的服务器只有主库，而且已经跑了生产线上应用了，现在由于业务需要第一次做从库，
此时可能需要和公司申请停机维护时间（要确认这个时间段内可以做一次全备），集在用户访问量最小，且不影响内部其他业务运转的时间点来停机（包括锁表）配置主从复制，一般都是凌晨进行。
注意：停机（锁表，停库）的最小时间段，为锁表备份的时间，也就是说无需等待主从配置好。
当然也可以不申请停机时间，在定时任务备份时，每天的夜里的定时备份时做一些措施即可实现。

无须熬夜在工作时间轻松配置从库

全备及全备过程中binlog位置的信息或者直接用--master-data参数解决。

1）一键做主从复制脚本

1、锁表备份全备一份

2、锁表前后取得showmaster status值记录日志里这样就可以在白天从容的实现主从同步了。这个脚本还是很容易实现的，如：

脚本方法1：

#!/bin/sh
MYUSER=root
MYPASS="yjscloud"
MYSOCK=/application/mysql/tmp/mysql.sock
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=${DATA_PATH}/mysqllogs_'date +%F'.log
DATA_FILE=${DATA_PATH}/mysql_backup_'date +%F'.sql.gz
MYSQL_PATH=/application/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"
MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B --single-transaction -e"
cat |$MYSQL_CMD<>$LOG_FILE;
system $MYSQL_CMD -e "show master status"|tail -1 >>$LOG_FILE;
system ${MYSQL_DUMP} | gzip > $DATA_FILE;
EOF
$MYSQL_CMD -e "unlock tables;"

脚本方法2：

#!/bin/sh
MYUSER=root
MYPASS="yjscloud"
MYSOCK=/application/mysql/tmp/mysql.sock
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=${DATA_PATH}/mysqllogs_'date +%F'.log
DATA_FILE=${DATA_PATH}/mysql_backup_'date +%F'.sql.gz
MYSQL_PATH=/application/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"
MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B --master-data=1 --single-transaction -e"
${MYSQL_DUMP} | gzip > $DATA_FILE;

有了mysql_backup_xxxx-xx-xx.sql.gz和mysqllogs_xxxx-xx-xx.log内容做从库就不远了

不停主库一键批量创建从库

一键创建从库方法1

把上面备份的mysql_backup_xxxx-xx-xx.sql.gz发布到想做从库同步的机器上，可以做多台。
然后再制造如下批量做从库脚本，一键完成多台机器从库的制作

#!/bin/sh
MYUSER=root
MYPASS="yjscloud"
MYSOCK=/application/mysql/tmp/mysql.sock
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=${DATA_PATH}/mysqllogs_'date +%F'.log
DATA_FILE=${DATA_PATH}/mysql_backup_'date +%F'.sql.gz
MYSQL_PATH=/application/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"

#recover
cd ${DATA_PATH}
gzip -d mysql_backup_'date +%F'.sql.gz
$MYSQL_CMD <  mysql_backup_'date +%F'.sql

#config slave
cat |$MYSQL_CMD<$LOG_FILE
mail -s "mysql slave result" yjscloud@gmail.com < $LOG_FILE

特别提醒：

此脚本中change master参数要根据mysqllog_xxxx-xx-xx.log修改

一键创建从库方法2

#!/bin/sh
MYUSER=root
MYPASS="yjscloud"
MYSOCK=/application/mysql/tmp/mysql.sock
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=${DATA_PATH}/mysqllogs_'date +%F'.log
DATA_FILE=${DATA_PATH}/mysql_backup_'date +%F'.sql.gz
MYSQL_PATH=/application/mysql/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK"

#recover
cd ${DATA_PATH}
gzip -d mysql_backup_'date +%F'.sql.gz
$MYSQL_CMD <  mysql_backup_'date +%F'.sql

#config slave
cat |$MYSQL_CMD<$LOG_FILE
mail -s "mysql slave result" yjscloud@gmail.com < $LOG_FILE

相关mysql主从复制技术技巧概览

登陆数据库查看mysql线程同步状态

主库：

m-116

从库：

m-117

复制主线程状态

下面列出了主服务器的Binlog Dump线程的state列最常见的状态。如果你没有在主服务器上看见任何binlog dump线程，这说明复制没有在运行，即目前没有连接任何从服务器。

Sending binlog events to slave
(二进制日志由各种事件组成，一个事件通常为一个跟新加一些其它信息，线程已经从二进制日志读取了一个事件并且正将它发送到从服务器)

Finished reading one binlog;switchingto next binlog
(线程已经读完二进制日志文件并且正打开下一个发送到服务器的日志文件)

Has sent all binlog to slave;waitingfor binlog to be updated
(线程已经从二进制日志读取所有主要的跟新并已经发送到从服务器线程正空闲，等待由主服务器上新的跟新导致的出现在二进制日志中新事件)

Waiting to finalize termination
(线程停止时发生一个很简单的状态。)

复制从I/O线程状态

下面列出了从服务器的I/O线程的state列的最常见的状态。该状态也出现在Slave_IO_State列，由SHOW SLAVE STATUS显示，这说明你可以通过该语句仔细浏览所发生的事情

Connecting to master(线程正试图连接主服务器)

Checking master version(建立同主服务器之间的连接后立即临时出现的状态)

Registering slave on master(建立同主服务器之间的连接后立即临时出现的状态)

Requesting binlog dump(建立同主服务器之间的连接后立即临时出现的状态。线程向主服务器发送索取从请求的二进制日志文件名和位置开始的二进制日志的内容)

Waiting to reconnect after a failed binlog dump request(如果二进制日志转储请求失败[由于没有连接]，线程进入睡眠状态，然新连接，可以使用--master-connect-retry 选项指定重试之间的间隔)

Reconnecting after a failed binlog dump request
(线程正尝试重新连接主服务器)

Waiting for master to send event
(线程已经连接上主服务器，正等待二进制日志事件到达，如果主服务器正空闲，会持续较长的时间。如果等待继续slave_read_timeout秒，则发生超时，此时线程认为连接被中断并企图重新连接。)

Queueing master event to the relay log
(线程已经读取一个事件，正将它复制到中继日志供SQL线程来处理)

Waiting to reconnect after a failed master event read
(读取时（由于没有连接）出现错误。线程企图重新连接前将睡眠master-connect-retry秒。)

Reconnecting after a failed master event read
(线程正尝试重新连接主服务器。当连接重新建立后，状态变为Waiting for master to send event)

Waiting for the slave SQL thread to free enough relay log space
正使用一个非零relay_log_space_limit值，中继日志已经增长到其组合大小超过该值。I/O线程正(等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间。)

Waiting for slave mutex on exit
(线程停止时发生的一个很简单的状态。)

复制从SQL线程状态

下面列出了从服务器的SQL线程的State列的最常见的状态。

Reading event from the relay log
(线程已经从中继日志读取一个事件，可以对事件进行处理了)

Has read all relay log; waiting for the slave I/0 thread to update it
(线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志；)

Waiting for slave mutex on exit
(线程停止发生的一个简单的状态I/O线程的state列也可以显示语句的文本。这说明线程已经从中继日志读取了一个事件。从中提取了语句，并且正在执行语句。)

查看mysql线程同步状态的用途

通过mysql线程同步状态查看数据库同步时是否完成，用于主库宕机或者人工数据库主从切换迁移等。
主库宕机选择最快的从库提升为主，就需要查看，当然也可以利用mysql的半同步功能，选择固定库的提升为主。

生产场景mysql主从复制读写分离授权方案实战

当配置好mysql主从复制后，所有对数据库内容的更新就必须在主服务器上进行。因为数据复制时单向的，只有在主库上更新，才能避免用户对主服务器上数据库内容的更新与从库上数据库内容的更新一致，而不发生冲突。

生产mysql复制环境用户授权方案

假设为博客数据库授权，大家可以做下面的授权

1、生产环境主库用户授权：

grant select,insert,update,delete on 'blog' .* to 'blog'@'192.168.0.%' identified by 'yjscloud';

提示：特殊业务可能权限会略多，如果业务安全性要求不高，也可以all privileges权限

2、生产环境从库用户的授权：

grant select,insert,update,delete on 'blog' .* to 'blog'@'192.168.0.%' identified by 'yjscloud';
revoke insert ,update,delete on blog.* from 'blog'@'192.168.0.%';

提示：可以结合read-only参数共同做。

3、生产授权案例说明：这里表示给192.168.0.0/24的用户blog管理数据库的所有表（*表示所有表）只读权限（select）密码为yjscloud。

grant select,insert,update,delete on 'blog' .* to 'blog'@'192.168.0.%' identified by 'yjscloud';

生产环境主从库用户授权：

主库：

grant select,insert,update,delete on 'blog' .* to 'blog'@'192.168.0.%' identified by 'yjscloud';

从库：

grant select on 'blog' .* to 'blog'@'192.168.0.%' identified by 'yjscloud';

实现上述授权方案最简单的办法是在主库配置binlog-ignore-db=mysql

通过忽略授权表的方式防止数据写从库方法及实践

方法

生产环境中一般会采取忽略授权表方式的同步，然后对从服务器（slave）上的用户仅授权select读权限。不同步mysql库，这样我们就保证主库和从库相同的用户可以授权不同的权限。忽略mysql库和information_schema库的主从同步

replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

提示：忽略记录binlog日志的参数binlog-ignore-db一般用于系统的库和表

结论：如果想要在从库上忽略mysql库的同步，方法：

1、只有在[主从]库上分别设置replicate-ignore-db = mysql才可以做到从库不同步mysql库。

2、在主库上设置binlog-ignore-db=mysql不记录有关mysql库更新binlog，来达到从库不同步mysql库

实践

修改配置文件my.cnf将如下代码复制到[mysqld]标签下

replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

修改配置文件后需要重启数据库

/etc/init.d/mysqld restart

重启的时候我遇到了一个报错ERROR! MySQL server PID file could not be found
可能进程里已经存在mysql进程

解决方法：用命令“ps -ef|grep mysqld”查看是否有mysqld进程，如果有使用“kill -9 进程号”杀死，然后重新启动mysqld！

m-118

更多解决办法请参考博客

测试

在主库

m-119

在从库

m-120

在主库创建数据库查看主从同步是否正常

m-121

在从库查看

m-122

mysql主从复制指定不同库表同步参数说明

master端：

--binlog-do-db 二进制日志记录的数据库（多数据库用逗号，隔开）
--binlog-ignore-db 二进制日志中忽略数据库 （多数据库用逗号，隔开）

以下是mysql主从忽略授权表的方法案例：

master端：

binlog-do-db=YYY 需要同步的数据库。不添加这行表示同步所有
binlog-ignore-db = mysql   这是不记录binlog，来达到从库不同步mysql库，以确保各自权限
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema

slave端：

--replication-do-db    设定需要复制的数据库（多数据库使用逗号，隔开）
--replication-ignore-db 设定需要忽略的复制数据库 （多数据库使用逗号，隔开）
--replication-do-table   设定需要复制的表
--replication-ignore-table 设定需要忽略的复制表
--replication-wild-do-table 同replication-do-table功能一样，但是可以通配符
--replication-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

互为同步配置实例：

1）A、B互为主从同步test，不同步mysql:

两个数据库配置中均配置：

binlog-do-db=test
binlog-ignore-db=mysql
replication-do-db=test
replication-ignore-db=mysql

2）A、B互为为主从同步test，不同步其他数据库，新库创建的也不会同步

两个数据库配置中均配置：

binlog-do-db=test
replication-do-db=test

3）A、B互为为主从不同步mysql，同步其他数据库，譬如新创建的数据库也会同步

binlog-ignore-db=mysql
replication-ignore-db=mysql

4）A、B互为主从同步所有数据库，包括新建的数据库:

两个数据库配置中均不设置上述四项

通过read-only参数防止数据写入从库方法

除了上面在从库仅做select的授权外，还可以在slave服务器启动选项增加参数或者在my.cnf配置文件中加read-only参数来确保从库只读，当然授权用户和read-only参数二者同时操作效果更佳，这也是生产环境中使用的方案。read-only参数可以让slave服务器只允许来自slave服务器线程或具有super权限的用户更新。可以确保slave服务器不接受来自普通用户的更新，slave服务器启动项增加--read-only也是同样功能

注意：

1、my.cnf里加入read-only后，重启服务使参数生效

2、root用户及all权限的用户可以插入，删除数据

3、具备insert,update,delete等权限的用户无法通过read-only的限制

m-123

重新启动mysql

m-124

进入从库创建一个用户

mysql> grant select,insert,update,delete on *.* to 't_user'@'localhost' identified by '123';
mysql> flush privileges;

然后用这个用户登录数据库，尝试插入数据

m-125

提示为只读，无权插入数据

模拟故障

模拟故障

MySQL从库数据冲突导致同步停止在slave创建一个库，然后master创建一个同名的库就会出现这种错误

m-126

解决办法一：

stop slave;
set global sql_slave_skip_counter =1;
start slave;

1）对于普通的互联网业务，忽略问题不是很大，当然，要确定不影响公司业务的前题下

2）企业场景解决主从同步，主从不一致对当前业务更重要，然后如果主从数据一致也很重要，在找个时间恢复一下这个从库。

这样slave就会和master去同步，主要关键点：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0   #0表示已经同步状态

提示：set global sql_slave_skip_counter =n; #取值>0忽略执行N个更新。

方法二：根据错误号跳过指定的错误

slave-skip-errors = 1032,1062,1007   #一般由于入库重复导致的失败就可以忽略

对于错误代码，应使用slave服务器错误日志中错误消息提供的编码和show slave status的输出。也可以使用不推荐的all值忽略所有错误消息，不考虑所发生的错误。无需而言，如果使用该值，我们不能保证数据的完整性。在这种情况下，如果slave服务器的数据与master服务器上的不相近请不要
抱怨，已经警告过你了

附：mysql错误代码大全

让MySQL从库记录binlog方法

需要记录binlog的情况

1、当前从库还要作为其他从库的主库

2、把从库作为备份服务器时需要开启binlog

在从库的my.cnf中加入如下参数

log-slave-updates
log-bin=mysql-bin
expire_logs_days = 7

应用场景：级联复制或从库做数据备份。A-->B-->C，B服务器要开启记录binlog日志功能

主多从宕机从库切换主继续和从同步过程详解

看两个线程的更新状态

登陆从库show processlist\G

m-127

登陆从库31,32分别查看：

cat /application/mysql/data/master.info

确保更新完毕，看看那个从库那个最快，经过测试没有延迟的情况POS差距很小，甚至是一致的

授权同步用户和主库一样

1）确保所有relay log全部更新完毕

在每个从库执行stop slave io_thread; show processlist;
直到看到Has read all relay log;表示从库更新都执行完毕；

2）登陆要提升权限的从库

stop slave;
retset master;
quit;

3）进到数据库数据目录

删除master.info relay-log.info

cd /application/mysql/data
rm -f master.info relay-log.info

检查授权表，read-only等参数

4）提升从库为主库

vi /etc/my.cnf

开启：

log-bin = mysql-bin

如果存在log-slave-updates，read-only等一并注释

重启数据库
/etc/init.d/mysqld restart

到此为止，提升主库完毕

5）如果主库服务器没有宕，需要去主库拉取binlog补全提升主库的从库

6）其他从库操作

已检查（同步user rep均存在）

登陆从库

stop slave;
change master to master_host = '192.168.0.31';   #如果不同步，就指定位置点
start slave；
show slave status\G

主库宕机切换到此完成

7）修理损坏的主库，完成后作从库使用或者切换

现在是主库意外宕机，假如我们又计划切换。怎么做？

a、主库锁表。

b、登陆所有的库查看同步状态，是否完成。

其他与前面的一样。

|| 版权声明
作者：废权
链接：https://blog.yjscloud.com/archives/72
声明：如无特别声明本文即为原创文章仅代表个人观点，版权归《废权的博客》所有，欢迎转载，转载请保留原文链接。
THE END
分享
二维码
MySQL系列（六）：MySQL主从复制高级应用
回顾 mysql主从同步配置步骤 1）准备两台数据库环境，或者单台多实例环境，能否正常启动和登陆 2）配置my.cnf文件，主库配置log-bin和server-id参数，从库配置……
<<上一篇
下一篇>>
文章目录
关闭
目 录