MySQL主从复制之异步复制
MYSQL主从复制方式有默认的复制方式异步复制,5.5版本之后半同步复制,5.6版本之后新增GTID复制,包括5.7版本的多源复制。
MYSQL版本:5.7.20
操作系统版本:linux 6.7 64bit
1、异步复制
MYSQL 默认的复制方式,就是主库写入binlog日志后即可成功返回客户端,无须等待binlog日志传递给从库的过程。但这样一旦主库发生宕机,就有可能出现数据丢失的情况。
1.1搭建异步主从
1、 server-id 不一样
2、 开启binlog,建议开启log_slave_updates,让从库也写binlog,方便后期扩展架构
3、 binlog格式为row。
主库操作:
创建主从复制账号
create user 'rep'@'192.16.20.%' identified by 'mysql';
grant replication slave on *.* to 'rep'@'192.16.20.%';
初始化:
mysqldump -S /tmp/mysql3307.sock --single-transaction -uroot -pmysql --master-data=2 -A > salve.sql (我全库导入的有问题,只用的test库)
注意:必须加参数 –master-data=2,让备份出来的文件中记录备份这一刻binlog文件与position号,为搭建主从环境做准备。查看备份文件中记录的当前binlog文件和position号。
scp salve.sql 172.16.20.21:/binlogbak
mysql -S /tmp/mysql3307.sock -uroot -pmysql < slave_test.sql
在数据库命令行执行配置主从命令。
change master to
master_host='172.16.20.32',
master_user='rep',
master_password='mysql',
master_port=3307,
master_log_file='mysql-binlog.000010',
MASTER_LOG_POS=797;
start slave;
stop slave;
reset slave all; 清空从库的所有配置信息。
start slave;
当前从库I/O和SQL thread都是呈现Yes状态,代表从库上面操作已经完成了。
Master_Log_File= Relay_Master_Log_File;
Read_Master_Log_Pos= Exec_Master_Log_Pos
证明目前没有主从延迟状态。
Slave_IO_Running:从库上I/O thread 负责请求和接收主库传递来的binlog信息。
Slave_SQL_Running:从库上SQL thread负责应用relay中的binlog的信息。
1.2主从复制故障处理
1 、主从故障之主键冲突,错误代码为1062
原因:由于误操作,从从库上执行写操作,导致再在主库上执行相同的操作,由于主键冲突,主从复制状态会报错。所以生产环境建议在从库上开启read only,避免在从库执行写操作。
在主库上建表t1;
现在从库插入数据,后面再在主库上插入相同的语句。
主库:
从库:
主库执行相同语句:
从库报错如下:
报错代码:
Last_Errno: 1062
处理办法:
利用percona-toolkit 工具:
mount /dev/sr0 /mnt
yum -y install perl-DBD-MySQL
./pt-slave-restart -S /tmp/mysql3307.sock -uroot -pmysql
在查看主从状态,已经恢复正常:
2 、主从故障之主库更新数据,从库找不到而报错,错误代码为10032
上一个错误是主从都有相同的数据,我们可以直接通过percona-toolkit工具跳过错误。但如果从库上少数据,就不能跳过错误了,需要找到缺少的数据,在从库上从新执行一遍。
故障原因:由于误操作,在从库上执行delete 删除操作,导致主从数据不一致。这时再在主库执行同条数据的更新操作,由于从库没有该数据,SQL无法再从库上实现。
模拟故障:
先在从库服务器的test库下的t表中,执行delete删除语句操作。
再在主库上执行:相同数据的update更新操作。
从库报错如下:
报错代码1032。
解决办法:
根据报错信息所知道Binlog文件和position(7153)号,在主库上,通过mysqlbinlog 命令,找到在主库上执行的那条SQL语句导致的主从报错。
/usr/local/mysql5.7/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /mydata/mysql/mysql3307/logs/mysql-binlog.000010 |grep -A 10 7153
insert into t2 values(1,'bbb');
生产上如果丢失数以万计条的数据,建议重新搭建主从确保数据一致性。
从库跳过错误:
./pt-slave-restart -S /tmp/mysql3307.sock -uroot -pmysql
重新同步成功:
show slave status\G;
1.3主从故障之主从server-id一致
由于粗心,安装的时候用模板并没有修改servier-id。(修改从库server-id成不同的值)
1.4主从故障之跨库操作,丢失数据
原因:在主库中设置binlog-do-db参数,使用的binlog记录格式为statement模式,导致在主库上执行跨库操作时,从库没有复制成功,丢失数据。
故障操作描述:
在主库的参数文件中添加binlog-do-db=test,代表只复制zs这个库,并且主库binlog_format 设置为statement。