使用mysql bin 恢复数据

root@localhost:(none):12: >flush logs;
root@localhost:(none):12: >show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------
----+
| mysql-bin.000030 | 120 | | |
|
+------------------+----------+--------------+------------------+---------------
----+
# 1.创建binlog库
create database binlog;
use binlog
# 2.创建表
create table binlog_table(id int);
# 3.插入数据
insert into binlog_table values(1);
+------+
| id   |
+------+
| 1    |
+------+
# 4.插入数据
insert into binlog_table values(2);
insert into binlog_table values(3);
commit;
select * from binlog_table;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
+------+
# 5.删除数据
delete from binlog_table where id=1;
commit;
root@localhost:binlog:18: >select * from binlog_table;
+------+
| id   |
+------+
| 2    |
| 3	   |
+------+
# 6.修改数据
update binlog_table set id=22 where id=2;
commit;
root@localhost:binlog:19: >select * from binlog_table;
+------+
| id |
+------+
| 22 |
| 3 |
+------+
# 7.删表
drop table binlog_table;
# 8.删库
drop database binlog;

使用binlog 截取数据

# 1.准备新环境
[root@db02 ~]# /etc/init.d/mysqld stop
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=1
log-bin=/app/mysql/data/mysql-bin
binlog_format=row
socket=/opt/mysql.sock
[root@db02 ~]# rm -fr /app/mysql-5.6.50/data
[root@db02 ~]# /app/mysql-5.6.50/scripts/mysql_install_db --user=mysql --
basedir=/app/mysql --datadir=/app/mysql/data
[root@db02 ~]# /etc/init.d/mysqld start


# 2.将旧数据库数据进行全备
[root@db01 data]# mysqldump -uroot -p123 -A > /tmp/full.sql

# 3.新环境创建一个可以远程连接的用户
mysql> grant all on *.* to root@'%' identified by '123';


# 4.将全备恢复到新环境
[root@db01 data]# mysql -uroot -p123 -h172.16.1.52 < /tmp/full.sql


# 5.旧环境截取binlog
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv
/app/mysql/data/mysql-bin.000030
起始位置点：120
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv
/app/mysql/data/mysql-bin.000030 |grep -iC 10 delete
结束位置点：832
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=832
/app/mysql/data/mysql-bin.000030 > /tmp/binlog.sql


# 6.恢复截取的数据到新环境
[root@db01 data]# mysql -uroot -p123 -h172.16.1.52 < /tmp/binlog.sql


# 7.全备后的新增数据恢复到新环境


# 8.应用割接
- 开发改代码
- 运维导出新环境数据恢复到旧环境

存在的问题

1.生产中肯定不会刷新binlog
2.生产中肯定会一直提供服务，实时都在写入数据

3.生产中截取数据时，可能会截取到其他库的数据
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv -d binlog
/app/mysql/data/mysql-bin.000030
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=832 -d binlog
/app/mysql/data/mysql-bin.000030 > /tmp/binlog.sql

### binlog只能用来当成是增量数据的备份一定要配合mysqldump逻辑备份的全备一起使用