MySQL uses mysqldump backup, restore

  mysqldump is a utility that MySQL uses to store databases. It primarily produces a SQL script that contains the commands necessary to recreate the database from scratch Create table insert mysqldump  syntax  :     Default configuration Read path:   /etc/mysql/my.cnf     /etc/my.cnf    ~/. My.cnf           usage: mysqldump [options]  database [tables]          OR      mysqldump [options] --databases [options] db1 [db2 db3 ...]           or     mysqldump [ options] --all-databases [options]     Common Parameters  :           -u, --user=name                 named User          -h, --host=name                designated host           -p, --password[=name]           Specify password          -A, --all-databases             Specify all libraries,-A and--all-databases do not use both           -B, --databases                 specify only--databases to back up a single database, separate multiple databases with spaces, or if you do not specify--databases to back up the entire database ; Specify only dbname to backup a single table; Use spaces between multiple tables         --master-data[=#]              0  = off, default =  0                                         1  represents the beginning of the print Change master command information, Useful in copying scenes                                          2  means to add comment information, record the current binary log and where it is located           --single-transaction           if all are &NBSP;INNODB engine tables, you do not need to use the  --lock-all-tables; based on this option, a hot standby InnoDB table can be implemented; A single large transaction is initiated, and the impact on the global service may be persistent          --lock-all-tables              Request a lock table before performing a backup, typically locking only the database and tables that are currently being backed up          --add-drop-database             delete a corresponding database that already exists before recovering           --add-drop-table               Delete a table that already exists before recovering           -C, --compress                  Compress data first and then transfer it over the network           -E, --events                    backing up data at the same time, backup event Scheduler code          -r, - -routines                 Back up data at the same time, backup stored procedures and storage functions          -d, --no-data                   indicates that a backup-only table structure does not back up data, duplicate table structures, Empty libraries are useful for other servers. Not available at any other time           --opt                           also launches various advanced options       Backup Method  :        full + increment + binary log              prerequisites To add parameters in the configuration  :                default-storage-engine = InnoDB         Set the default storage engine to innodb                innodb_file_per_table=1                 set a single table space per table                 log-bin=mysql-bin                       Enable binary features                     backup Process  :           Full-scale backup  :                using the InnoDB engine, Backup using the--single-transaction option does not require locking the table to implement the hot standby; using a non-INNODB engine, the backup needs to use the--lock-all-tables option to request lock all tables after starting backup               mysqldump -uroot -pmypass  -- Single-transaction  --all-databases --master-data=2  > /backup/mysql_ fullbak.sql          Incremental Backup  :              mysql> SHOW MASTER STATUS;             See which log file and location are currently in                       less  /backup/ mysql_fullbak.sql       see where to start              mysqlbinlog -- start-position=4579 --stop-position=4868 mysql-bin.000015 > /backup/increment.sql    Specify start and end locations export to incremental backup            binary log backup:             mysql> INSERT INTO test  (name,age )  VALUES  (' Tom ');           an incremental backup, insert a piece of data             mysql> DROP DATABASE hello;                                       Delete Database at this time            mysql> SHOW MASTER  status;                                       See which log file and location are currently in            mysqlbinlog -- start-position=4868 mysql-bin.000015               See the start of the binary log before the database crashes, the end location must be before the database is deleted location             mysqlbinlog --start-position=4868 --stop-position=5062 mysql-bin.000015 > / tmp/hello.sql     binary log start-up location to pre-crash location data export     recovery process  :          Prerequisites  :  Empty All files in the data directory, reinitialize the database            mysql> SET GLOBAL sql_log_bin=0;          Recovery requires a temporary shutdown of the binary log, such as not shutting down, the recovery data will be logged into the binary log to increase the log management difficulty          mysql> FLUSH LOGS;                        Manually scrolls the binary log          mysql <  /backup/mysql_ fullbak.sql       restore full-scale backup           mysql <  /backup/increment.sql           Restore Incremental Backups          mysql <  /tmp/hello.sql                   export binary data before resuming crashes         need to use  mysqlcheck -c  --all-databases  after recovery   Check all the tables for problems, remember to do a full backup of the database after the completion of the .     to this, using Mysqldump Backup recovery completed, if there are errors please advise!

This article is from "Why No!" blog, be sure to keep this source http://mydove.blog.51cto.com/8487472/1790005

MySQL uses mysqldump backup, restore