Shulou(Shulou.com)06/01 Report--

1. Various usage instructions

a. The simplest usage:

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

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

Mysqldump-uroot-p123 test > test.dump

The generated test.dump file contains table-building statements (generate database structure) and insert statements that insert data.

B.-- opt

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

. The table statement contains drop table if exists tableName

. Insert contains a lock table statement before lock tables tableName write,insert followed by unlock tables

c. Cross-host backup

Use the following command to copy the sourceDb on host1 to the targetDb of host2, provided that the targetDb database has been created on the host2 host:

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

-C instructs data transmission between hosts to use data compression

d. Backup list structure only

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

Only the table structure will be available. -- databases indicates the database to back 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:

Mysqldump-- all-databases > test.dump

e. Restore a database from a backup file

Mysql [database name]

< [backup file name] 2、结合Linux的cron命令实现定时备份 比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式，那么可在/etc/crontab配置文件中加入下面代码行： 30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip >

/ mnt/disk2/database_ `date'+% mmury% dmurf% Y``. Sql.gz

The first five parameters represent minutes, hours, days, months and years, respectively, and the asterisk indicates any. Date'+% mmi% dmi% Y' get the MM-DD-YYYY format of the current date.

3. An example of a complete Shell script backing up MySQL database

# 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 on the system. Each time backup.sh is executed, all files under the / backup directory that start with the name backup are moved to the / oldbackup directory.

The execution plan for the above script is as follows:

# crontab-e301 * / backup.sh

4. Mysqldump full backup + mysqlbinlog binary log incremental backup

Restoring data from mysqldump backup files loses updated data from the backup point, so incremental backups are also required in conjunction with mysqlbinlog binary logs. Make sure that my.ini or my.cnf contains the following configuration to enable binary logging, or mysqld-log-bin:

[mysqld] log-bin=mysql-bin

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

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

For example, if the incremental binary log file generated in this way is mysql-bin.000003, the data recovery time is as follows:

12shell > 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, which are used to accurately recover data before a certain time or skip a problem period. The relevant contents in the MySQL document description are directly excerpted as follows:

5.9.3.1. Specify recovery time for MySQL 4.1.4, you can specify the start and end time in DATETIME format with the-- start-date and-- stop-date options in the mysqlbinlog statement. For example, suppose you execute a SQL statement to delete a large table at 10:00 this morning (today is April 20, 2005). To restore tables and data, you can restore the backup from the previous night and enter: 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 don't detect the wrong SQL statement entered a few hours later, you may want to resume the activity that occurs later. Based on these, you can use the date and time to run mysqlbinlog:mysqlbinlog again-- 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 will run. Combining the dump file the night before execution and the two lines of mysqlbinlog can restore all data to the second before 10:00 You should check the log to make sure the time is right. The next section describes how to implement it. 5.9.3.2. Specifying the recovery location can also specify the log location without specifying a date and time and using the options of mysqlbinlog-- start-position and-- stop-position. They work the same as the start and end date options, except that they give the location number from the log. Using log locations is a more accurate recovery method, especially when many transactions occur at the same time due to destructive SQL statements. To determine the location number, you can run mysqlbinlog to find the time frame in which an unwanted transaction was executed, but the result should be redirected to the text file for checking. 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 this command will create a small text file in the / tmp directory and display 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. Comments should be made if the location number in the binary log is used to stop and continue the restore operation. Use log_pos plus a number to mark the position. After restoring 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\ will restore all transactions to the stop location. The next line resumes all transactions from the given starting position to the end of the binary log. Because the output of mysqlbinlog includes the previous SET TIMESTAMP statement recorded by each SQL statement, the recovered data and the associated MySQL log will reflect the original time of transaction execution. Backup command example: # For MyISAM mysqldump-user=root-- all-databases-- flush-privileges-- lock-all-tables-- master-data=1\-- flush-logs-- triggers-- routines-- events-- hex-blob > $BACKUP_DIR/Full_dump_DATABASE.sql # For MyISAM mysqldump-- user=root-- all-databases-- flush-privileges-- single-transaction-- master-data=1\ -flush-logs-- triggers-- routines-- events-- hex-blob > $BACKUP_DIR/Full_dump_DATABASE.sql

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.