mysqldump备份mysql数据详解

mysqldump命令详解

1.简介
mysqldump为MySQL逻辑备份工具，产生一系列SQL语句，之后重新执行以产生备份的库、表及数据。也可产生CSV、XML等格式的数据。适用于各类引擎的表。

运行mysqldump需一定的权限。如：备份表的最低权限为select，备份视图需show view权限，备份触发器需trigger权限。
若不使用--single-transaction选项则还需--lock tables权限，不同的选项可能需要对应的不同的权限。

相应的，载入备份文件也至少需与创建备份文件时相同的权限。

grant select,reload,super,replication slave,replication client,show view,alter routine,event,trigger \

on *.* to 'backup'@'localhost' identified by 'q6eAaTxOcsbA';
flush privileges;

2.语法
备份一个或多个表：
    mysqldump [options] db_name [table_name……]

备份一个或多个库：
    mysqldump [options] --databases dbname……

备份所有数据库：
    mysqldump [options] --all-databases

3.性能与扩展性
提供了在恢复前查看并编辑备份文件的便捷性和灵活性。不适用于大数据量的备份(即使备份过程较快，但在恢复数据时因插入、建索引等操作会造成高昂I/O开销)。
对于大规模备份，考虑使用物理方法，如：xtrabackup(适于InnoDB、MyISAM及其他表).

4选项
mysqldump会依次从下列配置文件中读取默认：
    1.   /etc/my.cnf
    2.   /etc/mysql/my.cnf
    3.   /usr/etc/my.cnf
    4.   ~/.my.cnf

mysqldump仅从配置文件的下列标签中读取默认选项：
    Ø  [mysqldump]
    Ø  [client]

常用mysqldump举例：

全局锁，锁定所有库，所有表，只能读。不能写。
FLUSH TABLES WITH READ LOCK;

导出全部库
mysqldump -uroot -p  --all-databases --quick --single-transaction > /tmp/all_db.sql
mysqldump -uroot -p  -A --quick --single-transaction > /tmp/all_db.sql

导出一个库
mysqldump -uroot -p  test --quick --single-transaction > /tmp/all_db.sql

导出多个库
mysqldump -uroot -p  --database test python --quick --single-transaction > /tmp/all_db.sql
mysqldump -uroot -p  -B test python --quick --single-transaction > /tmp/all_db.sql

备份记录主BINLOG信息：
--dump-slave        表示，将主binlog位置信息记录。
--master-data=2        将主binlog位置信息记录。

 

导出完整SQL记录备份集：
mysqldump -uroot -p -A --default-character-set=utf8mb4 -c -R --triggers -E --quick --single-transaction > /log/test.sql
备份数据，存储过程，触发器，事件，使用utf8mb4字符集，-c采用完整insert语句.

----在slave上备份会记录master的日志文件和pos位置

mysqldump -uroot -p -A --default-character-set=utf8mb4 --flush-logs -R --triggers -E -q --dump-slave=2 --single-transaction > /log/test.sql

-----在master上备份会记录    自己的日志文件和pos位置。

mysqldump -uroot -p -A --default-character-set=utf8mb4 -R --triggers -E -q --master-data=2 --single-transaction > /log/yaya.sql

 

推荐mysqldump配置：

导出所有数据，导出前刷日志，导出存储过程，函数，触发器，多values快速导出，事务。将binlog位置备份到备份中,gzip压缩。
mysqldump -uroot -p -A --default-character-set=utf8mb4 -F -R -E --triggers -q -e --master-data=2 --single-transaction |gzip -9 > /db/test.sql.gz

等效：
mysqldump \
    --host=localhost \
    --user=root \
    --password='mysql' \
    --all-databases \
    --default-character-set=utf8mb4 \
    --flush-logs \
    --routines \
    --events \
    --triggers \
    --quick \
    --extended-insert \
    --master-data=2 \
    --single-transaction | gzip -9 >/db/test.s