mysqldump
主要参数
-A --all-databases # 导出所有数据库
-B --databases # 指定数据库, 支持多库; 单库时可以省略
--tables # 指定表, 会覆盖 --databases or -B 选项
--ignore-table=db1.t1 # 忽略表, 指定多个
-F, --flush-logs
# 开始导出前刷新 binlog 日志, 方便后续的增量备份或添加从库
注意: 假如一次导出多个数据库(使用选项 --databases 或者 --all-databases),
将会逐个数据库刷新日志;
除使用 --lock-all-tables 或者 --master-data 外;
在这种情况下, 日志将会被刷新一次, 相应的所以表同时被锁定;
因此, 如果打算同时导出和刷新日志应该使用 --lock-all-tables 或者 --master-data 和 --flush-logs;
会执行 FLUSH TABLES, 等待关闭全部表
--extended-insert # 将多行数据合并成一个 insert 语句, 默认有开启
--skip-extended-insert # 关闭 sql 合并, 导出 sql 时, 将每行数据生成一个 insert 语句
-t, --no-create-info # 只有数据, 不包含创建表等创建动作, 即只有 insert
--master-data=[1,2]
--source-data[=#]
# 5.8+ 改名为 source-data
# 将 binlog 的位置和偏移量 追加到输出文件中;
# 1 输出 CHANGE MASTER 命令;
# 2 输出的 CHANGE MASTER 命令前有添加注释信息;
# 该选项将打开 --lock-all-tables 选项,
# 除非 --single-transaction 也被指定, 该选项自动关闭 --lock-tables 选项;
# 添加该选项时, 为了获取 show master status 信息, 会获取一次全局锁,
# 不应该高频率地使用此选项, 单库备份这种频率较低的可以使用
--dump-slave[=#]
--dump-replica[=#]
# 和 source-data 效果一样, 只是方便从库添加
--include-master-host-port
# 配合 --dump-slave 使用, 在文件内添加主节点的ip和端口, 方便直接添加从库
-w, --where=name # 只备份符合条件的, 一般用于增量备份
-d --no-data # 只导出表结构
-q --quick
# 强制 mysqldump 从服务器每次查询一行数据而不是查询整个表;
# 避免大表导出时查询刷爆内存且热点数据被刷出缓冲池, 通常建议总是启用该选项;
--single-transaction
# 在单个事务中执行动作; 保证一致性和解决 锁表的问题
# 当只使用此选项进行备份时, 不会产生表锁
# 适用于 InnoDB 存储引擎, 需要确保导出时无 DDL 操作
# 此选项会自动关闭 --lock-tables
-l, --lock-tables
# 写锁, 导出过程中依次锁住每个 schema 下所有表(只能保证各 schema 下表导出的一致性),
# 被锁的表只能读, MyISAM 存储引擎常用
--skip-lock-tables
-x, --lock-all-tables
# 提交请求锁定所有数据库中的所有表, 以保证数据的一致性;
# 这是一个全局读锁, 并且自动关闭 --single-transaction 和 --lock-tables 选项;
# 适合 MyISAM 存储引擎
--flush-privileges
# 有与 mysql 这个库相关的数据导出时, 最好是添加这个
# 在转储 mysql 数据库后, 发出一个 FLUSH PRIVILEGES 语句
--triggers # 转储触发器, 默认关闭
--skip-triggers
默认导出表中是带着触发器的, 不要触发器可以用 --skip-triggers
-n, --no-create-db
-Y, --all-tablespaces 导出全部表空间
-y, --no-tablespaces # Do not dump any tablespace information 不导出任何表空间信息
--add-drop-database
--add-drop-database Add a DROP DATABASE before each create.
--add-drop-table # 在每个 create 语句之前增加一个 drop table; 默认为 if drop
--skip-add-drop-table # 不添加 if drop; 直接就是 create
# data
--add-locks # 在 INSERT 语句前添加 lock
--allow-keywords # 允许使用关键字 做为列名
--ignore-error=name
--ignore-table=name
-E, --events # Dump events 事务; mysqldump 默认没有导出事务和存储过程
-R --routines # 转储存储过程(函数和过程)
--hex-blob
# 以十六进制格式转储二进制字符串(BINARY, VARBINARY, BLOB);
# 避免导出的二进制字符串乱码
--set-gtid-purged=OFF # 关闭 gtid 相关的事件转储
--net-buffer-length=
# TCP/IP和套接字通信的缓冲区大小
# 导出值不能比目标数据库的值大
--max-allowed-packet=
# 发送到服务器或从服务器接收的最大数据包长度
--tz-utc # 需要跨时区导入导出时, 需要添加
# 会自动转换 TIMESTAMP 字段内容为 utc0 时区的时间,
# 并设置在导出文件顶部设置时区 TIME_ZONE='+00:00'
# 只影响 TIMESTAMP 类型, DATETIME 等不受影响实例
单表
# 普通备份
# DROP TABLE IF EXISTS; CREATE TABLE; LOCK TABLES; INSERT INTO; UNLOCK TABLES;
mysqldump db t1 > t1.sql
# 备份表数据 - 只有数据不含创建表
# LOCK TABLES; INSERT INTO; UNLOCK TABLES;
mysqldump -t db t1 > t1.sql
# 备份表结构
# DROP TABLE IF EXISTS; CREATE TABLE;
mysqldump -d db1 t_1 > t_1.sql
# 多表
mysqldump db t1 t2 t3 > t_all.sql
# 排除部分表
mysqldump db1 --ignore-table=db1.test1 --ignore-table=db1.test2 > /data/bak/db1.sql
# 指定 where 条件导出表的部分数据
mysqldump -w "id=6032" t_1 > /tmp/where.sql单库
# 普通备份; 即多个 普通表 备份的组合
mysqldump db1 > db1-db.sql
# 仅导出单个 db 数据
mysqldump -u root -p db1 -t > /data/bak/db1.sql
# 单库备份 - 只备份表结构
mysqldump -d -B db1 > db1-db.sql多库
# 全量备份所有内容 - 常用
mysqldump -u root -p \
--all-databases --add-drop-table --routines --triggers --events \
> /path/to/full_backup_$(date +%Y%m%d).sql
# 导出所有 db 结构和数据
mysqldump -uroot -p -A > /data/bak/all.sql
# 导出所有 db 结构
mysqldump -uroot -p -A -d > /data/bak/all_struct.sql
# 仅导出所有 db 数据不导表结构
mysqldump -uroot -p -A -t > /data/bak/all_data.sql
# 导出多个指定 db
mysqldump --databases db1 db2 > /data/bak/muldbs.sql特殊场景
# 单库备份命令(推荐); 在单个事务中执行备份, 并且刷新 binlog
mysqldump -h 10.2.1.5 -u root -p -A \
--source-data=2 --single-transaction --flush-logs > back-2023-03-21.sql
mysqldump --master-data=2 --lock-all-tables --flush-logs --all-databases > /root/alldb.sql
# 普通备份, 不记录 gtid
mysqldump -u root -p -B doc \
--set-gtid-purged=OFF \
--single-transaction > db_doc-2023-02-09.sql
# 备份 -> 恢复 | 异机恢复
mysqldump -h host1 -u root -p -B db1 --tables t1 | mysql -h host2 -u user1 -p db2
# 备份-压缩-恢复 适合小数据量场景
mysqldump -h host1 -u user1 -p'pw' mydb | gzip > mydb.sql.gz
gunzip < mydb.sql.gz | mysql -h host2 -u user1 -p'pw' mydb
# 条件备份+过滤恢复+严格确认数据
mysqldump -u root -p -S /data/mysql_3306/mysql.sock \
--databases cmcc --tables t_name \
--where='VERIFY_TIME BETWEEN "2018-03-29 03:00:00" and "2018-03-29 03:59:59"' >> c1.sql
grep --ignore-case 'insert into `t_name`' c1.sql >> xxx.sql
sed -i '/t_name/s/t_name/t_name_0711/g' xxx.sql
/opt/app/mysql/bin/mysql -u root -p cmcc < xxx.sql
INSERT INTO t_name SELECT * FROM t_name_0711;脚本实例
脚本1
#!/bin/bash
# mysql_backup
# 0 3 * * * sh /bigdata/mysql_backup.sh >> /bigdata/mysql_backup.log 2>&1 &
# 完善的备份机制
# 1. 启动前检查实例是否具备可备份状态;
# 包括是否在线, 当前负载，磁盘空间是否足够，远程服务器是否能连接
# 2. 备份 -> 压缩 -> 异地同步
# 3. md5 检验, 避免以后文件传输过程中损坏
DB_USER="root"
DB_PASS="password"
DB_HOST="192.168.1.100"
BACK_BASE_DIR="/bigdata/mysql"
LOG_FILE=${BACK_BASE_DIR}/backup.log
DUMP="/usr/local/mysql/bin/mysqldump"
REMOTE_SERVER="10.2.1.5"
REMOTE_USER="user1"
REMOTE_DIR="/data/mysql_backup"
function log() {
echo "$(date "+%F %H:%M:%S") $@" >> $LOG_FILE
}
#===================================================================
# pre
# 获取当前日期 2024-02-03
DATE=`date +%F`
BACK_DIR=${BACK_BASE_DIR}/${DATE}
BACK_FILE=${BACK_DIR}/mysql_backup_${DATE}.sql
log "start backup"
mkdir -p $BCK_DIR/$DATE
#===================================================================
$DUMP -u$DB_USER -p$DB_PASS -h$DB_HOST --all-databases > ${BACK_FILE}
log "backup end, start compress"
#===================================================================
gzip ${BACK_FILE}
# 可选并行压缩
# pigz -9 -p 24 ${BACK_FILE}
log "backup task compress end"
#==========================================================
# CHECK FILE MD5SUM
log "pre check file md5sum"
md5sum ${BACK_FILE}.gz >> $LOG_FILE
log "check file md5sum end"
#==========================================================
# old file clear
yestoday=$(date -d '-7 day' +%Y-%m-%d) # 取 7 天前的时间
log "delete old backup file"
log "del ${BACK_BASE_DIR}/${yestoday}"
rm -rf ${BACK_BASE_DIR}/${yestoday}
log "del old backup file end"
# 只保留最近一周的备份
# find ${BACK_BASE_DIR} -mtime +2 -exec rm -rf {} \;
#==========================================================
log "start sync backup file to remote server"
scp -r ${BACK_DIR} ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_DIR}
log "all task end"
#==========================================================脚本2
#!/bin/sh
# 1. 先停止复制 - 保障一致性
# 2. 备份完成后再开启同步
mkdir -p /server/backup/
mysql -u$USER -p$PASS -S $SOCK -e "stop slave SQL_THREAD;"
# 实际上还需要排除系统库 mysql|information_schema|performance_schema
DB_LIST=$(mysql -u$USER -p$PASS -S $SOCK -e "show databases"|sed '1,2d'|/bin/egrep -v "${EXCLUDE[*]}")
for dbname in ${DB_LIST}
do
TABLE_LIST=$(mysql -u$USER -p$PASS -S $SOCK -e "show tables from $dbname;"|sed '1d')
for tablename in ${TABLE_LIST}
do
mkdir -p /server/backup/$DATE/$dbname
mysqldump --lock-tables=0 -u$USER -p$PASS -S $SOCK ${dbname} $tablename |gzip >/server/backup/$DATE/${dbname}/${dbname}_${tablename}_$DATE.sql.gz
done
done
mysql -u$USER -p$PASS -S $SOCK -e "start slave SQL_THREAD;"关于备份时的锁
避免只使用 --master-data=2 选项, 此选项会获取全局锁, 大量表频繁获取时, 容易阻塞业务。
最好是始终使用 --single-transaction 参数, 避免一直锁定。
--lock-all-tables 或 --lock-tables 参数, 会加锁；
加 --master-data=2 或 --flush-logs 参数时, 会执行:
FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;
不加锁, 但是会导致备份时间延长, 或无法备份完成
FLUSH TABLES
FLUSH TABLES 关闭所有打开的表, 强制关闭所有正在使用的表, 并刷新查询缓存和预准备语句缓存, 不会刷新脏块, 会被锁阻塞, 会等待所有正在运行的 SQL 执行结束
如果当前有正在运行的慢 SQL, 执行 FLUSH TABLES; 就会被阻塞, 需要等待 SQL 执行结束
FLUSH TABLES WITH READ LOCK 关闭所有打开的表并使用全局读锁锁定所有数据库的所有表, 不会刷新脏块,会被锁阻塞 如果存在锁, FLUSH 会直接返回错误
最后更新于