Mysqldump incremental backup, full backup and recovery

It is important to back up your database if the database tables are missing or corrupted. If a system crash occurs, you certainly want to be able to recover your table as much as possible with the least amount of data recovered to the state when the crash occurred. Scenario: Perform a full backup every Sunday, mysqldump incremental backup Every 1 o'clock in the afternoon

Mysqldump Incremental Backup Configuration

The prerequisite for performing an incremental backup is that MySQL turns on the log-bin log switch, for example, by adding a my.ini or my.cnf

Log-bin=/opt/data/mysql-bin

The string after "Log-bin=" is recorded in the log directory, and is generally recommended on a different disk than the MySQL data directory.

Mysqldump Incremental Backup

Assume that Sunday 1 o'clock in the afternoon performs a full backup for the MyISAM storage engine.

Mysqldump–lock-all-tables–flush-logs–master-data=2-u root-p Test > Backup_sunday_1_pm.sql

Replace –lock-all-tables with –single-transaction for InnoDB
Flush-logs to end the current log, generate a new log file
The master-data=2 option will record the name of the new log file after the full backup in output SQL.

References for later recovery, such as the output of a backup SQL file, contain:

Change MASTER to Master_log_file= ' mysql-bin.000002′, master_log_pos=106;

Mysqldump Incremental Backup Additional instructions:

If Mysqldump plus –delete-master-logs clears the previous log to free up space. However, if the server is configured as a mirrored replication master, it is dangerous to delete the MySQL binary log with Mysqldump–delete-master-logs because the contents of the binary log may not be fully processed from the server. In this case, it is more secure to use PURGE MASTER logs.

Use Mysqladmin flush-logs daily to create a new log and end the previous log write process. And take the previous log backup, for example in the previous example, to start saving the log file under the Data directory mysql-bin.000002,

###################
Recovering a full backup
Mysql-u Root-p < Backup_sunday_1_pm.sql

Recovering incremental Backups
Mysqlbinlog mysql-bin.000002 ... | Mysql-u root-p Note that the recovery process will also be written to the log file, if the amount of data is large, it is recommended to turn off the log function

--compatible=name
It tells MySQLdump that the exported data will be compatible with which database or which old version of the MySQL server. Values can be ANSI, MySQL323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. , to use a few values, separate them with commas. Of course, it is not guaranteed to be fully compatible, but is as compatible as possible.

--complete-insert,-c
The exported data takes the full INSERT method that contains the field name, that is, all the values are written in one line. This can improve insertion efficiency, but may be affected by the Max_allowed_packet parameter, causing the insert to fail. Therefore, it is prudent to use this parameter, at least I do not recommend it.

--default-character-set=charset
Specifies which character set to export the data in, and if the data table is not in the default Latin1 character set, you must specify this option when exporting, or you will have garbled problems after importing the data again.

--disable-keys
Tell MySQLdump to add/*!40000 ALTER table Table DISABLE KEYS at the beginning and end of the INSERT statement */; and/*!40000 ALTER table Table ENABLE KEYS */; statement, which can greatly increase the speed of the INSERT statement, because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.

--extended-insert = True|false
By default, MySQLdump turns on--complete-insert mode, so if you don't want to use it, use this option to set its value to false.

--hex-blob
Export binary string fields using hexadecimal format. This option must be used if you have binary data. The field types affected are BINARY, VARBINARY, BLOB.

--lock-all-tables,-x
Before starting the export, the commit request locks all tables in all databases to ensure data consistency. This is a global read lock, and the--single-transaction and--lock-tables options are turned off automatically.

--lock-tables
It is similar to--lock-all-tables, but locks the currently exported data table instead of locking down all the tables in the library at once. This option applies only to the MyISAM table, if the Innodb table is available with the--single-transaction option.

--no-create-info,-t
Exports only data without adding a CREATE TABLE statement.

--no-data,-d
No data is exported, only the database table structure is exported.

--opt
This is just a shortcut option, which is equivalent to adding--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables at the same time-- Quick--set-charset option. This option allows MySQLdump to export data quickly, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with--skip-opt. Note that if you run MySQLdump without specifying the--quick or--opt option, the entire result set is placed in memory. Problems can occur if you export a large database.

--quick,-q
This option is useful when exporting large tables, forcing MySQLdump to cache records directly from the server query instead of getting all the records back into memory.

--routines,-r
Export stored procedures and custom functions.

--single-transaction
This option submits a begin SQL statement before exporting the data, and begin does not block any applications and ensures consistent state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB. This option and the--lock-tables option are mutually exclusive because lock tables causes any pending transactions to be implicitly committed. To export large tables, you should use the--quick option together.

--triggers
Export the trigger at the same time. This option is enabled by default and is disabled with--skip-triggers.

Mysqldump incremental backup, full backup and recovery