MySQL-备份与还原

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL-备份与还原

刘小四 2018-08-03 22:28:00 浏览3231
展开阅读全文

 

前言

对于我们运维来说,在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数据库有以下几个表

image

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

image

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

3、修改students表(新数据和日志)

1)没修改前

image

2)加记录

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

image

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

image

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、看表存在不

image

2、看表数据正不正确

image

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用户可以读写其他用户是不能修改数据的

image

。。。

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

image

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)从合并的二进制日志文件中找到误操作的指令删除或注释

image

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

mysql <binlogs.sql

。。。

验证

1、删除的表看看有了没

image

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

image

OK

还原成功

欢迎补充--

网友评论

登录后评论
0/500
评论
刘小四
+ 关注