利用binlog进行数据库的还原
发表于:2024-11-28 作者:千家信息网编辑
千家信息网最后更新 2024年11月28日,前言:在学习mysql备份的时候,深深的感受到mysql的备份还原功能没有oracle强大;比如一个很常见的恢复场景:基于时间点的恢复,oracle通过rman工具就能够很快的实现数据库的恢复,但是m
千家信息网最后更新 2024年11月28日利用binlog进行数据库的还原
前言:在学习mysql备份的时候,深深的感受到mysql的备份还原功能没有oracle强大;比如一个很常见的恢复场景:基于时间点的恢复,oracle通过rman工具就能够很快的实现数据库的恢复,但是mysql在进行不完全恢复的时候很大的一部分要依赖于mysqlbinlog这个工具运行binlog语句来实现,本文档介绍通过mysqlbinlog实现各种场景的恢复;
一、测试环境说明:使用mysqlbinlog工具的前提需要一个数据库的完整性备份,所以需要事先对数据库做一个完整的备份,本文档通过mysqlbackup进行数据库的全备(mysqlbackup的使用:http://blog.itpub.net/12679300/viewspace-1329578/);
二、测试步骤说明: 数据库的插入准备工作 2.1 在时间点A进行一个数据库的完整备份; 2.2 在时间点B创建一个数据库BKT,并在BKT下面创建一个表JOHN,并插入5条数据; 2.3 在时间点C往表JOHN继续插入数据到10条;
数据库的恢复工作 2.4 恢复数据库到时间点A,然后检查数据库表的状态; 2.5 恢复数据库到时间点B,检查相应的系统状态; 2.6 恢复数据库到时间点C,并检查恢复的状态;
三、场景模拟测试步骤(备份恢复是一件很重要的事情) 3.1 执行数据库的全备份;
4.2 mysqlbinlog的其他常用参数:
参数的组合使用:
总结:备份有时候永远都用不上,但是你永远也不知道什么时候会用上,正所谓养兵千日用兵一时,作为一个合格的DBA有个可用的备份,就可以做到胸有成竹;
前言:在学习mysql备份的时候,深深的感受到mysql的备份还原功能没有oracle强大;比如一个很常见的恢复场景:基于时间点的恢复,oracle通过rman工具就能够很快的实现数据库的恢复,但是mysql在进行不完全恢复的时候很大的一部分要依赖于mysqlbinlog这个工具运行binlog语句来实现,本文档介绍通过mysqlbinlog实现各种场景的恢复;
一、测试环境说明:使用mysqlbinlog工具的前提需要一个数据库的完整性备份,所以需要事先对数据库做一个完整的备份,本文档通过mysqlbackup进行数据库的全备(mysqlbackup的使用:http://blog.itpub.net/12679300/viewspace-1329578/);
二、测试步骤说明: 数据库的插入准备工作 2.1 在时间点A进行一个数据库的完整备份; 2.2 在时间点B创建一个数据库BKT,并在BKT下面创建一个表JOHN,并插入5条数据; 2.3 在时间点C往表JOHN继续插入数据到10条;
数据库的恢复工作 2.4 恢复数据库到时间点A,然后检查数据库表的状态; 2.5 恢复数据库到时间点B,检查相应的系统状态; 2.6 恢复数据库到时间点C,并检查恢复的状态;
三、场景模拟测试步骤(备份恢复是一件很重要的事情) 3.1 执行数据库的全备份;
点击(此处)折叠或打开
- [root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //运行数据库的完整备份
点击(此处)折叠或打开
- mysql> SELECT CURRENT_TIMESTAMP;
- +---------------------+
- | CURRENT_TIMESTAMP |
- +---------------------+
- | 2014-11-26 17:51:27 |
- +---------------------+
- 1 row in set (0.01 sec)
- mysql> show databases; //尚未创建数据库BKT
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | john |
- | mysql |
- | performance_schema |
- +--------------------+
- 4 rows in set (0.03 sec)
- mysql> Ctrl-C --
- Aborted
- [root@mysql02 data]# mysql -uroot -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \\g.
- Your MySQL connection id is 2
- Server version: 5.5.36-log Source distribution
- Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000001 | 107 | | | //当前数据库log的pos状态
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
- mysql> SELECT CURRENT_TIMESTAMP; //当前的时间戳 当前时间点A
- +---------------------+
- | CURRENT_TIMESTAMP |
- +---------------------+
- | 2014-11-26 17:54:12 |
- +---------------------+
- 1 row in set (0.00 sec)
- mysql> create database BKT; //创建数据库BKT
- Query OK, 1 row affected (0.01 sec)
- mysql> create table john (id varchar(32));
- ERROR 1046 (3D000): No database selected
- mysql> use bkt;
- ERROR 1049 (42000): Unknown database \'bkt\'
- mysql> use BKT;
- Database changed
- mysql> create table john (id varchar(32));
- Query OK, 0 rows affected (0.02 sec)
- mysql> insert into john values(\'1\');
- Query OK, 1 row affected (0.01 sec)
- mysql> insert into john values(\'2\');
- Query OK, 1 row affected (0.01 sec)
- mysql> insert into john values(\'3\');
- Query OK, 1 row affected (0.00 sec)
- mysql> insert into john values(\'4\');
- Query OK, 1 row affected (0.01 sec)
- mysql> insert into john values(\'5\');
- Query OK, 1 row affected (0.01 sec)
- mysql> SELECT CURRENT_TIMESTAMP; //插入5条数据后数据库的时间点B,记录该点便于数据库的恢复
- +---------------------+
- | CURRENT_TIMESTAMP |
- +---------------------+
- | 2014-11-26 17:55:53 |
- +---------------------+
- 1 row in set (0.00 sec)
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000001 | 1204 | | | //当前binlog的pos位置
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
点击(此处)折叠或打开
- mysql> insert into john values(\'6\');
- Query OK, 1 row affected (0.02 sec)
- mysql> insert into john values(\'7\');
- Query OK, 1 row affected (0.01 sec)
- mysql> insert into john values(\'8\');
- Query OK, 1 row affected (0.01 sec)
- mysql> insert into john values(\'9\');
- Query OK, 1 row affected (0.01 sec)
- mysql> insert into john values(\'10\');
- Query OK, 1 row affected (0.03 sec)
- mysql> show master status;
- +------------------+----------+--------------+------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +------------------+----------+--------------+------------------+
- | mysql-bin.000001 | 2125 | | |
- +------------------+----------+--------------+------------------+
- 1 row in set (0.00 sec)
- mysql> SELECT CURRENT_TIMESTAMP;
- +---------------------+
- | CURRENT_TIMESTAMP |
- +---------------------+
- | 2014-11-26 17:58:08 |
- +---------------------+
- 1 row in set (0.00 sec)
点击(此处)折叠或打开
- [root@mysql02 data]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back
- MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26]
- Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
- mysqlbackup: INFO: Starting with following command line ...
- mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql
- --backup-dir=/backup/ copy-back
- mysqlbackup: INFO:
- IMPORTANT: Please check that mysqlbackup run completes successfully.
- At the end of a successful \'copy-back\' run mysqlbackup
- prints \"mysqlbackup completed OK!\".
- 141126 17:59:58 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-26.17-59-58_copy_back.log
- --------------------------------------------------------------------
- Server Repository Options:
- --------------------------------------------------------------------
- datadir = /data/mysql
- innodb_data_home_dir = /data/mysql
- innodb_data_file_path = ibdata1:10M:autoextend
- innodb_log_group_home_dir = /data/mysql/
- innodb_log_files_in_group = 2
- innodb_log_file_size = 5242880
- innodb_page_size = Null
- innodb_checksum_algorithm = none
- --------------------------------------------------------------------
- Backup Config Options:
- --------------------------------------------------------------------
- datadir = /backup/datadir
- innodb_data_home_dir = /backup/datadir
- innodb_data_file_path = ibdata1:10M:autoextend
- innodb_log_group_home_dir = /backup/datadir
- innodb_log_files_in_group = 2
- innodb_log_file_size = 5242880
- innodb_page_size = 16384
- innodb_checksum_algorithm = none
- mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
- 141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads
- 1 read-threads 1 write-threads
- mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin.
- Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.
- 141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.
- mysqlbackup: Progress in MB: 200 400 600
- 141126 18:00:22 mysqlbackup: INFO: Copying the database directory \'john\'
- 141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'mysql\'
- 141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'performance_schema\'
- 141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files.
- 141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile0\'
- 141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile1\'
- 141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql
- 141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully.
- 141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to \'/data/mysql\'
- mysqlbackup completed //数据库恢复完成
点击(此处)折叠或打开
- [root@mysql02 data]# chmod -R 777 mysql //需要授权后才能打开
- [root@mysql02 data]# cd mysql
- [root@mysql02 mysql]# ll
- 总用量 733220
- -rwxrwxrwx. 1 root root 305 11月 26 18:00 backup_variables.txt
- -rwxrwxrwx. 1 root root 740294656 11月 26 18:00 ibdata1
- -rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile0
- -rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile1
- drwxrwxrwx. 2 root root 4096 11月 26 18:00 john
- drwxrwxrwx. 2 root root 4096 11月 26 18:00 mysql
- drwxrwxrwx. 2 root root 4096 11月 26 18:00 performance_schema
- -rwxrwxrwx. 1 root root 8488 11月 26 18:00 server-all.cnf
- -rwxrwxrwx. 1 root root 1815 11月 26 18:00 server-my.cnf //没有BKT数据库
- [root@mysql02 mysql]# service mysqld start //启动数据库
点击(此处)折叠或打开
- [root@mysql02 mysql2]# pwd //备份的时候,需要备份binlog日志,之前的binlog目录为/data/mysql2
- /data/mysql2
- [root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //根据post的位置进行恢复,当前的pos位置为107,恢复到pos位置到1203
- Enter password:
- [root@mysql02 mysql2]# mysql -uroot -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \\g.
- Your MySQL connection id is 3
- Server version: 5.5.36-log Source distribution
- Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | BKT |
- | john |
- | mysql |
- | performance_schema |
- +--------------------+
- 5 rows in set (0.02 sec)
- mysql> use BKT
- Database changed
- mysql> show tables;
- +---------------+
- | Tables_in_BKT |
- +---------------+
- | john |
- +---------------+
- 1 row in set (0.00 sec)
- mysql> select * from john;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- | 3 |
- | 4 |
- | 5 |
- +------+
- 5 rows in set (0.01 sec) //查看数据库恢复成功
点击(此处)折叠或打开
- [root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" mysql-bin.000001| mysql -uroot -p123456 //本次通过基于时间点的恢复,恢复到时间点C
- Warning: Using unique option prefix start-date instead of start-datetime is deprecated and will be removed in a future release. Please use the full name instead.
- Warning: Using unique option prefix stop-date instead of stop-datetime is deprecated and will be removed in a future release. Please use the full name instead.
- [root@mysql02 mysql2]# mysql -uroot -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \\g.
- Your MySQL connection id is 6
- Server version: 5.5.36-log Source distribution
- Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | BKT |
- | john |
- | mysql |
- | performance_schema |
- +--------------------+
- 5 rows in set (0.00 sec)
- mysql> use BKT
- Database changed
- mysql> select * from john;
- +------+
- | id |
- +------+
- | 1 |
- | 2 |
- | 3 |
- | 4 |
- | 5 |
- | 6 |
- | 7 |
- | 8 |
- | 9 |
- | 10 |
- +------+
- 10 rows in set (0.00 sec) //经过检查成功恢复到时间点C
4.2 mysqlbinlog的其他常用参数:
-h 根据数据库的IP
-P 根据数据库所占用的端口来分
-server-id 根据数据库serverid来还原(在集群中很有用)
-d 根据数据库名称
参数的组合使用:
点击(此处)折叠或打开
- [root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" -d BKT -h 127.0.0.1 /var/lib/mysql/mysql-bin.000001 |mysql -u root -p
- #如果有多个binlog文件,用逗号隔开;
总结:备份有时候永远都用不上,但是你永远也不知道什么时候会用上,正所谓养兵千日用兵一时,作为一个合格的DBA有个可用的备份,就可以做到胸有成竹;
数据
数据库
备份
时间
到时
测试
位置
文件
时候
状态
检查
参数
场景
工具
运行
重要
成功
事情
内容
功能
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
广州东信达软件开发有限公司
网络安全管理局电话
数据库中安全的定义
4个u服务器
nba2k垃圾服务器
广州新华互联网科技学校是技校吗
虚拟现实和信息网络技术
虚拟机如何建立dns服务器
全球通信网络安全
计算机网络技术是文员吗
网络技术和软件哪个好
简述服务器与pc的不同
网易云游戏服务器在德国吗
java写数据库的增删改查
中国书画家联谊会师资数据库
中原银行软件开发一面二面
技术支持转软件开发
北京交易软件开发教程
协同软件开发哪个好用
im服务器开发
通过代码导出数据库结构变成文档
云开发服务器添加数据
索菱股份有限公司软件开发
我的世界弄什么服务器好玩
cache 数据库 修改记录
数据库中级证属于软考吗
软件开发 公司 北京
手机号加密存储数据库
烟台软件开发培训机构
北京交易软件开发教程