MySQL database binary log backup and recovery steps

Using binary log backup and database restoration in mysql is a relatively advanced database backup operation method. enable the mysql-bin function in ini. I will introduce it later.

Basic Concepts

Definition:

The binary log contains all statements that update data or have potentially updated data (for example, no DELETE matching any row.

Purpose:

1. The main purpose of the binary log is to restore to make it possible to update the database as much as possible, because the binary log contains all updates made after backup.

2. Binary logs are also used to record all statements that will be sent to the slave server on the master replication server.

Adverse effects:

If binary logs are enabled when the server is running, the performance is about 1% slower.

MySQL disables binary logs by default. Manually change the configuration file to enable binary logs.

In my. cnf (my. ini in windows), add

Log-bin = mysql-bin

Binlog_format = mixed // This line describes the mode. Copy the two rows directly to mysqld.

After restarting the service, you can see the new log file.

Log location

> If no file name is specified, Mysql uses the hostname-binfile.

> If a relative path is specified, it is assumed that the path is relative to the data directory.

> Mysql adds a digital index after the file name. Therefore, the final form of the file is filename. number. If you provide the extension (for example,-log

Bin = file_name.extension), the extension is removed and ignored.

Change Policy:

Use indexes to loop files. The following conditions will be used to loop to the next index.

1. Server restart

2. The server is updated.

3. The maximum log length is max_binlog_size.

4. The log is refreshed by mysql> flush logs;

Tool introduction:

Shell> mysqlbinlog [option] binlogFile> newfile

For example, D: mysqllog> mysqlbinlog binlog.000001> 1.txt

Example:

Log-bin = "D:/mysql/log/binlog", the file D:/mysql/log/binlog.000001 will appear in this folder.

FAQs

1. How to clear binlog

>>> Use the following two Commands

PURGE {MASTER | BINARY} logs to 'Log _ name' // log_name will not be cleared

PURGE {MASTER | BINARY} logs before 'date' // date is not cleared

Example:

Mysql> purge master logs to 'binlog. 100 ′;

Query OK, 0 rows affected (0.01 sec)

Mysql> purge master logs before '2017-09-22 00:00:00 ′;

Query OK, 0 rows affected (0.05 sec)

>>> Or use a command

RESET MASTER

Delete all previous binlogs and generate new binlogs.

The suffix starts from 000001.

Note: If you have an active slave server that is currently reading one of the logs you are trying to delete,

This statement does not work, but fails with an error.

However, if the slave server is slave and you happen to have cleared one of the logs it wants to read, the slave server cannot be copied after it is started.

This statement can be run safely when the slave server is being copied. You do not need to stop them.

2. Record the configuration of binary logs

Binlog-do-db = sales only records the sales database

Binlog-ignore-db = except for the sales database, no records are recorded, and all other records are recorded.

However, if you do not use $ dbname before operating the database, all SQL statements will not be recorded.

If use $ dbname is used, the Judgment Rule depends on $ dbname, instead of the database operated in SQL.

3. Inaccurate processing of binary logs

By default, binary logs are not synchronized with the hard disk each time they are written. Therefore, if the operating system or machine (not just the MySQL server) crashes, it is possible that the last

Sentence loss.

To prevent this situation, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize binary logs with the hard disk after each N binary logs are written.

Even if sync_binlog is set to 1, the table content and binary log content may be inconsistent in the event of a crash.

If the MySQL server finds that the binary log is shortened when the crash is restored (that is, at least one InnoDB Transaction successfully committed is missing ),

If sync_binlog = 1 and the hard disk/file system can be synchronized as needed (some do not need it, the error message is output ("binary log <Name> is smaller than expected ").

In this case, the binary log is inaccurate and the replication should begin with the data snapshot of the master server.

Mysqldump -- databases repltest -- flush-logs -- opt repltest> q:/repltest_full_01. SQL

The above statement can be used to refresh the log while backing up and generate a new log, so that the content in the new log is equivalent to the incremental part after the backup. This method can be used to achieve mysql

Volume backup: restores the database to a data backup node that was previously backed up, and then executes the subsequent binary log, because the binary log essentially records all data change operations,

Incremental backup or restoration at a certain point in time

Clear logs

If a large number of binary logs are generated every day and these logs are not cleared for a long time, the disk space will be greatly wasted. Therefore, regular log cleanup is a key for DBA to maintain mysql.

Work

1) RESET MASTER

In the above view the log storage folder, the binary log naming format is mysql-bin. *, * represents the log serial number, the serial number is increasing, where there is a mysql-bin.index is the log

Cited file, which records the maximum number of logs

Execute the reset master name to delete all logs. The new logs start from scratch.

2) purge master logs to & PURGE MASTER LOGS BEFORE

Run the purge master logs to 'mysql-bin. ******* 'command TO delete all LOGS before the ******' number.

Run the purge master logs before 'yyyy-mm-dd hh: mm: ss' command: all logs earlier than 'ss time' are deleted.

3)-EXPIRE_LOGS_DAYS

This parameter is used to set the log Expiration days. Expired logs will be automatically deleted, which reduces the workload of log management and requires modifying my. cnf.

EXPIRE_LOGS_DAYS = 3 // logs are saved for three days. logs that expire three days later are automatically deleted.

Restore

Bin-log records all mysql event operations. When a catastrophic mysql error occurs, you can use bin-log for complete recovery, time point-based recovery, and location-based recovery.

Complete recovery: assume that we use mysqldump to back up the database at every morning. However, the database fails at the next day and the data cannot be accessed. Therefore, we need to restore the data first.

Use the files backed up in the early morning of yesterday to restore to the status at AM. Use mysqlbinlog to restore the binlog since mysqldump backup.

/Usr/local/mysql/bin/mysqlbinlog bin.000001 | mysql-u root-p

In this way, the database can be completely restored to the full State before the crash.

----- Instance -------

For a small number of databases, we can perform full backup every day, because it does not take much time, but when the database is very large, we are unlikely to perform a full backup every day, and change it

One full backup, one Incremental Backup every day similar to this backup policy. The principle of Incremental backup is to use mysql binary logs, so we must enable the binary log function.

I. Incremental Backup

1. For example, we make a full backup at eleven o'clock P.M. on Sunday:

/Usr/local/mysql/bin/mysqldump -- single-transaction -- flush-logs -- master-data = 2 -- all-databases> fullbackup_sunday_11_PM. SQL

In the SQL file, we will see two rows:

-Position to start replication or point-in-time recovery from

-Change master to MASTER_LOG_FILE = 'bin-log.20.02 ', MASTER_LOG_POS = 107;

The second line contains the information we need, that is, the changes made after the backup will be saved to the bin-log.000002 binary.

2. perform an incremental backup at eleven o'clock P.M. on Monday:

Mysqladmin flush-logs

A new binary log file bin-log.000003 will be generated, and the bin-log.000002 will save all the changes from Sunday eleven o'clock P.M. till now, we just need to replace this file

To a safe place. Then on Tuesday we made an incremental backup, or execute the same command, and then we saved the bin-log.000003 file.

Ii. Restore backup

For example, if a fault occurs at noon on Wednesday and needs to be restored, we first import the complete backup on Sunday:

Mysql-u root-p <fullbackup_sunday_3_AM. SQL

Next we import incremental backups on Monday and Tuesday:

/Usr/local/mysql/bin/mysqlbinlog bin-log.000002 bin-log.000003 | mysql-u root-p

At this point we have recovered all the backup data, we can also find the bin-log.000004 to further restore the latest data.

After mysqlbinlog is added, -- database "specific database" can restore the incremental information of a specific database.

Add -- stop-date = "13:15:00" to restore the specific time point of the log