Mysql大数据备份及恢复

数据库 MySQL
MySQL备份一般采取全库备份、日志备份;MySQL出现故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间;mysql的二进制日志记录着该数据库的所有增删改的操作日志还包括了这些操作的执行时间

【引自攀岩人生的博客】MySQL备份一般采取全库备份、日志备份;MySQL出现故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间;mysql的二进制日志记录着该数据库的所有增删改的操作日志还包括了这些操作的执行时间

Binlog的用途:主从同步、恢复数据库

使用binlog工具备份

查看binlog是否开启,因为默认是关闭的

 

从上图可知off为关闭状态,一般logbin为只读,在/etc/my.cnf下开启

 

重启数据库

 

重启后在目录下查看是否生成bin日志,黄色表示为日志

创建数据库,表,数据

 

创建表的时候 auto_increment为自增

重新开始一个新的日志文件

flush logs; #重新生成新的二进制文件

delete from tb1 where id=2; #删除id列中的序列号为2的数据方便测试

insert into tb1(name) values('tom'); #创建一个新的数据名为tom

查看数据

 

查看mysql上的二进制文件日志

 

查看二进制日志事件

  1. mysql> show binlog events; 
  2.  
  3. +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+ 
  4.  
  5. | Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                              | 
  6.  
  7. +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+ 
  8.  
  9. | mysql-bin.000001 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.13-log, Binlog ver: 4                                             | 
  10.  
  11. | mysql-bin.000001 |  123 | Previous_gtids |         1 |         154 |                                                                                   | 
  12.  
  13. | mysql-bin.000001 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              | 
  14.  
  15. | mysql-bin.000001 |  219 | Query          |         1 |         316 | create database test1                                                             | 
  16.  
  17. | mysql-bin.000001 |  316 | Anonymous_Gtid |         1 |         381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              | 
  18.  
  19. | mysql-bin.000001 |  381 | Query          |         1 |         525 | use `test1`; create table tb1(id int primary key auto_increment,name varchar(20)) | 
  20.  
  21. | mysql-bin.000001 |  525 | Anonymous_Gtid |         1 |         590 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              | 
  22.  
  23. | mysql-bin.000001 |  590 | Query          |         1 |         663 | BEGIN                                                                             | 
  24.  
  25. | mysql-bin.000001 |  663 | Table_map      |         1 |         713 | table_id: 108 (test1.tb1)                                                         | 
  26.  
  27. | mysql-bin.000001 |  713 | Write_rows     |         1 |         758 | table_id: 108 flags: STMT_END_F                                                   | 
  28.  
  29. | mysql-bin.000001 |  758 | Xid            |         1 |         789 | COMMIT /* xid=10 */                                                               | 
  30.  
  31. | mysql-bin.000001 |  789 | Anonymous_Gtid |         1 |         854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                              | 
  32.  
  33. | mysql-bin.000001 |  854 | Query          |         1 |         927 | BEGIN                                                                             | 
  34.  
  35. | mysql-bin.000001 |  927 | Table_map      |         1 |         977 | table_id: 108 (test1.tb1)                                                         | 
  36.  
  37. | mysql-bin.000001 |  977 | Write_rows     |         1 |        1026 | table_id: 108 flags: STMT_END_F                                                   | 
  38.  
  39. | mysql-bin.000001 | 1026 | Xid            |         1 |        1057 | COMMIT /* xid=11 */                                                               | 
  40.  
  41. | mysql-bin.000001 | 1057 | Rotate         |         1 |        1104 | mysql-bin.000002;pos=4          | # 此处为日志轮换事件,执行flush logs引起的 
  42.  
  43. +------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+ 
  44.  
  45. 17 rows in set (0.00 sec)  

查看指定二进制日志的事件(上面那个命令只能查看默认1的)

  1. mysql> show binlog events in 'mysql-bin.000002'
  2.  
  3. +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 
  4.  
  5. | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  | 
  6.  
  7. +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 
  8.  
  9. | mysql-bin.000002 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.13-log, Binlog ver: 4 | 
  10.  
  11. | mysql-bin.000002 | 123 | Previous_gtids |         1 |         154 |                                       | 
  12.  
  13. | mysql-bin.000002 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  | 
  14.  
  15. | mysql-bin.000002 | 219 | Query          |         1 |         292 | BEGIN                                 | 
  16.  
  17. | mysql-bin.000002 | 292 | Table_map      |         1 |         342 | table_id: 108 (test1.tb1)             | 
  18.  
  19. | mysql-bin.000002 | 342 | Delete_rows    |         1 |         391 | table_id: 108 flags: STMT_END_F       | 
  20.  
  21. | mysql-bin.000002 | 391 | Xid            |         1 |         422 | COMMIT /* xid=14 */                   | 
  22.  
  23. | mysql-bin.000002 | 422 | Anonymous_Gtid |         1 |         487 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  | 
  24.  
  25. | mysql-bin.000002 | 487 | Query          |         1 |         560 | BEGIN                                 | 
  26.  
  27. | mysql-bin.000002 | 560 | Table_map      |         1 |         610 | table_id: 108 (test1.tb1)             | 
  28.  
  29. | mysql-bin.000002 | 610 | Write_rows     |         1 |         654 | table_id: 108 flags: STMT_END_F       | 
  30.  
  31. | mysql-bin.000002 | 654 | Xid            |         1 |         685 | COMMIT /* xid=15 */                   | 
  32.  
  33. +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 
  34.  
  35. 12 rows in set (0.00 sec)  

使用mysqlbinlog工具的-v(--verbose)选项,该选项会将行事件重构成被注释掉的伪SQL语句,如果想看到更详细的信息可以将该选项给两次如-vv,这样可以包含一些数据类型和元信息的注释内容,如

先切换到binlog所在的目录下

mysqlbinlog mysql-bin.000001

mysqlbinlog -v mysql-bin.000001

mysqlbinlog -vv mysql-bin.000001

-h,-P,-p,-u等,这些参数仅在指定了--read-from-remote-server后有效。

恢复导出binlog日志

 

恢复完成后查看表的数据是否完整

常见的选项有:

--start-datetime #从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件

--stop-datetime #从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件

--start-position #从二进制日志中读取指定position 事件位置作为开始

--stop-position #从二进制日志中读取指定position 事件位置作为事件截至

 

因为备份得时候把删除那项跳过了,所以id为2的zhangsan也恢复了

使用mysqldump备份恢复

它是用于备份和数据迁移的工具,一般在数据量比较小的情况下使用如几个G,当数据比较大的情况下建议不使用;mysqldump可以对单(多)个表、单(多)个数据库及所有数据库进行导出操作;

  1. mysqldump [options] db_name [tbl_name ...] #导出指定数据库或单个表 
  2.  
  3. mysqldump [options] --databases db_name ... #导出多个数据库 
  4.  
  5. mysqldump [options] --all-databases #导出所有  

备份数据库test1

  1. mysqldump -p123456 --flush-logs test1 > /opt/test1.spl 
  2.  
  3. #备份整个数据库,并且重新开启一个新的binlog 
  4.  
  5. mysql -p123456 test1 </opt/test1.spl #从备份的目录中导入  

创建数据测试mysqldump工具

 

查看数据

 

创建备份的目录及备份数据库和cp bin.000001二进制文件

 

清楚二进制00000.2之前的日志

 

删除数据测试

 

然后在进行备份第二个二进制文件

 

进行数据库恢复

   

  

 

查看表的数据是否恢复 

 

上面数据已经全部恢复 希望能帮到你 

责任编辑:庞桂玉 来源: 51CTO博客
相关推荐

2017-07-10 16:00:47

Mysql数据备份数据还原

2017-01-06 08:24:23

备份恢复大数据

2010-10-14 10:18:28

MySQL数据库备份

2016-10-19 16:50:43

大数据

2010-10-14 09:36:47

MySQL命令行

2016-06-30 10:38:34

大数据备份恢复

2009-04-17 11:28:16

Oracle备份恢复

2018-09-11 10:30:18

MySQL存储引擎数据备份

2010-03-02 09:47:03

Fedora MySQ

2011-08-02 16:03:47

Oracle数据库冷备份冷备份异地恢复

2016-10-13 09:14:03

大数据备份

2009-11-20 09:29:53

2023-12-07 15:09:23

2019-02-28 21:20:50

MySQL备份与恢复数据库

2018-06-29 08:17:53

2020-07-23 19:42:12

MySQL备份恢复数据库

2023-01-11 08:05:23

XtraBackupMySQL备份

2010-09-06 16:02:00

DB2

2023-09-12 09:45:54

Java数据库

2016-10-25 15:59:22

大数据备份IT周刊
点赞
收藏

51CTO技术栈公众号