MysqL差异备份与恢复
binlog :日志,记录了所有的写操作
1.开启mysql服务器的二进制日志功能
[root@clq ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
#skip-grant-tables
server-id=1 #服务id
log-bin=mysql-bin #日志服务
2.数据存放的位置/opt/data(二进制安装方式,其它安装路径不同)差异备份实现之前要先进行全量备份再进行差异备份,如下加数据:
#先进行全量备份(二进制日志方式)
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-$(date "+%Y%m%d").sql
[root@clq ~]# ll
-rw-r--r--. 1 root root 876049 8月 26 09:40 all-20210826.sql
1.查看正在用的日志文件
[root@clq data]# cat mysql-bin.index
./mysql-bin.000001 #误删之前的日志文件信息
2.查看mysql的数据表信息
mysql> select * from student02;
+----+------+------+-------+
| id | name | age | score |
+----+------+------+-------+
| 5 | tom | NULL | 80 |
| 6 | xixi | NULL | 89.5 |
| 7 | haha | NULL | 96 |
| 8 | oppo | NULL | 98 |
| 9 | A | 1 | 99 |
| 10 | B | 2 | 99 |
| 11 | C | 1 | 99 |
| 12 | D | 2 | 99 |
+----+------+------+-------+
8 rows in set (0.00 sec)
3.实现差异备份之前加点数据到表中(差异备份的数据)
mysql> select * from student02;
+----+------+------+-------+
| id | name | age | score |
+----+------+------+-------+
| 5 | tom | NULL | 80 |
| 6 | xixi | NULL | 89.5 |
| 7 | haha | NULL | 96 |
| 8 | oppo | NULL | 98 |
| 9 | A | 1 | 99 |
| 10 | B | 2 | 99 |
| 11 | C | 1 | 99 |
| 12 | D | 2 | 99 |
| 13 | E | 1 | 99 |
| 14 | F | 2 | 99 |
| 15 | G | 1 | 99 |
| 16 | H | 2 | 99 |
+----+------+------+-------+
3.进行误删(切记生产环境不要做)
[root@clq data]# mysql -uroot -p -e 'drop database clq;'
[root@clq data]# mysql -uroot -p -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4.刷新创建新的二进制日志
[root@clq ~]# mysqladmin -uroot -p flush-logs
[root@clq data]# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002 #刷新产生的日志=误删日志标识
5.恢复之前完全备份的数据
[root@clq ~]# mysql -uroot -p < all-20210506.sql
#查看数据
mysql> select * from student02;
+----+------+------+-------+
| id | name | age | score |
+----+------+------+-------+
| 5 | tom | NULL | 80 |
| 6 | xixi | NULL | 89.5 |
| 7 | haha | NULL | 96 |
| 8 | oppo | NULL | 98 |
| 9 | A | 1 | 99 |
| 10 | B | 2 | 99 |
| 11 | C | 1 | 99 |
| 12 | D | 2 | 99 |
+----+------+------+-------+
8 rows in set (0.00 sec)
6.查看到误删前一刻的日志信息
mysql> show binlog events in 'mysql_bin.000001';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
| mysql-bin.000001 | 972 | Xid | 1 | 1003 | COMMIT /* xid=446 */ |
| mysql-bin.000001 | 1003 | Anonymous_Gtid | 1 | 1068 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1068 | Query (<--要恢复的日志号) | 1 | 1157 | drop database clq(误删时的日志信息) |
| mysql-bin.000001 | 1157 | Rotate | 1 | 1204 | mysql-bin.000002;pos=4
7通过日志号恢复之前的增量的数据(增量备份恢复)
开始位置–start-position=# 暂停位置–stop-position=#
[root@clq ~]#mysqlbinlog --stop-position=1068(日志号) /opt/data/mysql_bin.000001 |mysql -uroot -p
+----+------+------+-------+
| id | name | age | score |
+----+------+------+-------+
| 5 | tom | NULL | 80 |
| 6 | xixi | NULL | 89.5 |
| 7 | haha | NULL | 96 |
| 8 | oppo | NULL | 98 |
| 9 | A | 1 | 99 |
| 10 | B | 2 | 99 |
| 11 | C | 1 | 99 |
| 12 | D | 2 | 99 |
| 13 | E | 1 | 99 |
| 14 | F | 2 | 99 |
| 15 | G | 1 | 99 |
| 16 | H | 2 | 99 |
+----+------+------+-------+
完成!!!
8.修改日志备份的文件,来实现增量数据的还原
[root@mysql ~]# mysqlbinlog /opt/data/mysql-bin.000002 > diff.sql
[root@clq ~]# vim diff.sql
...
COMMIT/*!*/;
# at 425
#210826 14:09:03 server id 1 end_log_pos 490 CRC32 0x77739e44 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 490
#210826 14:09:03 server id 1 end_log_pos 579 CRC32 0x570b702e Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1629958143/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
drop database clq #此处为删除库的语句
/*!*/;
# at 579
#210826 14:09:25 server id 1 end_log_pos 626 CRC32 0xe9da1edc Rotate to mysql-bin.000006 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#只留这几行
COMMIT/*!*/;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#进行差异备份的数据恢复
[root@mysql ~]# mysql -u root -p < diff.sql
mysql> select * from student02;
+----+------+------+-------+
| id | name | age | score |
+----+------+------+-------+
| 5 | tom | NULL | 80 |
| 6 | xixi | NULL | 89.5 |
| 7 | haha | NULL | 96 |
| 8 | oppo | NULL | 98 |
| 9 | A | 1 | 99 |
| 10 | B | 2 | 99 |
| 11 | C | 1 | 99 |
| 12 | D | 2 | 99 |
| 13 | E | 1 | 99 |
| 14 | F | 2 | 99 |
| 15 | G | 1 | 99 |
| 16 | H | 2 | 99 |
+----+------+------+-------+