Use mysqldump to import data and mysqldump incremental backups (mysqldump use method) _mysql

1. Various usage instructions

A. The simplest use:

Copy Code code as follows:

Mysqldump-uroot-ppassword [Database name]
> [Dump file]

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

Copy Code code as follows:

mysqldump-uroot-p123 Test > Test.dump

The generated Test.dump file contains the Build table statement, which generates the database structure, and inserts the data into the INSERT statement.

B.--opt

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

The Build Table statement contains drop table if exists tablename

Include a lock table statement before insert lock tables tablename Write,insert contains unlock tables

C. Cross-host backup

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

Copy Code code as follows:

Mysqldump--host=host1--opt sourcedb| MySQL--host=host2-c targetdb

-C indicates data transfer between hosts using compression

D. Backing up only the table structure

Copy Code code as follows:

Mysqldump--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 databases on a MySQL host, you can use the--all-databases option as follows:

Copy Code code as follows:

Mysqldump--all-databases
> Test.dump

E. Recovering a database from a backup file

Copy Code code as follows:

MySQL [database name] < [backup file name]

2, the combination of Linux cron command to implement a scheduled backup

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

Copy Code code as follows:

1 * * * root mysqldump-u Root-ppassword--all-databases | gzip >/mnt/disk2/database_ ' Date ' +%m-%d-%y '. sql.gz

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

3, a complete shell script backup MySQL Database sample

Copy Code code as follows:

#vi/backup/backup.sh

#!bin/bash
Cd/backup
echo "You are in Backup dir"
MV backup*/oldbackup
echo "Old DBs are moved to Oldbackup folder"
File = backup-$Now. sql
Mysqldump-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. Every time the backup.sh is executed, all files under the/backup directory that begin with backup are moved to the/oldbackup directory.

The execution plan for the above script is as follows:

Copy Code code as follows:

#crontab-E
1 * * */backup.sh

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

Recovering data from a mysqldump backup file loses the update data that starts at the backup point, so you also need to combine the Mysqlbinlog binary log incremental backup. Ensure that the following configuration is included in the My.ini or my.cnf to enable binary logging, or mysqld---log-bin:

Copy Code code as follows:

[Mysqld]
Log-bin=mysql-bin

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

Copy Code code as follows:

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

This generates an incremental binary log file, such as mysql-bin.000003, to recover the data as follows:

Copy Code code as follows:

Shell> Mysql-uroot-ppwd < Backup_sunday_1_pm.sql
shell> 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 recover data accurately before a certain point in time or to skip the middle of a problem period of recovery data, directly extract the MySQL document description of the relevant content is as follows:

5.9.3.1. Specify recovery time

For MySQL 4.1.4, you can specify the start and end times of the DateTime format through the--start-date and--stop-date options in the Mysqlbinlog statement. For example, suppose that at 10:00 today (today is April 20, 2005), execute the SQL statement to delete a large table. To recover the tables and data, you can restore the previous night's backup and enter:

Copy Code code as follows:

Mysqlbinlog--stop-date= "2005-04-20 9:59:59"/var/log/mysql/bin.123456 \
| Mysql-u root-pmypwd

This command restores all data up to the date and time given in datetime format in the--stop-date option. If you do not detect the wrong SQL statement entered after a few hours, you may want to restore the activity that occurred later. Depending on these, you can use the date and time to run the Mysqlbinlog again:

Copy Code code as follows:

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 logs in from 10:01 will run. The two rows of the dump file and Mysqlbinlog on the eve of the combined execution can restore all data to one second before 10:00. You should check the log to make sure the time is accurate. The next section describes how to implement.

5.9.3.2. Specify recovery location

You can also specify the log location by using Mysqlbinlog options--start-position and--stop-position without specifying a date and time. They function the same as the start and end dates, and the difference is given the number of positions from the log. Using log locations is a more accurate method of recovery, especially when many transactions occur concurrently due to destructive SQL statements. To determine the location number, you can run Mysqlbinlog to find a time range that performs an unexpected transaction, but you should point the results back to a text file for inspection. The action method is:

Copy Code code as follows:

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

This command creates a small text file in the/tmp directory and displays the SQL statement when the incorrect SQL statement is executed. You can open the file in a text editor and look for statements that you don't want to repeat. If the location number in the binary log is used to stop and resume the recovery operation, you should comment. Mark position with Log_pos plus a number. After restoring the previous backup file using the location number, you should enter the following from the command line:

Copy Code code as follows:

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 line above reverts to all transactions until the stop position. The next line restores all transactions from the given starting position to the end of the binary log. Because the output of Mysqlbinlog includes the set timestamp statements that precede each SQL statement, the recovered data and the associated MySQL log reflect the original time of the transaction execution.