mysqdump+binlog恢复数据
备份全库
[root@db01 b]# mysqldump -uroot -poldboy123 -A > /b/full.sql
Warning: Using a password on the command line interface can be insecure.
[root@db01 b]#ll
total 656
-rw-r--r-- 1 root root 667860 Mar 11 15:39 full.sql
打开看下都是一些插入类的语句比较容易读懂,可以用awk.sed,等命令过滤需要的内容
[root@db01 b]#vim full.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mysql`;
-- Table structure for table `columns_priv`
DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `columns_priv` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
对单库进行备份
[root@db01 b]#mysqldump -uroot -poldboy123 -B test test1 test2 > /b/test_jt.sql
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000029 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
-F flush-logs刷新binlog 有几个库生成几个binlog
[root@db01 b]#mysqldump -uroot -poldboy123 -A -R --triggers --flush-logs > /b/flush.sql
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000032 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
模拟删库
周日23:00全备此命令就是定时任务里的命令
[root@db01 b]#mysqldump -uroot -poldboy123 -A -R --triggers --master-data=2 --single-transaction | gzip > /b/all_`date +%F-%H-%M-S`.sql.gz
模拟数据变化查,看插入的数据
mysql> create table ttt.nnn(id int primary key auto_increment not null, name char(20) not null);
mysql> insert into nnn values(1,'aa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into nnn values(2,'bb');
Query OK, 1 row affected (0.00 sec)
mysql> insert into nnn values(3,'cc');
Query OK, 1 row affected (0.00 sec)
mysql> insert into nnn values(4,'dd');
Query OK, 1 row affected (0.00 sec)
mysql> select * from ttt.nnn;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000028 | 850 | | | |
+------------------+----------+--------------+------------------+-------------------+
模拟删库
mysql> drop databases ttt;
查看起始点
这个就是全备时候加了 master-data=2 ,生成的备份文件,22行有恢复数据的起点,120
[root@db01 ~]#sed -n '22p' /b/all_2019-03-11-19-03-55.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=120;
查看当前binlog是000028
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000028 | 939 | | | |
+------------------+----------+--------------+------------------+-------------------+
查看结束点
结束点位置 ,就是drop pos的位置
mysql> show binlog events in "mysql-bin.000028";
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------------------+
| mysql-bin.000028 | 4 | Format_desc | 6 | 120 | Server ver: 5.6.40-log, Binlog ver: 4
| mysql-bin.000028 | 120 | Query | 6 | 224 | create database ttt charset utf8
| mysql-bin.000028 | 224 | Query | 6 | 384 | use `ttt`; create table ttt.nnn(id int primary key auto_increment not null, name char(20) not null) |
| mysql-bin.000028 | 384 | Query | 6 | 455 | BEGIN
| mysql-bin.000028 | 455 | Table_map | 6 | 503 | table_id: 323 (ttt.nnn)
| mysql-bin.000028 | 503 | Write_rows | 6 | 546 | table_id: 323 flags: STMT_END_F
| mysql-bin.000028 | 546 | Table_map | 6 | 594 | table_id: 323 (ttt.nnn)
| mysql-bin.000028 | 594 | Write_rows | 6 | 637 | table_id: 323 flags: STMT_END_F
| mysql-bin.000028 | 637 | Table_map | 6 | 685 | table_id: 323 (ttt.nnn) |
| mysql-bin.000028 | 685 | Write_rows | 6 | 728 | table_id: 323 flags: STMT_END_F
| mysql-bin.000028 | 728 | Table_map | 6 | 776 | table_id: 323 (ttt.nnn)
| mysql-bin.000028 | 776 | Write_rows | 6 | 819 | table_id: 323 flags: STMT_END_F
| mysql-bin.000028 | 819 | Xid | 6 | 850 | COMMIT /* xid=3690 */
| mysql-bin.000028 | | Query | 6 | 939 | drop database ttt
+------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------------------
导出截取的binlog起始点和结束点,这些数据就是删库,的数据
[root@db01 ~]#mysqlbinlog -uroot -poldboy123 --start-position=120 --stop-position=850 /data/mysql/mysql-bin.000028 > /b/delttt_binlog.sql
停止binlog写入
mysql> set sql_log_bin=1
恢复全备及 恢复截取的binlog
mysql> source /b/all_2019-03-11-19-03-55.sql;
mysql> source /b/delttt_binlog.sql;
数据恢复了
mysql> select * from ttt.nnn;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
| 4 | dd |
+----+------+
最新文章
- DOM节点属性
- 程序员最喜爱的12个Android应用开发框架二(转)
- weka特征选择(IG、chi-square)
- mysql给root开启远程访问权限,修改root密码
- Java 包装类中的静态函数
- 使用 mina 传输大字节数组
- Log4NET简介
- ###g++编译器
- sort uniq妙用
- 重拾python
- Nuget常用命令操作
- iOS安全攻防之越狱设备检测
- JavaScript中你所不知道的Object(一)
- Winform数据库连接app.config文件配置
- Lua中的函数
- js获取子元素的内容
- (转)simple-framework(MaliSDK框架分析)
- Vue.js学习笔记(一) - 起步
- query更多的筛选用法
- spring boot 1
热门文章
- phpstorm2019.1 实现保存(ctrl+s)同时格式化代码
- ODAC Developer Downloads - Oracle Universal Installer
- 解决VM Workstation安装VMware Tools显示灰色的办法
- linux 下core文件生成、路径、格式设置及调试
- makefile那些事儿
- linux安装jira
- 191128A学习入门-典型信号,单位冲激信号
- 【并行计算-CUDA开发】GPU并行编程方法
- 关于UiAutomator无法识别的元素
- 002 spring boot框架,引入mybatis-generator插件,自动生成Mapper和Entity