Mysql database logs, backup and rollback operations

1. Enable the binary log configuration: in Windows. if you want to configure the directory location such as the log-bin of ini to the mysqllog file on disk D as binlog. You can configure it as follows: [mysqld] log-binD: mysqllogbinlog Note: The last path here uses instead

1. Enable the binary log configuration: in Windows. if you want to configure the directory location such as the log-bin of ini to the mysqllog file on disk D as binlog. You can configure it as follows: [mysqld] log-bin = D: \ mysqllog/binlog Note: The last path here uses/instead

1. Enable the binary log Configuration:

In Windows, if you want to configure directory location for mysql's my. ini log-bin and so on, assume that you want to configure the log of log-bin to the mysqllog file of disk D as binlog. The configuration is as follows:

[Mysqld]

Log-bin = "D: \ mysqllog/binlog" NOTE: The last path here uses "/" instead of "\" in the Windows File System

Ii. binary log processing:

1. View

Mysqlbinlog.exe. You can find it in the Bin directory of the Server in the installation directory.

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

2. Stop Logging

Mysql> SET SQL _LOG_BIN = 0/1 to 0: indicates that the record is paused; otherwise, it is 1;

3. Delete binary logs

Mysql> RESETMASTER; (Note that all data will be deleted. Be careful !!!!)

Mysql> purge master logs to filename. number: delete LOGS with a number less than number;

Mysql> purge master logs before 'yyyy-mm-dd hh: MM: ss': logs before deletion;

4. Refresh the log to add one to the current log number.

Using mysqladmin.exe. You can find it in the Bin directory of the Server in the installation directory.

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

Run cmd> mysqladmin-u root-p flush-logs: enter the password.

After execution, a new log file binlog.20.03 is added to the binary log directory. Then, the new log of the server will be written to binlog.20.03.

This method is suitable for Incremental backup.

Iii. database backup and Restoration

Export mysqldump.exe. It can be found in the Bin directory of the Server in the installation directory.

1. Full backup:

Cmd> mysqldump-u root-p test> test. SQL: enter the password to transfer the data library named testto the test. SQL file in the directory where mysqldump.exe is located.

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

The purpose of refreshing log files is to tell the server to write logs to new log files. Therefore, you do not need to back up the logs in the future. Instead, you only need to back up the new log files. Implement "Incremental Backup"

Cmd> after mysqldump-u root-p -- flush-logs is executed, more new log files will be generated. For example, three log files are generated when three databases are backed up. Because the backup of the three databases is considered to be executed by three different transactions. Therefore, after the -- single-transaction tag is added, it will be used as a transaction. Therefore, only one log file is added.

3. restore operation:

Note: Generally, binary logs of the system are opened. Therefore, the restored SQL code is also written to the log during restoration. Obviously, these codes do not necessarily need to exist. Therefore, binary logs can be paused to avoid this problem. However, during the test, I found that SET SQL _LOG_BIN = 0 cannot be used. 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 logs generated during restoration without affecting the original logs)

2) execute the command to restore a database: test: cmd> mysql-u-root-p <test. SQL;

3) execute the incremental logs to the database test (Be careful not to execute the binary logs of other databases ):

Cmd> mysqlbinlog log. number | mysql-u root-p test.

You can add the -- database [name] parameter to specify that you only select to incrementally restore the SQL statement of a database. For example, for the test database:

Cmd> mysqlbinlog log. number -- database test | mysql-u root-p test

4) You can delete the log files generated during this process. Because it is the original data.