备份与还原
备份类型:
完全备份:整个数据集
部分备份:只备份数据子集,如部分库或表
增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
二进制日志文件不应该与数据文件放在同一磁盘
冷、温、热备份
冷备:读写操作均不可进行
温备:读操作可执行;但写操作不可执行
热备:读写操作均可执行
MyISAM:温备,不支持热备
InnoDB:都支持
物理和逻辑备份
物理备份:直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快
逻辑备份:从数据库中“导出”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度
备份时需要考虑的因素
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长
备份什么数据
二进制日志、InnoDB的事务日志
程序代码(存储过程、函数、触发器、事件调度器)
服务器的配置文件
备份工具
cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
LVM的快照:先加锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
mysqldump:逻辑备份工具,适用所有存储引擎,温备;支持完全或部分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份
xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
MariaDB Backup: 从MariaDB 10.1.26开始集成,基于PerconaXtraBackup 2.3.8实现
mysqlbackup:热备份, MySQL Enterprise Edition组件
mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库
基于LVM的备份
1.请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
2.记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE
3.创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
4.释放锁
mysql> UNLOCK TABLES;
5.挂载快照卷,执行数据备份
6.备份完成后,删除快照卷
7.制定好策略,通过原卷备份二进制日志
mysqldump使用
mysqldump工具:客户端命令,通过mysql协议连接至mysql服务器进行备份
语法:
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3…]
mysqldump [OPTIONS] –A [OPTIONS]
示例:
备份db数据库
mysqldump db > db.sql
*这条命令只备份数据库里的表数据,不会生成创建数据库的语句
*还原前需要自己创建数据库
加上-B可以生成数据库创建语句
mysqldump -B db > db.sql
备份多个数据库
mysqldump -B db1 db2 db3 > db.sql
备份所有数据库
mysqldump -A > db.sql
*这条命令会备份information_schema和performance_schema之外的所有数据库
备份db数据库中的test表
mysqldump db test > db.sql
mysqldump常见选项:
-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[=#]: 此选项须启用二进制日志
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 一起使用实现,此时只刷新一次日志。
示例:
现有二进制日志文件
mysql-bin.000001
mysql-bin.