mysql数据库备份与恢复
(完全备份,增量备份,差异备份)
数据库常用备份方案
数据库备份方案:
- 全量备份
- 增量备份
- 差异备份
备份方案 | 特点 |
---|---|
全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
增量备份 | 增量备份是指在一次全备份或上一次增量备份后,以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着,第一次增量备份的对象 是进行全备后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件,如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时,我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
当公司数据非常少的时候用全备
数据量非常多的时候选全备和差备(星期天凌晨做全备:休息日,没人动数据库)
mysql备份工具mysqldump
//语法:
mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
//常用的OPTIONS:
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机,请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3307
//备份整个数据库(全备)
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -pLuchengyang111!
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 2
Server version: 5.7.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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 |
| lu |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use lu
Database changed
mysql> show tables;
+--------------+
| Tables_in_lu |
+--------------+
| student |
+--------------+
1 row in set (0.00 sec)
mysql> exit
Bye
//在做备份操作之前需要先将指定ip授权
mysql> grant all on *.* to 'root'@192.168.134.148 identified by '12345678';
Query OK, 0 rows affected, 1 warning (0.01 sec)
//重新读取授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
[root@localhost ~]# mysqldump -uroot -p12345678 -h192.168.134.148 --all-databases > all-2023-10-04-16-40.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure. //如果在命令行中输入了密码,则会出现此行告警,告诉我们在命令行界面输入密码是不安全的
//用安全方式输入密码
[root@localhost ~]# mysqldump -uroot -p -h192.168.134.148 --all-databases > all-2023-10-04-16-44.sql
Enter password: //输入密码
[root@localhost ~]# ls
all-2023-10-04-16-44.sql anaconda-ks.cfg
//备份lu库的student表
[root@localhost ~]# mysqldump -uroot -p -h192.168.134.148 lu student > table-2023-10-04-17-40.sql
Enter password:
[root@localhost ~]# ls
all-2023-10-04-16-44.sql anaconda-ks.cfg table-2023-10-04-17-40.sql
//备份lu库
[root@localhost ~]# mysqldump -uroot -p -h192.168.134.148 --databases lu > lu-2023-09-04-23-16.sql
Enter password: //输入密码
[root@localhost ~]# ls
all-2023-10-04-16-44.sql lu-2023-09-04-23-16.sql table-2023-10-04-17-40.sql
误删
//模拟误删数据库
mysql> drop database lu;
Query OK, 2 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql数据恢复
//恢复lu数据库
[root@localhost ~]# ls
all-2023-10-04-16-44.sql lu-2023-09-04-23-16.sql
anaconda-ks.cfg table-2023-10-04-17-40.sql
[root@localhost ~]# mysql -uroot -p -h192.168.134.148 < lu-2023-09-04-23-16.sql
Enter password:
//查看
[root@localhost ~]# mysql -uroot -pLuchengyang111! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| lu |
| mysql |
| performance_schema |
| sys |
+--------------------+
//恢复lu数据库的student表
mysql> use lu;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> source table-2023-10-04-17-40.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...
mysql> show tables;
+--------------+
| Tables_in_lu |
+--------------+
| student |
| student10 |
+--------------+
2 rows in set (0.00 sec)
//将备份的数据库中的表恢复到其他的数据库当中
如:
mysql> create database yyr; //创建一个数据库
Query OK, 1 row affected (0.00 sec)
mysql> show databases; //查看
+--------------------+
| Database |
+--------------------+
| information_schema |
| lu |
| mysql |
| performance_schema |
| sys |
| yyr |
+--------------------+
6 rows in set (0.00 sec)
[root@localhost ~]# mysql -uroot -p yyr < table-2023-10-04-17-40.sql
Enter password: //输入密码
[root@localhost ~]# mysql -uroot -pLuchengyang111!
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 19
Server version: 5.7.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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 |
| lu |
| mysql |
| performance_schema |
| sys |
| yyr |
+--------------------+
6 rows in set (0.00 sec)
mysql> use yyr
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_yyr |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_yyr |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lu |
| mysql |
| performance_schema |
| sys |
| yyr |
+--------------------+
6 rows in set (0.00 sec)
给yyr数据库做完全备份
[root@localhost ~]# mysqldump -uroot -p -h192.168.134.148 --all-databases > all-databases.sql
Enter password:
[root@localhost ~]# ls
all-2023-10-04-16-44.sql all-databases.sql
//删除所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lu |
| mysql |
| performance_schema |
| sys |
| yyr |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database lu;
Query OK, 2 rows affected (0.01 sec)
mysql> drop database mysql ;
Query OK, 31 rows affected, 2 warnings (0.03 sec)
mysql> drop database sys;
Query OK, 101 rows affected, 2 warnings (0.04 sec)
mysql> drop database yyr;
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> drop database performance_schema;
Query OK, 88 rows affected, 2 warnings (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
//数据库恢复
[root@localhost ~]# mysql -uroot -p -h192.168.134.148 < all-databases.sql
Enter password: 12345678
[root@localhost ~]# mysql -uroot -pLuchengyang111! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| lu |
| mysql |
| yyr |
+--------------------+
//由于performance_schema和sys这两个库在备份的文件中没有,所以在恢复数据库的时候就不会恢复这两个库,想要恢复这两个库的话,可以初始化数据库,但我们那些有数据的数据库则又会删除,同时由于这两个库没有对我的数据造成任何影响,所以我们可以继续下面的操作
差异备份与恢复
mysql差异备份
开启MySQL服务器的二进制日志功能
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
user = mysql
pid-file = /tmp/mysql.pid
skip-name-resolve
server-id=1 //设置服务器标识符
log-bin=mysql_bin //开启二进制日志功能
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
对数据库进行完全备份
[root@localhost ~]# mysql -uroot -pLuchengyang111!
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 2
Server version: 5.7.22-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 |
| lu |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from lu;
+--------------------+
| Tables_in_wangqing |
+--------------------+
| runtime |
| student |
+--------------------+
2 rows in set (0.01 sec)
mysql> select * from lu.runtime;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 1 | tom | 10 |
| 2 | jerry | 30 |
+------+-------+------+
2 rows in set (0.01 sec)
mysql> select * from lu.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |
| 8 | chenshuo | 10 |
| 9 | wangwu | 3 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
11 rows in set (0.00 sec)
//完全备份
[root@localhost ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202310050916.sql
Enter password:
//表示如果你要恢复到某个时间点,你的全备的固定用法
[root@localhost data]# ls
all-202310050916.sql
//增加新内容
mysql> use yyr
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_yyr |
+---------------+
| course |
| student |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | m | 160 | 1 |
| 2 | Green | 23 | m | 158 | 2 |
| 3 | Henry | 23 | f | 185 | 1 |
| 4 | Jane | 22 | m | 162 | 3 |
| 5 | Jim | 24 | f | 175 | 2 |
| 6 | John | 21 | f | 172 | 4 |
| 7 | Lily | 22 | m | 165 | 4 |
| 8 | Susan | 23 | m | 170 | 5 |
| 9 | Thomas | 22 | f | 178 | 5 |
| 10 | Tom | 23 | f | 165 | 5 |
| 11 | LiMing | 22 | m | 180 | 7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)
//新增一条
mysql> insert student(name,age,sex,height,course_id) value('yyr',18,'f',160,8);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | m | 160 | 1 |
| 2 | Green | 23 | m | 158 | 2 |
| 3 | Henry | 23 | f | 185 | 1 |
| 4 | Jane | 22 | m | 162 | 3 |
| 5 | Jim | 24 | f | 175 | 2 |
| 6 | John | 21 | f | 172 | 4 |
| 7 | Lily | 22 | m | 165 | 4 |
| 8 | Susan | 23 | m | 170 | 5 |
| 9 | Thomas | 22 | f | 178 | 5 |
| 10 | Tom | 23 | f | 165 | 5 |
| 11 | LiMing | 22 | m | 180 | 7 |
| 12 | yyr | 18 | f | 160 | 8 |
+----+--------+------+------+--------+-----------+
12 rows in set (0.00 sec)
mysql> update student set course_id = 6 where name = 'LiMing'; //将liming课程改为6
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | m | 160 | 1 |
| 2 | Green | 23 | m | 158 | 2 |
| 3 | Henry | 23 | f | 185 | 1 |
| 4 | Jane | 22 | m | 162 | 3 |
| 5 | Jim | 24 | f | 175 | 2 |
| 6 | John | 21 | f | 172 | 4 |
| 7 | Lily | 22 | m | 165 | 4 |
| 8 | Susan | 23 | m | 170 | 5 |
| 9 | Thomas | 22 | f | 178 | 5 |
| 10 | Tom | 23 | f | 165 | 5 |
| 11 | LiMing | 22 | m | 180 | 6 |
| 12 | yyr | 18 | f | 160 | 8 |
+----+--------+------+------+--------+-----------+
12 rows in set (0.00 sec)
误删:
[root@localhost ~]# mysql -uroot -e 'drop database yyr;'
[root@localhost ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| lu |
| mysql |
| performance_schema |
| sys |
+--------------------+
注:当我们误删数据库后,应该立即设置读锁,不允许再有人往数据库当中写东西,因为在我们恢复数据库的时候是将数据库恢复到我们删除数据库前的时间,在此期间如果还有人在写入的话,那他写入的东西就会在我们恢复数据库的同时消失,会造成数据丢失。
[root@localhost ~]# ll /opt/data
total 123036
...
drwxr-x---. 2 mysql mysql 4096 Sep 5 13:39 mysql
-rw-r-----. 1 mysql mysql 895 Sep 5 14:21 mysql_bin.000006 //在没有刷新二进制日志之前执行的所有动作都在这个文件里面
-rw-r-----. 1 mysql mysql 19 Sep 5 14:09 mysql_bin.index //这个文件中写的是我们的日志都写到哪个地方去了
-rw-r-----. 1 mysql mysql 5 Sep 5 13:53 mysql.pid
...
[root@localhost ~]# cat /opt/data/mysql_bin.index
./mysql_bin.000006 //这个文件显示当前日志记到哪里去了(创建、修改)
//刷新创建新的二进制日志
[root@localhost ~]# mysqladmin -uroot -pLuchengyang111! flush-logs
[root@localhost ~]# ll /opt/data
total 123040
drwxr-x---. 2 mysql mysql 4096 Sep 5 13:39 mysql
-rw-r-----. 1 mysql mysql 942 Sep 5 14:34 mysql_bin.000006
-rw-r-----. 1 mysql mysql 154 Sep 5 14:34 mysql_bin.000007 //刷新二进制日志之后则会生成一个新的这样的文件,我们后续的动作也都会存放在这个文件中
-rw-r-----. 1 mysql mysql 38 Sep 5 14:34 mysql_bin.index
[root@localhost ~]# cat /opt/data/mysql_bin.index
./mysql_bin.000006
./mysql_bin.000007
恢复完全备份
[root@localhost ~]# mysql -uroot -p < all-202310050916.sql
Enter password:
[root@localhost ~]# mysql -uroot -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| yyr |
+--------------------+
[root@localhost ~]# mysql -uroot -e 'show tables from yyr;'
+---------------+
| Tables_in_yyr |
+---------------+
| course |
| student |
+---------------+
[root@localhost ~]# mysql -uroot -e 'select * from yyr.course;'
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MYSQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
| 6 | HTML |
+----+-------------+
[root@localhost ~]# mysql -uroot -e 'select * from yyr.student;'
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | m | 160 | 1 |
| 2 | Green | 23 | m | 158 | 2 |
| 3 | Henry | 23 | f | 185 | 1 |
| 4 | Jane | 22 | m | 162 | 3 |
| 5 | Jim | 24 | f | 175 | 2 |
| 6 | John | 21 | f | 172 | 4 |
| 7 | Lily | 22 | m | 165 | 4 |
| 8 | Susan | 23 | m | 170 | 5 |
| 9 | Thomas | 22 | f | 178 | 5 |
| 10 | Tom | 23 | f | 165 | 5 |
| 11 | LiMing | 22 | m | 180 | 7 |
+----+--------+------+------+--------+-----------+
恢复差异备份
[root@localhost ~]# ll /opt/data
total 123884
-rw-r-----. 1 mysql mysql 56 Sep 5 13:40 auto.cnf
-rw-------. 1 mysql mysql 1676 Sep 5 13:40 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 client-cert.pem
-rw-------. 1 mysql mysql 1680 Sep 5 13:40 client-key.pem
-rw-r-----. 1 mysql mysql 486 Sep 5 13:52 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Sep 5 19:41 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Sep 5 19:41 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Sep 5 13:40 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Sep 5 14:09 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Sep 5 19:40 mysql
-rw-r-----. 1 mysql mysql 942 Sep 5 14:34 mysql_bin.000006
-rw-r-----. 1 mysql mysql 867770 Sep 5 19:40 mysql_bin.000007
-rw-r-----. 1 mysql mysql 38 Sep 5 14:34 mysql_bin.index
-rw-r-----. 1 mysql mysql 5 Sep 5 13:53 mysql.pid
drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 performance_schema
-rw-------. 1 mysql mysql 1680 Sep 5 13:40 private_key.pem
-rw-r--r--. 1 mysql mysql 452 Sep 5 13:40 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Sep 5 13:40 server-cert.pem
-rw-------. 1 mysql mysql 1680 Sep 5 13:40 server-key.pem
drwxr-x---. 2 mysql mysql 8192 Sep 5 13:39 sys
drwxr-x---. 2 mysql mysql 94 Sep 5 19:40 yyr
//检查误删数据库的位置在什么地方
[root@localhost ~]# mysql -uroot -p12345678
mysql> show binlog events in 'mysql_bin.000006';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000006 | 4 | Format_desc | 10 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| mysql_bin.000006 | 123 | Previous_gtids | 10 | 154 | |
| mysql_bin.000006 | 154 | Anonymous_Gtid | 10 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000006 | 219 | Query | 10 | 290 | BEGIN |
| mysql_bin.000006 | 290 | Table_map | 10 | 348 | table_id: 182 (yyr.student) |
| mysql_bin.000006 | 348 | Write_rows | 10 | 403 | table_id: 182 flags: STMT_END_F |
| mysql_bin.000006 | 403 | Xid | 10 | 434 | COMMIT /* xid=1062 */ |
| mysql_bin.000006 | 434 | Anonymous_Gtid | 10 | 499 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000006 | 499 | Query | 10 | 570 | BEGIN |
| mysql_bin.000006 | 570 | Table_map | 10 | 628 | table_id: 182 (yyr.student) |
| mysql_bin.000006 | 628 | Update_rows | 10 | 710 | table_id: 182 flags: STMT_END_F |
| mysql_bin.000006 | 710 | Xid | 10 | 741 | COMMIT /* xid=1064 */ |
| mysql_bin.000006 | 741 | Anonymous_Gtid | 10 | 806 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql_bin.000006 | 806 | Query | 10 | 895 | drop database yyr | //可以看到此行就是误删yyr数据库的动作
| mysql_bin.000006 | 895 | Rotate | 10 | 942 | mysql_bin.000007;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec)
//使用mysqlbinlog恢复差异备份,此处我们要将执行动作恢复到执行删除数据库之前,也就是上表的806之前
[root@localhost ~]# mysqlbinlog --stop-position=806 /opt/data/mysql_bin.000006 | mysql -uroot -p12345678
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -e 'select * from yyr.student;'
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | m | 160 | 1 |
| 2 | Green | 23 | m | 158 | 2 |
| 3 | Henry | 23 | f | 185 | 1 |
| 4 | Jane | 22 | m | 162 | 3 |
| 5 | Jim | 24 | f | 175 | 2 |
| 6 | John | 21 | f | 172 | 4 |
| 7 | Lily | 22 | m | 165 | 4 |
| 8 | Susan | 23 | m | 170 | 5 |
| 9 | Thomas | 22 | f | 178 | 5 |
| 10 | Tom | 23 | f | 165 | 5 |
| 11 | LiMing | 22 | m | 180 | 6 |
| 12 | yyr | 18 | f | 160 | 8 |
+----+--------+------+------+--------+-----------+
//我们可以看到,表中数据恢复到了我们完全备份后再次修改的时候,证明恢复差异备份成功
//在此之后我们的执行动作则都会存放在刷新的二进制日志里面
//可以通过此命令进行查询
mysql> show binlog events in 'mysql_bin.000007'\G
举例说明:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| FTX |
| ftx |
| mysql |
| performance_schema |
| sys |
| test |
| yyr |
+--------------------+
8 rows in set (0.00 sec)
mysql> show binlog events in 'mysql_bin.000007'\G
......
......
*************************** 472. row ***************************
Log_name: mysql_bin.000007
Pos: 868416
Event_type: Query
Server_id: 10
End_log_pos: 868510
Info: create database test
472 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show binlog events in 'mysql_bin.000007'\G
......
......
*************************** 474. row ***************************
Log_name: mysql_bin.000007
Pos: 868575
Event_type: Query
Server_id: 10
End_log_pos: 868660
Info: drop database test
474 rows in set (0.00 sec)