MySQL-备份与还原

前言

对于我们运维来说，在mysql数据库领域，别的不说，最起码要会两大技能！

第一大技能：备份与还原

第二大技能：主从异步

关于这两大技能我们先来说说第一个

备份与还原

备份：我们按时定点来备份数据，当下数据最值钱，所以我们要确保数据的安全。

平常我们都是7天一大备，三天两头一小备，也就是说，一周来一个完全备份，1、2、天来一个增量或差异备份。

确保那天服务器宕机或误操作，能恢复过来。

还原：当服务器负重过量会导致宕机，或有时候我们误操作，删除了某张重要的数据表等等，这时候就要用到我们之前备份的数据来恢复。

简单说下备份的类型，

冷备：读写操作均不可进行

温备：读操作可执行；但写操作不可执行

热备：读写操作均可执行

MyISAM：温备，不支持热备
InnoDB: 都支持

简单说下常用的备份工具，

LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份

mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份

xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份

备注：以上除了xtrabackup，都是基于完整备份包以及二进制日志来恢复数据库的，二进制日志记录了增删改语句的操作指令，拿过来重放，

建议：建议二进制日志和数据文件分开存放 --

1、关闭网络链接，只监听本地端口访问，(在备份还原的时候用)

skip-networking=1

2、禁止主机名解析，ip地址访问的时候，禁止反向解析（建议加上）

skip_name_resolve = on

3、每个表单独使用一个表空间存储表的数据和索引（建议加上）

innodb_file_per_table = on

4、开启并指定二进制文件存放位置

log_bin=/...

。。。

准备

1、准备两台主机，我用.17充当mysql主服务器，.57来还原用。

2、17主mysql数据库有以下几个表

3、目录

/data/mysq/：下存放数据库数据

/app/logs/：下存放二进制日志

。。。

实战

备份阶段

1、完全备份

mysqldump -pcentos -A -F --master-data=2 --single-transaction |gzip > /data/all-`date +%F`.sql.gz

备注：

-A：备份全部数据库

-F：切换二进制日志

--master-data=2：记录了基于二进制那个位置备份的，这个点之后是新数据，备份结束点，主从改为1

--single-transaction：保证数据的一致性

2、解压并查看

gzip -d all-2018-08-03.sql.gz
less all-2018-08-03.sql

备注：记住这个文件以及数字，一会还原的时候要用

3、修改students表（新数据和日志）

1）没修改前

2）加记录

insert students (name,age)values('gaoda1hao',20);

3）查看现在的二进制变化

4）假设现在这台mysql服务器宕机崩溃了，下面开始还原

。。。

还原阶段

上面规划的是用57这台主机来还原，首先这台主机必须是干净的mysql数据库

1、scp传送

1）传送完全备份文件到目标主机

scp all-2018-08-03.sql 192.168.43.57:

2）传送上面图中00005文件以及之后的二进制文件到目标主机

scp mysql-bin.000005 192.168.43.57:

2、57主机来还原

注意：还原的时候要禁止除了你之外的所有用户的访问。

停止mysql服务，在my.cnf配置文件中可以加上上面说到一项

1）开启服务前提下并完全备份数据包

mysql < all-2018-08-03.sql

备注：现在只还原到了，备份时的状态，下面再次还原备份后到宕机这段时候发生的操作

2）导入二进制日志

mysqlbinlog  --start-position=385 /root/mysql-bin.000005 > /app/binlog.sql

备注：把00005中从385开始的指令语句导入到一个文件中

mysql < /app/binlog.sql

备注：导入二进制记录的指令

。。。

验证

1、看表存在不

2、看表数据正不正确

OK 还原成功

下面再来演示一个案例-误删除表的恢复

比如我们在某天的12点中做了完全备份，在下一次做完全备份中间有一天执行了删除某张表，删除后等了几个小时才发现表删除了，

在这种场景下，来恢复数据，下面来模拟下。

模拟顺序：完全备份--用户对表的数据修改--删除某张表--未发现用户继续对其他表操作--当访问被删除的表时发现--

准备工作和上面一样，下面进入正题

前几步和前面一样

1、完全备份

mysqldump -pcentos -A -F --master-data=2 --single-transaction |gzip > /data/all-`date +%F`.sql.gz

2、模拟误删除表前的修改

insert students (name,age)values('gaoda1hao',20);

。。。

备注：这期间加了好多数据，可能二进制文件已经满了，进行了切换

3、模拟二进制文件的切换

flush logs;
#

接着增加几条记录

insert students (name,age)values('gaoda2hao',22);

4、模拟误删除数据表

drop table students;

备注：删除后还么有发现，接着增加其他表的数据

insert teachers (name,age)values('gaoda3',30);

5、发现问题并及时禁止用户写权限（只能读）

flush tables with read lock;

备注：现在只能root用户可以读写其他用户是不能修改数据的

。。。

6、干净系统还原

1）停止服务

systemctl stop mairadb

2）删除所有mysql数据库数据

rm -rf /var/lib/mysql/

3）确保用户不能访问服务器 配置文件加上

skip-networking

4）启动服务

7、还原完全备份

1）解压完全备份包

gzip -d all-2018-08-03.sql.gz

2）查看解压的文件确定位置

less all-2018-08-03.sql

3）把完整备份传送到57目标主机还原

mysql <all-2018-08-03.sql

8、还原增量备份

1）合并00004以及之后的二进制日志文件

mysqlbinlog  --start-position=385 mysql-bin.000004 > /data/binlogs.sql
mysqlbinlog   mysql-bin.000005 >> /data/binlogs.sql
mysqlbinlog   mysql-bin.000006 >> /data/binlogs.sql

2）从合并的二进制日志文件中找到误操作的指令删除或注释

3）scp传送到57目标主机还原

mysql <binlogs.sql

。。。

验证

1、删除的表看看有了没

2、在表删除后其他的表改的记录还在不

OK

还原成功

欢迎补充--