mysqldump MySQL备份工具

mysqldump是MySQL自带的备份工具,用于执行逻辑备份。它可以备份一个或多个数据库默认生成一个可以被执行的SQL语句集合。也可以生成CSV、其他分割文本、或者XML格式的输出。

常用语句

# 备份一个数据库
mysqldump -h ip -P port -u user -p password db_name > backup-file.sql
# 从备份中进行恢复
## 方法1
shell> mysql -h ip -P port -u user -p password db_name < backup-file.sql
## 方法2
shell> mysql -h ip -P port -u user -p password -e "source /path-to-backup/backup-file.sql" db_name
## 备份指定的数据库
mysqldump -h ip -P port -u user -p password --databases db_name1 [db_name2 ...] > my_databases.sql
## 备份所有数据库
mysqldump -h ip -P port -u user -p password --all-databases > all_databases.sql
##备份主库,以用来恢复作为从库
mysqldump -h ip -P port -u user -p password --all-databases --master-data --single-transaction > all_databases.sql
## 备份从库,以用来恢复作为从库
mysqldump -h ip -P port -u user -p password --all-databases --events --routines --triggers --dump-slave --include-master-host-port --apply-slave-statements >all_databases.sql
## 备份所有数据库,并包含存储过程和触发器
mysqldump -h ip -P port -u user -p password --all-databases --routines --triggers > backup-file.sql
## 备份所有数据库表结构,不包括数据,不锁表
mysqldump -h ip -P port -u user -p password --single-transaction --all-databases --no-data > backup-file.sql
## 只备份数据,不包括数据结构
mysqldump -h ip -P port -u user -p password --all-databases --no-create-info > backup-file.sql

权限和注意事项

  • mysqldump需要SELECT权限来备份表,SHOW VIEW权限来备份视图,TRIGGER 权限来备份触发器。当不使用--single-transaction选项时,还需要LOCK TABLES权限。
  • 要从备份文件中读取数据,必须具有执行其中包含的语句的权限。
  • InnoDB存储引擎建议使用--single-transaction,可以不锁表进行备份
  • 备份数据库时会结果中包含use切换db,备份数据库中的指定表时结果中不包含use

语法

以下是三种常见的使用mysqldump的语法,备份一个或多个表、备份一个或多给完整的库,或者备份整个MySQL服务器

  • shell> mysqldump [options] db_name [tbl_name ...]
  • shell> mysqldump [options] --databases db_name ...
  • shell> mysqldump [options] --all-databases
    如果要备份整个数据库,在db_name后不要加任何表名,或者使用--database或--all-databases选项

常用选项

筛选选项

  • --all-databases , -A
    备份所有数据库中的所有表。
  • --databases , -B
    备份几个数据库。通常,mysqldump将命令行上的第一个名称参数视为数据库名称,将后续名称视为表名称。使用此选项,它将所有名称参数视为数据库名称。CREATE DATABASE和USE语句包含在每个新数据库之前的输出中。
    此选项可用于备份INFORMATION_SCHEMA和performance_schema数据库,即使使用该--all-databases选项,通常也不会备份这两个表。
  • --ignore-table= db_name.tbl_name
    不要备份给定的表,必须使用数据库和表名来指定该表。要忽略多个表,请多次使用此选项。此选项也可用于忽略视图。
  • --no-data , -d
    不要写任何表行信息(即不要备份表内容)。如果要仅备份CREATE TABLE表的语句(例如,通过加载备份文件来创建表的空副本),这将非常有用。
  • --events , -E
    在输出中包含备份数据库的事件计划程序事件。此选项需要这些数据库的EVENT权限。
  • --routines , -R
    在输出中包含备份数据库的存储例程(过程和函数)。此选项需要表mysql.proc的SELECT权限。
  • --triggers
    在输出中包含每个备份表的触发器。默认情况下启用此选项; 使用--skip-triggers禁用。
  • --where=' where_condition ' , -w ' where_condition '
    仅备份由给定WHERE条件选择的行。如果条件包含空格或其他对命令解释程序特殊的字符,则必须引用该条件。
    例子:
    --where="user='jimf'" -w"userid>1" -w"userid<1"

连接选项

  • --host=host_name,-h host_name
  • --login-path=name
  • --password,-p
  • --port=port_num,-P port_num
  • --socket=path,-S path
  • --user=user_name,-u user_name

DDL选项

  • --add_drop_database
    在每个CREATE DATABASE语句前写入一个DROP DATABASE语句。这个选项通常和用--all-database或者--database选项结合使用,因为除了使用以上选项外不会有CREATE DATABASE语句写入。
  • --add_drop_table
    在每个CREATE TABLE语句前写入一个DROP TABLE语句
  • --add_drop_trigger
    在每个CREATE TRIGGER语句前写入一个DROP TRIGGER语句
  • --all-tablespaces,-Y
    将创建NDB表使用的任何表空间所需的所有SQL语句添加到表备份中。在mysqldump的输出中没有包含此信息。这个选项目前只与NDB集群表相关。
  • --no-create-db,-n
    在给定--databases或--all-databases选项的情况下,禁止CREATE DATABASE语句
  • --no-create-info,-t
    不要编写创建表语句来创建每个备份表。
  • --replace
    将INSERT语句转换为REPLACE语句

复制选项

mysqldump经常用于在一个复制结构的从库中创建一个空的、或者包含数据的实例。以下选项用于在复制的主库或从库上备份和恢复数据

将主库备份恢复为从库

  • --master-data[=value]
    使用这个选项来备份一个复制的主库,它可以作为一个从库恢复到其他服务器上。
    它会在备份的输出中添加一个CHAGE MASTER TO语句,其中包括主库的二进制日志坐标(文件名和位置)。这是从库需要开始复制的主库坐标
    选项值为1或者2,默认值为1

    • 1 执行CHANGE MASTER 语句
    • 2 CHANGE MASTER TO 语句作为注释写入到备份文件中,不会实际执行

这个选项需要RELOAD权限并且二进制日志必须是可用的。
--master-data选项自动关闭--lock-table。除非指定--single-transaction,否则它还会开启--lock-all-tables。在这种情况下,会在开始备份时生成全局读锁。

  • --delete-master-logs
    在复制的主库中,通过发送一个PURGER BINARY LOGS语句来删除二进制日志。这个选项在指定--master-data时自动启用
  • --dump-slave[=value]
    这个选项和--master-data类似,除了它是用来备份复制的从库并在另一个服务器上恢复为同一个主库的另一个从库。它让备份的输出中包含一个CHANGE MASTER TO语句并包含被备份的从库的主库的二进制日志的坐标(文件名和位置)。这个CHANGE MASTER TO语句从SHOW SLAVE STATUS的输出中读取Relay_Master_Log_File和Exec_Master_Log_Pos并且使用他们作为MASTER_LOG_FILE和MASTER_LOG_POS.这是从库需要开始复制的主库坐标
    --dump-slave使用主库中被使用的坐标而不是被备份的服务器,另外,指定这个选项会导致--master-data选项被覆盖(如果同时使用--dump-slave和--master-data会忽略--master-data的内容)
    这个选项的值和--master-data的值通过相同的方式使用(设置为空或者1将把CHANGE MASTER TO语句写入到备份文件中,设置为2则这个语句将作为SQL注释写入)并且一样也可以启用或禁用其他选项,并以同样的方式处理锁定
    这个选项导致 mysqldump在备份前停止slave SQL thread并在备份结束后重新启动它
    --allpy-slave-statement可以与--include-master-host-port和--dump-slave选项结合使用
  • --include-master-host-port
    在使用--dump-slave选项时,添加MASTER_HOST和MASTER_PORT选项
  • --apply-slave-statements
    在一个使用--dump-slave选项的备份中,在CHANGE MASTER TO语句前增加一个STOP SLAVE语句并且在之后增加一个START SLAVE语句

事务选项

以下选项权衡备份操作的性能,以及导出数据的可靠性和一致性。

  • --add-locks
    使用LOCK TABLES和UNLOCK TABLES语句环绕每个表备份。重新加载备份文件时,这会导致更快的插入。请参见第8.2.4.1节“优化INSERT语句”。
  • --flush-logs , -F
    在开始备份之前刷新MySQL服务器日志文件。此选项需要RELOAD权限。如果将此选项与选项结合使用--all-databases,则会为每个备份的数据库刷新日志。例外情况是使用--lock-all-tables,, --master-data或--single-transaction:在这种情况下,日志只刷新一次,对应于所有表被锁定的时刻FLUSH TABLES WITH READ LOCK。如果你希望你的备份和刷新日志到恰好在同一时刻发生,你应该使用--flush-logs同在一起--lock-all-tables,--master-data或--single-transaction。
  • --flush-privileges
    FLUSH PRIVILEGES备份mysql数据库后,在备份输出中添加语句。只要备份包含mysql数据库以及依赖于数据库中的数据以mysql进行适当恢复的任何其他数据库,就应该使用此选项。
    注意
    要从旧版本升级到MySQL 5.7或更高版本,请不要使用--flush-privileges。有关这种情况下的升级说明,请参见第2.11.1.2节“影响升级到MySQL 5.7的更改”。
  • --lock-all-tables , -x
    锁定所有数据库中的所有表。这是通过在整个备份期间获取全局读锁来实现的。此选项自动关闭--single-transaction和--lock-tables。
  • --lock-tables , -l
    对于每个备份的数据库,在备份它们之前锁定要备份的所有表。这些表被锁定READ LOCAL以允许在MyISAM表的情况下进行并发插入。对于事务表,例如InnoDB,--single-transaction是一个更好的选择,--lock-tables因为它根本不需要锁定表。
    由于--lock-tables每个数据库的锁表都是单独的,因此该选项不保证备份文件中的表在数据库之间在逻辑上是一致的。不同数据库中的表可能会以完全不同的状态备份。
    某些选项,例如--opt,自动启用--lock-tables。如果要覆盖它,请使用--skip-lock-tables选项列表的末尾。
  • --no-autocommit
    将INSERT每个备份表的语句包含在语句SET autocommit = 0和COMMIT语句中。
  • --order-by-primary
    如果存在这样的索引,则备份按其主键或其第一个唯一索引排序的每个表的行。这在备份MyISAM要加载到InnoDB表中的表时很有用,但是使备份操作需要相当长的时间。
  • --shared-memory-base-name= name
    在Windows上,使用共享内存名称,用于使用共享内存与本地服务器建立的连接。默认值为MYSQL。共享内存名称区分大小写。
    必须使用--shared-memory启用共享内存连接的选项启动服务器。
  • --single-transaction
    此选项将事务隔离模式设置为,REPEATABLE READ并在备份数据之前将SQL语句发送到服务器START TRANSACTION。它仅对事务表有用,例如InnoDB,因为它在START TRANSACTION发出时不备份任何应用程序时备份数据库的一致状态。
    使用此选项时,应记住只有InnoDB表以一致状态备份。例如,使用此选项时备份的任何表MyISAM或MEMORY表仍可能更改状态。
    虽然--single-transaction备份过程,以确保有效的备份文件(正确的表的内容和二进制日志坐标),没有其他的连接应使用以下语句:ALTER TABLE,CREATE TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE。一致读取不会与这些语句隔离,因此在要备份的表上使用它们会导致mysqldumpSELECT执行该操作以检索表内容以获取不正确的内容或失败。
    该--single-transaction选项与--lock-tables选项是相互排斥的,因为LOCK TABLES会导致任何挂起的事务隐含提交
    要备份大表,请将--single-transaction选项与--quick选项组合使用。