mysql 主从故障恢复处理
端午假期期间主库发生了故障,在处理故障过程中忘记“slave stop”就马上对主库mysql进行了restart操作。操作完成之后主库故障恢复,但从库出现Slave_SQL_Running: No故障,以下记录主从故障恢复过程。
处理过程1:简单处理尝试
指定跳过错误(可多连续几次执行)
slave stop;
set global sql_slave_skip_counter=1;
slave start;
show slave status \G;
在次 show slave status \G; 查看依然无效果,可结合mysql_error.log日志进行分析。
处理过程2:先暴力恢复从库
在主库中查看master状态,并将Position复制
mysql> show master status;
+----------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+-----------+--------------+------------------+
| log-bin.000034 | 476287664 | | |
+----------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
停止从库并将Position更改为主库最新的值, 这样做能够让从库同步暂时恢复,但会跳过一部分数据造成主从数据不一致情况。称这段时间马上将业务的从库查询更改成主库或另一个从库。
slave stop;
change master to master_host='10.199.99.56',master_user='mysync',master_password='?????',master_log_file='log-bin.000034', master_log_pos=476287664;
slave start;
show slave status \G;
处理过程3:拉取主库备份数据重新创建从库
通常在做从库的时候需要将主库锁表或停止写操作,此时会对业务造成一定影响。采用Xtrabackup做备份能够避免该类问题,能够快速备份和快速恢复mysql数据库。 参考文章: http://www.tuicool.com/articles/7jURfq
先安装Xtrabackup
主库:需要先安装mysql客户端
yum install mysql -y
yum install perl-Time-HiRes* -y
yum install perl-DBD-MySQL -y
wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/RPM/rhel6/x86_64/percona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm
rpm -ivh percona-xtrabackup-2.1.9-744.rhel6.x86_64.rpm
对主库进行备份
主库:用innobackupex命令创建一个备份,默认备份生成在 /data/mysql_backup/2016-06-12_12-59-15/ 目录下
innobackupex --defaults-file=/data/mysql_data/my.cnf --user root --password ????? /data/mysql_backup/
用备份重新制作从库
从库:将备份文件拷贝至从库中(如果是压缩包的话记得解压时加上 tar -i参数)
mkdir -p /data/mysqlbak_pullmaster/2016-06-12_12-59-15
scp -r root@10.199.99.56:/data/mysql_backup/2016-06-12_12-59-15 /data/mysqlbak_pullmaster/2016-06-12_12-59-15
scp -r root@10.199.99.56:/data/mysql_data/my.cnf /data/mysqlbak_pullmaster/2016-06-12_12-59-15/my.cnf.master
从库:停止mysql、备份my.cnf文件,清空mysql_data
mysql> slave stop;
cp /data/mysql_data/my.cnf /data/mysqlbak_pullmaster/my.cnf
service mysqld stop
rm -rf /data/mysql_data/*
从库:innobackupex导入备份 (注意:my.cnf用主库配置,但要注意从的datadir路径是否和主一致)
innobackupex-1.5.1 --defaults-file=/data/mysqlbak_pullmaster/2016-06-12_12-59-15/my.cnf.master --use-memory=4G --apply-log --redo-only /data/mysqlbak_pullmaster
innobackupex-1.5.1 --defaults-file=/data/mysqlbak_pullmaster/2016-06-12_12-59-15/my.cnf.master --copy-back /data/mysqlbak_pullmaster
重新启动mysql
cp /data/mysqlbak_pullmaster/my.cnf /data/mysql_data/my.cnf
chown -R mysql.mysql /data/mysql_data/
service mysqld start
从库:查看xtrabackup_binlog_info文件中的File&Position
cat /data/mysqlbak_pullmaster/2016-06-12_12-59-15/xtrabackup_binlog_info
从库:根据上面查看到的File&Position制作从库
mysql> change master to master_host='10.199.99.56',master_user='mysync',master_password='?????',master_log_file='log-bin.000034', master_log_pos=470327275;
mysql> slave start;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.199.99.56
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: log-bin.000034
Read_Master_Log_Pos: 478661622
Relay_Log_File: szwg-m91-db-webmedia02-relay-bin.000002
Relay_Log_Pos: 8334596
Relay_Master_Log_File: log-bin.000034
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
从库恢复
从库恢复,将业务查询操作切回从库
innobackupex工具安装
yum install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.* package -y
wget ftp://xiaocai:*****@10.79.**.50/xtrabackup-1.6.7-356.rhel6.x86_64.rpm
yum install xtrabackup-1.6.7-356.rhel6.x86_64.rpm
innobackupex-1.5.1 -v
#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#rpm -ivh xtrabackup-1.6.7-356.rhel6.x86_64.rpm