加入收藏 | 设为首页 | 会员中心 | 我要投稿 聊城站长网 （https://www.0635zz.com/）- 智能语音交互、行业智能、AI应用、云计算、5G!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

Mysql 全量备份、binlog如何复原数据

发布时间：2023-08-21 14:53:58 所属栏目：MySql教程 来源：
导读：本文主要给大家介绍Mysql 全量备份、binlog如何恢复数据，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续里面关注我的更新文章的。

某天工作时间，一个二货犯晕登错生产当测试环境了，直接
本文主要给大家介绍Mysql 全量备份、binlog如何恢复数据，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续里面关注我的更新文章的。    
 
某天工作时间，一个二货犯晕登错生产当测试环境了，直接drop了一个数据库,需要紧急恢复!可利用备份的数据文件以及增量的 binlog 文件进行数据恢复。
 
具体思路归纳几点：
 
1、恢复条件为 MySQL 要开启 binlog 日志功能，并且要全备和增量的所有数据。
 
2、恢复时建议对外停止更新，即禁止更新数据库。（这点很重要）
 
3、先恢复全量，然后把全备时刻点以后的增量日志，按顺序恢复成 SQL 文件，
 
4、然后把文件中有问题的SQL语句删除(也可通过时间和位置点)，再恢复到数据库。
 
具体实例演示：
 
1、首先要确保MySQL开启了binlog日志功能，检查如下结果
 
Mysql 全量备份、binlog如何恢复数据
 
mysql> show variables like '%log_bin%';
 
+---------------------------------+-----------------------------+
 
| Variable_name                   | Value                       |
 
+---------------------------------+-----------------------------+
 
| log_bin                         | ON                          |
 
| log_bin_basename                | /mysql_data/mysql-bin       |
 
| log_bin_index                   | /mysql_data/mysql-bin.index |
 
| log_bin_trust_function_creators | OFF                         |
 
| log_bin_use_v1_row_events       | OFF                         |
 
| sql_log_bin                     | ON                          |
 
+---------------------------------+-----------------------------+
 
6 rows in set (0.01 sec)
 
2、查看当前测试表里面数据信息后面好做对比
 
mysql> select * from Student;
 
+-----------+-----------+------+------+-------+
 
| Sno       | Sname     | Ssex | Sage | Sdept |
 
+-----------+-----------+------+------+-------+
 
| 200215121 | 李勇      | 男   |   20 | CS    |
 
| 200215122 | 刘晨      | 女   |   19 | CS    |
 
| 200215123 | 王敏      | 女   |   18 | MA    |
 
| 200215125 | 张立      | 女   |   19 | IS    |
 
| 200215126 | 虎威      | 男   |   25 | CS    |
 
| 200215127 | 魏大师    | 男   |   35 | IS    |
 
| 200215128 | 老谢      | 男   |   33 | MA    |
 
| 200215129 | 小贾      | 男   |   30 | CS    |
 
| 200215130 | 会民      | 男   |   23 | CS    |
 
| 200215131 | 陈兴      | 男   |   33 | MA    |
 
| 200215132 | 阿帆      | 男   |   36 | IS    |
 
| 200215133 | 国良      | 男   |   40 | IS    |
 
| 200215134 | 老宋      | 男   |   40 | IS    |
 
| 200215135 | 光光      | 男   |   35 | IS    |
 
| 200215136 | 王老板    | 女   |   27 | IS    |
 
+-----------+-----------+------+------+-------+
 
15 rows in set (0.00 sec)
 
3、现在进行全备份
 
mysqldump -u root -p -B -F -R -x  student|gzip > /mysql_backup/student_$(date +%Y%m%d_%H%M%S).sql.gz
 
参数说明：
 
-B：指定数据库
 
-F：刷新日志
 
-R：备份存储过程等
 
-x：锁表
 
4、再次插入新数据
 
INSERT INTO Student VALUES ('200215137','程程','女',30,'IS');
 
INSERT INTO Student VALUES ('200215138','琪琪','男',29,'MA');
 
INSERT INTO Student VALUES ('200215139','龙龙','男',27,'IS');
 
5、检查是否插入成功，如下可以看出已经插入成功
 
mysql> select * from Student;
 
+-----------+-----------+------+------+-------+
 
| Sno       | Sname     | Ssex | Sage | Sdept |
 
+-----------+-----------+------+------+-------+
 
| 200215121 | 李勇      | 男   |   20 | CS    |
 
| 200215122 | 刘晨      | 女   |   19 | CS    |
 
| 200215123 | 王敏      | 女   |   18 | MA    |
 
| 200215125 | 张立      | 女   |   19 | IS    |
 
| 200215126 | 虎威      | 男   |   25 | CS    |
 
| 200215127 | 魏大师    | 男   |   35 | IS    |
 
| 200215128 | 老谢      | 男   |   33 | MA    |
 
| 200215129 | 小贾      | 男   |   30 | CS    |
 
| 200215130 | 会民      | 男   |   23 | CS    |
 
| 200215131 | 陈兴      | 男   |   33 | MA    |
 
| 200215132 | 阿帆      | 男   |   36 | IS    |
 
| 200215133 | 国良      | 男   |   40 | IS    |
 
| 200215134 | 老宋      | 男   |   40 | IS    |
 
| 200215135 | 光光      | 男   |   35 | IS    |
 
| 200215136 | 王老板    | 女   |   27 | IS    |
 
| 200215137 | 程程      | 女   |   30 | IS    |
 
| 200215138 | 琪琪      | 男   |   29 | MA    |
 
| 200215139 | 龙龙      | 男   |   27 | IS    |
 
+-----------+-----------+------+------+-------+
 
18 rows in set (0.00 sec)
 
6.此时误操作，删除了student数据库
 
mysql> drop database student;
 
Query OK, 3 rows affected (0.11 sec)
 
mysql> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| mysql              |
 
| performance_schema |
 
| sys                |
 
+--------------------+
 
4 rows in set (0.00 sec)
 
7.查看全备之后备份文件
 
[root@ocbsdb01 mysql_backup]# cd  /mysql_backup
 
[root@ocbsdb01 mysql_backup]# gzip -d student_20170829_090319.sql.gz
 
[root@ocbsdb01 mysql_backup]# ls
 
student_20170829_090319.sql
 
[root@ocbsdb01 mysql_backup]# vim student_20170829_090319.sql
 
8.检查并移动binlog文件，并导出为 SQL 文件剔除其中的 drop 语句，查看 MySQL 的数据存放目录，
 
由下面可知是在/mysql_data下，将 binlog 文件导出SQL文件，并vim编辑它删除其中的 drop 语句。
 
/home/mysql/mysql5/bin/mysqlbinlog   --no-defaults  /tmp/mysql-bin.000004 > /tmp/04.sql
 
注意：在恢复全备数据之前必须将该 binlog文件移出，否则恢复过程中，会继续写入语句到 binlog，最终导致增量恢复数据部分变得比较混乱。
 
在转换sql的时候可能会报错，具体信息如下：
 
[root@ocbsdb01 tmp]# /home/mysql/mysql5/bin/mysqlbinlog   /tmp/mysql-bin.000004 > /tmp/04.sql
 
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
 
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。
 
两个方法可以解决这个问题
 
在MySQL的配置/etc/my.cnf中将default-character-set=utf8 修改为 character-set-server = utf8，但是这需要重启MySQL服务，如果你的MySQL服务正在忙，那这样的代价会比较大。
 
用mysqlbinlog --no-defaults mysql-bin.000004 命令打开
 
9、开始恢复全备数据
 
[root@ocbsdb01 tmp]# mysql -u root -p < /mysql_backup/student_20170829_090319.sql
 
Enter password:
 
查看数据看看，student在不在,可以看到已经在了
 
mysql> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| mysql              |
 
| performance_schema |
 
| student            |
 
| sys                |
 
+--------------------+
 
5 rows in set (0.00 sec)
 
11、开始恢复增量数据
 
使用04.sql文件恢复全备时刻到删除数据库之间新增的数据，编辑04bin.sql #删除里面的drop语句
 
[root@ocbsdb01 tmp]# vim 04.sql
 
将drop 操作下面的内容删除
 
drop database student
 
/*!*/;
 
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
 
DELIMITER ;
 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 
不然会报如下错误：
 
ERROR 1790 (HY000) at line 96: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID.
 
The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
 
我的binlog文件具体内容如下：
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 
DELIMITER /*!*/;
 
# at 4
 
#170829  9:03:22 server id 201609  end_log_pos 123 CRC32 0x669b3a18 Start: binlog v 4, server v 5.7.18-log created 170829  9:03:22
 
# Warning: this binlog is either in use or was not closed properly.
 
BINLOG '
 
Wr2kWQ+JEwMAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
 
ARg6m2Y=
 
'/*!*/;
 
# at 123
 
#170829  9:03:22 server id 201609  end_log_pos 154 CRC32 0xf909764e Previous-GTIDs
 
# [empty]
 
# at 154
 
#170829  9:04:42 server id 201609  end_log_pos 219 CRC32 0x754f68f2 Anonymous_GTIDlast_committed=0sequence_number=1
 
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 
# at 219
 
#170829  9:04:42 server id 201609  end_log_pos 294 CRC32 0xdef39415 Querythread_id=7exec_time=0error_code=0
 
SET TIMESTAMP=1503968682/*!*/;
 
SET @@session.pseudo_thread_id=7/*!*/;
 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 
SET @@session.sql_mode=1344274432/*!*/;
 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 
/*!\C utf8 *//*!*/;
 
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 
SET @@session.lc_time_names=0/*!*/;
 
SET @@session.collation_database=DEFAULT/*!*/;
 
BEGIN
 
/*!*/;
 
# at 294
 
#170829  9:04:42 server id 201609  end_log_pos 359 CRC32 0xad757652 Table_map: `student`.`Student` mapped to number 225
 
# at 359
 
#170829  9:04:42 server id 201609  end_log_pos 421 CRC32 0x5da9edf3 Write_rows: table id 225 flags: STMT_END_F
 
BINLOG '
 
qr2kWROJEwMAQQAAAGcBAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
 
Bv48HlJ2da0=
 
qr2kWR6JEwMAPgAAAKUBAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM3Bueoi+eoiwPlpbMeAAJJ
 
U/PtqV0=
 
'/*!*/;
 
# at 421
 
#170829  9:04:42 server id 201609  end_log_pos 452 CRC32 0xfcd87186 Xid = 168
 
COMMIT/*!*/;
 
# at 452
 
#170829  9:04:42 server id 201609  end_log_pos 517 CRC32 0xe47a26ba Anonymous_GTIDlast_committed=1sequence_number=2
 
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 
# at 517
 
#170829  9:04:42 server id 201609  end_log_pos 592 CRC32 0x0d3e44d1 Querythread_id=7exec_time=0error_code=0
 
SET TIMESTAMP=1503968682/*!*/;
 
BEGIN
 
/*!*/;
 
# at 592
 
#170829  9:04:42 server id 201609  end_log_pos 657 CRC32 0x98d94728 Table_map: `student`.`Student` mapped to number 225
 
# at 657
 
#170829  9:04:42 server id 201609  end_log_pos 719 CRC32 0x32c7750e Write_rows: table id 225 flags: STMT_END_F
 
BINLOG '
 
qr2kWROJEwMAQQAAAJECAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
 
Bv48HihH2Zg=
 
qr2kWR6JEwMAPgAAAM8CAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM4BueQqueQqgPnlLcdAAJN
 
QQ51xzI=
 
'/*!*/;
 
# at 719
 
#170829  9:04:42 server id 201609  end_log_pos 750 CRC32 0x92ebbf95 Xid = 169
 
COMMIT/*!*/;
 
# at 750
 
#170829  9:04:42 server id 201609  end_log_pos 815 CRC32 0xfe05a22e Anonymous_GTIDlast_committed=2sequence_number=3
 
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 
# at 815
 
#170829  9:04:42 server id 201609  end_log_pos 890 CRC32 0x551c26a4 Querythread_id=7exec_time=0error_code=0
 
SET TIMESTAMP=1503968682/*!*/;
 
BEGIN
 
/*!*/;
 
# at 890
 
#170829  9:04:42 server id 201609  end_log_pos 955 CRC32 0x67c477d0 Table_map: `student`.`Student` mapped to number 225
 
# at 955
 
#170829  9:04:42 server id 201609  end_log_pos 1017 CRC32 0x5d3f2503 Write_rows: table id 225 flags: STMT_END_F
 
BINLOG '
 
qr2kWROJEwMAQQAAALsDAAAAAOEAAAAAAAEAB3N0dWRlbnQAB1N0dWRlbnQABf7+/gL+CP4h/jz+
 
Bv48HtB3xGc=
 
qr2kWR6JEwMAPgAAAPkDAAAAAOEAAAAAAAEAAgAF/+AJMjAwMjE1MTM5Bum+mem+mQPnlLcbAAJJ
 
UwMlP10=
 
'/*!*/;
 
# at 1017
 
#170829  9:04:42 server id 201609  end_log_pos 1048 CRC32 0x2d5f70ba Xid = 170
 
COMMIT/*!*/;
 
# at 1048
 
#170829  9:06:30 server id 201609  end_log_pos 1113 CRC32 0xb8cdf9b6 Anonymous_GTIDlast_committed=3sequence_number=4
 
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 
# at 1113
 
#170829  9:06:30 server id 201609  end_log_pos 1214 CRC32 0x69d17a84 Querythread_id=7exec_time=0error_code=0
 
SET TIMESTAMP=1503968790/*!*/;
 
drop database student
 
/*!*/;
 
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
 
DELIMITER ;
 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
 
调整好后开始恢复增量数据
 
[root@ocbsdb01 tmp]# mysql -u root -p  < 04.sql
 
Enter password:
 
再次查看数据库，发现全备份到删除数据库之间的那三条数据也恢复了!!
 
mysql> select * from Student;
 
+-----------+-----------+------+------+-------+
 
| Sno       | Sname     | Ssex | Sage | Sdept |
 
+-----------+-----------+------+------+-------+
 
| 200215121 | 李勇      | 男   |   20 | CS    |
 
| 200215122 | 刘晨      | 女   |   19 | CS    |
 
| 200215123 | 王敏      | 女   |   18 | MA    |
 
| 200215125 | 张立      | 女   |   19 | IS    |
 
| 200215126 | 虎威      | 男   |   25 | CS    |
 
| 200215127 | 魏大师    | 男   |   35 | IS    |
 
| 200215128 | 老谢      | 男   |   33 | MA    |
 
| 200215129 | 小贾      | 男   |   30 | CS    |
 
| 200215130 | 会民      | 男   |   23 | CS    |
 
| 200215131 | 陈兴      | 男   |   33 | MA    |
 
| 200215132 | 阿帆      | 男   |   36 | IS    |
 
| 200215133 | 国良      | 男   |   40 | IS    |
 
| 200215134 | 老宋      | 男   |   40 | IS    |
 
| 200215135 | 光光      | 男   |   35 | IS    |
 
| 200215136 | 王老板    | 女   |   27 | IS    |
 
| 200215137 | 程程      | 女   |   30 | IS    |
 
| 200215138 | 琪琪      | 男   |   29 | MA    |
 
| 200215139 | 龙龙      | 男   |   27 | IS    |
 
+-----------+-----------+------+------+-------+
 
18 rows in set (0.00 sec)
 
 

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的优质浏览效果

        Copygight © 2016-2023 https://www.0635zz.com/ All Rights Reserved. 聊城站长网