MySQL逻辑备份工具mysqldump

mysqldump

简介

mysqldump这个程序最开始是Igor Romanenko写的。它是一个MySQL/MariaDB逻辑备份的工具，备份的逻辑就是将指定的数据库/表中的数据以SQL语句的形式一条一条导出。还原的时候也是执行一条条sql语句，达到还原的目的。

用法

shell> mysqldump [options] db_name [tbl_name ...]  #备份指定库中的表
shell> mysqldump [options] --databases db_name ...   #备份指定数据库，可以多个
shell> mysqldump [options] --all-databases   #备份所有库

常用选项

• -A， --all-databases
  备份所有数据库
• -B, --databases
  对单个数据库，或多个数据库进行备份,会有CREATE DATABASE和USE 语句，如果不使用-B备份单库，则不会有这两个语句
• - -add-drop-database
  在每次创建之前添加DROP DATABASE。通常与 --all-databases 一起用
• - -compact
  提供较少的详细输出（对调试很有用）。禁用结构注释和页眉/页脚构造。
• -E, --events
  导出事件
• -F, --flush-logs
  在开始备份之前刷新MariaDB服务器binlog日志。如果将此选项与–databases=or --all-databases选项结合使用，则会为每个备份的数据库刷新日志。例外情况是使用–lock-all-tables或–master-data：在这种情况下，日志只刷新一次，对应于所有表被锁定的时刻。如果你希望你的转储和刷新日志在同一确切的时刻发生，你应该使用–flush-logs与一起两种–lock-all-tables或–master-data。
• -f， --force
  即使在表转储期间发生SQL错误，也要继续。
• -h name， --host=name
  从给定主机上的MariaDB或MySQL服务器连接并备份数据。默认主机是localhost
• –ignore-table=name
  不要备份指定的表。要指定多个要忽略的表，请多次使用该指令，每个表使用一次。必须使用数据库和表名指定每个表，例如，–ignore-table=database.table。
• -x， --lock-all-tables
  锁定所有数据库中的所有表。这是通过在整个转储期间获取全局读锁来实现的。指定–single-transaction和–lock-tables的时候此选项自动关闭(一般用于myisam表温备，一般不用)
• -l, --lock-tables
• - -log-error=name
  通过将警告和错误附加到指定文件来记录警告和错误。默认是不进行日志记录。
• - -master-data[=#]
  使binlog日志位置和文件名附加到输出，如果该选项设置为1（默认值），则将其作为CHANGE MASTER命令输出; 如果设置为2，则该命令将以注释符号作为前缀。除非另有说明，否则此–master-data选项将打开。在MariaDB 5.3之前，这将在转储开始时短时间内进行全局读锁定（有这个参数之后就不需要用-x和-l参数锁表了，因为这个参数会自动会锁表，备份完了也会自动释放掉锁。）
• -q， --quick
  此选项对于转储大型表非常有用。它强制mysqldump一次从服务器一行检索表的行，然后将结果直接输出到stdout，而不是检索整个行集并在写出之前在内存中缓冲它。默认为开，用于–skip-quick禁用。
• –single-transaction
  此选项在备份数据之前向服务器发送START TRANSACTION SQL语句。它仅适用于InnoDB之类的事务表，使用此选项时，应记住只有InnoDB表以一致状态备份。单事务功能不仅取决于引擎是事务性的还是能够REPEATABLE-READ，还取决于START TRANSACTION WITH CONSISTENT SNAPSHOT。
  该–single-transaction选项与–lock-tables选项互斥，因为LOCK TABLES导致任何挂起的事务被隐式提交。因此，此选项自动关闭–lock-tables（针对innodb进行热备）
  要转储大表，您应该将–single-transaction选项与–quick。
• -R, --routines
  备份存储过程和函数数据（备份一般都带上这个参数）
• - -triggers
  备份触发器数据（备份一般都带上这个参数）
• -d
  仅表结构
• -t
  仅数据

实例

备份所有库,并保存到~/alldb_backup.sql

备份所有库,并保存到~/alldb_backup.sql

mysqldump -uroot -pcentos --flush-logs --quick   --single-transaction --master-data=2  --all-databases> ~/alldb_backup.sql

还原

mysql -uroot -pcentos < ~/alldb_backup.sql

备份单个hellodb单个库

mysqldump -uroot -pcentos   --flush-logs --quick   --single-transaction  --databases hellodb > ~/hellodb_backup.sql

还原

mysql -uroot -pcentos < ~/hellodb.sql 

备份hellodb数据库中的students表

mysqldump -uroot -pcentos   --flush-logs --quick   --single-transaction   hellodb students > ~/hellodb_students_backup.sql

还原

mysql -uroot -pcentos hellodb < ~/hellodb_students_backup.sql

备份mydb和test数据库

mysqldump -B mydb test > mydb_test.sql

备份mydb库下的t1表和Orders表

mysqldump mydb Orders t1 > Orders_t1.sql

分库备份

for i in `mysql -e'show databases;' | sed 1d` ;do mysqldump -B $i >${i}.sql ; done;  

记录全备后的binlog位置，并滚动日志

mysqldump -A  -R --triggers  --master-data=2 -F > full.sql

压缩备份并打上时间戳

mysqldump -A  -R --triggers  --master-data=2 -F | gzip   > full_$(date +%F).sql.gz

解压

zcat full_2019-01-18.sql.gz  > full_2019-01-18.sql
gzip -d full_2019-01-18.sql.gz 

注意
还原的时候可以先关闭binlog日志，不然数据会再次写到binlog日志中

mysql> set @@session.sql_log_bin=off
mysql> source sql文件