MySQL灾难恢复实战

原创

603627156 2016-03-18 11:10:21 博主文章分类：MySQL高级实战 ©著作权

文章标签 MySQL 数据恢复 dump 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者603627156的原创作品，谢绝转载，否则将追究法律责任

一、MySQL增量恢复必备条件。

思路迁移-可以通过sersync做异地脚本备份后的内容，放置linux本机机器故障。

(1)检查MySQL运行是否正常

[root@sersync 3307]# netstat -tlnp | grep mysql

tcp 0 0 :::3307 :::* LISTEN 5675/mysqld

tcp 0 0 :::3308 :::* LISTEN 3376/mysqld

tcp 0 0 :::3306 :::* LISTEN 4579/mysqld

[root@sersync 3307]# mkdir /server/backup -p 创建备份目录

(2)开启MySQL log bin 日志功能

MySQL 数据库开启了log bin 参数记录bin log日志功能如下：

[root@sersync 3307]# grep log_bin /data/3307/my.cnf

log_bin = /data/3307/mysql-bin

[root@sersync 3307]#

重启mysql检查发现确实多出了mysql-bin.000001，mysql-bin.index

[root@sersync 3307]# ls

data mysql mysql-bin.index mysql.sock

my.cnf mysql-bin.000001 mysqld.pid mysql_tangbo3307.err

[root@sersync 3307]#

(3)需要存在MySQL 数据库全备

生产环境mysqldump备份命令

[root@sersync backup]# mysqldump -uroot -p111111 -S /data/3307/mysql.sock --single-transaction -B caiwu | gzip > /server/backup/bbpay_caiwu_$(date +%F).sql.gz

(4)生产环境mysqldunmp备份脚本

#!/bin/sh

################################################

#this scripts is created by tangbo

#tangbo QQ:79313760

################################################

#parameter defined start

BAKDATE=`date +%F`

MYUSER=root

MYPASS="111111"

MYSOCK=/data/3307/mysql.sock

DBNAME="caiwu"

MAIN_PATH=/server/backup

DATA_PATH=/server/backup

LOG_FILE=${DATA_PATH}/mysql_logs_${BAKDATE}.log

DATA_FILE=${DATA_PATH}/mysql_backup_${BAKDATE}.sql.gz

#command defined

MYSQL_PATH=/usr/local/mysql/bin

#MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -A -B -F --single-transaction -e"

MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -B $DBNAME --default-character-set=gbk --single-transaction -e"

#backup command

${MYSQL_DUMP} | gzip > $DATA_FILE

#check backup result

du -sh $DATA_FILE >$LOG_FILE

mail -s "${BAKDATE} mysql bak log" 79313760@qq.com < $LOG_FILE

##################end##########################

二、MySQL增量恢复案例图解

--见图片--

三、恢复数据库场景

(1)创建数据库；

mysql> create database caiwu CHARACTER SET utf8 COLLATE utf8_general_ci;

(2)创建utf8格式表

set names utf8；

CREATE TABLE `caiwus` (

`id` int(30) NOT NULL AUTO_INCREMENT COMMENT '员工号',

`name` varchar(60) NOT NULL COMMENT '员工名字',

`ages` varchar(60) DEFAULT NULL COMMENT '员工年龄',

`job` varchar(30) DEFAULT NULL COMMENT '工作岗位',

`pay` float DEFAULT NULL COMMENT '薪金',

`time` date DEFAULT NULL COMMENT '入职时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8；

(3)向caiwus表中插入数据

insert into caiwus(name,ages,job,pay,time) values('唐波',27,'网络管理',4000,20140930);

insert into caiwus(name,ages,job,pay,time) values('王非',32,'技术工程师',6000,20150110);

insert into caiwus(name,ages,job,pay,time) values('汪波',37,'DBA',8000,20130320);

insert into caiwus(name,ages,job,pay,time) values('陈思思',24,'行政管理',3500,20150112);

insert into caiwus(name,ages,job,pay,time) values('刘玉',27,'财务',4330,20152010);

insert into caiwus(name,ages,job,pay,time) values('谢红',28,'销售工程师',3000,20130930);

insert into caiwus(name,ages,job,pay,time) values('陈税',27,'销售管理',4000,20140930);

insert into caiwus(name,ages,job,pay,time) values('唐思',27,'销售管理',4100,20140930);

insert into caiwus(name,ages,job,pay,time) values('陈杨',27,'销售管理',4700,20140930);

(4)定时任务数据库备份脚本---用前面的生产环境mysqldunmp备份脚本（生产用脚本）

[root@sersync backup]# chmod +x mysqlbak.sh

[root@sersync backup]# crontab -l

#mysql backup by tangbo on 2015-12-22

00 00 * * * /bin/bash /server/backup/mysqlbak.sh >/dev/null 2>&1

*/1 * * * * /usr/sbin/ntpdate time.nist.gov &> /dev/null 2>&1

[root@sersync backup]#

(5)由于是测试手工模拟执行备份脚本

[root@sersync backup]# sh mysqlbak.sh

[root@sersync backup]# ll 一定要检查备份的大小

total 16

-rw-r--r-- 1 root root 2718 Dec 31 2015 bbpay_caiwu_2015-12-31.sql

-rw-r--r-- 1 root root 1101 Dec 31 13:46 mysql_backup_2015-12-31.sql.gz

-rwxr-xr-x 1 root root 925 Dec 31 13:46 mysqlbak.sh

-rw-r--r-- 1 root root 51 Dec 31 13:46 mysql_logs_2015-12-31.log

[root@sersync backup]#egrep -v "#|\*|--|^$" bbpay_caiwu_2015-12-31.sql

[root@sersync home]# gzip -d mysql_backup_2015-12-31.sql.gz 解压

[root@sersync home]# ls 查看内容

lost+found my.cnf mysql mysql_backup_2015-12-31.sql

[root@sersync home]# vim mysql_backup_2015-12-31.sql

(6)备份后检查bin-log日志，明显增加了。bin log日志一般是加1，超高1.1G会滚动增加。

[root@sersync backup]# ll /data/3307/

total 64

drwxr-xr-x 5 mysql mysql 4096 Dec 31 2015 data

-rw-r--r-- 1 root root 2688 Dec 31 2015 my.cnf

-rwxr-xr-x 1 root root 1118 Dec 29 18:14 mysql

-rw-rw---- 1 mysql mysql 10977 Dec 31 13:51 mysql-bin.000001

-rw-rw---- 1 mysql mysql 120 Dec 31 13:51 mysql-bin.000002

-rw-rw---- 1 mysql mysql 56 Dec 31 13:51 mysql-bin.index

-rw-rw---- 1 mysql mysql 5 Dec 31 2015 mysqld.pid

srwxrwxrwx 1 mysql mysql 0 Dec 31 2015 mysql.sock

-rw-r----- 1 mysql root 24277 Dec 31 2015 mysql_tangbo3307.err

[root@sersync backup]#

(7)模拟用户继续更新数据

我们在0点时刻进行备份之后，用户还是会时刻更新数据的；这儿模拟实时更新数据。

insert into caiwus(name,ages,job,pay,time) values('王鹏',29,'技术工程师',5500,20130212);

insert into caiwus(name,ages,job,pay,time) values('张杰',27,'开发工程师',8800,20130210);

insert into caiwus(name,ages,job,pay,time) values('葛林',26,'行政管理',3300,20150322);

insert into caiwus(name,ages,job,pay,time) values('谢玲',32,'销售管理',3200,20130620);

insert into caiwus(name,ages,job,pay,time) values('吴彩虹',22,'销售管理',2200,20150127);

insert into caiwus(name,ages,job,pay,time) values('王杰',29,'销售经理',4350,20140828);

insert into caiwus(name,ages,job,pay,time) values('陈坤',33,'销售管理',3300,20140930);

insert into caiwus(name,ages,job,pay,time) values('谢思',45,'总裁',12000,20110930);

(8)更新后的内容

mysql> select * from caiwus;

+----+-----------+------+-----------------+-------+------------+

| id | name | ages | job | pay | time |

+----+-----------+------+-----------------+-------+------------+

| 1 | 唐波 | 27 | 网络管理 | 4000 | 2014-09-30 |

| 2 | 王非 | 32 | 技术工程师 | 6000 | 2015-01-10 |

| 3 | 汪波 | 37 | DBA | 8000 | 2013-03-20 |

| 4 | 陈思思 | 24 | 行政管理 | 3500 | 2015-01-12 |

| 5 | 刘玉 | 27 | 财务 | 430 | 0000-00-00 |

| 6 | 谢红 | 28 | 销售工程师 | 3000 | 2013-09-30 |

| 7 | 陈税 | 27 | 销售管理 | 4000 | 2014-09-30 |

| 8 | 唐思 | 27 | 销售管理 | 4100 | 2014-09-30 |

| 9 | 陈杨 | 27 | 销售管理 | 4700 | 2014-09-30 |

| 10 | 王鹏 | 29 | 技术工程师 | 5500 | 2013-02-12 |

| 11 | 张杰 | 27 | 开发工程师 | 8800 | 2013-02-10 |

| 12 | 葛林 | 26 | 行政管理 | 3300 | 2015-03-22 |

| 13 | 谢玲 | 32 | 销售管理 | 3200 | 2013-06-20 |

| 14 | 吴彩虹 | 22 | 销售管理 | 2200 | 2015-01-27 |

| 15 | 王杰 | 29 | 销售经理 | 4350 | 2014-08-28 |

| 16 | 陈坤 | 33 | 销售管理 | 3300 | 2014-09-30 |

| 17 | 谢思 | 45 | 总裁 | 12000 | 2011-09-30 |

+----+-----------+------+-----------------+-------+------------+

17 rows in set (0.00 sec)

四、模拟数据库遭到破坏

(1)执行删除数据库命令

mysql> drop database caiwu;

Query OK, 1 row affected (0.13 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

mysql> 财务表不在了！！！！！！

(2)发现故障并且检查原因

数据库出问题10分钟左右，公司上报故障，要求解决，查看原因，准备恢复。

五、###############增量恢复全过程#####################

(1)首先检查全备及binlog日志

查看凌晨全备

[root@sersync backup]# ll /server/backup/

total 12

-rw-r--r-- 1 root root 1101 Dec 31 13:51 mysql_backup_2015-12-31.sql.gz

-rwxr-xr-x 1 root root 927 Dec 31 13:51 mysqlbak.sh

-rw-r--r-- 1 root root 51 Dec 31 13:51 mysql_logs_2015-12-31.log

[root@sersync backup]#

查看所有binlog

[root@sersync backup]# ls -lrt /data/3307/mysql-bin.*

-rw-rw---- 1 mysql mysql 10977 Dec 31 13:51 /data/3307/mysql-bin.000001

-rw-rw---- 1 mysql mysql 56 Dec 31 13:51 /data/3307/mysql-bin.index

-rw-rw---- 1 mysql mysql 2707 Dec 31 14:29 /data/3307/mysql-bin.000002

[root@sersync backup]#

(2)立即刷新并备份出binlog

[root@sersync backup]# mysqladmin -uroot -p111111 -S /data/3307/mysql.sock flush-logs 刷新binlog

[root@sersync backup]# ls -lrt /data/3307/mysql-bin.* 查看binlog刷新情况

-rw-rw---- 1 mysql mysql 10977 Dec 31 13:51 /data/3307/mysql-bin.000001

-rw-rw---- 1 mysql mysql 2754 Dec 31 14:37 /data/3307/mysql-bin.000002

-rw-rw---- 1 mysql mysql 120 Dec 31 14:37 /data/3307/mysql-bin.000003

-rw-rw---- 1 mysql mysql 84 Dec 31 14:37 /data/3307/mysql-bin.index

[root@sersync backup]#

[root@sersync backup]# cp /data/3307/mysql-bin.000003 /server/backup 复制到备份目录

(3)恢复binlog生成sql语句

[root@sersync backup]# mysqlbinlog mysql-bin.000003 >bin.sql

[root@sersync backup]#egrep -v "#|\*|--|^$" bin.sql 检查生成的语句（注意，把错误的语句注释掉）

[root@sersync backup]# vim bin.sql 打开它，注释掉不要的语句

##(4)##开始恢复

1.首先恢复全备那一块的内容，检查全备后，恢复增量的内容

[root@sersync backup]# ls -l mysql_backup_2015-12-31.sql.gz

-rw-r--r-- 1 root root 1101 Dec 31 13:51 mysql_backup_2015-12-31.sql.gz

[root@sersync backup]# gzip -d mysql_backup_2015-12-31.sql.gz 解压

2.查看全备内容，确认内容是否在

[root@sersync backup]# egrep -v "#|\*|--|^$" mysql_backup_2015-12-31.sql

USE `caiwu`;

DROP TABLE IF EXISTS `caiwus`;

CREATE TABLE `caiwus` (

`id` int(30) NOT NULL AUTO_INCREMENT COMMENT '员工号',

`name` varchar(60) NOT NULL COMMENT '员工名字',

`ages` varchar(60) DEFAULT NULL COMMENT '员工年龄',

`job` varchar(30) DEFAULT NULL COMMENT '工作岗位',

`pay` float DEFAULT NULL COMMENT '薪金',

`time` date DEFAULT NULL COMMENT '入职时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

LOCK TABLES `caiwus` WRITE;

INSERT INTO `caiwus` VALUES (1,'?2¨','27','θ?1??4000,'2014-09-30'),(2,'ε·?,'32','???1¤3?|',6000,'2015-01-10'),(3,'δ2¨','37','DBA',8000,'2013-03-20'),(4,'3???','24','??1??3500,'2015-01-12'),(5,'μ?','27','2ǎ媴30,'0000-00-00'),(6,'лo?'28','к?1¤3?|',3000,'2013-09-30'),(7,'3?°','27','к?1??4000,'2014-09-30'),(8,'??','27','к?1??4100,'2014-09-30'),(9,'3??'27','к?1??4700,'2014-09-30');

UNLOCK TABLES;

[root@sersync backup]#

3.内容都在，开始恢复全备内容。

[root@sersync backup]# mysql -uroot -p111111 -S /data/3307/mysql.sock < mysql_backup_2015-12-31.sql

检查恢复结果：

全备数据回来了：

mysql> select * from caiwus;

+----+-----------+------+-----------------+------+------------+

| id | name | ages | job | pay | time |

+----+-----------+------+-----------------+------+------------+

| 1 | 唐波 | 27 | 网络管理 | 4000 | 2014-09-30 |

| 2 | 王非 | 32 | 技术工程师 | 6000 | 2015-01-10 |

| 3 | 汪波 | 37 | DBA | 8000 | 2013-03-20 |

| 4 | 陈思思 | 24 | 行政管理 | 3500 | 2015-01-12 |

| 5 | 刘玉 | 27 | 财务 | 430 | 0000-00-00 |

| 6 | 谢红 | 28 | 销售工程师 | 3000 | 2013-09-30 |

| 7 | 陈税 | 27 | 销售管理 | 4000 | 2014-09-30 |

| 8 | 唐思 | 27 | 销售管理 | 4100 | 2014-09-30 |

| 9 | 陈杨 | 27 | 销售管理 | 4700 | 2014-09-30 |

+----+-----------+------+-----------------+------+------------+

9 rows in set (0.00 sec)

mysql>

4.恢复增量数据

[root@sersync backup]# mysql -uroot -p111111 -S /data/3307/mysql.sock < bin.sql

检查恢复结果：

全备数据回来了：

mysql> select * from caiwu.caiwus;

+----+-----------+------+-----------------+-------+------------+

| id | name | ages | job | pay | time |

+----+-----------+------+-----------------+-------+------------+

| 1 | 唐波 | 27 | 网络管理 | 4000 | 2014-09-30 |

| 2 | 王非 | 32 | 技术工程师 | 6000 | 2015-01-10 |

| 3 | 汪波 | 37 | DBA | 8000 | 2013-03-20 |

| 4 | 陈思思 | 24 | 行政管理 | 3500 | 2015-01-12 |

| 5 | 刘玉 | 27 | 财务 | 430 | 0000-00-00 |

| 6 | 谢红 | 28 | 销售工程师 | 3000 | 2013-09-30 |

| 7 | 陈税 | 27 | 销售管理 | 4000 | 2014-09-30 |

| 8 | 唐思 | 27 | 销售管理 | 4100 | 2014-09-30 |

| 9 | 陈杨 | 27 | 销售管理 | 4700 | 2014-09-30 |

| 10 | 王鹏 | 29 | 技术工程师 | 5500 | 2013-02-12 |

| 11 | 张杰 | 27 | 开发工程师 | 8800 | 2013-02-10 |

| 12 | 葛林 | 26 | 行政管理 | 3300 | 2015-03-22 |

| 13 | 谢玲 | 32 | 销售管理 | 3200 | 2013-06-20 |

| 14 | 吴彩虹 | 22 | 销售管理 | 2200 | 2015-01-27 |

| 15 | 王杰 | 29 | 销售经理 | 4350 | 2014-08-28 |

| 16 | 陈坤 | 33 | 销售管理 | 3300 | 2014-09-30 |

| 17 | 谢思 | 45 | 总裁 | 12000 | 2011-09-30 |

+----+-----------+------+-----------------+-------+------------+

17 rows in set (0.00 sec)

mysql>

5.成功数据全部回来了！！！！！！！！

上一篇：MySQL主主复制实战

下一篇：华为-H3C交换机登录权限控制

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯