1.mysql差异备份
开启mysql服务的二进制日志功能
[root@ye ~]# vim /etc/my.cnf
在里面添加下面两个:
server-id = 1 --服务器的标志符
log-bin = mysql_bin --开启二进制日志功能
//重启服务:
[root@ye ~]# systemctl restart mysqld
查看数据库内容,然后全量备份
[root@ye ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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 |
| pengye |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from pengye;
+------------------+
| Tables_in_pengye |
+------------------+
| aaa |
| bbb |
| one |
+------------------+
3 rows in set (0.00 sec)
mysql> use pengye;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from pengye.one;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)
[root@ye ~]# mysqldump -uroot -p123456 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902221846.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@ye ~]# ls
all-201902221846.sql anaconda-ks.cfg
在表里添加新内容,并修改内容
mysql> insert one values (8,'QQQ',11),(9,'AAA',12);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from one;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | QQQ | 11 |
| 9 | AAA | 12 |
+----+-----------+------+
8 rows in set (0.00 sec)
//修改内容
mysql> update one set age =33 where id = 9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from one;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | QQQ | 11 |
| 9 | AAA | 33 |
+----+-----------+------+
8 rows in set (0.00 sec)
模拟误删数据库,误删后要立即刷新二进制文件
mysql> drop database pengye;
Query OK, 3 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//由上可见pengye这个数据库已经被删除了
刷新创建新的二进制文件
[root@ye ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@ye ~]# ll /var/lib/mysql
total 122964
-rw-r-----. 1 mysql mysql 56 Feb 19 03:13 auto.cnf
-rw-------. 1 mysql mysql 1679 Feb 19 03:13 ca-key.pem
-rw-r--r--. 1 mysql mysql 1107 Feb 19 03:13 ca.pem
-rw-r--r--. 1 mysql mysql 1107 Feb 19 03:13 client-cert.pem
-rw-------. 1 mysql mysql 1675 Feb 19 03:13 client-key.pem
-rw-r-----. 1 mysql mysql 560 Feb 22 04:04 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Feb 22 05:56 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Feb 22 05:56 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Feb 19 03:13 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Feb 22 05:46 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Feb 22 03:53 mysql
-rw-r-----. 1 mysql mysql 919 Feb 22 05:59 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 Feb 22 05:59 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 Feb 22 05:59 mysql_bin.index
srwxrwxrwx. 1 mysql mysql 0 Feb 22 04:04 mysql.sock
-rw-------. 1 mysql mysql 5 Feb 22 04:04 mysql.sock.lock
drwxr-x---. 2 mysql mysql 8192 Feb 19 03:13 performance_schema
-rw-------. 1 mysql mysql 1675 Feb 19 03:13 private_key.pem
-rw-r--r--. 1 mysql mysql 451 Feb 19 03:13 public_key.pem
-rw-r--r--. 1 mysql mysql 1107 Feb 19 03:13 server-cert.pem
-rw-------. 1 mysql mysql 1679 Feb 19 03:13 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Feb 19 03:13 sys
恢复完全备份,查看表one时,发现只恢复了没有修改数据前的内容
[root@ye ~]# mysql -uroot -p123456 < all-201902221846.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ye ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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 |
| pengye |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from pengye.one;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
+----+-----------+------+
6 rows in set (0.00 sec)
恢复到没有误删之前的数据库
//查看当前正在用的文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 | 778719 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
//查看日志,找到误删记录在哪
mysql> show binlog events in 'mysql_bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.25-log, Binlog ver: 4 |
| mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 219 | Query | 1 | 293 | BEGIN |
| mysql_bin.000002 | 293 | Table_map | 1 | 345 | table_id: 145 (pengye.one) |
| mysql_bin.000002 | 345 | Write_rows | 1 | 400 | table_id: 145 flags: STMT_END_F |
| mysql_bin.000002 | 400 | Xid | 1 | 431 | COMMIT /* xid=511 */ |
| mysql_bin.000002 | 431 | Anonymous_Gtid | 1 | 496 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 496 | Query | 1 | 570 | BEGIN |
| mysql_bin.000002 | 570 | Table_map | 1 | 622 | table_id: 145 (pengye.one) |
| mysql_bin.000002 | 622 | Update_rows | 1 | 678 | table_id: 145 flags: STMT_END_F |
| mysql_bin.000002 | 678 | Xid | 1 | 709 | COMMIT /* xid=521 */ |
| mysql_bin.000002 | 709 | Anonymous_Gtid | 1 | 774 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000002 | 774 | Query | 1 | 872 | drop database pengye |
| mysql_bin.000002 | 872 | Rotate | 1 | 919 | mysql_bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
//恢复到误删之前的数据库
[root@ye ~]# mysqlbinlog --stop-position=774 /var/lib/mysql/mysql_bin.000002 | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@ye ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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> select * from pengye.one;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | QQQ | 11 |
| 9 | AAA | 33 |
+----+-----------+------+
8 rows in set (0.00 sec)