MySQL incremental backup (log backup)

MySQL database in the process of use, generally need to use the database for backup processing, for small amount of data can be through the mysqldump command for database full backup, but when the amount of database data reached a certain level, it is obvious that incremental backup more suitable.

MySQL incremental backups are made primarily from binary log files. The MySQL database automatically logs the user's operation of the MySQL database to a file in binary form, which can be restored using a backup file when the user wishes to recover.

This article mainly describes how to implement a backup of the database files on the local server to the remote server on the local server. (The following CMD needs to jump to the MySQL Bin folder path, eg:cd C:\Program files\mysql\mysql Server 5.7\bin)

1. Full backup: Database full backup on the remote server

cmd> mysqldump-u root-p Test > Test.sql: Enter a password that will back up the database named Test to the Mysqldump.exe file of the directory where Test.sql is located, Test.sql can write directly to the disk The absolute path is saved.

2. Restore Backup: Database Full restore on the local server

cmd> mysqldump-u root-p Test < test.sql: Enter the password, test.sql the absolute path of the restored file back to the copy on the local server, and implement the restore to the database after running.

3. Open 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 Log-bin logs to binlog.log files under the E-Drive Bak folder. You can configure the following:

# Binary Logging.
Log-bin=e:/bak/logbin.log

Make sure the path exists after you turn it on, and then restart the MySQL service to have 2 more files in the E:/bak directory

Logbin.index This is the incremental backup of the index file logbin.000001 the file that the user operates on the database 4. Log restore:Subsequent additions and deletions to the database are written to the corresponding records in the log, at which time the log files on the remote server are copied to the local server for an incremental backup restore. Log restore command: Restore all---------: Mysqlbinlog e:/mysqldb/logbin.000001 | 　　　　　　　　　 Mysql-u root-p pdserver Restore by Time-----: Mysqlbinlog--stop-datatime= "2017-04-10 20:50:00" e:/mysqldb/logbin.000001                             The above sentence means that it has been restored to 2017-04-10 20:50:00 stop following location to restore----: Mysqlbinlog--stop-position= "e:/mysqldb/logbin.000001" | The above statement means recovering to a position of 100.

MySQL incremental backup (log backup)