catalogue

preface

1, Log management of MySQL

1. Overview

2. Log classification of MySQL

3. MySQL log on

4. Enter the database to check whether the corresponding log is enabled

four point one   Check whether the general query log is enabled

four point two   Check whether the binary log is enabled

5. View slow query log related functions

five point one   Check whether the slow query log function is enabled

five point two   View slow query time settings

five point three   Set the method of starting slow query in the database-

2, MySQL full backup and recovery

1. Importance of data backup

2. Causes of data loss

3. Classification of database backup

three point one    Classification from the perspective of physics and logic

three point two    Classification from the perspective of database backup strategy

three point three    Difference between physical backup and logical backup

4. Common backup methods

four point one    Physical cold standby

four point two   Special backup tools mydump or mysqlhotcopy

four point three    Incremental backup by enabling binary logs

four point four    Backup through third-party tools

3, MySQL full backup

1. Concept

2. Advantages and disadvantages of full backup

3. Full backup classification

three point one   Physical cold backup and recovery

three point two    mysqldump backup and recovery

IV   MySQL full backup and recovery

1. Physical cold backup and recovery

one point one    Build table

one point two   Physical cold backup  

one point three   Physical cold recovery

2. mysqldump backup and recovery

two point one   Full backup of one or more complete libraries (including all tables therein)

two point two   Fully back up all libraries in the MySQL server

two point three    Fully backs up some tables in the specified library

two point four   View backup files

two point five   Restore database

two point six   Restore data table

5, MySQL incremental backup and recovery

1. Mysql incremental backup

2,   Features of incremental backup

3. Significance of MySQL binary log to backup

4. Incremental backup

four point one    Enable binary log function

four point two   The database or table can be fully backed up every week

four point three   Incremental backup can be performed every day to generate new binary log files (e.g. MySQL bin. 00000 3)

four point four    Insert new data to simulate the addition or change of data

four point five   Generate a new binary log file again

four point six     View the contents of the binary log file

5. Incremental recovery - General recovery

five point one    Simulate recovery steps for lost changed data

five point two   Simulate recovery steps for all lost data   

6. Incremental recovery -- breakpoint backup

six point one    Check the binary file to determine the instruction number and time

six point two   Location based recovery

six point three   Point in time based recovery

summary

preface

With the development of business, the company's business and scale continue to expand, and the website has accumulated a large amount of user information and data. For an Internet company, user and business data are the foundation. Once the company's data is disordered or lost, it is a disaster for Internet companies. In order to prevent data loss caused by system operation error or system failure, the company requires to strengthen the reliability of user data, comprehensively strengthen data level backup, and recover at the first time in case of failure.

1, Log management of MySQL

1. Overview

• Log files play an important role in database backup and recovery
•   Common log files are saved in / usr/local/mysql/data directory by default
•   You can modify, open and close the log path in [mysqld] in the / etc/my.cnf configuration file

2. Log classification of MySQL

• Error log
• General query log
• Binary log
• Slow query log

3. MySQL log on

• By permanently modifying the MySQL configuration file

vim /etc/my.cnf
[mysqld]
##The error log is used to record error messages that occur when MySQL is started, stopped, or running. It is enabled by default
#Specify the location and file name of the error log
log-error=/usr/local/mysql/data/mysql_error.log

##The general query log is used to record all MySQL connections and statements. It is closed by default
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

##Binary log is used to record all statements that have updated data or have potentially updated data, record data changes, and can be used for data recovery. It is enabled by default
log-bin=mysql-bin
#You can also log_bin=mysql-bin

##Slow query log, used to record all execution times exceeding long_ query_ For the statement of time seconds, you can find which query statements take a long time to execute for optimization. It is closed by default
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5  #Set that statements executed for more than 5 seconds are recorded. The default is 10 seconds

systemctl restart mysqld.service 

 

4. Enter the database to check whether the corresponding log is enabled

four point one   Check whether the general query log is enabled

show variables like 'general%';

 

four point two   Check whether the binary log is enabled

show variables like 'log_bin%';

 

5. View slow query log related functions

five point one   Check whether the slow query log function is enabled

show variables like '%slow%';

 

five point two   View slow query time settings

show variables like 'long_query_time';

 

five point three   Set the method of starting slow query in the database-

set global slow_query_log=ON;

 

2, MySQL full backup and recovery

1. Importance of data backup

• The primary purpose of backup is disaster recovery
• In a production environment, data security is critical
• Any loss of data can have serious consequences

2. Causes of data loss

• Program error
• Human operation error
• Arithmetic error
• Disk failure
• Disasters (e.g. fire, earthquake) and theft

3. Classification of database backup

three point one    Classification from the perspective of physics and logic

Physical backup: backup of physical files (such as data files, log files, etc.) of database operating system

• Methods of physical backup
  • Cold backup (offline backup): it is performed when the database is closed
  • Hot backup (online backup): the database is running and depends on the log file of the database
  • Warm backup: the backup operation is performed when the database is locked in a table (not writable but readable)

Logical backup: backup of database logical components (such as tables and other database objects)

three point two    Classification from the perspective of database backup strategy

Full backup: complete backup of data every time, that is, the backup of the whole database, database structure and file structure. What is saved is the database at the time of backup completion.

Differential backup: backup all files that have been modified since the last full backup. The backup time node is from the last full backup, and the amount of backup data will be larger and larger.

Incremental backup: only those files modified after the last full backup or incremental backup will be backed up. Taking the time of the last full backup or the last incremental backup as the time point, only the data changes between them are backed up, so the amount of data backed up is small, the occupied space is small, and the backup speed is fast.

three point three    Difference between physical backup and logical backup

Physical backup: physical backup is to copy data from the host to the standby with disk blocks as the basic unit.
Logical backup: logical backup is to copy data from the host to the standby based on files.

4. Common backup methods

four point one    Physical cold standby

The database is closed during backup, and the database files are packaged directly

Backup is fast and recovery is the simplest

four point two   Special backup tools mydump or mysqlhotcopy

mysqldump is a common logical backup tool

mysqlhotcopy only has backup MyISAM and ARCHIVE tables

four point three    Incremental backup by enabling binary logs

Incremental backup is supported. Binary log must be enabled during incremental backup.

Binary log files provide users with replication and restore the information required for database changes after the backup point.

In case of incremental backup (including data modification since the last full backup or incremental backup), the binary log needs to be refreshed.

four point four    Backup through third-party tools

The third-party tool Percona xtraBackup is a free MySQL hot backup software. It supports online hot backup of Innodb and xtraDB, and MySQL table backup. However, the backup of MyISAM table should be carried out under the condition of table lock.

3, MySQL full backup

1. Concept

It is a backup of the entire database, database structure and file structure

Save the database at the completion time of backup

It is the basis of differential backup and incremental backup

2. Advantages and disadvantages of full backup

• Advantages: simple and convenient backup and recovery operation
• Disadvantages:
  • There is a lot of duplication in the data
  • Takes up a lot of backup space
  • Long backup and recovery time

3. Full backup classification

three point one   Physical cold backup and recovery

Close MySQL database

Use the tar command to package the database folder directly

Simply replace the existing MySQL directory

three point two    mysqldump backup and recovery

MySQL has its own backup tool, which can facilitate the backup of MySQL

You can export the specified libraries and tables as SQL scripts

Use the mysql command to import the backed up data

IV   MySQL full backup and recovery

1. Physical cold backup and recovery

one point one    Build table

mysql> use test;                                                              
Database changed
mysql> create table if not exists test (
    -> id int(4) not null auto_increment,
    -> name varchar(10) not null,
    -> sex char(10) not null,
    -> hobby varchar(50),
	-> primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
| sex   | char(10)    | NO   |     | NULL    |                |
| hobby | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into test values(1,'zhangsan','male','running');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(2,'lisi','female','singing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
+----+----------+--------+---------+
2 rows in set (0.00 sec)

one point two   Physical cold backup  

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# yum install -y xz
[root@localhost ~]# tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#Compressed backup

one point three   Physical cold recovery

Delete data table

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, 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 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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# systemctl stop mysqld

Recover database files

[root@localhost ~]# tar Jxvf /opt/mysql_all_2021-08-31.tar.xz -C /
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, 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                |
| test               |
| usr                |
+--------------------+
6 rows in set (0.00 sec)

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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

mysql> select * from test;
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
+----+----------+--------+---------+
2 rows in set (0.00 sec)

2. mysqldump backup and recovery

two point one   Full backup of one or more complete libraries (including all tables therein)

mysqldump -u user name - p [password] - databases database name 1 [database name 2]... > / backup path / backup file name.sql
The exported is the database script file
For example:
mysqldump -u root -p --databases test > /opt/test.sql
Backup test library

[root@localhost ~]# mysqldump -u root -p --databases test > /opt/test.sql
Enter password: 
[root@localhost ~]# cd /opt
[root@localhost opt]# ls
mysql-5.7.20                 mysql-boost-5.7.20.tar.gz  rh
mysql_all_2021-10-26.tar.xz  test.sql

mysqldump -u root -p --databases mysql test > /opt/mysql_test.sql
Backup mysql and test libraries

[root@localhost opt]# mysqldump -u root -p --databases mysql test > /opt/mysql_test.sql
Enter password: 
[root@localhost opt]# ls
mysql-5.7.20                 mysql-boost-5.7.20.tar.gz  rh
mysql_all_2021-10-26.tar.xz  mysql_test.sql             test.sql

two point two   Fully back up all libraries in the MySQL server

mysqldump -u user name - p [password] - All Databases > / backup path / backup file name.sql
For example:
mysqldump -u root -p --all-databases > /opt/all.sql
Backup all libraries

[root@localhost opt]# mysqldump -u root -p --all-databases > /opt/all.sql
Enter password: 
[root@localhost opt]# ls
all.sql       mysql_all_2021-10-26.tar.xz  mysql_test.sql  test.sql
mysql-5.7.20  mysql-boost-5.7.20.tar.gz    rh

two point three    Fully backs up some tables in the specified library

mysqldump -u user name - p [password] [- d] library name [table name 1] [table name 2]... > / backup path / backup file name.sql
Use the "- d" option to explain that only the table structure of the database is saved
If the "- d" option is not used, the table data will also be backed up
For example:
mysqldump -u root -p test test > /opt/test_test.sql
Back up the test table in the test library

[root@localhost opt]# mysqldump -u root -p test test > /opt/test_test.sql
Enter password: 
[root@localhost opt]# ls
all.sql       mysql_all_2021-10-26.tar.xz  mysql_test.sql  test.sql
mysql-5.7.20  mysql-boost-5.7.20.tar.gz    rh              test_test.sql

two point four   View backup files

grep -v "^--" /opt/test_test.sql | grep -v "^/" | grep -v "^$"

[root@localhost opt]# grep -v "^--" /opt/test_test.sql | grep -v "^/" | grep -v "^$"
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `sex` char(10) NOT NULL,
  `hobby` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'zhangsan','male','running'),(2,'lisi','female','singing');
UNLOCK TABLES;

two point five   Restore database

Delete database

[root@localhost opt]# mysql -u root -p -e 'drop database test;'
#The "- e" option is used to specify the command to be executed after connecting to MySQL. After the command is executed, it will exit automatically
Enter password: 
[root@localhost opt]# mysql -u -root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Restore database  

[root@localhost opt]# mysql -u root -p < /opt/test.sql 
Enter password: 
[root@localhost opt]# mysql -u -root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

two point six   Restore data table

Delete data table

[root@localhost opt]# mysql -u root -p -e 'drop table test.test;'
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'show tables from test;'
Enter password: 

Restore data table
When the backup file contains only the backup of the table, but not the statement of the created library, the library name must be specified when performing the import operation, and the target library must exist

[root@localhost opt]# mysql -u root -p test < /opt/test_test.sql 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'show tables from test;'
Enter password: 
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+

5, MySQL incremental backup and recovery

1. Mysql incremental backup

Problems with full backup using mysqldump
● duplicate data in backup data
● too long backup and recovery time                                                                                                               Is the file or content added / changed since the last backup
MySQL does not provide a direct incremental backup method
Incremental backup can be realized indirectly through binary logs provided by MySQL

2,   Features of incremental backup

No duplicate data, small amount of backup, time period
Recovery requires that all incremental backups can be restored after the last full backup and full backup, and all incremental backups should be reversely restored one by one

3. Significance of MySQL binary log to backup

● the binary log stores all operations that update or possibly update the database
● the binary log starts recording after the MySQL server is started, and the file reaches max_binlog_size or re create a new log file after receiving the flush logs command
● just periodically execute the flush logs method to recreate new logs, generate binary file sequences, and save these logs to a safe place in time to complete an incremental backup for a period of time

4. Incremental backup

four point one    Enable binary log function

[root@localhost opt]# vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
##Optional. Specifies that the binary log record format is MIXED
##There are three different recording formats for binary log: state (based on SQL STATEMENT), ROW (based on ROW) and MIXED (MIXED mode). The default format is state
server-id = 1

[root@localhost opt]# systemctl restart mysqld
[root@localhost opt]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 154 8 31 / 21:17 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql  19 8 31 / 21:17 /usr/local/mysql/data/mysql-bin.index

four point two   The database or table can be fully backed up every week

[root@localhost opt]# mysqldump -u root -p test test > /opt/test_test_$(date +%F).sql
#Back up the test database test table
Enter password: 
[root@localhost opt]# mysqldump -u root -p --all-databases test > /opt/test_$(date +%F).sql
#Backup test library
[root@localhost opt]# ls
all.sql                      mysql_test.sql       test_test_2021-08-31.sql
mysql-5.7.20                 rh                   test_test.sql
mysql_all_2021-08-31.tar.xz  test_2021-08-31.sql
mysql-boost-5.7.20.tar.gz    test.sql 

four point three   Incremental backup can be performed every day to generate new binary log files (e.g. MySQL bin. 00000 3)

[root@localhost opt]# mysqladmin -u root -p flush-logs
Enter password: 
[root@localhost opt]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 201 8 31 / 21:19 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 154 8 31 / 21:19 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql  38 8 31 / 21:19 /usr/local/mysql/data/mysql-bin.index

four point four    Insert new data to simulate the addition or change of data

[root@localhost opt]# mysql -u root -p -e "insert into test.test values(3,'wangwu','male','game');"
Enter password: 
[root@localhost opt]# mysql -u root -p -e "insert into test.test values(4,'zhaoliu','female','reading');"
Enter password: 
[root@localhost opt]# mysql -u root -p -e "select * from test.test;"
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
|  3 | wangwu   | male   | game    |
|  4 | zhaoliu  | female | reading |
+----+----------+--------+---------+

four point five   Generate a new binary log file again

[root@localhost opt]# mysqladmin -u root -p flush-logs
#The database operation in step 4 above will be saved to the mysql-bin.000002 file. If the database data changes again, it will be saved to the mysql-bin.000003 file
Enter password: 
[root@localhost opt]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 201 8 31 / 21:19 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 750 8 31 / 21:22 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql  38 8 31 / 21:19 /usr/local/mysql/data/mysql-bin.index

four point six     View the contents of the binary log file

[root@localhost opt]# cp /usr/local/mysql/data/mysql-bin.000002 /opt/
[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 

 -- Base64 output = decode rows: use the 64 bit encoding mechanism to decode and read by line
-v: Show details

5. Incremental recovery - General recovery

five point one    Simulate recovery steps for lost changed data

[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;' 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =4;' 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
+----+----------+--------+---------+
[root@localhost opt]# mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
|  3 | wangwu   | male   | game    |
|  4 | zhaoliu  | female | reading |
+----+----------+--------+---------+

five point two   Simulate recovery steps for all lost data   

[root@localhost opt]# mysql -u root -p -e 'drop table test.test;'
Enter password: 
[root@localhost opt]# mysql -u root -p test < /opt/test_test_2021-08-31.sql 
Enter password: 
[root@localhost opt]# mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
|  3 | wangwu   | male   | game    |
|  4 | zhaoliu  | female | reading |
+----+----------+--------+---------+

6. Incremental recovery -- breakpoint backup

six point one    Check the binary file to determine the instruction number and time

[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210831 21:19:41 server id 1  end_log_pos 123 CRC32 0x04f0b9c1 	Start: binlog v 4, server v 5.7.20-log created 210831 21:19:41
# Warning: this binlog is either in use or was not closed properly.
# at 123
#210831 21:19:41 server id 1  end_log_pos 154 CRC32 0xfdc17b75 	Previous-GTIDs
# [empty]
# at 154
#210831 21:22:32 server id 1  end_log_pos 219 CRC32 0x5c0382cc 	Anonymous_GTIlast_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210831 21:22:32 server id 1  end_log_pos 294 CRC32 0x0e5399b9 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1630416152/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#210831 21:22:32 server id 1  end_log_pos 418 CRC32 0x81d36f40 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1630416152/*!*/;
insert into test.test values(3,'wangwu','male','game')
/*!*/;
# at 418
#210831 21:22:32 server id 1  end_log_pos 449 CRC32 0x275cc650 	Xid = 100
COMMIT/*!*/;
# at 449
#210831 21:22:57 server id 1  end_log_pos 514 CRC32 0xe136f17c 	Anonymous_GTIlast_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 514
#210831 21:22:57 server id 1  end_log_pos 589 CRC32 0xb9d988bb 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1630416177/*!*/;
BEGIN
/*!*/;
# at 589
#210831 21:22:57 server id 1  end_log_pos 719 CRC32 0x86622d35 	Query	thread_id=11	exec_time=0	error_code=0
SET TIMESTAMP=1630416177/*!*/;
insert into test.test values(4,'zhaoliu','female','reading')
/*!*/;
# at 719
#210831 21:22:57 server id 1  end_log_pos 750 CRC32 0x9f0efa32 	Xid = 103
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

# at 294
#210831 21:22:32
##User data of "wangwu" is inserted

# at 589
#210831 21:22:57
##User data of "zhaoliu" is inserted

six point two   Location based recovery

Only recover the data before operation ID "589", that is, do not recover the data of "zhaoliu"
Delete restore

[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;' 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =4;' 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
+----+----------+--------+---------+

Restore

[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='589' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
|  3 | wangwu   | male   | game    |
+----+----------+--------+---------+

Only recover the data of "zhaoliu" and skip the data recovery of "wangwu"
Delete restore

[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;' 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
+----+----------+--------+---------+

Restore

[root@localhost opt]# mysqlbinlog --no-defaults --start-position='589' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
|  4 | zhaoliu  | female | reading |
+----+----------+--------+---------+

six point three   Point in time based recovery

Only the data before 21:22:57 is recovered, that is, the data of "wangwu" is not recovered
Delete restore

[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =4;' 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
+----+----------+--------+---------+

Restore

[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2021-08-31 21:22:57' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
|  3 | wangwu   | male   | game    |
+----+----------+--------+---------+

Only recover the data of "zhaoliu" and skip the data recovery of "wangwu"
Delete restore

[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;' 
Enter password: 
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
+----+----------+--------+---------+

Restore

[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2021-08-31 21:22:57' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password: 
+----+----------+--------+---------+
| id | name     | sex    | hobby   |
+----+----------+--------+---------+
|  1 | zhangsan | male   | running |
|  2 | lisi     | female | singing |
|  4 | zhaoliu  | female | reading |
+----+----------+--------+---------+

summary

If you restore all data before an SQL statement, stop at the location node or time point of the statement
If you restore an SQL statement and all subsequent data, start from the location node or time point of the statement

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 jj