MySQL数据备份与恢复之mysqldump
mysqldump备份数据库
1.mysqldump备份数据库属于完全备份,属于文本备份,本质是对数据库执行建库建表创建记录。
2.mysqldump参考:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
mysqldump工具语法
1.mysqldump [OPTIONS] database [tables]
2.mysqldump [OPTIONS] --databases|-A [OPTIONS] DB1 [DB2 DB3…]
3.mysqldump [OPTIONS] --all-databases|-B [OPTIONS]
- 语法1不建议使用,只能备份单库并且备份中没有创建表信息
mysqldump选项说明
1.-A, --all-databases
- 备份所有数据库,含create database信息
2.-B,–databases db1,db2…
- 指定备份的数据库,包括create database语句
3.-E,–events
- 备份相关的所有event scheduler
4.-R,–routines
- 备份所有存储过程和自定义函数
5.–triggers
- 备份表相关触发器,默认启用,用–skip-triggers,不备份触发器
6.–default-character-set=…
- 指定字符集,默认utf8
7.–master-data[=#]
-
1:所备份的数据之前加一条记录为CHANGE MASTER TO语句,非注释,不指定#,默认为1
-
2:记录为注释的CHANGE MASTER TO语句,此选项会自动关闭–lock-tables功能,自动打开-x | --lock-all-tables功能(除非开启–single-transaction)
-
注:此选项须启用二进制日志
8.-F, --flush-logs
- 备份前滚动日志
说明:锁定表完成后,执行flush logs命令,生成新的二进制日志文件,配合-A 或 -B 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和–single-transaction或-x,–master-data 一起使用实现,此时只刷新一次日志。
9.–compact
- 去掉注释,适合调试,生产不使用
10.-d, --no-data
- 只备份表结构
11.-t, --no-create-info
- 只备份数据,不备份create table
12.-n,–no-create-db
- 不备份create database,可被-A或-B覆盖
13.–flush-privileges
- 备份mysql或相关时需要使用
14.** -f, --force**
- 忽略SQL错误,继续执行
15.–hex-blob
- 使用十六进制符号转储二进制列,当有包括BINARY,VARBINARY,BLOB,BIT的数据类型的列时使用,避免乱码
16.-q, --quick
- 不缓存查询,直接输出,加快备份速度
mysqldump之MyISAM备份选项:
MyISAM不支持事务,因此不支持热备,支持温备,必须先锁定要备份的库,启动备份操作锁定方法如下:
1.-x,–lock-all-tables
- 加全局读锁,锁定所有库的所有表,同时加–singletransaction或–lock-tables选项会关闭此选项功能
- 注意: 数据量大时,可能会导致长时间无法并发访问数据库
2.-l,–lock-tables
- 对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,–skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致。
3.注意: 以上选项对InnoDB表一样生效,实现温备,但不推荐使用
mysqldump之InnoDB备份选项
1.–single-transaction
- 先执行START TRANSACTION指令开启事务
- 以事务方式启动备份,因此事务的隔离级别必须为REPEATABLE READ
保证数据可重复读,备份的是启动备份事务时间点那一个的数据库状态
2.选项说明:
- 此选项通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE,即确定备份过程中不会执行DDL语言,否则将导致备份时间点的数据状态改变。
注:备份大型表时,建议将–single-transaction选项和–quick结合一起使用
mysqldump生产环境中的使用
1.innodb备份策略
- mysqldump –uroot –A –F [–E –R] –single-transaction –master-data=1 [–flush-privileges] [–triggers] [–default-character-set=utf8] [–hex-blob] >KaTeX parse error: Expected group after '_' at position 15: BACKUP/fullbak_̲BACKUP_TIME.sql
2.MyISAM备份策略
- mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >KaTeX parse error: Expected group after '_' at position 15: BACKUP/fullbak_̲BACKUP_TIME.sql
mysqldump备份示例
1.示例使用的数据库版本:MariaDB version:5.5.60
2.使用的存储引擎是innodb
【数据库】
# yum install -y mariadb-server
# mysql_secure_installation
对配置文件添加如下两项:
[mysqld]
log-bin
innodb-file-per-table
# systemctl start mariadb
【建库】
- CREATE DATABASE testdb DEFAULT CHARACTER SET utf8;
【建表】
CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,DEFAULT 'M',
PRIMARY KEY (`StuID`)
);
【添加记录】
INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M'),(2,'Shi Potian',22,'M'),(3,'Xie Yanke',53,'M'),(4,'Ding Dian',32,'M'),(5,'Yu Yutong',26,'M');
【查看数据库】
> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | 需要备份
| mysql | 需要备份
| performance_schema |
+--------------------+
【备份】
1、备份数据库
# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 |gzip > /data/`date +%F`_fullbak.sql.gz
2、备份二进制日志
# tar cf /data/binlog.tar /var/lib/mysql/mariadb-bin.*
【更改数据库】
> insert students(name,age)values('Nie Feng',30);
> update students set age=15 where stuid=5;
【数据库损坏】
> drop database hellodb;
【还原数据库】
还原数据库注意事项:
①数据的还原无论什么方式都需要禁止用户访问
②还原的二进制日志记录是没有意义的
还原操作:
①利用防火墙禁止用户访问
②临时关闭二进制日志记录
> set sql_log_bin=0;
③还原备份
> source /data/2019-09-21_fullbak.sql
④还原备份后的更改
注:其中二进制日志中也记录的> drop database hellodb;
# mysqlbinlog /var/lib/mysql/mariadb-bin.000002 > /data/binlogbak.sql
# vim /data/binlogbak.sql
drop database hellodb 找到删库的SQL语句删除
然后再还原:
> source /data/binlogbak.sql
还原完毕!!
mysqldump备份总结
1.mysqldump备份
- mysqldump属于完全备份
- mysqldump还原仅能还原至备份点的数据状态,结合二进制日志还原
- mysqldump适用于数据库不大的场景,可以对数据库进行每天完全备份的场景
2.备份的数据库
- 数据库的备份数据库本机存放一份,远程主机存放两份。
- 建议至少三份。
- 制定合理的备份策略
3.二进制日志
- 必须开启
- 计划任务每天备份
- 增长迅猛,需要监控
- 定期清理
- 建立存放独立分区,与数据隔离,结合READ
- 保存格式选择ROW或者MIXED