mysql差异备份

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)