差异备份
备份上一次的完全备份后发生变化的所有文件。
差异备份是指在一次全备份后到进行差异备份的这段时间内
对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。
开启mysql服务器的二进制的日志功能
[root@wuliyong ~]# vim /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@wuliyong ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
- 查看数据库的内容
[root@wuliyong ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 |
| performance_schema |
| sys |
| wzy |
+--------------------+
5 rows in set (0.00 sec)
mysql> use wzy;
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> show tables;
+---------------+
| Tables_in_wzy |
+---------------+
| yiyi |
+---------------+
1 row in set (0.00 sec)
mysql> select * from yiyi;
+------+------+
| name | age |
+------+------+
| tom | 12 |
| mary | 16 |
| haha | 14 |
+------+------+
3 rows in set (0.00 sec)
- 读数据库进行完全备份
[root@wuliyong ~]# mysqldump -uroot -pyong123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902221511.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@wuliyong ~]# ll /opt/data/
总用量 122944
-rw-r-----. 1 mysql mysql 56 2月 21 20:47 auto.cnf
-rw-r-----. 1 mysql mysql 361 2月 21 21:05 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 2月 21 21:05 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月 21 21:05 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月 21 20:47 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月 21 21:08 ibtmp1
drwxr-x---. 2 mysql mysql 4096 2月 21 20:47 mysql
-rw-r-----. 1 mysql mysql 154 2月 21 21:08 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 2月 21 21:08 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 2月 21 21:05 mysql.pid
drwxr-x---. 2 mysql mysql 8192 2月 21 20:47 performance_schema
drwxr-x---. 2 mysql mysql 8192 2月 21 20:47 sys
-rw-r-----. 1 mysql mysql 12589 2月 21 21:05 wuliyon
- 在数据库里面新增加内容：
[root@wuliyong ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 wzy.yiyi;
+------+------+
| name | age |
+------+------+
| tom | 12 |
| mary | 16 |
| haha | 14 |
+------+------+
3 rows in set (0.00 sec)
mysql> update yiyi set age = 14 where name = 'haha';
ERROR 1046 (3D000): No database selected
mysql> use wzy
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> insert into yiyi values('kk',6),('ll',7);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from wzy.yiyi;
+------+------+
| name | age |
+------+------+
| tom | 12 |
| mary | 16 |
| haha | 14 |
| kk | 6 |
| ll | 7 |
+------+------+
5 rows in set (0.00 sec)
- 然后在修改内容：
mysql> update yiyi set age = 7 where name = 'll';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> select * from wzy.yiyi;
+------+------+
| name | age |
+------+------+
| tom | 12 |
| mary | 16 |
| haha | 14 |
| kk | 6 |
| ll | 7 |
+------+------+
5 rows in set (0.00 sec)
mysql> quit
Bye
- 模拟误删数据
[root@wuliyong ~]# mysql -uroot -pyong123! -e 'drop database wzy;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wuliyong ~]# mysql -uroot -pyong123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@wuliyong ~]# ll /opt/data/
总用量 122944
-rw-r-----. 1 mysql mysql 56 2月 21 20:47 auto.cnf
-rw-r-----. 1 mysql mysql 361 2月 21 21:05 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 2月 21 21:22 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月 21 21:22 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月 21 20:47 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月 21 21:08 ibtmp1
drwxr-x---. 2 mysql mysql 4096 2月 21 20:47 mysql
-rw-r-----. 1 mysql mysql 569 2月 21 21:22 mysql_bin.000002
-rw-r-----. 1 mysql mysql 19 2月 21 21:08 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 2月 21 21:05 mysql.pid
drwxr-x---. 2 mysql mysql 8192 2月 21 20:47 performance_schema
drwxr-x---. 2 mysql mysql 8192 2月 21 20:47 sys
-rw-r-----. 1 mysql mysql 12690 2月 21 21:13 wuliyong.err
[root@wuliyong ~]# mysqladmin -uroot -pyong123! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@wuliyong ~]# ll /opt/data/
总用量 122948
-rw-r-----. 1 mysql mysql 56 2月 21 20:47 auto.cnf
-rw-r-----. 1 mysql mysql 361 2月 21 21:05 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 2月 21 21:22 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月 21 21:22 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月 21 20:47 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月 21 21:08 ibtmp1
drwxr-x---. 2 mysql mysql 4096 2月 21 20:47 mysql
-rw-r-----. 1 mysql mysql 616 2月 21 21:23 mysql_bin.000002
-rw-r-----. 1 mysql mysql 154 2月 21 21:23 mysql_bin.000003
-rw-r-----. 1 mysql mysql 38 2月 21 21:23 mysql_bin.index
-rw-r-----. 1 mysql mysql 6 2月 21 21:05 mysql.pid
drwxr-x---. 2 mysql mysql 8192 2月 21 20:47 performance_schema
drwxr-x---. 2 mysql mysql 8192 2月 21 20:47 sys
-rw-r-----. 1 mysql mysql 12690 2月 21 21:13 wuliyong.err
- 恢复误删的数据
[root@wuliyong ~]# mysql -uroot -p < all-201902221511.sql
Enter password:
[root@wuliyong ~]# mysql -uroot -pyong123!
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 12
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 |
| performance_schema |
| sys |
| wzy |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from yiyi;
ERROR 1046 (3D000): No database selected
mysql> show tables from wzy;
+---------------+
| Tables_in_wzy |
+---------------+
| yiyi |
+---------------+
1 row in set (0.00 sec)
mysql> select * from wzy.yiyi;
+------+------+
| name | age |
+------+------+
| tom | 12 |
| mary | 16 |
| haha | 14 |
+------+------+
3 rows in set (0.00 sec)
- 查找到误删数据库记录的位置：
[root@wuliyong ~]# mysql -uroot -pyong123!
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 13
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 | 784744 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql_bin.000002'\G
*************************** 1. row ***************************
Log_name: mysql_bin.000002
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.23-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql_bin.000002
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: mysql_bin.000002
Pos: 154
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 219
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
Log_name: mysql_bin.000002
Pos: 219
Event_type: Query
Server_id: 1
End_log_pos: 290
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql_bin.000002
Pos: 290
Event_type: Table_map
Server_id: 1
End_log_pos: 339
Info: table_id: 140 (wzy.yiyi)
*************************** 6. row ***************************
Log_name: mysql_bin.000002
Pos: 339
Event_type: Write_rows
Server_id: 1
End_log_pos: 384
Info: table_id: 140 flags: STMT_END_F
*************************** 7. row ***************************
Log_name: mysql_bin.000002
Pos: 384
Event_type: Xid
Server_id: 1
End_log_pos: 415
Info: COMMIT /* xid=481 */
*************************** 8. row ***************************
Log_name: mysql_bin.000002
Pos: 415
Event_type: Anonymous_Gtid
Server_id: 1
End_log_pos: 480
Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 9. row ***************************
Log_name: mysql_bin.000002
Pos: 480
Event_type: Query
Server_id: 1
End_log_pos: 569
Info: drop database wzy
*************************** 10. row ***************************
Log_name: mysql_bin.000002
Pos: 569
Event_type: Rotate
Server_id: 1
End_log_pos: 616
Info: mysql_bin.000003;pos=4
- 进行差异恢复，恢复到表‘yiyi’里面‘ll’被删掉之前:
[root@wuliyong ~]# mysqlbinlog --stop-position=480 /opt/data/mysql_bin.000002 |mysql -uroot -pyong123!
mysql: [Warning] Using a password on the command line interface can be insecure.
- 在到数据库里面去查看数据是否恢复到第一次完全备份前：
[root@wuliyong ~]# mysql -uroot -pyong123!
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 17
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> use wzy;
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 yiyi;
+------+------+
| name | age |
+------+------+
| tom | 12 |
| mary | 16 |
| haha | 14 |
| kk | 6 |
| ll | 7 |
+------+------+
5 rows in set (0.00 sec)