Enable binary log function

Set the storage directory and file name prefix of binary log files in the configuration file / etc/my.cnf:

log-bin=/var/lib/mysql/mybinlog/mysql-bin
server-id=1 

Enable exclusive tablespaces:

innodb_file_per_table=1

explain:

1. According to the above configuration, after the database service is restarted, a binary log file will be automatically generated and saved in the / var/lib/mysql/mybinlog directory, and the name of the binary log file will be prefixed with MySQL bin, followed by the serial number 00000 1.

The name of the log file generated for the first time is mysql-bin.00000 1; If you refresh the log, a new log file will be generated with the name of mysql-bin.00000 2; If you continue to refresh the log, a log file named mysql-bin.00000 3 will be generated, and so on.

2. The variable server ID must be configured while configuring the variable log bin, otherwise the database service cannot be restarted at all.

3. If exclusive tablespace is enabled, each table has its own independent tablespace file; The default is shared tablespace, that is, all databases use one tablespace. It is unclear whether this function must be turned on

Turn binary logging off / on

mysql> set global sql_log_bin=0;
mysql> set global sql_log_bin=1;

Refresh binary log files

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

Note:
1. A new binary log file will be generated
2. The database operation logs in the old log file will not be copied to the new log file

View the storage location of binary log files

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------+
| Variable_name                   | Value                                   |
+---------------------------------+-----------------------------------------+
| log_bin                         | ON                                      |
| log_bin_basename                | /var/lib/mysql/mybinlog/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mybinlog/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                     |
| log_bin_use_v1_row_events       | OFF                                     |
| sql_log_bin                     | ON                                      |
+---------------------------------+-----------------------------------------+
6 rows in set (0.01 sec)

Look at the variable log_ bin_ The value of basename shows that the binary log files are stored in the directory / var/lib/mysql/mybinlog /

The essence of recovering data using binary log files

In fact, the binary log mechanism is to record some DDL and DML operation statements into the binary log file. If you want to use the file to recover data, you must find the command statements of relevant data, and then execute these command statements. Therefore, you need to understand the contents of the log file and be able to extract the relevant command statements. For the command to extract the command statement you want, see the following.

Binary log extraction / export to script file

--Start position: specifies where to start exporting logs
--Stop position: Specifies the end position of the export log
--Start datetime: Specifies the time from which to export the log. The time format is: 2005-12-25 11:25:56
--Stop datetime: Specifies the end time of the export log

[root@htlwk0001host ~]# mysqlbinlog --no-defaults  --start-position=1125 --stop-position=1344 /var/lib/mysql/mybinlog/mysql-bin.000004  > /root/test/001.sql

View the last location of the current binary log

mysql> show master status;  

It doesn't make sense to view the last location of the log file.

View the contents of the binary log file

[root@htlwk0001host ~]# mysqlbinlog --no-defaults /var/lib/mysql/mybinlog/mysql-bin.000004

Execute SQL script files to recover data

[root@htlwk0001host ~]# mysql -uroot -pqpw123.com -D test < /root/test/004.sql;

Binary log analysis

Specify recovery time

For MySQL 4.1.4, you can specify the start and end time of data recovery through -- start date and -- stop date options in the mysqlbinlog statement.

For example, suppose you accidentally delete a table at 10:00 this morning (today is April 20, 2005). To restore tables and data, you can first restore the backup of the previous night (assuming that you regularly backup the database every morning, and a new binary log file mysql-bin.00004 is automatically generated after backup), and then execute the following statement:

[root@htlwk0001host ~]# mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000004 | mysql -uroot -p123456

There may be a problem with this command, because there are many database operation statements before 9:59:59 on April 20, 2005. These operation statements are not only the operation statements between the last backup time and 9:59:59 on April 20, 2005, but also countless operation statements before the last backup time. Are these operation statements also included? Are they all executed? There must be an error in the implementation, so I feel there is a problem.

Then you may want to recover the data generated after 10:01:00 on April 20, 2005. You can use the date and time to run mysqlbinlog again:

[root@htlwk0001host ~]# mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -uroot -p123456

Specify recovery location

Instead of specifying the date and time, you can use the options of mysqlbinlog -- start position and -- stop position to specify the log location. Their function is the same as the start and end date options, except that they give the position number in the log content. Using log location is a more accurate recovery method, especially when many transactions occur while executing destructive SQL statements. To determine the location number, you must check the contents of the log file to find the time range in which unexpected transactions are executed. It is recommended that you export the log data of this time range to a text file for inspection. You can execute the following statement:

[root@htlwk0001host ~]# mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/mysql-bin.000004 > /tmp/mysql_restore.sql

Execute all transactions up to the stop location, that is, restore the data to the specified stop location:

[root@htlwk0001host ~]# mysqlbinlog --stop-position="368312" /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd 

All transactions from the given starting position to the end of the binary log will be resumed:

[root@htlwk0001host ~]# mysqlbinlog --start-position="368315" /var/log/mysql/mysql-bin.000004 | mysql -u root -pmypwd

Incremental backup demo

Backup database

First, make a complete backup of a database:

[root@htlwk0001host ~]# mysqldump -h10.6.208.183 -uroot -p123456  -P3306 --single-transaction  --master-data=2  test > test.sql

After executing the above command statements, a backup script file test.sql will be generated in the current working directory.

In the test.sql file, we will see:

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;

It means that all changes after backup will be saved to bin-log.000002 binary file, and the corresponding position of backup data in the log file is 107.

Operation data

Then insert two records into the student table under the test database, and then execute the flush logs command to generate a new binary log file bin-log.000003. Before flush logs, the database change operation log is saved in the file bin-log.000002, and the operation log added to the previous table student is saved in the file bin-log.00002.

Then add two records to the teacher table under the test database, and then delete the student table and the teacher table. After the command flush logs is executed, the operation logs of adding and deleting tables are recorded in the new binary log file bin-log.000003.

Recover data

Next, let's restore the data. First, import the full backup data:

[root@htlwk0001host ~]# mysql -h10.6.208.183 -uroot -p123456  -P3306 < test.sql

Recover bin-log.000002:

[root@htlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/bin-log.000002 | mysql -h10.6.208.183 -uroot -p123456 -P3306

Recovery part bin-log.000003:

You need to check the contents of the file bin-log.000003, find the location where the data is deleted, then export the log data before this location to the SQL script file, and then execute the script file to recover the deleted data; Of course, after you find the location point to recover the data, you can also directly specify the location point and recover the data through the following statement:

[root@htlwk0001host ~]# mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000003 --stop-position=208 | mysql -h10.6.208.183 -uroot -p123456  -P3306

Error summary

unknown variable 'default-character-set=utf8mb4'

[root@htlwk0001host ~]# mysqlbinlog /var/lib/mysql/mybinlog/mysql-bin.000004
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'

reason:
The tool mysqlbinlog cannot recognize the instruction default character set = utf8mb4 in the configuration of binlog.

solve:

1. Add -- no defaults parameter
When using the command mysqlbinlog, add the option -- no defaults.
For example:

[root@htlwk0001host ~]# mysqlbinlog --no-defaults /var/lib/mysql/mybinlog/mysql-bin.000004

2. Modify the configuration file my.cnf
Note out default character set = utf8mb4 and use character set server = utf8mb4. After setting, you need to restart the MySQL service.