Restore the MySQL database using the mysqldump command backup

1. Various usage instructions

A. The simplest usage:

[database name[dump file]   

The above command backs up the specified database to a dump file (dump file), such as:

> Test. Dump

The generated Test.dump file contains the Build Table statement (the build database structure OH) and insert statement that inserts the data.

B.--opt

If you add the--opt parameter, the resulting dump file is slightly different:

. The Build Table statement contains the drop table if exists tableName

. Insert contains a lock table statement before lock tables tableName Write,insert contains unlock tables

C. Cross-host backup

Use the following command to copy the Sourcedb on host1 to Host2 Targetdb, provided the HOST2 database has been created on the TARGETDB host:

--host=host1--opt sourcedb| mysql--host=host2-c targetdb

-C indicates data transfer between hosts using compression

D. Backing up the table structure only

--no-data--databases mydatabase1 mydatabase2 mydatabase3 > Test.dump

Only the table structure will be backed up. --databases indicates the database to be backed up on the host. If you want to back up all the databases on a MySQL host, you can use the--all-databases option, as follows:

--all-databases> Test.  Dump  

E. Recovering a database from a backup file

[database name[backup file name]   

　　Backing up multiple databases

Grammar:

Mysqldump-u username-p--databases dbname2 dbname2 > Backup.sql

The--databases option is added, followed by multiple databases

Mysqldump-u root-p--databases Test mysql > D:\backup.sql

　　Back up all databases

The syntax for backing up all databases with the mysqldump command is as follows:

Mysqldump-u username-p-all-databases > Backupname.sql

Example:

Mysqldump-u-root-p-all-databases > D:\all.sql

2, with Linux cron command to achieve scheduled backup

For example, to back up all the databases on a host and compress the dump file to GZ format every 1:30, add the following line of code to the/etc/crontab configuration file:

--all-databases | gzip >/mnt/disk2/database_ ' Date ' +%m-%d-%y '. sql.gz

The preceding 5 parameters represent minutes, hours, days, months, years, and asterisks, respectively. Date ' +%m-%d-%y ' gets the mm-dd-yyyy format of the current date.

3 . A complete shell script backup MySQL Database example

#Vi/backup/backup.sh#!bin/BASHCD/backupecho  "you is in backup Dir" mv backup*/oldbackupecho " old DBS is moved to oldbackup folder "file = Backup- $Now. sqlmysqldump-u user-p password database-name > $File echo your database backup successfully completed "  

The above script file is saved as backup.sh, and two directories/olcbackup and/backup have been created in the system. Each time the backup.sh is executed, the files that begin with the/backup directory are moved to the/oldbackup directory.

The execution plan for the above script is as follows:

#crontab-E1 * * */backup.  SH  

4,mysqldump full-volume backup +mysqlbinlog binary log Incremental backup

Recovering data from a mysqldump backup file will lose the updated data from the backup point, so you will also need to combine the Mysqlbinlog binary log incremental backup. Make sure that the following configuration is included in My.ini or my.cnf to enable binary logging, or mysqld---log-bin:

[Mysqld]log-bin=mysql-bin

The mysqldump command must bring the--flush-logs option to generate the new binary log file:

--single-transaction--flush-logs--master-data=2 > Backup.sql

This generates an incremental binary log file such as mysql-bin.000003, so the data is restored as follows:

12	shell> mysql -uroot -pPwd < backup_sunday_1_PM.sqlshell> mysqlbinlog mysql-bin.000003 | mysql -uroot -pPwd

In addition Mysqlbinlog can also specify--start-date 、--stop-date 、--start-position and--stop-position parameters, Used to accurately recover data to a certain point before or skip the middle of a period of time to recover data, directly extracts the MySQL document description of the relevant content as follows:

5.9.3.1. Specifying the recovery time for MySQL 4.1.4, you can specify the start and end time of the DateTime format in the Mysqlbinlog statement through the--start-date and--stop-date options. For example, suppose that today 10:00 (today is April 20, 2005), execute the SQL statement to delete a large table. To restore the table and data, you can restore the backup of the previous night and enter: Mysqlbinlog--stop-date= "2005-04-20 9:59:59"/var/log/mysql/bin.123456 | Mysql-u root-pmypwd The command restores all data as of the date and time given in the--stop-date option in datetime format. If you do not detect a few hours after entering the wrong SQL statement, you may want to restore the activity that occurs later. According to these, you can use the date and time to run again mysqlbinlog:mysqlbinlog--start-date= "2005-04-20 10:01:00"/var/log/mysql/bin.123456 | Mysql-u Root-pmypwd in this row, the SQL statement that is logged in from 10:01 runs. Combined execution of the previous night's dump file and two lines of Mysqlbinlog can restore all data to one second before 10:00. You should check the logs to make sure the time is correct. The next section describes how to implement it. 5.9.3.2. Specify a recovery location you can also specify a log location without specifying a date and time, and using the Mysqlbinlog option--start-position and--stop-position. They function the same as the start and End Date option, and the difference is that the location number from the log is given. Using the log location is a more accurate method of recovery, especially when many transactions occur simultaneously due to destructive SQL statements. To determine the location number, you can run Mysqlbinlog to find the time range for the transaction that you did not expect, but you should re-point the result to a text file for review.  How to do this: Mysqlbinlog--start-date= "2005-04-20 9:55:00"--stop-date= "2005-04-20 10:05:00"/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql the command will create a small text file in the/tmp directory, which will display the SQL statement when the wrong SQL statement was executed. You can open the article with a text editorPieces and look for the statements you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, comments should be made. Use Log_pos plus a number to mark the position. After recovering the previous backup file using the location number, you should enter the following from the command line: Mysqlbinlog--stop-position= "368312"/var/log/mysql/bin.123456 | Mysql-u root-pmypwd mysqlbinlog--start-position= "368315"/var/log/mysql/bin.123456 | Mysql-u root-pmypwd \ The 1th row above will revert to all transactions until the stop location. The next line restores all transactions from the given starting position until the end of the binary log. Because the output of Mysqlbinlog includes a set TIMESTAMP statement before each SQL statement is recorded, the recovered data and the associated MySQL log will react to the original time of the transaction execution.

Restore the MySQL database using the mysqldump command backup