本文详细介绍了MySQL数据库在遇到故障时的恢复流程，包括全备数据、binlog日志的使用及误操作后的恢复措施。
模拟MySQL数据库故障恢复全过程
1、安装好mysql数据库，开启binlog日志
2、模拟写数据，往lvnian库的test表写100条数据，进行全备数据。并且把处理相关的binlog日志
3、继续写数据50条
4、这个时候不小心误删除数据。
5、发现进行误操作
6、查看全备和binlog日志是否存在。把增量binlog和全备保存起来，避免误操作，导致数据二次损坏。
7、处理相关binlog日志。
8、把全备数据进行恢复
9、把增量备份进行恢复
10、恢复完成后，检查是否恢复成功
注意：在增量备份恢复时，发现误操作之后，最好就停止对我提供服务。
如果继续要对外提供服务，那么就再次刷新binlog日志，让binlog写到另外一个binlog文件中
##############################################
##############################################
1、安装好mysql数据库，开启binlog日志
egrep "log-bin" /etc/my.cnf
ll /application/mysql/data/
########################################
[root@M_MYSQL ~]# egrep "log-bin" /etc/my.cnf
log-bin=mysql-bin
[root@M_MYSQL ~]# ll /application/mysql/data/
total 28700
-rw-rw----. 1 mysql mysql 18874368 Oct 1 12:14 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:14 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:05 ib_logfile1
-rw-r-----. 1 mysql root 3458 Oct 1 12:14 M_MYSQL.err
-rw-rw----. 1 mysql mysql 5 Oct 1 12:14 M_MYSQL.pid
drwx------. 2 mysql root 4096 Oct 1 12:05 mysql
-rw-rw----. 1 mysql mysql 107 Oct 1 12:14 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 Oct 1 12:14 mysql-bin.index
drwx------. 2 mysql mysql 4096 Oct 1 12:05 performance_schema
drwxr-xr-x. 2 mysql mysql 4096 Oct 1 12:05 test
[root@M_MYSQL ~]#2、创建lvnian库;test表
模拟往test表写数据，往test表写100条数据；
mysql -uroot -plvnian;
create database lvnian;
use lvnian;
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key(id)
);
quit;
mysql -uroot -plvnian -e " use lvnian; select * from test ;"
for n in `seq 100`;do mysql -uroot -plvnian -e "use lvnian;insert test values($n,'lvnian$n'); " ;done;
mysql -uroot -plvnian -e "use lvnian; select * from test;"####检查数据内容
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "use lvnian; select * from test;"
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | lvnian1 |
| 2 | lvnian2 |
| 3 | lvnian3 |
| 4 | lvnian4 |
| 5 | lvnian5 |
...
...
| 96 | lvnian96 |
| 97 | lvnian97 |
| 98 | lvnian98 |
| 99 | lvnian99 |
| 100 | lvnian100 |
+-----+-----------+
[root@M_MYSQL ~]#3、然后进行全备数据。
并且把处理相关的binlog日志。
ll /application/mysql/data/
mysqldump -uroot -p'lvnian' -A -B -F --events --master-data=2 |gzip >/backup/server/mysql.bak.sql.gz
ll /application/mysql/data/
ll /backup/server/
##############################################
[root@M_MYSQL ~]# ll /application/mysql/data/
total 28720
-rw-rw----. 1 mysql mysql 18874368 Oct 1 12:26 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:26 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:05 ib_logfile1
drwx------. 2 mysql mysql 4096 Oct 1 12:24 lvnian
-rw-r-----. 1 mysql root 3458 Oct 1 12:14 M_MYSQL.err
-rw-rw----. 1 mysql mysql 5 Oct 1 12:14 M_MYSQL.pid
drwx------. 2 mysql root 4096 Oct 1 12:05 mysql
-rw-rw----. 1 mysql mysql 19838 Oct 1 12:26 mysql-bin.000001
-rw-rw----. 1 mysql mysql 19 Oct 1 12:14 mysql-bin.index
drwx------. 2 mysql mysql 4096 Oct 1 12:05 performance_schema
drwxr-xr-x. 2 mysql mysql 4096 Oct 1 12:05 test
======
[root@M_MYSQL ~]# mysqldump -uroot -p'lvnian' -A -B -F --events --master-data=2 |gzip >/backup/server/mysql.bak.sql.gz
======
[root@M_MYSQL ~]# ll /application/mysql/data/
total 28724
-rw-rw----. 1 mysql mysql 18874368 Oct 1 12:26 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:26 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:05 ib_logfile1
drwx------. 2 mysql mysql 4096 Oct 1 12:24 lvnian
-rw-r-----. 1 mysql root 3458 Oct 1 12:14 M_MYSQL.err
-rw-rw----. 1 mysql mysql 5 Oct 1 12:14 M_MYSQL.pid
drwx------. 2 mysql root 4096 Oct 1 12:05 mysql
-rw-rw----. 1 mysql mysql 19881 Oct 1 12:29 mysql-bin.000001
-rw-rw----. 1 mysql mysql 107 Oct 1 12:29 mysql-bin.000002 ####这个是备份后的日志文件
-rw-rw----. 1 mysql mysql 38 Oct 1 12:29 mysql-bin.index
drwx------. 2 mysql mysql 4096 Oct 1 12:05 performance_schema
drwxr-xr-x. 2 mysql mysql 4096 Oct 1 12:05 test
======
[root@M_MYSQL ~]# ll /backup/server/
total 144
-rw-r--r--. 1 root root 145125 Oct 1 12:29 mysql.bak.sql.gz经过上面操作，现在开始写数据的binlog日志为mysql-bin.000002
4、继续写数据50条
for n in `seq 101 150`;do mysql -uroot -plvnian -e "use lvnian;insert test values($n,'zengliang$n'); " ;done;
mysql -uroot -plvnian -e "use lvnian; select * from test;"#######################################################
[root@M_MYSQL ~]# for n in `seq 101 150`;do mysql -uroot -plvnian -e "use lvnian;insert test values($n,'zengliang$n'); " ;done;
mysql -uroot -plvnian -e "use lvnian; select * from test;"
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "use lvnian; select * from test;"
+-----+--------------+
| id | name |
+-----+--------------+
| 1 | lvnian1 |
| 2 | lvnian2 |
| 3 | lvnian3 |
| 4 | lvnian4 |
| 5 | lvnian5 |
...
...
| 95 | lvnian95 |
| 96 | lvnian96 |
| 97 | lvnian97 |
| 98 | lvnian98 |
| 99 | lvnian99 |
| 100 | lvnian100 |
| 101 | zengliang101 |
| 102 | zengliang102 |
| 103 | zengliang103 |
| 104 | zengliang104 |
| 105 | zengliang105 |
...
...
| 146 | zengliang146 |
| 147 | zengliang147 |
| 148 | zengliang148 |
| 149 | zengliang149 |
| 150 | zengliang150 |
+-----+--------------+
[root@M_MYSQL ~]#5、误删除数据
mysql -uroot -plvnian -e "use lvnian; select * from test;"
mysql -uroot -plvnian -e "show databases;"
误操作
mysql -uroot -plvnian -e "drop database lvnian;"
mysql -uroot -plvnian -e "drop database test;"
检查误操作结果
mysql -uroot -plvnian -e "show databases;"
mysql -uroot -plvnian -e "use lvnian; select * from test;"#########################################################
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| lvnian |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "use lvnian; select * from test;"
+-----+--------------+
| id | name |
+-----+--------------+
| 1 | lvnian1 |
| 2 | lvnian2 |
| 3 | lvnian3 |
| 4 | lvnian4 |
| 5 | lvnian5 |
...
...
| 95 | lvnian95 |
| 96 | lvnian96 |
| 97 | lvnian97 |
| 98 | lvnian98 |
| 99 | lvnian99 |
| 100 | lvnian100 |
| 101 | zengliang101 |
| 102 | zengliang102 |
| 103 | zengliang103 |
| 104 | zengliang104 |
| 105 | zengliang105 |
...
...
| 146 | zengliang146 |
| 147 | zengliang147 |
| 148 | zengliang148 |
| 149 | zengliang149 |
| 150 | zengliang150 |
+-----+--------------+
[root@M_MYSQL ~]#
####################
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "drop database lvnian;"
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "drop database test;"
####################
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@M_MYSQL ~]# mysql -uroot -plvnian -e "use lvnian; select * from test;"
ERROR 1049 (42000) at line 1: Unknown database 'lvnian'
[root@M_MYSQL ~]#确定是进行误操作了，已经把test、和lvnian这两个库给删除了
##############################################
##############################################
##############################################
6、查看全备和binlog日志
ll /application/mysql/data/
ll /backup/server/ ##############################################
[root@M_MYSQL ~]# ll /application/mysql/data/
total 28724
-rw-rw----. 1 mysql mysql 18874368 Oct 1 12:40 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:40 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Oct 1 12:05 ib_logfile1
-rw-r-----. 1 mysql root 3458 Oct 1 12:14 M_MYSQL.err
-rw-rw----. 1 mysql mysql 5 Oct 1 12:14 M_MYSQL.pid
drwx------. 2 mysql root 4096 Oct 1 12:05 mysql
-rw-rw----. 1 mysql mysql 19881 Oct 1 12:29 mysql-bin.000001
-rw-rw----. 1 mysql mysql 10273 Oct 1 12:40 mysql-bin.000002
-rw-rw----. 1 mysql mysql 38 Oct 1 12:29 mysql-bin.index
drwx------. 2 mysql mysql 4096 Oct 1 12:05 performance_schema
[root@M_MYSQL ~]# ll /backup/server/
total 144
-rw-r--r--. 1 root root 145125 Oct 1 12:29 mysql.bak.sql.gz
[root@M_MYSQL ~]#检查结果为。全部和增量binlog日志都在
##############################################
##############################################
##############################################
7、把增量binlog和全备保存起来，避免误操作，导致数据二次损坏。
mkdir -p /backup/server/mysql
cp /application/mysql/data/mysql-bin.* /backup/server/mysql
cp /backup/server/mysql.bak.sql.gz /backup/server/mysql
cp /application/mysql/data/mysql-bin.* /backup/server/
tree /backup/server/##############################################
[root@M_MYSQL ~]# cp /application/mysql/data/mysql-bin.* /backup/server/mysql
[root@M_MYSQL ~]# cp /backup/server/mysql.bak.sql.gz /backup/server/mysql
[root@M_MYSQL ~]# cp /application/mysql/data/mysql-bin.* /backup/server/
[root@M_MYSQL ~]#
[root@M_MYSQL ~]#
[root@M_MYSQL ~]# tree /backup/server/
/backup/server/
├── mysql
│ ├── mysql.bak.sql.gz
│ ├── mysql-bin.000001
│ ├── mysql-bin.000002
│ └── mysql-bin.index
├── mysql.bak.sql.gz
├── mysql-bin.000001
├── mysql-bin.000002
└── mysql-bin.index
##############################################
##############################################
##############################################8、找出误操作命令并删除
发现误操作，备份增量和全量备份数据后，找出相关人员，了解误操作的原因。从binglog日志中找出误操作的命令。把误操作命令在binlog日志中删除，进行恢复全备和恢复增量备份（也就是恢复binlog内容）
cat mysql-bin.02.log
vi mysql-bin.02.log
cat mysql-bin.02.log
=========================
[root@M_MYSQL server]# cat mysql-bin.02.log
...
...
...
/*!*/;
# at 9977
#151001 12:34:55 server id 1 end_log_pos 10080 Query thread_id=156 exec_time=0 error_code=0
SET TIMESTAMP=1443674095/*!*/;
insert test values(150,'zengliang150')
/*!*/;
# at 10080
#151001 12:34:55 server id 1 end_log_pos 10107 Xid = 1394
COMMIT/*!*/;
# at 10107
#151001 12:40:34 server id 1 end_log_pos 10192 Query thread_id=160 exec_time=0 error_code=0
SET TIMESTAMP=1443674434/*!*/;
drop database lvnian
/*!*/;
# at 10192
#151001 12:40:35 server id 1 end_log_pos 10273 Query thread_id=161 exec_time=0 error_code=0
SET TIMESTAMP=1443674435/*!*/;
drop database test
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;从上面可以看到误操作命令是倒数第5行和倒数第10行的drop database test和drop database lvnian命令。
所以我们需要在这两条命令上面加速注释，也就是修改成下面的内容
[root@M_MYSQL server]# cat mysql-bin.02.log
...
...
...
COMMIT/*!*/;
# at 10107
#151001 12:40:34 server id 1 end_log_pos 10192 Query thread_id=160 exec_time=0 error_code=0
SET TIMESTAMP=1443674434/*!*/;
#drop database lvnian
/*!*/;
# at 10192
#151001 12:40:35 server id 1 end_log_pos 10273 Query thread_id=161 exec_time=0 error_code=0
SET TIMESTAMP=1443674435/*!*/;
#drop database test
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
"mysql-bin.02.log" 685L, 21960C written
[root@M_MYSQL server]#误操作处理完毕，下面进行全备和增量备份的恢复
9、全量备份恢复
gzip -d /backup/server/mysql.bak.sql.gz
mysql -uroot -p'lvnian' < /backup/server/mysql.bak.sql
mysql -uroot -p'lvnian' -e "show databases;"
mysql -uroot -p'lvnian' -e "use lvnian;select * from test"
####################
[root@M_MYSQL server]# mysql -uroot -p'lvnian' < /backup/server/mysql.bak.sql
[root@M_MYSQL server]# mysql -uroot -p'lvnian' -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| lvnian |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@M_MYSQL server]# mysql -uroot -p'lvnian' -e "use lvnian;select * from test"
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | lvnian1 |
| 2 | lvnian2 |
| 3 | lvnian3 |
| 4 | lvnian4 |
| 5 | lvnian5 |
...
...
| 95 | lvnian95 |
| 96 | lvnian96 |
| 97 | lvnian97 |
| 98 | lvnian98 |
| 99 | lvnian99 |
| 100 | lvnian100 |
+-----+-----------+
[root@M_MYSQL server]#全备恢复成功
下面恢复增量备份，也就是从处理之后的binlog中恢复数据
10、恢复增量备份，并检查恢复成功是否
mysql -uroot -p'lvnian' < /backup/server/mysql-bin.02.log
mysql -uroot -p'lvnian' -e "show databases;"
mysql -uroot -p'lvnian' -e "use lvnian;select * from test"
[root@M_MYSQL server]# mysql -uroot -p'lvnian' < /backup/server/mysql-bin.02.log
[root@M_MYSQL server]# mysql -uroot -p'lvnian' -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| lvnian |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@M_MYSQL server]# mysql -uroot -p'lvnian' -e "use lvnian;select * from test"
+-----+--------------+
| id | name |
+-----+--------------+
| 1 | lvnian1 |
| 2 | lvnian2 |
| 3 | lvnian3 |
| 4 | lvnian4 |
...
...
| 95 | lvnian95 |
| 96 | lvnian96 |
| 97 | lvnian97 |
| 98 | lvnian98 |
| 99 | lvnian99 |
| 100 | lvnian100 |
| 101 | zengliang101 |
| 102 | zengliang102 |
| 103 | zengliang103 |
| 104 | zengliang104 |
| 105 | zengliang105 |
...
...
| 145 | zengliang145 |
| 146 | zengliang146 |
| 147 | zengliang147 |
| 148 | zengliang148 |
| 149 | zengliang149 |
| 150 | zengliang150 |
+-----+--------------+
[root@M_MYSQL server]#
[root@M_MYSQL server]#增量备份恢复成功
上面就是一个完整的数据库出现故障后的一个数据恢复过程
转载于:https://blog.51cto.com/lvnian/1699750
扫一扫
1007
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
