Detailed explanation of using mysqldump command example

1, Various usage instructions

A. The simplest usage:

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

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

mysqldump -uroot -p123 test > test.dump

The generated test.dump file contains table creation statements (generating database structures) and insert statements for inserting data.

B. --opt

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

. The table creation statement contains drop table if exists tableName

. Before insert, it contains a lock table statement lock tables tableName write, and after insert, it contains 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 indicates between hosts The data transfer uses data compression

D. Only the table structure will be backed up

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

Will only back up the table structure. --databases indicates the databases on the host to be backed up. 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. From Backup files and restore database

mysql [database name] < [backup file name]

　Back up multiple databases

Syntax:

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

　Added the --databases option, followed by multiple databases

mysqldump -u root -p --databases test mysql > D:\backup.sql

　Back up all databases

　mysqldump The syntax of the command to back up all databases is as follows:

mysqldump -u username -p -all-databases > BackupName.sql

Example:

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

2, Combined with the Linux cron command to achieve scheduled backup

For example, if you need to back up all databases on a certain host at 1:30 a.m. every day and compress the dump file into gz format, you can do it in /etc/ Add the following line of code to the 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 respectively. The asterisk means any. date '+%m-%d-%Y' gets the current date in MM-DD-YYYY format.

3, A complete Shell script to back up MySQL database example

#vi /backup/backup.sh#!bin/bash
cd /backupecho "You are in backup dir"mv backup* /oldbackupecho "Old dbs are moved to oldbackup folder"File = backup-$Now.sql
mysqldump -u user -p password database-name > $Fileecho "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 backup.sh is executed, all files with names starting with backup in the /backup directory will be moved to the /oldbackup directory.

Develop an execution plan for the above script as follows:

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

4, mysqldump full backup + mysqlbinlog binary log increment Backup

Restoring data from the mysqldump backup file will lose the updated data starting from the backup point, so it needs to be combined with the mysqlbinlog binary log incremental backup. Make sure 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 be carried with --flush-logs option to generate a new binary log file:

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

The incremental binary log file generated in this way is, for example, mysql-bin.000003, then the data recovery is as follows:

##shell> mysql -uroot -pPwd < backup_sunday_1_PM.sql

In addition, mysqlbinlog can also specify the --start-date, --stop-date, --start-position and --stop-position parameters for To accurately restore data to a certain time or skip a certain problem period in the middle, directly excerpt the relevant content from the MySQL documentation as follows:

5.9.3.1. 指定恢复时间
对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：
mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456 \
     | mysql -u root -pmypwd
该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 \
     | mysql -u root -pmypwd \
在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

5.9.3.2. 指定恢复位置
也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：
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
该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

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 \ 
上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。

The above is the detailed content of Detailed explanation of using mysqldump command example. For more information, please follow other related articles on the PHP Chinese website!