Using mysqldump to realize the data restore of +binlog of all-Library backup

With the growth of the business, the database is also multiplied, the original use of the whole library backup is no longer suitable for the current database, easy to backup 10g-20g, too much disk space, so consider a more concise and faster and more disk space-saving backup method, This is the idea of using Binlog logs for backup and recovery, the following are the specific implementation methods:

Environment Introduction:

Operating system: Centos 7.2

Database: Mysql 5.6

I. Installing MySQL and changing configuration files

Installation is not specifically described, many online tutorials, configuration files need to add the following options:

Vim/etc/my.cnf

Log_bin = Mysql-binlog #开启binlog日志功能, default in MySQL's DataDir directory

Show variables like ' Log_bin '; #进入Mysql查看binlog日志是否开启

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/9A/0A/wKiom1lQpkaz_CEaAAANq-NpGUw922.png-wh_500x0-wm_ 3-wmp_4-s_2981004345.png "title=" Log_bin "width=" 236 "height=" "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width : 236px;height:100px; "alt=" Wkiom1lqpkaz_ceaaaanq-npguw922.png-wh_50 "/>

Two. Create experimental data

Because of the newly built database, there is no data in the log, and a new database and table are created to experiment

#创建t1库

Create DATABASE T1;

#创建tab1表

CREATE TABLE T1.TAB1 (ID int primary KEY auto_increment,name varchar (20));

#插入两条数据

Insert into T1.TAB1 (name) VALUES (' Zhangsan ');

Insert into T1.TAB1 (name) VALUES (' Lisi ');

Three. Make a full library backup and log backup

#进行全库备份 and generate a new log

mysqldump-uroot-p123456--flush-logs T1 >/opt/t1_ ' Date +%y%m%d '. sql

#备份日志文件, a few logs are backed up before a full-library backup

cp/usr/local/mysql/binlog/mysql.bin.000001/opt/

Four. Simulating the deletion of data

Delete from t1.tab1 where id=2;

#插入新数据

Insert into T1.TAB1 (name) VALUES (' Wangwu ');

Five. Back up the Binlog log file after mysqldump

cp/usr/local/mysql/binlog/mysql.bin.000002/opt/

Six. Using mysqldump to realize the data restore of the whole library backup +binlog

1. mysql-uroot-p123456 Tab1 </opt/t1_20170626.sql #还原删除前的全部数据, this time there should be two data, Zhangsan and Lisi

2. Mysqlbinlog-v/usr/local/mysql/binlog/mysql.bin.000002 #分析新开启的binlog日志文件, the start and end of the time of misoperation, just skip this period of time

   650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/9A/0D/wKiom1lQuyOS0MPLAAB1B8ZZsCo894.png-wh_500x0-wm_ 3-wmp_4-s_1741147517.png "title=" Binlog "alt=" Wkiom1lquyos0mplaab1b8zzsco894.png-wh_50 "/>

3. Recovering data from Binlog

   Mysqlbinlog--stop-position=120/opt/mysql.bin.000002|mysql-uroot-p123456

   Mysqlbinlog--start-position=291/opt/mysql.bin.000002|mysql-uroot-p123456

4. View recovery Status

   SELECT * from T1.TAB1; #此时表中有三条数数据为恢复成功

The manual backup recovery process has been fully completed, next time to say how to script this process

This article is from "Linux Operations Technology" blog, please be sure to keep this source http://forall.blog.51cto.com/12356505/1942061

Use mysqldump to implement +binlog data restore for full-Library backup