MySQL database log, backup and rollback operations

First, open the binary log configuration:

Under Windows systems, when configuring directory locations for MySQL's My.ini log-bin and so on, assume that you want to configure the Log-bin log to the Mysqllog file for the D drive as Binlog. You can configure the following:

[Mysqld]

log-bin= "D:\mysqllog/binlog" Note: The last path here is "/" instead of "\" on the Windows file system

Two, binary log processing:

1. View

Use Mysqlbinlog.exe. Can be found in the bin directory in the server where the directory is installed.

View: cmd> mysqlbinlog.exe filename (add--database [databasename] to view SQL logs for a particular database)

2. Pause Logging

Mysql> set SQL_LOG_BIN=0/1 to 0: indicates pause recording; otherwise 1;

3. Delete binary log

Mysql> Resetmaster; (Note that will remove all, be careful!!!!) ）

Mysql> PURGE MASTER LOGS to Filename.number: Delete log less than number;

Mysql> PURGE MASTER LOGS before ' yyyy-mm-dd hh:MM:ss ': delete log before log;

4. Refresh the log so that the current log number is increased by one

Use MysqlAdmin.exe. Can be found in the bin directory of the server where the directory is installed.

For example, the current server log file is: binlog.000002

Then execute: cmd> mysqladmin-u root-p flush-logs: Enter password

After execution, a new log file is added to the binary log directory: binlog.000003. Thereafter, the server's new log will be written to binlog.000003.

This is a good practice for incremental backup use.

Third, database backup and restore

Use Mysqldump.exe. Can be found in the bin directory of the server where the directory is installed.

1. Full backup:

cmd> mysqldump-u root-p Test > Test.sql: Entering the password will back up the database named Test to the Mysqldump.exe file in the directory where the Test.sql resides.

2. Fully back up the entire server database and refresh the log file:

The purpose of refreshing the log file is to tell the server to write the log to the new log file, so that future backups do not have to be fully backed up, only the new log files need to be backed up. Implementing "Incremental Backup"

Cmd> mysqldump-u root-p--flush-logs will generate more new log files after the execution. If you back up 3 databases, 3 more log files will be generated. Because backups of 3 databases are considered to be performed by 3 different transactions. Therefore, plus: after the--single-transaction tag, it will act as a transaction. Therefore, only one log file is added.

3. Restore operation:

Note: As a general rule, the binary log of the system is turned on, so when you perform a restore, the restored SQL code is also written to the log. When it is clear that this code does not necessarily need to exist. Therefore, it can be avoided by pausing the binary log, but when I tested it, I found that the SET sql_log_bin=0 was not able to do it. Therefore, the following practices can be used:

1), refresh a log file: cmd> mysqladmin-u root-p flush-logs. (this file is used to store the log generated during the restore without affecting the original log)

2), perform restore a database:test:cmd> mysql-u-root-p < Test.sql;

3), then execute the "increment" log to the database test (be careful not to perform the binary log of the other database):

cmd> Mysqlbinlog Log.number | Mysql-u root-p test.

You can add a parameter for--database [name] to specify that you only select SQL for incrementally restoring a database. As for the test database:

cmd> mysqlbinlog log.number--database Test | Mysql-u root-p Test

4), the log files generated during this process can be considered for deletion. Because it's the original data.

MySQL database log, backup and rollback operations