目录
MySQL数据库备份和恢复 - 备份恢复概述
- 为什么要备份
- 备份注意要点
- 还原要点
- 备份类型：
- 备份时需要考虑的因素
- 备份什么
- 备份工具
- 冷备份 cp tar
- mysqldump备份工具
- 模拟数据库崩溃，最大限度还原数据
- mysqldump的分库备份
- mysqldump的MyISAM存储引擎相关的备份选项：
- mysqldump的InnoDB存储引擎相关的备份选项：
- 生产环境实战备份策略
- 将误删除了的某个表进行还原
- 备份恢复概述
备份恢复概述
为什么要备份
灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景备份注意要点
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据还原要点
做还原测试，用于测试备份的可用性
还原演练备份类型：
* 完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
* 完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
* 注意：二进制日志文件不应该与数据文件放在同一磁盘
* 冷、温、热备份
冷备：读、写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
* 物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度备份时需要考虑的因素
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长备份什么
数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件备份工具
cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对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来快速备份数据库冷备份 cp tar
- 确定数据库是否关闭，没关闭，关闭掉。（适合于可以停止访问的公司类型）
[root@centos7 ~]#ss -ntl # 确定3306端口关闭
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:6000 *:*
LISTEN 0 5 192.168.122.1:53 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
LISTEN 0 128 127.0.0.1:6012 *:*
LISTEN 0 128 :::111 :::*
LISTEN 0 128 :::6000 :::*
LISTEN 0 128 :::22 :::*
LISTEN 0 128 ::1:631 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 128 ::1:6010 :::*
LISTEN 0 128 ::1:6011 :::*
LISTEN 0 128 ::1:6012 :::*
# 没关闭使用这条命令关闭
[root@centos7 ~]#systemctl stop mariadb
MariaDB [(none)]> show databases; # 备份前建立一个数据库或表用以测试是否可以还原成功
+--------------------+
| Database |
+--------------------+
| db1 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
- 打包并压缩mysql目录
[root@centos7 data]#tar -zcvf mysql-$(date +%Y%m%d-%H%M).tar.gz /data/mysql;
;
;
;
;
;
;
;
;
;
mysqldump: Got error: 1044: "Access denied for user ''@'localhost' to database 'hellodb'" when selecting the database
- 删除库做实验
[root@centos7 ~]# mysqldump hellodb > /data/hellodb.sql # 先导出数据库
MariaDB [(none)]> drop database hellodb; # 删除库
Query OK, 7 rows affected (0.00 sec)
MariaDB [(none)]> show databases; # 查看已经删除掉了
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> create database hello; # 创建一个数据库名字不一样都可以
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use hello # 进入创建的数据库
Database changed
MariaDB [hello]> source /data/hellodb.sql # 把sql脚本读入到这个库里
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [hello]> show tables; # 查看表，表都在。
+-----------------+
| Tables_in_hello |
+-----------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-----------------+
7 rows in set (0.00 sec)这种方法可以恢复，但是数据库的格式和字符集都是默认的，这种方法不建议用。- 第二种方法（推荐使用方法）
-B 挑选指定的数据库做备份
[root@centos7 ~]# mysqldump -B hellodb mysql > /data/hellodb_mysql.sql # 导出生成sql脚本，最好加上时间格式。
[root@centos7 ~]# vim /data/hellodb_mysql.sql # 查看一下这个文件
# 这个sql脚本里面有这一行是用来创建库和定义库的格式的，加上-B才会有这一行。
CREATE DATABASE `hellodb` ;
USE `hellodb`;
MariaDB [(none)]> show create database hellodb; # 查看hellodb数据库的字符集和格式
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| hellodb | CREATE DATABASE `hellodb` |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)- 发送到远程主机做测试
[root@centos7 ~]# scp /data/hellodb_mysql.sql 192.168.39.27:/root
root@192.168.39.27's password:
hellodb_mysql.sql 100% 509KB 85.6MB/s 00:00 - 导入sql脚本
MariaDB [(none)]> show databases; # 远程主机的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> source /root/hellodb_mysql.sql # 导入sql脚本
MariaDB [mysql]> show databases; # 查看数据库生成
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [mysql]> show create database hellodb; # 查看hellodb字符集，是和原本一摸一样的。
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| hellodb | CREATE DATABASE `hellodb` |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
- 第三种方法 （所有的数据库做备份） （完全备份）
[root@centos7 ~]# mysqldump -A |gzip > /data/All.sql.gz # 备份连带压缩一起执行
[root@centos7 ~]# ll /data/
total 140
-rw-r--r-- 1 root root 140945 Nov 30 16:00 All.sql.gz
# 还原下面会做现在就解释一下。模拟数据库崩溃，最大限度还原数据
[root@centos7 ~]# mysqldump -A --master-data=2 |gzip > /data/all_'date +%F'.sql.gz
mysqldump: Error: Binlogging on server not active
# 上面不成功是因为二进制日志没有启用- 启用二进制日志
# 事先创建好二进制日志存放的路径
[root@centos7 ~]# chown -R mysql:mysql /data/mysql/ # 创建完路径记得更改权限
[root@centos7 ~]# vim /etc/my.cnf # 修改配置文件
[mysqld]
log-bin=/data/mysql/bin_log # 指定二进制日志存放路径（最好和数据库是分开的） 最后的是指定日志的前缀。
[root@centos7 ~]# systemctl restart mariadb.service # 重启服务- 完全备份数据库
[root@centos7 ~]# mysqldump -A --master-data=2 |gzip > /data/all_`ate +%F`.sql.gz
[root@centos7 ~]# ll /data/
total 140
-rw-r--r-- 1 root root 141043 Nov 30 16:29 all_2019-11-30.sql.gz- 模拟添加一条记录，创建一个账号。
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> insert students (name,age,gender)value('a',20,'M');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | a | 20 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
MariaDB [hellodb]> grant all on hellodb.* to test@'192.168.39.%' identified by 'centos'; # 创建一个账号
Query OK, 0 rows affected (0.00 sec)- 假设数据库崩溃了（删之前确定二进制日志独立出来了）
[root@centos7 ~]# rm -rf /var/lib/mysql
[root@centos7 backup]# mysqlbinlog --start-position=245 /data/mysql/bin_log.000006|grep -C3 -i drop
# at 856 # 这个位置
#191130 18:07:32 server id 1 end_log_pos 961 Query thread_id=35 exec_time=error_code=0
SET TIMESTAMP=1575108452;
DROP TABLE `test`
;
DELIMITER ;
# End of log file
- 然后打开导出来的文件删掉或注释掉误操作
[root@centos7 backup]# vim /backup/inc.sql
# at 856
#191130 18:07:32 server id 1 end_log_pos 961 Query thread_id=35 exec_time=0 error_code=0
SET TIMESTAMP=1575108452;
#DROP TABLE `test` # 这行注释掉其他的可以不用管
;
DELIMITER ;
# End of log file- 删除数据库（可以在一个新的主机上做这个实验也可以）
[root@centos7 backup]# rm -rf /var/lib/mysql/* # 删除数据库
[root@centos7 backup]# systemctl restart mariadb.service # 重启服务生成数据库
[root@centos7 backup]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql 16384 Nov 30 18:33 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 30 18:33 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Nov 30 18:33 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Nov 30 18:33 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Nov 30 18:33 ib_logfile1
drwx------ 2 mysql mysql 4096 Nov 30 18:33 mysql
srwxrwxrwx 1 mysql mysql 0 Nov 30 18:33 mysql.sock
drwx------ 2 mysql mysql 4096 Nov 30 18:33 performance_schema
drwx------ 2 mysql mysql 6 Nov 30 18:33 test- 登录数据库关闭二进制日志
[root@centos7 backup]# mysql
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)- 还原完全备份
MariaDB [(none)]> source /backup/allbackup_2019-11-30_18:03:08.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
.....(省略)
MariaDB [test]> show databases; # 查看数据库是否还原
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| hello |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
- 再还原增量备份(注意是修改过误操作的那个文件)
MariaDB [test]> source /backup/inc.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 1 row affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR at line 65 in file: '/backup/inc.sql': No query specified # 这里没有执行就是我注释掉的drop命令（报错不用管）
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)- 开启二进制日志
MariaDB [db1]> set sql_log_bin=1; # 确保数据还原完毕再打开
Query OK, 0 rows affected (0.00 sec)- 查看数据是否还原
MariaDB [db1]> show tables; # 表没有被删掉
+---------------+
| Tables_in_db1 |
+---------------+
| test |
+---------------+
1 row in set (0.00 sec)
MariaDB [db1]> select * from test; # 添加的记录也在
+----+------+--------+
| id | name | mobile |
+----+------+--------+
| 1 | rose | |
| 2 | jack | |
+----+------+--------+
2 rows in set (0.00 sec)做以上实验确保你的二进制日志独立于数据库之外。
