5 备份和恢复
5.1 备份恢复概述
5.1.1 为什么要备份
灾难恢复:硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
5.1.2 备份类型
- 完全备份,部分备份
完全备份:整个数据集
部分备份:只备份数据子集,如部分库或表
- 完全备份、增量备份、差异备份
增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂
差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单
注意:二进制日志文件不应该与数据文件放在同一磁盘
5.1.3 备份什么
- 数据
- 二进制日志、InnoDB的事务日志
- 用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
- 服务器的配置文件
5.1.4 备份注意要点
- 能容忍最多丢失多少数据
- 备份产生的负载
- 备份过程的时长
- 温备的持锁多久
- 恢复数据需要在多长时间内完成
- 需要备份和恢复哪些数据
5.1.5 还原要点
- 做还原测试,用于测试备份的可用性
- 还原演练,写成规范的技术文档
5.1.6 备份工具
- cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份(需要停服务,无法保证数据时间一致性)
- LVM的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份(用的不多)
- mysqldump:逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备;支持完全或部分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份(主流工具)
- xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份(相对较少)
- MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现(付费)
- mysqlbackup:热备份, MySQL Enterprise Edition组件
- mysqlhotcopy:PERL 语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库(用的越来越少)
5.1.6 基于 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) 制定好策略,通过原卷备份二进制日志
5.1.7 实战案例:数据库冷备份和还原
#在目标服务器(10.0.0.18)安装mariadb-server,不启动服务
[root@centos8 ~]#dnf install mariadb-server
#在源主机(10.0.0.8)先启用二进制日志 (实验需要)
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin=/data/logbin/mysql-bin
[root@centos8 ~]#mkdir /data/logbin
[root@centos8 ~]#chown mysql.mysql /data/logbin
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#ll /data/logbin/
total 8
-rw-rw---- 1 mysql mysql 328 Jun 12 21:37 mysql-bin.000001
-rw-rw---- 1 mysql mysql 30 Jun 12 21:37 mysql-bin.index
#在源主机(10.0.0.8)执行停止服务
[root@centos8 ~]# systemctl stop mariadb
#复制相关文件
[root@centos8 ~]# scp /etc/my.cnf.d/mariadb-server.cnf 10.0.0.18:/etc/my.cnf.d/
[root@centos8 ~]# scp -r /var/lib/mysql/* 10.0.0.18:/var/lib/mysql/
[root@centos8 ~]# scp -r /data/logbin/ 10.0.0.18:/data/ #10.0.0.18事先存在/data/目录
#保留属性:推荐使用
[root@centos8 ~]#rsync -av /var/lib/mysql/ 10.0.0.18:/var/lib/mysql/
[root@centos8 ~]#rsync -av /data/logbin 10.0.0.18:/backup #数据库更新之前的二进制日志
[root@centos8 ~]#rsync -a /etc/my.cnf.d/mariadb-server.cnf 10.0.0.18:/backup
[root@centos8 ~]#ll /data/logbin/
total 8
-rw-rw---- 1 mysql mysql 351 Jun 12 21:44 mysql-bin.000001
-rw-rw---- 1 mysql mysql 30 Jun 12 21:37 mysql-bin.index
-rw-rw---- 1 mysql mysql 0 Jun 12 21:44 mysql-bin.state
#若源主机在备份之后又更新了数据库,则需要将最新的二进制日志也进行备份
[root@centos8 ~]#rsync -av /data/logbin 10.0.0.18:/backup/newlogbin
[root@centos8 ~]#ll /backup/newlogbin/
total 12
-rw-r----- 1 root root 351 Jun 13 23:16 mysql-bin.000001
-rw-r----- 1 root root 913 Jun 13 23:16 mysql-bin.000002
-rw-r----- 1 root root 60 Jun 13 23:16 mysql-bin.index
[root@centos8 ~]#mysqlbinlog /backup/newlogbin/mysql-bin.000002 > logbin.sql #mysqlbinlog可以查看二进制日志
MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> source /root/logbin.sql
MariaDB [hellodb]> set sql_log_bin=1;
#到此更新后的数据也都还原了
#在目标主机(10.0.0.18)执行
[root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql/
[root@centos8 ~]#chown -R mysql.mysql /data/logbin/
[root@centos8 ~]#systemctl start mariadb
注意:
还原数据时要把二进制日志功能停掉sql_log_bin,不然还原的过程也会存到日志里
5.2 mysqldump备份工具
5.2.1 mysqldump 说明
逻辑备份工具:
mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump:是MySQL的客户端命令,通过mysql协议连接至mysql服务器进行备份
命令格式:
mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份,但数据库本身定义不备份,造成的问题较多,如字符集,排序规则等若没有提前备份,则数据库不可用
mysqldump [OPTIONS] –B DB1 [DB2 DB3...] #支持指定数据库备份,包含数据库本身定义也会备份
mysqldump [OPTIONS