首页 / 日志 / mysql 二进制日志恢复数据实验
mysql 二进制日志恢复数据实验
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 二进制日志恢复数据实验，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含15413字，纯文字阅读大概需要23分钟。
内容图文
一：binlog基础知识 1.mysqlbinlog常见选项： --start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间 --stop--datetime:从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样 --start-position:从二进制日志中读取指定position事件位置作为起始 --stop-position:从二进制日志中读取指定position事件位置作为事件截止 2.binlog最重要的使用场景 mysql主从复制： 数据恢复 3.binlog文件类型 二进制日志索引文件(后缀.index），记录所有的二进制文件 二进制日志文件（后缀.00000*）,记录数据库所有的DDL和DML（除select外）的所有语句事件 4.开启binlog日志 编辑my.cnf 在[mysqld]区域添加 log-bin=mysql-bin 重启mysqld使配置生效 5.查看binlog是否开启 show variables like ‘log_%‘; 6.常用的binlog日志操作命令 查看所有binlog日志列表 show master logs; 查看master状态，即是最新一个binlog日志的编号名称，及最后一个操作事件pos结束点(position)值 show master status; 7.刷新log日志命令，flush logs; 产生一个新编号的binlog日志文件 8.清空所有binlog日志 reset master; 二.binlog日志查看分析 1.查看binlog日志 binlog是二进制文件，必须使用自带的mysqlbinlog命令查看 2.binlog日志查询命令 格式：show binlog events [IN ‘log_name‘] [FROM pos] [LIMIT [offset,] row_count]; 执行实例:show binlog events in ‘mariadb-bin.000009‘ from 95120 limit 0,5\G 95120是pos值 0是偏移量 偏移的意思是跳过中间几行的意思。 5是row_count 查询总条数 3.mysqlbinlog的语法格式 mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名 -------------------------------------------------------- 常用参数选项解释： --start-position 起始pos点 --stop-position 结束pos点 --start-datetime 起始时间点 --stop-datetime= 结束时间点 --database= 指定只恢复数据库(一台主机上往往有多个数据库，只限本地log日志) -------------------------------------------------------- 不常用选项： -u --user=name 连接到远程主机的用户名 -p --password[=name] 连接到远程主机的密码 -h --host=name 从远程主机上获取binlog日志 --read-from-remote-server 从某个MySQL服务器上读取binlog日志 4.做一个数据恢复的测试 测试的具体思路，这是测试前的所有数据库 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sampdb | | school | | test | | wordpress | +--------------------+ 7 rows in set (0.00 sec) 具体做法如下： 新建一个数据库test2 在数据库里生成表格，插入数据 做一个整个数据库的备份 更新表格中的数据 然后删除test2 要实现的目标： 恢复test2的数据 5. 具体步骤和参考命令： MariaDB [(none)]> MariaDB [(none)]> create database test2 default character set utf8 collate utf8_general_ci; 创建数据库 test2 设定为utf8 MariaDB [test2]> create table yg_info(id int,name varchar(10),age int,sex enum(‘f‘,‘m‘)) engine=innode; Query OK, 0 rows affected, 2 warnings (0.14 sec) 创建数据表 yg_info MariaDB [test2]> create table gongzi(name varchar(10),gz int)engine=innode; Query OK, 0 rows affected, 2 warnings (0.08 sec) 创建数据表工资 MariaDB [test2]> insert into yg_info values(‘1‘,‘jim‘,‘22‘,‘f‘),(‘2‘,‘tom‘,‘30‘,‘f‘); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test2]> insert into yg_info values(‘3‘,‘lili‘,‘20‘,‘m‘); Query OK, 1 row affected (0.01 sec) 插入三条数据给yg_info表格 MariaDB [test2]> insert into gongzi value(‘jim‘,‘18000‘),(‘tom‘,‘15000‘),(‘lili‘,‘12000‘); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 插入三条数据给gongzi表格 现在给这个数据库做一个备份 [root@localhost mysql]# mysqldump -uroot -p --database test2 >/root/test2.sql Enter password: [root@localhost mysql]# cd /root [root@localhost ~]# ls anaconda-ks.cfg mysql sampdb sedsrc shell software test2.sql [root@localhost ~]# 给yp_info插入一些数据 MariaDB [test2]> insert into yg_info values(‘4‘,‘am‘,‘30‘,‘f‘); Query OK, 1 row affected (0.00 sec) 更新gongzi表格的数据 MariaDB [test2]> insert into gongzi values(‘am‘,‘15000‘); Query OK, 1 row affected (0.00 sec) MariaDB [test2]> update gongzi set gz=‘16000‘ where name=‘lili‘; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 这个时候手贱了，删除了test2 MariaDB [test2]> drop database test2; Query OK, 2 rows affected (0.05 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sampdb | | school | | test | | wordpress | +--------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> 恢复丢失的数据 我们如果不知道，什么时候删除的database？ 首先要做的第一件事情，确定是什么时候执行的删除database? 看看日志文件 [root@localhost mariadb]# ll 总用量 355060 -rw-r--r-- 1 root root 49639299 8月 28 16:36 bin.txt -rw-rw---- 1 mysql mysql 214622082 8月 30 15:16 general.log -rw-rw---- 1 mysql mysql 50544070 8月 23 17:20 mariadb-bin.000001 -rw-rw---- 1 mysql mysql 48571274 8月 23 18:05 mariadb-bin.000002 -rw-rw---- 1 mysql mysql 264 8月 23 23:09 mariadb-bin.000003 -rw-rw---- 1 mysql mysql 4820 8月 24 17:42 mariadb-bin.000004 -rw-rw---- 1 mysql mysql 1333 8月 24 21:41 mariadb-bin.000005 -rw-rw---- 1 mysql mysql 2160 8月 24 22:28 mariadb-bin.000006 -rw-rw---- 1 mysql mysql 264 8月 25 17:34 mariadb-bin.000007 -rw-rw---- 1 mysql mysql 264 8月 26 12:23 mariadb-bin.000008 -rw-rw---- 1 mysql mysql 97945 8月 28 17:52 mariadb-bin.000009 -rw-rw---- 1 mysql mysql 2190 8月 30 15:15 mariadb-bin.000010 -rw-rw---- 1 mysql mysql 360 8月 30 09:25 mariadb-bin.index -rw-r----- 1 mysql mysql 58815 8月 30 10:32 mariadb.log -rw-rw---- 1 mysql mysql 3861 8月 30 10:29 slow.log [root@localhost mariadb]# 查看下这些日志 确定那个日志执行了drop database [root@localhost mariadb]# mysqlbinlog mariadb-bin.000010 |grep "drop database" drop database tpcc1000 drop database test2 [root@localhost mariadb]# 可以看到是000010这个日志记录了执行删除数据库命令 具体一点信息 [root@localhost mariadb]# mysqlbinlog mariadb-bin.000010 |grep -A 10 -B 11 "drop database test2" # at 1970 #170830 15:14:38 server id 1 end_log_pos 2080 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1504077278/*!*/; update gongzi set gz=‘16000‘ where name=‘lili‘ /*!*/; # at 2080 #170830 15:14:38 server id 1 end_log_pos 2107 Xid = 198 COMMIT/*!*/; # at 2107 #170830 15:15:54 server id 1 end_log_pos 2190 Query thread_id=34 exec_time=0 error_code=0 SET TIMESTAMP=1504077354/*!*/; drop database test2 /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost mariadb]# 分析： 删除数据库的position点区间是2107到2190,只要回复到position 点2107前就可以 时间点是：170830 15:15:54 我们前面做了一个备份 [root@localhost ~]# ll 总用量 16 -rw-------. 1 root root 1419 8月 14 22:48 anaconda-ks.cfg drwxr-xr-x 2 root root 23 8月 22 09:33 mysql drwxr-xr-x 11 root root 4096 8月 28 15:24 sampdb -rw-r--r-- 1 root root 43 8月 17 16:53 sedsrc drwxr-xr-x 2 root root 123 8月 22 09:32 shell drwxr-xr-x 4 root root 90 8月 23 15:22 software -rw-r--r-- 1 root root 2721 8月 30 15:08 test2.sql [root@localhost ~]# 备份的时间点 15：08分 第一部：先把备份文件还原回去 [root@localhost ~]# mysql -uroot -p </root/test2.sql Enter password: [root@localhost ~]# 检查下还原成功了吗 MariaDB [test2]> show tables; +-----------------+ | Tables_in_test2 | +-----------------+ | gongzi | | yg_info | +-----------------+ 2 rows in set (0.01 sec) MariaDB [test2]> select * from gongzi; +------+-------+ | name | gz | +------+-------+ | jim | 18000 | | tom | 15000 | | lili | 12000 | +------+-------+ 3 rows in set (0.00 sec) MariaDB [test2]> select * from yg_info -> ; +------+------+------+------+ | id | name | age | sex | +------+------+------+------+ | 1 | jim | 22 | f | | 2 | tom | 30 | f | | 3 | lili | 20 | m | +------+------+------+------+ 3 rows in set (0.00 sec) 还原成功 这只是还原了15:08之前的操作，15:08到15:15之间还有操作，我们没有还原。 这部分数据也必须还原。 到这一步发现，时间戳及其重要。 通过mysqlbinlog日志，对比时间戳，可以找出我们做备份的时间戳。 我这里的时间戳是： #170830 15:05:16 server id 1 end_log_pos 1334 Query thread_id=30 exec_time=0 error_code=0 SET TIMESTAMP=1504076716/*!*/; BEGIN /*!*/; # at 1334 #170830 15:05:16 server id 1 end_log_pos 1470 Query thread_id=30 exec_time=0 error_code=0 SET TIMESTAMP=1504076716/*!*/; insert into gongzi value(‘jim‘,‘18000‘),(‘tom‘,‘15000‘),(‘lili‘,‘12000‘) /*!*/; # at 1470 #170830 15:05:16 server id 1 end_log_pos 1497 Xid = 146 COMMIT/*!*/; # at 1497 #170830 15:12:11 server id 1 end_log_pos 1566 Query thread_id=32 exec_time=0 error_code=0 SET TIMESTAMP=1504077131/*!*/; 执行命令： [root@localhost mariadb]# mysqlbinlog mariadb-bin.000010 |sed -n ‘/15:05/,$p‘ 查看这个时间戳后执行的所有命令记录 [root@localhost mariadb]# mysqlbinlog mariadb-bin.000010 |sed -n ‘/15:05/,$p‘ #170830 15:05:16 server id 1 end_log_pos 1334 Query thread_id=30 exec_time=0 error_code=0 SET TIMESTAMP=1504076716/*!*/; BEGIN /*!*/; # at 1334 #170830 15:05:16 server id 1 end_log_pos 1470 Query thread_id=30 exec_time=0 error_code=0 SET TIMESTAMP=1504076716/*!*/; insert into gongzi value(‘jim‘,‘18000‘),(‘tom‘,‘15000‘),(‘lili‘,‘12000‘) /*!*/; # at 1470 #170830 15:05:16 server id 1 end_log_pos 1497 Xid = 146 COMMIT/*!*/; # at 1497 #170830 15:12:11 server id 1 end_log_pos 1566 Query thread_id=32 exec_time=0 error_code=0 SET TIMESTAMP=1504077131/*!*/; BEGIN /*!*/; # at 1566 #170830 15:12:11 server id 1 end_log_pos 1675 Query thread_id=32 exec_time=0 error_code=0 SET TIMESTAMP=1504077131/*!*/; insert into yg_info values(‘4‘,‘am‘,‘30‘,‘f‘) /*!*/; # at 1675 #170830 15:12:11 server id 1 end_log_pos 1702 Xid = 190 COMMIT/*!*/; # at 1702 #170830 15:13:40 server id 1 end_log_pos 1771 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1504077220/*!*/; BEGIN /*!*/; # at 1771 #170830 15:13:40 server id 1 end_log_pos 1874 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1504077220/*!*/; insert into gongzi values(‘am‘,‘15000‘) /*!*/; # at 1874 #170830 15:13:40 server id 1 end_log_pos 1901 Xid = 197 COMMIT/*!*/; # at 1901 #170830 15:14:38 server id 1 end_log_pos 1970 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1504077278/*!*/; BEGIN /*!*/; # at 1970 #170830 15:14:38 server id 1 end_log_pos 2080 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1504077278/*!*/; update gongzi set gz=‘16000‘ where name=‘lili‘ /*!*/; # at 2080 #170830 15:14:38 server id 1 end_log_pos 2107 Xid = 198 COMMIT/*!*/; # at 2107 #170830 15:15:54 server id 1 end_log_pos 2190 Query thread_id=34 exec_time=0 error_code=0 SET TIMESTAMP=1504077354/*!*/; drop database test2 /*!*/; # at 2190 #170830 15:30:09 server id 1 end_log_pos 2341 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/; SET @@session.sql_mode=524288/*!*/; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test2` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!*/; # at 2341 #170830 15:30:09 server id 1 end_log_pos 2460 Query thread_id=35 exec_time=0 error_code=0 use `test2`/*!*/; SET TIMESTAMP=1504078209/*!*/; DROP TABLE IF EXISTS `gongzi` /* generated by server */ /*!*/; # at 2460 #170830 15:30:09 server id 1 end_log_pos 2647 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; CREATE TABLE `gongzi` ( `name` varchar(10) DEFAULT NULL, `gz` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!*/; # at 2647 #170830 15:30:09 server id 1 end_log_pos 2756 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; /*!40000 ALTER TABLE `gongzi` DISABLE KEYS */ /*!*/; # at 2756 #170830 15:30:09 server id 1 end_log_pos 2825 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; BEGIN /*!*/; # at 2825 #170830 15:30:09 server id 1 end_log_pos 2959 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; INSERT INTO `gongzi` VALUES (‘jim‘,18000),(‘tom‘,15000),(‘lili‘,12000) /*!*/; # at 2959 #170830 15:30:09 server id 1 end_log_pos 2986 Xid = 231 COMMIT/*!*/; # at 2986 #170830 15:30:09 server id 1 end_log_pos 3094 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; /*!40000 ALTER TABLE `gongzi` ENABLE KEYS */ /*!*/; # at 3094 #170830 15:30:09 server id 1 end_log_pos 3214 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; DROP TABLE IF EXISTS `yg_info` /* generated by server */ /*!*/; # at 3214 #170830 15:30:09 server id 1 end_log_pos 3468 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; CREATE TABLE `yg_info` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `sex` enum(‘f‘,‘m‘) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!*/; # at 3468 #170830 15:30:09 server id 1 end_log_pos 3578 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; /*!40000 ALTER TABLE `yg_info` DISABLE KEYS */ /*!*/; # at 3578 #170830 15:30:09 server id 1 end_log_pos 3647 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; BEGIN /*!*/; # at 3647 #170830 15:30:09 server id 1 end_log_pos 3791 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; INSERT INTO `yg_info` VALUES (1,‘jim‘,22,‘f‘),(2,‘tom‘,30,‘f‘),(3,‘lili‘,20,‘m‘) /*!*/; # at 3791 #170830 15:30:09 server id 1 end_log_pos 3818 Xid = 241 COMMIT/*!*/; # at 3818 #170830 15:30:09 server id 1 end_log_pos 3927 Query thread_id=35 exec_time=0 error_code=0 SET TIMESTAMP=1504078209/*!*/; /*!40000 ALTER TABLE `yg_info` ENABLE KEYS */ /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost mariadb]# 我们需要恢复的就是这部分数据 分析 我们需要恢复的数据 position点范围为：1566---2107 [root@localhost mariadb]# mysqlbinlog --start-position=1566 --stop-position=2107 --database=test2 mariadb-bin.000010 |mysql -uroot -p 看看是不是恢复成功？ MariaDB [(none)]> use test2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [test2]> select * from gongzi; +------+-------+ | name | gz | +------+-------+ | jim | 18000 | | tom | 15000 | | lili | 16000 | | am | 15000 | +------+-------+ 4 rows in set (0.00 sec) MariaDB [test2]> select * from yg_info; +------+------+------+------+ | id | name | age | sex | +------+------+------+------+ | 1 | jim | 22 | f | | 2 | tom | 30 | f | | 3 | lili | 20 | m | | 4 | am | 30 | f | +------+------+------+------+ 4 rows in set (0.00 sec) 测试成功。
本文出自 “gome学习” 博客，请务必保留此出处http://goome.blog.51cto.com/4045241/1961123
mysql 二进制日志恢复数据实验
标签：binlog mysql
本文系统来源：http://goome.blog.51cto.com/4045241/1961123
内容总结
以上是互联网集市为您收集整理的mysql 二进制日志恢复数据实验全部内容，希望文章能够帮你解决mysql 二进制日志恢复数据实验所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】