1、mysqldump 说明
逻辑备份工具
mysqldump，mydumper，phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份
# 支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] database [tables]
# 支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
# 备份所有数据库，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS] # 常见通用选项
-u, --user=name User for login if not current user
-p, --password[=name] Password to use when connecting to server
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name… #指定备份的数据库，包括create database语句
-E, --events： #备份相关的所有event scheduler
-R, --routines： #备份所有存储过程和自定义函数
--triggers： #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #注意：MySQL8.0.26版以后，此选项变为--source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--
single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force #忽略SQL错误，继续执行
--hex-blob #使用十六进制符号转储二进制列，当有包括
BINARY,VARBINARY,BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick #不缓存查询，直接输出，加快备份速度# mysqldump的MyISAM存储引擎相关的备份选项
# MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
# 以上选项对InnoDB表一样生效，实现温备，但不推荐使用# mysqldump的InnoDB存储引擎相关的备份选项
#InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用
--single-transaction
# 此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
# 此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用
2、生产环境建议备份策略
# InnoDB建议备份策略
mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql# MyISAM建议备份策略
mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql3、mysqldump 备份还原案例
3.1、特定数据库的备份脚本
[root@centos79-mysql01 ~]# cat mysql_backup.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=123456
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p"$PASS" -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
[root@centos79-mysql01 ~]# bash mysql_backup.sh
[root@centos79-mysql01 ~]# ll /backup/
total 4
-rw-r--r-- 1 root root 2024 Nov 19 10:17 hellodb_2022-11-19_10-17-12.sql.gz
3.2、分库备份并压缩
for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip
> /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev
'^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -
B $db | gzip > /backup/$db.sql.gz;done
[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ev
'^(Database|information_schema|performance_schema)$' | sed -rn 's#(.*)#mysqldump
-B \1 | gzip > /backup/\1.sql.gz#p' |bash
[root@centos8 ~]#mysql -uroot -e 'show databases'|sed -rn
'/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 |
gzip > /backup/\1.sql.gz#p' |bash[root@centos79-mysql01 ~]# cat backup_db.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
[ -d "$DIR" ] || mkdir $DIR
for DB in `mysql -uroot -p123456 -e 'show databases' | grep -Ev "^Database|.*schema$"`;do
mysqldump -uroot -p123456 -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
done
[root@centos79-mysql01 ~]# bash backup_db.sh
[root@centos79-mysql01 ~]# ll /backup/
total 260
-rw-r--r-- 1 root root 1995 Nov 19 10:29 hellodb_2022-11-19_10-29-30.sql.gz
-rw-r--r-- 1 root root 235990 Nov 19 10:29 mysql_2022-11-19_10-29-30.sql.gz
-rw-r--r-- 1 root root 22299 Nov 19 10:29 sys_2022-11-19_10-29-30.sql.gz
3.3、完全备份和还原
# 开启二进制日志
[root@centos79-mysql01 ~]# vim /etc/my.cnf
[mysqld]
log-bin
# 备份
[root@centos79-mysql01 ~]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 | gzip > /backup/all-`date +%F`.sql.gz
# 删除数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> drop database hellodb；
# 还原
[root@centos79-mysql01 ~]# cd /backup/
[root@centos79-mysql01 backup]# ll
total 192
-rw-r--r-- 1 root root 193887 Nov 19 10:32 all-2022-11-19.sql.gz
[root@centos79-mysql01 backup]# gzip -d all-2022-11-19.sql.gz
[root@centos79-mysql01 backup]# ll
total 864
-rw-r--r-- 1 root root 884025 Nov 19 10:32 all-2022-11-19.sql
mysql> set sql_log_bin=off;
mysql> source /backup/all-2022-11-19.sql
mysql> set sql_log_bin=on;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)3.4、利用二进制日志，还原数据库最新状态
# 建议二进制日志与数据分开存放
[mysqld]
log-bin=/data/mysql/mysql-bin
# 完全备份，并记录备份的二进制位置
[root@centos79-mysql01 ~]# mysqldump -uroot -p123456 -A -F --default-character-set=utf8 --single-transaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
# 更新数据库hellodb数据
mysql> insert students (name,age,gender)value('mage',20,'M');
mysql> insert students (name,age,gender)value('wangning',22,'M');^C
# 删除数据库hellodb
[root@centos79-mysql01 ~]# rm -rf /data/mysql/hellodb
# 还原数据库
[root@centos79-mysql01 backup]# gzip -d all_2022-11-19.sql.gz
mysql> show master logs;
+-----------------------------+-----------+
| Log_name | File_size |
+-----------------------------+-----------+
| centos79-mysql01-bin.000001 | 177 |
| centos79-mysql01-bin.000002 | 10243 |
| centos79-mysql01-bin.000003 | 720 |
+-----------------------------+-----------+
mysql> set sql_log_bin=0;
mysql> source /backup/all_2022-11-19.sql
# 此时可以在数据库中查询到备份前的所有数据
# 恢复备份后更新的数据
[root@centos79-mysql01 backup]# grep '^-- CHANGE MASTER TO' all_2022-11-19.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='centos79-mysql01-bin.000003', MASTER_LOG_POS=154;
[root@centos79-mysql01 backup]# mysqlbinlog /data/mysql/centos79-mysql01-bin.000003 --start-position=154 > in.sql
mysql> set sql_log_bin=0;
mysql> source /backup/in.sql
mysql> set sql_log_bin=1;
# 恢复后可查看所有丢失的数据
mysql> use hellodb;
mysql> select * from students;
3.5、mysqldump 和二进制日志结合实现差异(增量)备份
# 完全备份，并记录备份的二进制位置
[root@centos79-mysql01 ~]# mysqldump -uroot -p123456 -A -F --default-character-set=utf8 --single-transaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
# 数据库持续更新数据
# 从备份时记录的二进制位置往后继续备份，实现差异(增量)备份
[root@centos79-mysql01 backup]# grep '^-- CHANGE MASTER TO' all_2022-11-19.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='centos79-mysql01-bin.000003', MASTER_LOG_POS=154;
[root@centos79-mysql01 backup]# mysqlbinlog /data/mysql/centos79-mysql01-bin.000003 --start-position=154 > in.sql
# 如果存在centos79-mysql01-bin.000004、centos79-mysql01-bin.000005则将两个二进制日志追加进备份文件
[root@centos79-mysql01 backup]# mysqlbinlog /data/mysql/centos79-mysql01-bin.000004 >> in.sql
[root@centos79-mysql01 backup]# mysqlbinlog /data/mysql/centos79-mysql01-bin.000005 >> in.sql3.6、恢复误删除的表
条件说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数
据库还原到10：10的状态，且恢复被删除的students表
# 模拟2：30做完全备份
[root@centos79-mysql01 ~]# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 | gzip > /backup/all_`date +%F_%T`.sql.gz
# 完全备份后，数据库持续更新
mysql> insert students (name,age,gender) values('mali',20,'f');
mysql> insert students (name,age,gender) values('mayun',20,'f');
mysql> insert students (name,age,gender) values('mahuateng',20,'f');
mysql> insert students (name,age,gender) values('jialin',20,'f');
# 模拟10：00误删除了表students
mysql> drop table students;
# 其他表持续更新
mysql> insert teachers (name,age,gender)values('hujiang',30,'M');
Query OK, 1 row affected (0.00 sec)
mysql> insert teachers (name,age,gender)values('linyan',30,'M');
Query OK, 1 row affected (0.00 sec)
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | hujiang | 30 | M |
| 6 | linyan | 30 | M |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
# 10：10发现表删除，进行还原
# 停止外部对数据库的访问
# 从完全备份中，找到二进制位置
mysql> show master logs;
+-----------------------------+-----------+
| Log_name | File_size |
+-----------------------------+-----------+
| centos79-mysql01-bin.000001 | 177 |
| centos79-mysql01-bin.000002 | 10243 |
| centos79-mysql01-bin.000003 | 778 |
| centos79-mysql01-bin.000004 | 2039 |
+-----------------------------+-----------+
4 rows in set (0.00 sec).
[root@centos79-mysql01 backup]# gzip -d all_2022-11-19_13\:16\:41.sql.gz
[root@centos79-mysql01 backup]# grep '^-- CHANGE MASTER TO' all_2022-11-19_13\:16\:41.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='centos79-mysql01-bin.000004', MASTER_LOG_POS=154;
# 备份从完全备份后产生的二进制日志
[root@centos79-mysql01 backup]# mysqlbinlog /data/mysql/centos79-mysql01-bin.000004 --start-position=154 > in.sql
# 找到误删除的语句，从备份中删除此语句
[root@centos79-mysql01 backup]# vim in.sql
DROP TABLE `students` /* generated by server */
[root@centos79-mysql01 backup] sed -i.bak '/^DROP TABLE/d' in.sql
# 利用完全备份和修改过的二进制日志进行还原
mysql> set sql_log_bin=0;
mysql> source /backup/in.sql
mysql> source /backup/all_2022-11-19_13:16:41.sql
mysql> set sql_log_bin=1;
# 查看表中数据，恢复成功
mysql> select * from teachers;
mysql> select * from students;
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
