Use mysqldump to import data and mysqldump incremental backup of mysqldump method of use

1. Various usage instructions

A. The simplest use:

mysqldump -uroot -pPassword [database name] 
> [dump file]

The command above specifies that the database is backed up to an dump file (dump file), such as:

mysqldump -uroot -p123 test > test.dump

The generated ES15en.dump file contains the build sentence (to generate the database structure) and the insert statement to insert the data.

B. --opt

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

The construction sentence contains drop table if exists tableName

insert contains 1 lock phrase before lock tables tableName write and after insert unlock tables

C. Cross-host backup

Use the following command to copy sourceDb on host1 to targetDb on host2 if the targetDb database has been created on the host2 host:

mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb

-ES58en indicates that data transmission between hosts USES data compression

D. Backup table structure only

mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > test.dump

Only the table structure will be backed up. --databases indicates the database to back up on the host. To back up all databases on an MySQL host, use the -- ES68en-ES69en option, as follows:

mysqldump --all-databases
> test.dump
 

E. Restore the database from the backup file

mysql [database name] < [backup file name]
 

2, cron combined with Linux command to achieve timed backup

For example, if you need to back up all databases on a host at 1:30 am every day and compress the dump file into gz format, you can add the following line in the /etc/crontab configuration file:

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

The first five parameters represent minutes, hours, days, months, and years, with asterisks representing arbitrary values. date '+% m-% d-% Y' gets the current date in MM-ES96en-ES97en format.

3, a complete Shell script backup MySQL database example

#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 ES107en.sh, and two directories /olcbackup and /backup have been created on the system. Each time backup.sh is executed, all files starting with backup in the /backup directory are first moved to the /oldbackup directory.

Develop an execution plan for the above script as follows:

#crontab -e
30 1 * * * /backup.sh
 

4. mysqldump full backup +mysqlbinlog2 base log incremental backup

Restoring data from the mysqldump backup file will lose updated data from the backup point, so you will also need to do an incremental backup in conjunction with the mysqlbinlog2 base log. Make sure my.ini or ES130en.cnf includes the following configuration to enable base 2 logging, or ES132en-log-ES134en:

[mysqld]
log-bin=mysql-bin

The mysqldump command must be accompanied with the -- flush-ES140en option to generate a new base 2 log file:

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

If the incremental base 2 log file is es145EN-ES146en.000003, the data recovery is 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-ES153en, -- ES154en-ES155en, -- ES156en-ES157en and -- ES158en-ES159en parameters, which are used to restore the data accurately before a certain time or to skip a certain period of time in the middle of the problem, and directly extract the relevant contents in the MySQL document as follows:

5.9.3.1. Specify a recovery time

For MySQL 4.1.4, you can specify the start and end times of the DATETIME format in the mysqlbinlog statement with the options -- ES168en-ES169en and -- ES170en-date. For example, suppose that at 10:00 this morning (today is April 20, 2005), the SQL statement is executed to delete a large table. To restore tables and data, you can restore the backup from the previous night and type:

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

This command will restore all data up to the date and time given in DATETIME format in the -- ES178en-ES179en option. If you do not detect the incorrect SQL statement entered a few hours later, you may want to resume the activity that follows. Based on these, you can make the date and time run mysqlbinlog again:

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 \
     | mysql -u root -pmypwd \

In this line, the SQL statement logged in from 10:01 am will run. Combining the overnight dump with two lines of mysqlbinlog will restore all data to 1 second before 10:00am. You should check the log to make sure the time is exact. The next section describes how.

5.9.3.2. Specify the recovery location

You can also specify the log location without specifying a date and time, using the mysqlbinlog options -- start-ES194en and -- stop-ES196en. They function the same as the start-stop options, except that the position number is given from the log. 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, run mysqlbinlog to find the time range in which an unexpected transaction was executed, but repoint the result to a text file for inspection. Operation method:

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 that displays the SQL statement when the wrong SQL statement was executed. You can open the file with a text editor and look for statements you don't want to repeat. If the position number in the base 2 log is used to stop and continue recovery operations, comment. Use log_pos plus a number to mark the position. After you have recovered the 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 \ 
 

Line 1 above restores all transactions to the stopped location. Line 1 below restores all transactions from the given starting position until the end of the base 2 log. Since the output of mysqlbinlog includes the SET TIMESTAMP statement before each SQL statement records, the recovered data and associated MySQL logs will reflect the original time of the transaction execution.