MYSQL的备份与恢复--逻辑备份mysqldump

我叫张贺，贪财好色。一名合格的LINUX运维工程师，专注于LINUX的学习和研究，曾负责某中型企业的网站运维工作，爱好佛学和跑步。
个人博客：传送阵
笔者微信：zhanghe15069028807

目录

• 0、备份与恢复概述
• 1、逻辑备份-完整备份与恢复
• 2、逻辑备份-增量备份与恢复
  • （1）环境准备
  • （2）恢复全量数据
  • （3）恢复增量备份
• 3、新来的开发妹子删了库！
  • （1）模拟环境准备
  • （2）全备
  • （3）再次插入数据
  • （4）开发妹子的误操作
  • （5）恢复全备
  • （6）进一步小心地恢复，跳过DELETE和DROP语句

0、备份与恢复概述

---

系统版本	mysql版本	安装方式
centos7	5.7.28	二进制安装

数据库要备份的内容：

• dababase数据文件
• binlog日志文件
• my.cnf配置文件

注：
1、所有的数据库备份文件都应当放在非数据库本地，而且建议备份多份。
2、仅备份没啥用，我们备份的目的是为了恢复，能够正常恢复才是最终目标，所以要经常进行恢复的演练，通常，恢复演练也是运维工作者的日常工作之一。

数据备份要考虑的因素：

• 数据的一致性
• 服务的可用性

数据库的备份模式：

• 完全备份
• 增量备份：在上一次备份的基础上，仅备份有变化的部分。
• 差异备份：在完全备份的基础上，仅备份有变化的部分。

逻辑备份与物理备份的区别：

• 逻辑备份：备份的是SQL语句，效率较低，用于中小型企业。
• 物理备份：备份的具体的数据，效率较高，用于大型企业。

1、逻辑备份-完整备份与恢复

---

mysql自带逻辑备份工具mysqldump，可以保证数据的一致性和服务的可用性。

>mysqldump -h 服务器 -u 用户名  -p 密码 数据库名 > 备份文件.sql
//常用参数
-A，--all-databases  #备份所有库
-B，--databases      #备份多个库
--single-transaction #保证数据的一致性和服务的可用性
--master-data=1|2    #通常等于1，记录binlog日志位置与文件名，追加至备份文件中
-F，--flush-logs     #备份之前刷新日志
-E，--events         #备份事件调度器代码
--triggers           #备份触发器
-R，--routines       #备份存储过程和存储函数

第一步：进入mysqld创建一个数据库test1

mysql -u root -p"cba-123"
mysql>create database test1;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+

创建好test1数据库之后，我们就要开始全量备份了，备份之前要先开启二进制日志。

第二步：在/etc/my.cnf文件里面开启二进制日志

vim /etc/my.cnf
[mysqld]
basedir=/soft/mysql
datadir=/soft/mysql/data
default_password_lifetime=0
server-id = 241				#id是做标识，随便填写
log-bin=/log/mysql/bin-log  #设置二进制日志存放的位置

第三步：保证二进制日志文件的存在和权限

mkdir -p /log/mysql/ ; touch /log/mysql/bin-log ; 
chown -R mysql:mysql /log/mysql/bin-log；

第四步：准备好全量备份好的文件存放的目录和权限

mkdir /backup/mysql -p
chown -R mysql:mysql /backup/mysql/

第五步：重启数据库进行全量备份

/etc/init.d/mysqld restart
mysqldump -uroot -pcba-123 
--all-databases 
--single-transaction 
--master-data=1 
--flush-logs > /backup/mysql/$(date +%F%H)-mysql-all.sql

第六步：破坏数据库，删除数据表

/etc/init.d/mysqld stop
rm -rf /soft/mysql/data/*

第七步：向全量备份文件里面追加不记录二进制日志的命令

sed -i '23a SET sql_log_bin=0;' /backup/mysql/2019-11-2810-mysql-all.sql 

向全量备份文件里面追加不记录二进制日志的命令的原因是因为我们在恢复的时候要重新执行一次SQL语句，这个语句没有记录的必要，如果记录的话还可能会导致恢复失败。

第八步：重启初始化数据库、启动数据库、并修改密码

/soft/mysql/bin/mysqld --initialize --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data
/etc/init.d/mysqld start
mysql -u root -p'U0ln8LE!ue=#'
mysql> ALTER USER USER() IDENTIFIED BY '123';
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

由于这是一个新的数据库，里面只有默认的库，并没有test1数据库。

第九步：导入全备的数据

mysql -u root -p'123' < /backup/mysql/2019-11-2810-mysql-all.sql 
mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
+--------------------+

其实在数据库内部也可以进行恢复，如下所示

mysql> set	sql_log_bin=0;
mysql> source /backup/mysql/2019-11-2810-mysql-all.sql 

导入之后当前的密码会不变，当进入数据库flush privileges之后，密码又恢复到备份时的cba-123了。

2、逻辑备份-增量备份与恢复

---

实战案例1: 数据库完整备份+数据库增量备份
新建数据表, 进行全量备份, 随着时间推移, 数据库突然奔溃

（1）环境准备

//备份之前
mysql> create database bgx;
mysql> create table bgx.t1 (id int,name varchar(20));
mysql> insert into bgx.t1 values (1,"bgx1");
mysql> insert into bgx.t1 values (2,"bgx2");
mysql> select * from bgx.t1;
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
+------+------+

//基于当前状态做一次全备
mysqldump -uroot -pcba-123 --all-databases --single-transaction --master-data=1 --flush-logs > /backup/mysql/$(date +%F%H)-mysql-all.sql

//进入数据库，再插入一些数据
mysql> insert into bgx.t1 values (3,"bgx3");
mysql> insert into bgx.t1 values (5,"tt");
mysql> select * from bgx.t1;
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
|    3 | bgx3 |
|    5 | tt   |
+------+------+
//模拟数据库崩溃，重启初始化，启动数据库,更改默认密码
/etc/init.d/mysqld stop
rm -rf /soft/mysql/data/*
/soft/mysql/bin/mysqld --initialize --user=mysql --basedir=/soft/mysql --datadir=/soft/mysql/data
/etc/init.d/mysqld start
mysql -p'1=FuY==qhkjd'
mysql> ALTER USER USER() IDENTIFIED BY '123';

（2）恢复全量数据

[root@mysql02 ~]# sed -i "23aSET sql_log_bin=0;" /backup/mysql/2019-11-2810-mysql-all.sql 
[root@mysql02 ~]# mysql -uroot -p'123' < /backup/mysql/2019-11-2810-mysql-all.sql
[root@mysql02 ~]# mysql -u root -p'123' -e "select * from bgx.t1"
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
+------+------+

（3）恢复增量备份

第一步：查看一下全量备份，备份到哪个点了，如下所示是154这个点，000001这个日志文件

[root@mysql02 ~]# sed -n '22p' /backup/mysql/2019-11-2810-mysql-all.sql 
CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000001', MASTER_LOG_POS=154;

全量仅备份到了154这个点，154后面的点全备文件里面就没有了，需要去000002以后的二进制文件里面找

第二步：根据 MASTER_LOG_POS恢复增量的数据

[root@mysql02 mysql]# pwd
/log/mysql
[root@mysql02 mysql]#mysqlbinlog --start-position=154 bin-log.000001 bin-log.000002 bin-log.000003 bin-log.000003 | mysql -uroot -p'123';
[root@mysql02 ~]# mysql -u root -p'123' -e "select * from bgx.t1"
+------+------+
| id   | name |
+------+------+
|    1 | bgx1 |
|    2 | bgx2 |
|    3 | bgx3 |
|    5 | tt   |
+------+------+

3、新来的开发妹子删了库！

上周，新来的开发妹子删了库，这件事不想再回忆了，以后打死也不会把数据库的root权限轻易给别人了。今天把当时的场景用虚拟机还原一下，然后复现一下数据恢复的过程，就当是个总结吧！说多了都是泪啊~

（1）模拟环境准备

[root@mysql02 ~]# mysql -p'cba-123'
mysql> create database bgxdb;
mysql> use bgxdb;
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,"ccr");
mysql> insert into t1 values (2,"tfr");
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
+------+------+

（2）全备

[root@mysql02 ~]# mysqldump -uroot -pcba-123 --all-databases --single-transaction --master-data=1 --flush-logs > /backup/mysql/$(date +%F%H)-mysql-all.sql

（3）再次插入数据

[root@mysql02 ~]# mysql -p'cba-123';
mysql> insert into bgxdb.t1 values
    -> (3,'tr1'),
    -> (4,'zx'),
    -> (5,'wq'),
    -> (6,'tj'),
    -> (7,'gwt');
mysql> select * from bgxdb.t1;
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
|    3 | tr1  |
|    4 | zx   |
|    5 | wq   |
|    6 | tj   |
|    7 | gwt  |
+------+------+

（4）开发妹子的误操作

mysql> delete from bgxdb.t1 where id = '2';
mysql> drop database bgxdb;

（5）恢复全备

[root@mysql02 ~]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2019-11-2812-mysql-all.sql 
[root@mysql02 ~]# mysql -u root -p'cba-123' < /backup/mysql/2019-11-2812-mysql-all.sql 
[root@mysql02 ~]# mysql -u root -p'cba-123' -e "select * from bgxdb.t1"
+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
+------+------+

（6）进一步小心地恢复，跳过DELETE和DROP语句

下面的操作就要小心翼翼了，不能一下子把二进制日志里面全备以后的操作全部恢复，一旦全部恢复了，那开发妹子的删除操作也会恢复，我们只能跳过误操作的地方。

[root@mysql02 ~]# sed -n '22p' /backup/mysql/2019-11-2812-mysql-all.sql 
CHANGE MASTER TO MASTER_LOG_FILE='bin_log.000002', MASTER_LOG_POS=154;
[root@mysql02 ~]# ls /log/mysql/  #全备之后只有一个` bin_log.000002`二进程日志文件
[root@mysql02 ~]# mysqlbinlog --start-position=154 --stop-position=577 
[root@mysql02 ~]# mysqlbinlog --start-position=670 --stop-position=701 
[root@mysql02 ~]# mysql -uroot -p'cba-123' -e "select * from bgxdb.t1"

+------+------+
| id   | name |
+------+------+
|    1 | ccr  |
|    2 | tfr  |
|    3 | tr1  |
|    4 | zx   |
|    5 | wq   |
|    6 | tj   |
|    7 | gwt  |
#被开发妹子删除的数据终于恢复回来了

注：上述案例在全备之后仅产生了一个二进制日志，如果有多个呢？该如何操作？如下所示

mysqlbinlog --base64-output="decode-rows" -v bin_log.000001 bin_log.000002 > test3.sql