如果用loginpath的可以用
mysqldump --login-path=dba
代替
mysqldump -uroot -p123456
常用命令
备份整个实例(dump全实例)
mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --single-transaction --column-statistics=0 --skip_add_locks --skip-lock-tables --master-data=2 -A | gzip > /data/mysqlbackup/dboop_dump`date '+%m-%d-%Y'`.sql.gz
备份实例中的用户库(用于实例迁移或升级)
mysql -uroot -p123456 -h127.0.0.1 -P3306 -e "show databases" |grep -Ev "Database|information_schema|mysql|performance_schema" | xargs mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --single-transaction --column-statistics=0 --skip_add_locks --skip-lock-tables --master-data=2 --databases > /data/mysqlbackup/dboop_dump0401.sql
-- 此时mysql.user用户也没有迁移过来,如果需要迁移用户,参考:
https://www.dboop.com/mysql/mysql%E5%B8%B8%E7%94%A8%E8%84%9A%E6%9C%AC_%E7%94%A8%E6%88%B7%E7%9B%B8%E5%85%B3/
导出db1、db2两个数据库的所有数据
mysqldump -uroot -p123456 --set-gtid-purged=OFF --skip_add_locks --skip-lock-tables --databases db1 db2 > /data/mysqlbackup/dboop_dump0401.sql
导出db1中的a1、a2表
mysqldump -uroot -p123456 --set-gtid-purged=OFF --skip_add_locks --skip-lock-tables --databases db1 --tables a1 a2 > /data/mysqlbackup/dboop_dump0401.sql
导出db1表a1中id=1的数据
- 如果多个表的条件相同可以一次性导出多个表
mysqldump -uroot -p123456 --set-gtid-purged=OFF --skip_add_locks --skip-lock-tables --databases db1 --tables a1 --where='id=1' > /data/mysqlbackup/dboop_dump0401.sql
只导出表结构不导出数据，–no-data
mysqldump -uroot -p123456 --set-gtid-purged=OFF --skip_add_locks --skip-lock-tables --no-data --databases db1 > /data/mysqlbackup/dboop_dump0401.sql
导出指定表数据（不要表结构）
-- 不要表结构
mysqldump -uroot -p123456 --set-gtid-purged=OFF --skip_add_locks --skip-lock-tables –no-create-info --databases db1 --tables a1 >/data/mysqlbackup/dboop_dump0401.sql
-- 要表结构
mysqldump -uroot -p123456 --set-gtid-purged=OFF --skip_add_locks --skip-lock-tables --databases db1 --tables a1 >/data/mysqlbackup/dboop_dump0401.sql
常见报错
A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions
-
出现
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events -
原因： 当前数据库实例中开启了 GTID 功能, 在开启有 GTID 功能的数据库实例中, 导出其中任何一个库, 如果没有显示地指定–set-gtid-purged参数, 都会提示这一行信息. 意思是默认情况下, 导出的库中含有 GTID 信息, 如果不想导出包含有 GTID 信息的数据库, 需要显示地添加–set-gtid-purged=OFF参数.
-
解决：加上参数：–set-gtid-purged=OFF
mysqldump:Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM..
- 出现mysqldump:Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.“number-of-buckets-specified”') FROM information_schema.COLUMN_STATISTICS
- 原因：MySQL8.0+的mysqldump默认启用了一个新标志，通过 -column-statistics=0来禁用他
- 解决： 加上参数：–column-statistics=0
mysqldump参数
- 因为mysqldump可能会有很多自定义的需求,所以把它重要的参数总结如下:
- 使用方式
mysqldump -h主机名 -P端口 -u用户名 -p密码 参数1 参数2.... > 备份文件.sql
–all-databases , -A (全部库)
- 导出全部数据库。
mysqldump -uroot -p123456 --all-databases > /data/mysqlbackup/dboop_dump0401.sql
mysqldump -uroot -p123456 -A > /data/mysqlbackup/dboop_dump0401.sql
–add-drop-database (drop库)
- 在create数据库之前先DROP DATABASE
- 默认关闭，所以一般在导入时需要保证数据库已存在。。 mysqldump -uroot -p123456 -A –add-drop-database > /data/mysqlbackup/dboop_dump0401.sql
–databases, -B (指定库)
- 指定要备份的数据库
- 参数后面所有名字参量都被看作数据库名。
mysqldump -uroot -p123456 --databases test1 test2 > /data/mysqlbackup/all2.sql
–no-create-db, -n (不创建库)
- 只导出数据，而不添加CREATE DATABASE 语句。
mysqldump -uroot -p123456 --host=localhost -A --no-create-db > /data/mysqlbackup/dboop_dump0401.sql
–no-create-info, -t (不创建表)
- 只导出数据，而不添加CREATE TABLE 语句。
mysqldump -uroot -p123456 --host=localhost -A --no-create-info > /data/mysqlbackup/dboop_dump0401.sql
–no-data, -d (不导出数据,仅结构)
- 不导出任何数据，只导出数据库表结构。
mysqldump -uroot -p123456 --host=localhost -A --no-data > /data/mysqlbackup/dboop_dump0401.sql
–routines, -R (存储过程/函数)
- 除了导出数据，还要导出存储过程，自定义函数。
mysqldump -uroot -p123456 --host=localhost -A --routines > /data/mysqlbackup/dboop_dump0401.sql
–tables (表)
- 覆盖-databases(-B)参数
- 需要指定导出的表名
mysqldump -uroot -p123456 --host=localhost --databases test1 --tables hd_acl_entry > /data/mysqlbackup/dboop_dump0401.sql
–triggers (触发器)
- 除了导出数据，还要导出触发器
- 默认启用，用-skip-triggers禁用它。
mysqldump -uroot -p123456 --host=localhost -A --triggers > /data/mysqlbackup/dboop_dump0401.sql
mysqldump -uroot -p123456 --host=localhost -A -skip-triggers > /data/mysqlbackup/dboop_dump0401.sql
–add-drop-table (drop表)
- 在每个CREATE TABLE语句前添加DROP TABLE语句
- 默认开启。
# 默认添加drop语句
mysqldump -uroot -p123456 -A > /data/mysqlbackup/dboop_dump0401.sql
# 取消drop语句
mysqldump -uroot -p123456 -A --skip-add-drop-table > /data/mysqlbackup/dboop_dump0401.sql
–ignore-table (忽略表)
- 不导出指定表。
- 指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。
mysqldump -uroot -p123456 --host=localhost -A --ignore-table=test1.a1 --ignore-table=test1.a2 > /data/mysqlbackup/dboop_dump0401.sql
–add-locks (加锁)
- 备份数据库表时锁定数据库表
- 默认为打开状态，使用–skip-add-locks取消选项
# 默认添加LOCK语句
mysqldump -uroot -p123456 -A > /data/mysqlbackup/dboop_dump0401.sql
# 取消LOCK语句
mysqldump -uroot -p123456 -A --skip-add-locks > /data/mysqlbackup/dboop_dump0401.sql
–lock-all-tables, -x (锁表)
- 锁定所有库中所有的表。这是通过在整个dump的过程中持有全局读锁来实现的。
- 自动关闭-single-transaction 和-lock-tables 选项。
mysqldump -uroot -p123456 --host=localhost -A --lock-all-tables > /data/mysqlbackup/dboop_dump0401.sql
–lock-tables, -l (锁表)
- 对所有表加读锁。
- 默认是打开的，用–skip-lock-tables来关闭
- 当导出多个数据库时，–lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump -uroot -p123456 --host=localhost -A --lock-tables > /data/mysqlbackup/dboop_dump0401.sql
–single-transaction (一致性)
- 通过将导出操作封装在一个事务内,保证导出数据的一致性。
- 只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作。其他引擎不能保证导出是一致的
- 当开启这个选项，要确保导出文件有效(正确的表数据和二进制日志位置)，就要保证没有其他连接会执行如下语句,因为这些语句会导致一致性快照失效
ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
会自动关闭–lock-tables
mysqldump -uroot -p123456 --host=localhost -A --single-transaction > /data/mysqlbackup/dboop_dump0401.sql
–compatible (兼容)
- 导出的数据将和其它数据库或旧版本的MySQL相兼容，它并不保证能完全兼容，而是尽量兼容。
- 值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等
# 要使用几个值，用逗号将它们隔开。
mysqldump -uroot -p123456 -A --compatible=ansi > /data/mysqlbackup/dboop_dump0401.sql
–default-character-set (字符集)
- 设置默认字符集
- 默认值为utf8
mysqldump -uroot -p123456 -A --default-character-set=utf8 > /data/mysqlbackup/dboop_dump0401.sql
—master-data (主从信息)
- 该选项将当前服务器的binlog的位置和文件名追加到输出文件中。
- 设置为1时：将会输出CHANGE MASTER 命令
- 设置为2时：输出的CHANGE MASTER命令前添加注释信息。
- 该选项将打开-lock-all-tables 选项，除非-single-transaction也被指定。
- 该选项自动关闭-lock-tables选项。
mysqldump -uroot -p123456 -A --master-data=2 > /data/mysqlbackup/dboop_dump0401.sql
–events, -E (事件)
- 除了导出数据，还要导出事件
mysqldump -uroot -p123456 -A --events > /data/mysqlbackup/event.sql
–fields-terminated-by (忽略字段)
- 导出文件中忽略给定字段。
- 与-tab选项一起使用，不能用于-databases和-all-databases选项
mysqldump -uroot -p123456 dboop table1 --tab="/data/mysqlbackup/" --fields-terminated-by="nickname"
–hex-blob (二进制)
- 使用十六进制格式导出二进制字符串字段。
- 如果有二进制数据就必须使用该选项。
- 影响到的字段类型有BINARY、VARBINARY、BLOB。
mysqldump -uroot -p123456 -A --hex-blob > /data/mysqlbackup/dboop_dump0401.sql
–insert-ignore (重复值)
- 在插入行时使用INSERT IGNORE语句.
mysqldump -uroot -p123456 --host=localhost -A --insert-ignore > /data/mysqlbackup/dboop_dump0401.sql
–replace (replace into )
- 使用REPLACE INTO 取代INSERT INTO
mysqldump -uroot -p123456 --host=localhost -A --replace > /data/mysqlbackup/dboop_dump0401.sql
—extended-insert
- 使用具有多个VALUES列的INSERT语法。
- 这样使导出文件更小，并加速导入时的速度。
- 默认为打开状态，使用-skip-extended-insert取消选项。
mysqldump -uroot -p123456 -A > /data/mysqlbackup/dboop_dump0401.sql
# 取消选项
mysqldump -uroot -p123456 -A --skip-extended-insert > /data/mysqlbackup/dboop_dump0401.sql
–log-error (输出日志)
- 附加警告和错误信息到给定文件
mysqldump -uroot -p123456 -A --log-error=/data/mysqlbackup/error_log.err > /data/mysqlbackup/dboop_dump0401.sql
–max_allowed_packet
- 设置服务器发送和接受的最大包长度。
mysqldump -uroot -p123456 -A --max_allowed_packet=10240 > /data/mysqlbackup/dboop_dump0401.sql
–set-charset (字符集)
- 将SET NAMES default_character_set加到输出中
- 默认为打开状态，使用–skip-set-charset关闭选项。
mysqldump -uroot -p123456 --host=localhost -A > /data/mysqlbackup/dboop_dump0401.sql
mysqldump -uroot -p123456 --host=localhost -A --skip-set-charset > /data/mysqlbackup/dboop_dump0401.sql
–opt (一些组合)
- 等同于下面的所有操作
–add-drop-table
–add-locks
–create-options
–quick
–extended-insert
–lock-tables
–set-charset
–disable-keys
默认开启, 可以用-skip-opt禁用
mysqldump -uroot -p123456 --host=localhost -A --opt > /data/mysqlbackup/dboop_dump0401.sql
mysqldump -uroot -p123456 --host=localhost -A -skip-opt > /data/mysqlbackup/dboop_dump0401.sql
–quick, -q
- 不缓冲查询，直接导出到标准输出。
- 适用于转储大的表
- 默认为打开状态，使用-skip-quick取消该选项。
mysqldump -uroot -p123456 --host=localhost -A > /data/mysqlbackup/dboop_dump0401.sql
mysqldump -uroot -p123456 --host=localhost -A --skip-quick > /data/mysqlbackup/dboop_dump0401.sql
–result-file, -r (指定结果文件)
- 直接输出到指定文件中。
- 该选项应该用在使用回车换行对（\r\n）换行的系统上（例如：DOS，Windows）。
- 该选项确保只有一行被使用。
mysqldump -uroot -p123456 --host=localhost -A -result-file=/data/mysqlbackup/result_file.txt > /data/mysqlbackup/dboop_dump0401.sql
–where, -w (指定where)
- 只转储给定的WHERE条件选择的记录。
- 注意:如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
mysqldump -uroot -p123456 --host=localhost -A --where="id='1'" > /data/mysqlbackup/dboop_dump0401.sql
已dump完成的文件,分离指定的库表
- 脚本准备
cd /data/soft
wget https://www.dboop.com/download/mysqldumpsplitter.sh
- 从mysqldump中提取单个数据库：
sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract DB --match_str dboop
-
上面的命令将从指定的“dboop_dump0401.sql”sql文件为指定的数据库创建sql，并将其以压缩格式存储到dboop.sql.gz。
-
从mysqldump中提取单个表：
sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract TABLE --match_str tbtest1
-
上面的命令将从指定的“dboop_dump0401.sql”mysqldump文件为指定的表创建sql，并将其以压缩格式存储到tbtest1.sql.gz。
-
从mysqldump中提取所有数据库：
sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract ALLDBS
上面的命令将从指定的“dboop_dump0401.sql”mysqldump文件中提取所有数据库，并将其以压缩格式存储到单个以库名命名的gz文件中。
- 从mysqldump中提取所有表：
sh mysqldumpsplitter.sh --source dboop_dump0401.sql --extract ALLTABLES
- 上面的命令将从指定的“dboop_dump0401.sql”mysqldump文件中提取所有表，并将其以压缩格式存储到单个命名的gz文件中。
经验值
- mysql实例大小:23G
- mysqldump主机:4c+8G (和实例在同一个虚拟机)
- dump备份用时:6分钟
- dump文件还原时间:80分钟
