MySQL database binary log backup and recovery steps

Basic concepts

Defined:

The binary log contains all the statements that have updated the data or have potentially updated the data (for example, a delete that does not match any of the rows).

Role:

1. The primary purpose of the binary log is to enable the database to be updated most likely as a result of the recovery, because the binary log contains all the updates that were made after the backup.

2. The binary log is also used to record all statements that will be sent to the server from the primary replication server.

Adverse effects:

When running the server, the performance is approximately 1% slower if binary logging is enabled.

MySQL default binary log is turned off, manually change the configuration file to open the binary log

Add under Mysqld under MY.CNF (Windows is My.ini)

Log-bin=mysql-bin

binlog_format=mixed //This line is to describe the pattern, and these two lines are copied directly to the MYSQLD

You can see the new log file when you restart the service.

Log location

>> if no filename is specified, MySQL uses the Hostname-bin file.

>> If a relative path is specified, the path is assumed to be relative to the data directory

>>mysql added a numeric index after the filename. So the final form of the file is Filename.number if you provide an extension in the log name (for example, –log

bin=file_name.extension), the extension is silently removed and ignored.

Replacement policy:

Use the index to loop the file, and the following conditions will loop to the next index

1. Server reboot

2. Server is updated

3. Log reached the maximum log length max_binlog_size

4. The log is refreshed mysql> flush logs;

Tools Introduction:

shell>>mysqlbinlog [option] binlogfile> NewFile

such as: D:mysqllog>mysqlbinlog binlog.000001 > 1.txt

An example:

log-bin= "D:/mysql/log/binlog" then, in this folder there will be files d:/mysql/log/binlog.000001 and so on

Problems

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 will not be cleared

Examples are as follows:

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

Query OK, 0 rows affected (0.01 sec)

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

Query OK, 0 rows affected (0.05 sec)

>>> or use command

RESET MASTER

Remove all previous binlog and regenerate the new Binlog

Suffix starting from 000001

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

This statement will not work, but will fail, along with an error.

However, if the secondary server is inactive and you happen to clean up one of the logs that it wants to read, the secondary server cannot replicate after it is started.

This statement is safe to run when the secondary server is replicating. You do not need to stop them.

2. The content configuration that records to the binary log knows

Binlog-do-db=sales Only Records Sales library

Binlog-ignore-db=sales except the sales library is not recorded, others are recorded

However, if the use $dbname is not used until the database is manipulated, then all SQL will not be logged

If use $dbname is used, the decision rule depends on the $dbname here rather than on the library of operations in SQL

3. Binary log inaccurate processing

By default, the binary logs are not synchronized with the hard disk every time you write. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the binary log in the final

Sentence lost.

To prevent this, you can use the Sync_binlog global variable (1 is the safest value, but also the slowest) so that the binary log synchronizes with the hard disk after every n binary log write.

Even if the sync_binlog is set to 1, there may be inconsistencies between the table content and the binary log content when a crash occurs.

If the MySQL server discovers that the binary log is shortened (that is, at least a successfully committed INNODB transaction) when the crash is restored,

If Sync_binlog = 1 and the hard disk/file system does not occur as needed (some unwanted), output the error message ("Binary log < name > smaller than expected").

In this case, the binary log is inaccurate and replication should start with a snapshot of the primary server's data.

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

The above sentence can refresh the log at the same time as the backup, generate a new log, so that the contents of the new log is equivalent to the incremental part of the backup, you can use this way to achieve the MySQL

A volume backup, which restores the database to a data backup node that was previously backed up, and then executes the binary log since the binary log essentially records the operation of all data changes,

To implement incremental backups or restore to a point in time

Clean log

If a large number of binary logs are generated on a daily basis, and these logs are not cleaned up for a long time, it can be a waste of disk space, so a regular cleanup log is a major DBA maintenance of MySQL

Job

1) RESET MASTER

In the folder where the log is viewed above, the binary log is named in mysql-bin.*,*, which represents the serial number of the log, the ordinal number is incremented, and the Mysql-bin.index is the log

The citation file, which records the maximum number of logs

We execute reset Master name Delete all log, new log start from scratch

2) PURGE Master LOGS to & PURGE master LOGS before

Execute the Purge MASTER LOGS to ' mysql-bin.****** ' command to delete all logs prior to ' the ' Hu ' number

Executes the purge MASTER LOGS before ' yyyy-mm-dd hh:mm:ss ' command to delete all logs before the ' Yyyy-mm-dd Hh:mm:ss ' time

3)-expire_logs_days

This parameter is to set the number of days the log expires, the expired log will be automatically deleted, which helps reduce the workload of our management log, need to modify MY.CNF

Expire_logs_days = 3//That is, save three days for the log, and automatically delete the expired log after three days

Recovery

Bin-log is a record of all MySQL event operations, when a catastrophic error in MySQL, can be done through the Bin-log full recovery, based on point-in-time recovery, and location-based recovery

Full recovery, assuming we use mysqldump to back up the database 2 o'clock in the morning every day, but at 9 in the next morning due to database failure, data is inaccessible, need to recover data, first

Restored to 2 o'clock in the morning with the files backed up yesterday in the early morning, using Mysqlbinlog to restore the Binlog since the mysqldump backup

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

So that the database can be completely restored to its full state before it crashes.

-----instance-------

A small number of databases we can make a full backup every day, because it will not take much time, but when the database is very large, we are not likely to do a full backup every day, and change to every

A full backup, a backup strategy like this one every day for incremental backups. The rationale for incremental backups is to use the MySQL binary log, so we must enable binary logging.

One, incremental backups

1, for example, we do a full backup in Sunday 11 o'clock in the afternoon:

/usr/local/mysql/bin/mysqldump--single-transaction--flush-logs--master-data=2--all-databases > Fullbackup_ Sunday_11_pm.sql

In the SQL file we see two lines:

–position to start replication or point-in-time recovery from

–change MASTER to Master_log_file= ' bin-log.000002′, master_log_pos=107;

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

2, then in Monday 11 o'clock in the afternoon we'll do an incremental backup:

Mysqladmin Flush-logs

A new binary log file will be generated bin-log.000003,bin-log.000002 saves all changes since Sunday 11 o'clock in the afternoon and we just need to keep this file

It's OK to go to a safe place. Then Tuesday we do an incremental backup, or execute the same command, when we save the bin-log.000003 file.

Second, restore backup

For example, Wednesday noon 12 O'Clock a failure, then need to restore, we first import Sunday full backup:

Mysql-u Root-p < Fullbackup_sunday_3_am.sql

We then import incremental backups for Monday and Tuesday:

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

Now that we have recovered all the backup data, we can also find bin-log.000004 to recover the latest data.

Mysqlbinlog add--database "specific library" to restore the incremental information of specific libraries

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