MySQL数据库
SQLyogEnt.exe
登陆查数据库 mysql> select * from abc.stad; +----+-----------+ | id | name | +----+-----------+ | 1 | abc1 | | 2 | abc2 | +----+-----------+
0点全量备份 [root@M ~]# mkdir /opt/backup [root@M ~]# mysqldump -uroot -p123456 -F -B --master-data=2 abc|gzip >/opt/backup/quanbei_$(date +%F).sql.gz [root@M ~]# ll /opt/backup/quanbei_2018-11-20.sql.gz -rw-r--r--. 1 root root 942 Nov 20 11:08 /opt/backup/quanbei_2018-11-20.sql.gz
写入数据 mysql> insert into abc.stad(name) values('abc123'); mysql> insert into abc.stad(name) values('abc134'); mysql> select * from stad; +----+-----------+ | id | name | +----+-----------+ | 1 | abc1 | | 2 | abc2 | | 3 | abc123 | | 4 | abc134 | +----+-----------+
模拟误操作，把库删除 mysql> drop database abc;
发现故障，排查问题检查全备份 [root@M ~]# ll /opt/backup/quanbei_2018-11-20.sql.gz -rw-r--r--. 1 root root 942 Nov 20 11:08 /opt/backup/quanbei_2018-11-20.sql.gz [root@M ~]# cd /opt/backup/ [root@M backup]# gzip -d quanbei_2018-11-20.sql.gz [root@M backup]# ll -rw-r--r--. 1 root root 2776 Nov 20 11:08 quanbei_2018-11-20.sql [root@M backup]# grep -i 'CHANGE' quanbei_2018-11-20.sql #检查mysqlbinlog确认 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=120;
检查增量的binlog [root@M backup]# ll /var/lib/mysql/mysql-bin.000017 -rw-rw----. 1 mysql mysql 789 Nov 20 11:13 /var/lib/mysql/mysql-bin.000017
刷新binlog，可以确认恢复的目标，只需要回复mysql-bin.000017即可！ [root@M ~]# mysqladmin -uroot -p123456 flush-logs; [root@M backup]# ll /var/lib/mysql/ -rw-rw----. 1 mysql mysql 656137 Nov 20 11:06 mysql-bin.000015 -rw-rw----. 1 mysql mysql 167 Nov 20 11:08 mysql-bin.000016 -rw-rw----. 1 mysql mysql 836 Nov 20 11:22 mysql-bin.000017 -rw-rw----. 1 mysql mysql 120 Nov 20 11:22 mysql-bin.000018
把binlog拷贝至其他地方，方便操作，保留binlog [root@M backup]# cp /var/lib/mysql/mysql-bin.000017 /opt/backup/ [root@M backup]# ll /opt/backup/ total 8 -rw-r-----. 1 root root 836 Nov 20 11:24 mysql-bin.000017 -rw-r--r--. 1 root root 2776 Nov 20 11:08 quanbei_2018-11-20.sql
整理汇总binlog，删除不需要的sql语句 [root@M backup]# mysqlbinlog /var/lib/mysql/mysql-bin.000017 >bin.sql [root@M backup]# vim bin.sql drop database abc #把这一行的数据删除，不然就白恢复了
查看数据是否恢复成功！ mysql> select * from abc.stad; +----+-----------+ | id | name | +----+-----------+ | 1 | abc1 | | 2 | abc2 | | 3 | abc123 | | 4 | abc134 | +----+-----------+
确认完毕，恢复成功！！！！！！