开启MySQL服务器的二进制日志功能
[root@zwy1 ~]# 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 server-id=1 #设置服务器标识符 log-bin =mysql_bin #开启二进制日志功能
[root@zwy1 ~]# service mysqld restart #重启服务
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
完全备份
[root@zwy1 ~]# mysqldump -uroot -p4564949a --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-210508.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@zwy1 ~]# ls all-210508.sql anaconda-ks.cfg mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz nginx-1.18.0 nginx-1.18.0.tar.gz pass
添加新内容
mysql> insert student (name,age)values('lisi',26),('heigou',24);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select *from student;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 1 | tom | 20 | NULL |
| 2 | jerry | 14 | NULL |
| 3 | zhangshan | 18 | NULL |
| 4 | lisi | 26 | NULL |
| 5 | heigou | 24 | NULL |
+----+-----------+------+-------+
5 rows in set (0.00 sec)
mysql> update student set score =90 where name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from student;
+----+-----------+------+-------+
| id | name | age | score |
+----+-----------+------+-------+
| 1 | tom | 20 | NULL |
| 2 | jerry | 14 | NULL |
| 3 | zhangshan | 18 | NULL |
| 4 | lisi | 26 | 90 |
| 5 | heigou | 24 | NULL |
+----+-----------+------+-------+
5 rows in set (0.00 sec)
模拟误删
[root@zwy1 ~]# mysql -e 'drop database school;' [root@zwy1 ~]# mysql -e 'show databases;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
刷新创建新的二进制日志
[root@zwy1 data]# ll total 122988 -rw-r-----. 1 mysql mysql 56 May 8 01:17 auto.cnf -rw-------. 1 mysql mysql 1676 May 8 01:17 ca-key.pem -rw-r--r--. 1 mysql mysql 1112 May 8 01:17 ca.pem -rw-r--r--. 1 mysql mysql 1112 May 8 01:17 client-cert.pem -rw-------. 1 mysql mysql 1676 May 8 01:17 client-key.pem -rw-r-----. 1 mysql mysql 405 May 8 02:17 ib_buffer_pool -rw-r-----. 1 mysql mysql 12582912 May 8 03:39 ibdata1 -rw-r-----. 1 mysql mysql 50331648 May 8 03:39 ib_logfile0 -rw-r-----. 1 mysql mysql 50331648 May 8 01:17 ib_logfile1 -rw-r-----. 1 mysql mysql 12582912 May 8 02:18 ibtmp1 drwxr-x---. 2 mysql mysql 4096 May 8 01:17 mysql -rw-r-----. 1 mysql mysql 177 May 8 02:17 mysql_bin.000002 -rw-r-----. 1 mysql mysql 941 May 8 03:39 mysql_bin.000003 -rw-r-----. 1 mysql mysql 154 May 8 03:39 mysql_bin.000004 -rw-r-----. 1 mysql mysql 57 May 8 03:39 mysql_bin.index -rw-r-----. 1 mysql mysql 5 May 8 02:18 mysql.pid drwxr-x---. 2 mysql mysql 8192 May 8 01:17 performance_schema -rw-------. 1 mysql mysql 1680 May 8 01:17 private_key.pem -rw-r--r--. 1 mysql mysql 452 May 8 01:17 public_key.pem -rw-r--r--. 1 mysql mysql 1112 May 8 01:17 server-cert.pem -rw-------. 1 mysql mysql 1676 May 8 01:17 server-key.pem drwxr-x---. 2 mysql mysql 8192 May 8 01:17 sys -rw-r-----. 1 mysql mysql 19203 May 8 02:18 zwy1.err [root@zwy1 data]# cat mysql_bin.index ./mysql_bin.000002 ./mysql_bin.000003 ./mysql_bin.000004
全量备份恢复
[root@zwy1 ~]# mysql -uroot < all-210508.sql [root@zwy1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | school | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> select * from student; ERROR 1046 (3D000): No database selected mysql> select * from school.student; +----+-----------+------+-------+ | id | name | age | score | +----+-----------+------+-------+ | 1 | tom | 20 | NULL | | 2 | jerry | 14 | NULL | | 3 | zhangshan | 18 | NULL | +----+-----------+------+-------+ 3 rows in set (0.00 sec)
检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000003'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql_bin.000003 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.33-log, Binlog ver: 4 | | mysql_bin.000003 | 123 | Previous_gtids | 10 | 154 | | | mysql_bin.000003 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000003 | 219 | Query | 10 | 293 | BEGIN | | mysql_bin.000003 | 293 | Table_map | 10 | 351 | table_id: 99 (school.student) | | mysql_bin.000003 | 351 | Write_rows | 10 | 410 | table_id: 99 flags: STMT_END_F | | mysql_bin.000003 | 410 | Xid | 10 | 441 | COMMIT /* xid=17 */ | | mysql_bin.000003 | 441 | Anonymous_Gtid | 10 | 506 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000003 | 506 | Query | 10 | 580 | BEGIN | | mysql_bin.000003 | 580 | Table_map | 10 | 638 | table_id: 99 (school.student) | | mysql_bin.000003 | 638 | Update_rows | 10 | 700 | table_id: 99 flags: STMT_END_F | | mysql_bin.000003 | 700 | Xid | 10 | 731 | COMMIT /* xid=19 */ | | mysql_bin.000003 | 731 | Anonymous_Gtid | 10 | 796 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000003 | 796 | Query | 10 | 894 | drop database school | | mysql_bin.000003 | 894 | Rotate | 10 | 941 | mysql_bin.000004;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 15 rows in set (0.00 sec)
使用mysqlbinlog恢复差异备份
[root@zwy1 ~]# mysqlbinlog --stop-position=796 /opt/data/mysql_bin.000003 |mysql -uroot -p4564949a mysql: [Warning] Using a password on the command line interface can be insecure. [root@zwy1 ~]# mysql -e 'select * from school.student;' +----+-----------+------+-------+ | id | name | age | score | +----+-----------+------+-------+ | 1 | tom | 20 | NULL | | 2 | jerry | 14 | NULL | | 3 | zhangshan | 18 | NULL | | 4 | lisi | 26 | 90 | | 5 | heigou | 24 | NULL | +----+-----------+------+-------+