mysql基于binlog的恢复

[root@stag-8-46 0104]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox'
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 21
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
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 master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 5662 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

--环境准备，记录当前的binlog位置mysql-bin.000002

mysql> use test
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 count(1) from t;
+----------+
| count(1) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql> desc t;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql>
mysql>
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 5662 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

--生成新的binlog

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

--将数据写入到新的binlog方便实验（插入记录1，2，3）

mysql> insert into t select null;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t select null;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t select null;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 910 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

--切换binlog在新的binlog中也插入数据（插入记录4，5，6）

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> insert into t select null;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t select null;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into t select null;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 910 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

--恢复开始
[root@stag-8-46 0104]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox'
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 22
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
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> use test
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 t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)

mysql> exit
Bye

--将binlog3中的提取操作记录到03.sql中
[root@stag-8-46 0104]# mysqlbinlog --no-defaults --start-position=4 mysql-bin.000003 > /tmp/0104/03.sql
mysqlbinlog: File 'mysql-bin.000003' not found (Errcode: 2 - No such file or directory)
[root@stag-8-46 0104]# pwd
/tmp/0104
[root@stag-8-46 0104]# cd /opt/MySQL/home/rsandbox_5_7_33/master/data
[root@stag-8-46 data]# mysqlbinlog --no-defaults --start-position=4 mysql-bin.000003 > /tmp/0104/03.sql

--将binlog4中的提取操作记录到04.sql中
[root@stag-8-46 data]# mysqlbinlog --no-defaults --start-position=4 mysql-bin.000004 > /tmp/0104/04.sql

--将binlog3、binlog4中的提取操作记录到0304.sql中
[root@stag-8-46 data]# mysqlbinlog --no-defaults --start-position=4 mysql-bin.00000[3..4] > /tmp/0104/0304.sql
[root@stag-8-46 data]#
[root@stag-8-46 data]#
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox'
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 23
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
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> use test
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 t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)

--truncate掉表

mysql> truncate table t;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox' ^C
[root@stag-8-46 data]# ls -l /tmp/0104
total 20
-rw-r--r-- 1 root root 7179 Jan 4 10:42 0304.sql
-rw-r--r-- 1 root root 3990 Jan 4 10:41 03.sql
-rw-r--r-- 1 root root 3948 Jan 4 10:41 04.sql
-rw-r--r-- 1 root root 2938 Jan 4 10:36 test.sql

--将binlog3中的提取操作记录的03.sql恢复到数据中
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox' < /tmp/0104/03.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox'
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 25
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
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> use test
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

--只恢复了binlog3中的提取操作记录
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

--继续实验

mysql> truncate table t;
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye

--将binlog4中的提取操作记录的04.sql恢复到数据中
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox' < /tmp/0104/04.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox'
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 27
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
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> use test
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

--只恢复了binlog4中的提取操作记录
mysql> select * from t;
+----+
| id |
+----+
| 4 |
| 5 |
| 6 |
+----+
3 rows in set (0.00 sec)

mysql> exit
Bye
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox'
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 28
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
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> use test
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> truncate table t;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> exit
Bye

--将binlog3、binlog4中的提取操作记录的0304.sql恢复到数据中
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox' < /tmp/0104/0304.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@stag-8-46 data]# mysql --socket=/tmp/mysql_sandbox20034.sock -umsandbox -p'msandbox'
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 30
Server version: 5.7.33-log MySQL Community Server (GPL)

Copyright (c) 2009-2022 Percona LLC and/or its affiliates
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> use test
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 t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+----+
6 rows in set (0.00 sec)

mysql>