278_DBA_备份_mysqldump
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
常用操作
使用mysqldump将元数据导出至SQL文件
mysqldump --databases xxx --single-transaction --order-by-primary --hex-blob --no-data --routines --events --set-gtid-purged=OFF -u root -p -h 192.168.151.18 -P 3306 |sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' > dump-defs.sql
使用mysqldump将数据导出至SQL文件
mysqldump --databases xxx --single-transaction --hex-blob --set-gtid-purged=OFF --no-create-info --skip-triggers -u root -p -h 192.168.151.18 -P 3306 -r dump-data.sql
#将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2
#-C参数可以启用压缩传递。
mysqldump --host=192.168.1.x -uroot -proot -C --databases test | mysql --host=192.168.1.x -uroot -proot test
重要参数
--single-transaction
备份开始之前 BEGIN SQL语句(start transaction)，以获得备份的一致性
BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于InnoDB。
和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
--lock-tables, -l
备份中依次锁住database 下的所有表，一般用于MyISAM，当前数据库只能进行读操作 且与 --single-transaction 互斥
--lock-all-tables, -x
备份中 对所有表上锁，避免 --lock-tables 不能同时锁住所有表的情况，
备注
当导出多个数据库时，--lock-tables依次为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可能不同。
--master-data
保存备份开始时间点的binlog的状态信息（binlog的位置和文件名）同时自动锁表（FTWRL）如果没有--single-transaction 会自动使用 --lock-all-tables
--master-data=1 备份中会有 change master to MASTER_LOG_FILE="XXXX-server-bin。000010"
--master-data=2 以注释的形式保存备份开始时间点的binlog的状态信息；如果配合--single-transaction，只对非InnoDB表进行锁表备份
--dump-slave
该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。
--set-gtid-purged=auto #auto/on/off
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto/on:在构建主从复制环境时需要的参数配置
--max-allowed-packet
mysqldump --triggers --master-data=2 --single-transaction --set-gtid-purged=ON --max-allowed-packet=256M >/data/backup/full.sql--no-create-db, -n 只导出数据,而不添加CREATE DATABASE 语句
--no-create-info, -t 只导出数据,而不添加CREATE TABLE 语句
--no-data, -d 只导出数据库表结构,不导出任何数据
--add-drop-database 每个数据库创建之前添加drop数据库语句
--add-drop-table 每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
--skip-add-drop-table 取消每个数据表创建之前添加drop数据表语句(默认每个表之前存在drop语句)
--skip-add-locks ---取消在每个表导出之前增加LOCK TABLES（默认存在锁
--skip-comments ---注释信息(默认存在)
--events, -E 导出事件。
--routines, -R 导出存储过程以及自定义函数。
--triggers 导出触发器。该选项默认启用，用--skip-triggers禁用它
--all-databases , -A 导出全部数据库
--all-tablespaces , -Y 导出全部表空间
--no-tablespaces , -y 不导出任何表空间信息
--databases, -B 导出几个数据库。参数后面所有名字参量都被看作数据库名。
--tables 覆盖--databases (-B)参数，指定需要导出的表名
--where = 'where_condition' (-w) 导出给定条件的数据，test 库下 a表，b列 > 2
mysqldump --single-transaction --databases db1 --tables a --where="b>2" > test_a.sql
--hex-blob
mysqldump导出一个文本文件如果BINARY、VARBINARY、BLOB类型，文本模式下可能不可见，--hex-blob将BINARY、VARBINARY、BLOB字段类型 备份为十六进制格式显示
# insert into "aa" values (0x61000000000000);
--tab,-T
每个表在给定路径创建tab分割的文本文件。每张表mysqldump会包括一个 table_name.sql文件(create table xxx) 以及table_name.txt(数据文件)
注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上,使用--tab不能指定--databases参数
mysqldump --add-drop-database --tab="/home/mysql" test
--quick, -q
--skip-quick取消该选项。
不缓冲查询，直接导出到标准输出。默认为打开状态
--tz-utc --skip-tz-utc
在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。参数全集
| Option Name |
Description |
| Add DROP DATABASE statement before each CREATE DATABASE statement |
|
| Add DROP TABLE statement before each CREATE TABLE statement |
|
| Add DROP TRIGGER statement before each CREATE TRIGGER statement |
|
| Surround each table dump with LOCK TABLES and UNLOCK TABLES statements 在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE (默认为打开状态，使用--skip-add-locks取消选项) |
|
| Dump all tables in all databases |
|
| Allow creation of column names that are keywords 允许创建是关键词的列名字, 这由表名前缀于每个列名做到 |
|
| Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output 在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。 |
|
| Use specified network interface to connect to MySQL Server |
|
| Directory where character sets are installed 字符集文件的目录 |
|
| Add comments to dump file 附加注释信息。默认为打开，可以用--skip-comments取消 |
|
| Produce more compact output |
|
| Produce output that is more compatible with other database systems or with older MySQL servers 导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options,要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容 |
|
| Use complete INSERT statements that include column names 使用完整的insert语句(包含列名称),这么做能提高插入效率,但是可能会受到max_allowed_packet参数的影响而导致插入失败 |
|
| Compress all information sent between client and server 在客户端和服务器之间启用压缩传递所有信息 |
|
| Include all MySQL-specific table options in CREATE TABLE statements 在CREATE TABLE语句中包括所有MySQL特性选项 (默认为打开状态) |
|
| Interpret all name arguments as database names 导出几个数据库。参数后面所有名字参量都被看作数据库名 |
|
| Write debugging lo 输出debug信息,用于调试 |
|
| Print debugging information when program exits检查内存和打开文件使用说明并退出 |
|
| Print debugging information, memory, and CPU statistics when program exits输出调试信息并退出 |
|
| Authentication plugin to use |
|
| Specify default character set 设置默认字符集,默认值为utf8 |
|
| Read named option file in addition to usual option files |
|
| Read only named option file |
|
| Option group suffix value |
|
| On a replication source server, delete the binary logs after performing the dump operation master备份后删除日志. 这个参数将自动激活--master-data |
|
| For each table, surround INSERT statements with statements to disable and enable keys 对于每个表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句,这样可以更快地导入dump出来的文件,因为它是在插入所有行后创建索引的,该选项只适合MyISAM表，默认为打开状态 |
|
| Include dump date as "Dump completed on" comment if --comments is given 将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。 |
|
| Include CHANGE MASTER statement that lists binary log coordinates of replica's source 该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。 设置为1时,将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释 |
|
| Enable cleartext authentication plugin |
|
| Dump events from dumped databases |
|
| Use multiple-row INSERT syntax 使用具有多个VALUES列的INSERT语法,这样使导出文件更小,并加速导入时的速度 默认为打开状态,使用--skip-extended-insert取消选项 |
|
| This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA 输出文件中的各个字段用给定字符包裹,与--tab选项一起使用,不能用于--databases和--all-databases选项 |
|
| This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA 输出文件中的各个字段忽略给定字符,与--tab选项一起使用,不能用于--databases和--all-databases选项 |
|
| This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA 输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项 mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by =”#” |
|
| This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA 导出文件中忽略给定字段,与--tab选项一起使用,不能用于--databases和--all-databases选项 |
|
| Flush MySQL server log files before starting dump 开始导出之前刷新日志 |
|
| Emit a FLUSH PRIVILEGES statement after dumping mysql database 在导出mysql数据库之后,发出一条FLUSH PRIVILEGES 语句 |
|
| Continue even if an SQL error occurs during a table dump 在导出过程中忽略出现的SQL错误 |
|
| Request RSA public key from server |
|
| Display help message and exit |
|
| Dump binary columns using hexadecimal notation |
|
| Host on which MySQL server is located |
|
| Ignore specified errors |
|
| Do not dump given table 不导出指定表,指定忽略多个表时,需要重复多次,每次一个表。每个表必须同时指定数据库和表名 例如：--ignore-table=database.table1 --ignore-table=database.table2 …… |
|
| Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave 在--dump-slave产生的'CHANGE MASTER TO..'语句中增加'MASTER_HOST=<host>，MASTER_PORT=<port>' |
|
| Write INSERT IGNORE rather than INSERT statements 在插入行时使用INSERT IGNORE语句 |
|
| This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA 输出文件的每行用给定字符串划分。与--tab选项一起使用，不能用于--databases和--all-databases选项 |
|
| Lock all tables across all databases 备份中对所有表上锁,避免 --lock-tables 不能同时锁住所有表的情况 |
|
| Lock all tables before dumping them 备份中依次锁住database 下的所有表,一般用于MyISAM,当前数据库只能进行读操作且与 --single-transaction 互斥 |
|
| Append warnings and errors to named file 附加警告和错误信息到给定文件 |
|
| Read login path options from .mylogin.cnf |
|
| Write the binary log file name and position to the output 保存备份开始时间点的binlog的状态信息（binlog的位置和文件名）同时自动锁表（FTWRL） |
|
| Maximum packet length to send to or receive from server 服务器发送和接受的最大包长度。 |
|
| Buffer size for TCP/IP and socket communication |
|
| Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements 使用autocommit/commit 语句包裹表 |
|
| Do not write CREATE DATABASE statements |
|
| Do not write CREATE TABLE statements that re-create each dumped table |
|
| Do not dump table contents |
|
| Read no option files |
|
| Same as --skip-set-charset 等同于--skip-set-charset |
|
| Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output |
|
| Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用 |
|
| Dump each table's rows sorted by its primary key, or by its first unique index 如果存在主键,或者第一个唯一键,对每个表的记录进行排序,在导出MyISAM表到InnoDB表时有效,但会使得导出工作花费很长时间 |
|
| Password to use when connecting to server |
|
| Connect to server using named pipe (Windows only) |
|
| Directory where plugins are installed |
|
| TCP/IP port number for connection |
|
| Print default options |
|
| Transport protocol to use |
|
| Retrieve rows for a table from the server a row at a time |
|
| Quote identifiers within backtick characters 使用（`）引起表和列名,默认为打开状态,使用--skip-quote-names取消该选项 |
|
| Write REPLACE statements rather than INSERT statements 使用REPLACE INTO 取代INSERT INTO. |
|
| Direct output to a given file 直接输出到指定文件中 mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt |
|
| Dump stored routines (procedures and functions) from dumped databases |
|
| Do not send passwords to server in old (pre-4.1) format |
|
| Path name to file containing RSA public key |
|
| Add SET NAMES default_character_set to output 添加'SET NAMES default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。 |
|
| Whether to add SET @@GLOBAL.GTID_PURGED to output |
|
| Shared-memory name for shared-memory connections (Windows only) |
|
| Issue a BEGIN SQL statement before dumping data from server 备份开始之前 BEGIN SQL语句(start transaction),以获得备份的一致性 |
|
| Do not add a DROP TABLE statement before each CREATE TABLE statement |
|
| Do not add locks |
|
| Do not add comments to dump file |
|
| Do not produce more compact output 导出更少的输出信息(用于调试),去掉注释和头尾等结构,可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys |
|
| Do not disable keys |
|
| Turn off extended-insert |
|
| Do not drop the mysql schema |
|
| Turn off options set by --opt |
|
| Do not retrieve rows for a table from the server a row at a time |
|
| Do not quote identifiers |
|
| Do not write SET NAMES statement |
|
| Do not dump triggers |
|
| Turn off tz-utc |
|
| --socket -S |
Unix socket file or Windows named pipe to use 指定连接mysql的socket文件位置 |
| Enable connection encryption |
|
| File that contains list of trusted SSL Certificate Authorities |
|
| Directory that contains trusted SSL Certificate Authority certificate files |
|
| File that contains X.509 certificate |
|
| Permissible ciphers for connection encryption |
|
| File that contains certificate revocation lists |
|
| Directory that contains certificate revocation-list files |
|
| File that contains X.509 key |
|
| Desired security state of connection to server |
|
| Verify host name against server certificate Common Name identity |
|
| Produce tab-separated data files |
|
| Override --databases or -B option |
|
| Permissible TLS protocols for encrypted connections |
|
| Dump triggers for each dumped table |
|
| Add SET TIME_ZONE='+00:00' to dump file |
|
| MySQL user name to use when connecting to server |
|
| Verbose mode 输出多种平台信息 |
|
| Display version information and exit输出mysqldump版本信息并退出 |
|
| Dump only rows selected by given WHERE condition |
|
| --xml -X |
Produce XML output导出XML格式 |
- 点赞
- 收藏
- 关注作者
评论（0）