Mysqldump参数大全

一、Mysqldump参数大全

--all-databases,-A：备份所有数据库

--all-tablespaces,-Y：备份所有表空间

--no-tablespaces,-y：不备份表空间

--add-drop-database：添加DROP DATABASE语句

--add-drop-table：添加DROP TABLE语句

--allow-keywords：允许关键字作为列名

--apply-slave-statements：应用从属服务器的事务

--character-sets-dir：指定字符集目录

--complete-insert,-c：完整插入，包含完整表结构

--create-options,-a：创建表时的选项

--debug-info：提供额外调试信息

--default-character-set：指定默认字符集

--delete-master-logs：删除主日志

--extended-insert,-e：扩展插入

--fields-terminated-by：指定字段分隔符

--fields-enclosed-by：指定字段包围符

--fields-optionally-enclosed-by：可选字段包围符

--fields-escaped-by：指定字段转义符

--hex-blob：以十六进制格式备份二进制数据

--include-master-host-port：包含主服务器地址和端口

--insert-ignore：忽略重复的插入

--lines-terminated-by：指定行分隔符

--lock-all-tables,-x：锁定所有表

--max_allowed_packet：指定最大数据包大小

--net_buffer_length：指定网络缓冲区大小

--no-create-db,-n：不创建数据库

--no-create-info,-t：不创建表信息

--no-set-names,-N：不设置字符集

--order-by-primary：按主键排序

--pipe(windows系统可用)：使用管道输出

--result-file,-r：指定结果文件

--routines,-R：备份存储过程和函数

--single-transaction：使用单个事务

--default_auth：指定默认认证方法

二、mysql数据使用逻辑备份mysqldump备份出来的文件有多大

有人问mysqldump出来的insert语句，是否可以按每 10 row一条insert语句的形式组织。

思考1：参数--extended-insert回忆过去所学：

表示使用长 INSERT，多 row在合并一起批量 INSERT，提高导入效率

--skip-extended-insert一行一个的短INSERT

均不满足群友需求，无法控制按每 10 row一条 insert语句的形式组织。

思考2：“避免大事务”之前一直没有考虑过这个问题。这个问题的提出，相信主要是为了“避免大事务”。所以满足 insert均为小事务即可。下面，我们来探讨一下以下问题：1.什么是大事务？

2.那么 mysqldump出来的 insert语句可能是大事务吗？

定义：运行时间比较长，操作的数据比较多的事务我们称之为大事务。

∘锁定太多的数据，造成大量的阻塞和锁超时，回滚所需要的时间比较长。

∘执行时间长，容易造成主从延迟。

前提，MySQL默认是自提交的，所以如果没有明确地开启事务，一条 SQL语句就是一条事务。在 mysqldump里，就是一条 SQL语句为一条事务。

注意，指的是 mysqldump的参数，而不是 mysqld的参数。官方文档提到: If you increase this variable, ensure that the MySQL server net_buffer_length system variable has a value at least this large.

意思是 mysqldump增大这个值，mysqld也得增大这个值，测试结论是不需要的。怀疑官方文档有误。

不调整的话，会出现以下报错：[root@192-168-199-198~]# mysql-uroot-proot-P3306-h192.168.199.198 test<16M.sqlmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2006(HY000) at line 46: MySQL server has gone away

相关测试最后，我放出我的相关测试步骤mysql> select version();+------------+| version()|+------------+| 5.7.26-log|+------------+1 row in set(0.00 sec)

造100万行数据

create database test;

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t values(1,1,'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyztuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz');

insert into t select* from t;#重复执行20次

#直到出现Records: 524288 Duplicates: 0 Warnings: 0

mysql> select count(*) from t;

--net-buffer-length=1M[root@192-168-199-198~]# mysqldump-uroot-proot-S/tmp/mysql3306.sock test t>1M.sql[root@192-168-199-198~]# du-sh 1M.sql225M 1M.sql[root@192-168-199-198~]# cat 1M.sql|grep-i insert|wc-l226

默认--net-buffer-length=1M的情况下，225M的SQL文件里有 226条 insert，平均下来确实就是每条 insert的 SQL大小为 1M。

--net-buffer-length=16M

默认--net-buffer-length=16M的情况下，225M的 SQL文件里有 15条 insert，平均下来确实就是每条 insert的 SQL大小为 16M。所以，这里证明了--net-buffer-length确实可用于拆分 mysqldump备份文件的SQL大小的。性能测试insert次数越多，交互次数就越多，性能越低。但鉴于上面例子的 insert数量差距不大，只有 16倍，性能差距不会很大(实际测试也是如此)。我们直接对比--net-buffer-length=16K和--net-buffer-length=16M的情况，他们insert次数相差了 1024倍。

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16K.sql

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16K.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@192-168-199-198~]# mysql-uroot-proot-S/tmp/mysql3306.sock-e"reset master";

mysql: [Warning] Using a password on the command line interface can be insecure.

[root@192-168-199-198~]# time mysql-uroot-proot-S/tmp/mysql3306.sock test<16M.sql

mysql: [Warning] Using a password on the command line interface can be insecure.

三、揭秘MySQL 8.4新版备份利器:全面解读Mysqldump参数与实战技巧

MySQL 8.4中，Mysqldump作为备份的有力工具，其功能强大且实用。这个内置的命令行工具主要负责创建数据库的逻辑备份，通过生成SQL脚本文件，精准地记录数据和结构，方便数据迁移、备份或导出到其他环境中。

Mysqldump的工作原理基于SQL语句的编写，它会遍历数据库的每个表、字段和索引，生成相应的创建表和插入数据的SQL语句。这使得备份后的恢复过程相对直接，只需运行生成的脚本即可恢复到备份时的状态。

尽管在处理大规模数据库时，Mysqldump可能面临性能瓶颈和一致性问题，但它的易用性和广泛应用性使其在众多场景中仍占主导。例如，当需要迁移数据库到不同的服务器，或者定期备份以防数据丢失时，Mysqldump都是首选工具。

使用Mysqldump时，理解并合理配置参数至关重要。参数的调整可以根据备份需求进行个性化设置，如压缩备份文件、忽略某些表或字段，甚至设置备份策略等。通过精准的参数控制，几乎可以适应任何MySQL数据库的备份需求。

总结来说，Mysqldump在MySQL 8.4中扮演着不可或缺的角色，尽管有其局限性，但通过灵活运用和参数优化，它能为数据库的备份和维护提供强大且实用的支持。