Shulou(Shulou.com)05/31 Report--

This article mainly explains "MySQLdump incremental backup, full backup and recovery method is what", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "MySQLdump incremental backup, full backup and recovery method is what" it!

It is important to back up your database in case of loss or corruption of database tables. If a system crash occurs, you want to be able to restore your tables to the state they were in at the time of the crash with as little data loss as possible. Scenario: Full backup every Sunday, MySQLdump incremental backup every day at 1pm

MySQLdump incremental backup configuration

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

log-bin=/opt/Data/MySQL-bin

The string after "log-bin=" is the log record directory, which is generally recommended to be placed on a disk different from the MySQL data directory.

MySQLdump incremental backup

Assume a full backup is performed at 1 p.m. on Sunday for MyISAM storage engine.

MySQLdump -lock-all-tables -flush-logs -master-data=2 -u root -p test > backup_sunday_1_PM.sql

For InnoDB replace-lock-all-tables with-single-transaction

flush-logs To end the current log, generate a new log file

The master-data=2 option records the name of the new log file after a full backup in the output SQL,

For reference in future recovery, for example, the backup SQL file exported contains:

CHANGE MASTER TO MASTER_LOG_FILE='MySQL-bin.000002′, MASTER_LOG_POS=106;

MySQLdump incremental backup Additional notes:

If MySQLdump is added-delete-master-logs clears previous logs to free up space. However, if the server is configured as a mirrored replication master, it is dangerous to delete MySQL binary logs with MySQLdump -delete-master-logs because the slave server may not have fully processed the contents of the binary log. In this case, it is safer to use PURGE MASTER LOGS.

Use MySQLadmin flush-logs periodically daily to create a new log and end the previous log writing process. And the previous log backup, for example, in the above example began to save the log file MySQL-bin.000002 , …

◆ Restore full backup

MySQL -u root -p < backup_sunday_1_PM.sql

◆ Restore incremental backups

MySQLbinlog MySQL-bin.000002 … |MySQL -u root -p Note that log files will also be written during this recovery process. If the data volume is large, it is recommended to turn off the log function first.

◆--compatible=name

It tells MySQL dump which database or older version of MySQL server the exported data will be compatible with. The values can be ansi, MySQL323, MySQL40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc. To use several values, separate them with commas. Of course, it is not guaranteed to be fully compatible, but as compatible as possible.

◆--complete-insert，-c

The exported data is in full INSERT mode with field names, that is, all values are written on one line. This improves insertion efficiency, but may be affected by the max_allowed_packet parameter and cause insertion failures. Therefore, this parameter needs to be used with caution, at least not recommended by me.

◆--default-character-set=charset

Specify which character set to use when exporting data. If the data table does not use the default latin1 character set, then this option must be specified when exporting, otherwise it will cause garbled characters after importing data again.

◆--disable-keys

Tells MySQLdump to add/* to the beginning and end of INSERT statements. 40000 ALTER TABLE TABLE DISABLE KEYS */; AND/*! 40000 ALTER TABLE TABLE ENABLE KEYS */; statement, which greatly speeds up insertion statements because it rebuilds the index after inserting all the data. This option only works with MyISAM tables.

◆--extended-insert = true|false

By default, MySQLdump is in--complete-insert mode, so if you don't want to use it, use this option and set it to false.

◆--hex-blob

Export binary string fields using hexadecimal format. This option must be used if binary data is available. The field types affected are BINARY, VARBINARY, BLOB.

◆--lock-all-tables，-x

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

◆--lock-tables

It is similar to--lock-all-tables, except that it locks the currently exported data table instead of locking all the tables in the library at once. This option only applies to MyISAM tables. If it is an Innodb table, use the--single-transaction option.

◆--no-create-info，-t

Export only data without adding CREATE TABLE statements.

◆--no-data，-d

No data is exported, only the database table structure.

◆--opt

This is just a quick option, equivalent to adding--add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset options as well. This option allows MySQLdump to export data quickly and export data back quickly. This option is 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. There may be problems if you export large databases.

◆--quick，-q

This option is useful when exporting large tables by forcing MySQLdump to fetch records directly from server queries rather than cache them in memory after fetching all records.

◆--routines，-R

Export stored procedures and custom functions.

◆--single-transaction

This option submits a BEGIN SQL statement before exporting the data. BEGIN does not block any applications and guarantees the consistent state of the database at the time of export. It applies only to transactional tables, such as InnoDB and BDB. This option and the--lock-tables option are mutually exclusive because LOCK TABLES makes any pending transactions implicitly commit. To export large tables, use the--quick option in conjunction.

◆--triggers

Trigger is also exported. This option is enabled by default and disabled with--skip-triggers.

At this point, I believe that everyone has a deeper understanding of "MySQLdump incremental backup, full backup and recovery method", may wish to actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.