Using Mysqldump to realize data restore of full database backup + binlog

With the growth of business, the database is also growing exponentially. The original full-database backup is no longer suitable for the current database. 10G-20G is easily backed up, which takes up too much disk space, so consider using a more concise, faster and more space-saving backup method. You have come up with the idea of using binlog logs for backup and recovery. Here are the specific implementation methods:

Environment introduction:

Operating system: Centos 7.2

Database: Mysql 5.6

one。 Install Mysql and change configuration files

Installation is not specific, there are many online tutorials, the configuration file needs to add the following options:

Vim / etc/my.cnf

Log_bin = mysql-binlog # enables the binlog log function, which is located under the datadir directory of mysql by default

Show variables like 'log_bin'; # enter Mysql to check whether the binlog log is enabled

two。 Create experimental data

Because of the newly built database, there is no data in the log, so create a new database and table to experiment.

# create T1 library

Create database t1

# create tab1 table

Create table t1.tab1 (id int primary key auto_increment,name varchar (20))

# insert two pieces of data

Insert into t1.tab1 (name) values ('zhangsan')

Insert into t1.tab1 (name) values ('lisi')

three。 Perform full library backup and log backup

# make a full database backup and generate new logs

Mysqldump-uroot-p123456-- flush-logs T1 > / opt/t1_ `date +% Y% m% d`.sql

# back up log files. Back up only a few logs before backing up the whole library.

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

four。 Simulated deletion of data

Delete from t1.tab1 where id=2

# insert new data

Insert into t1.tab1 (name) values ('wangwu')

five。 Back up the binlog log files after mysqldump

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

six。 Using Mysqldump to realize data restore of full database backup + binlog

Mysql-uroot-p123456 tab1 < / opt/t1_20170626.sql # restore all data before deletion, there should be two pieces of data, zhangsan and lisi

Mysqlbinlog-v / usr/local/mysql/binlog/mysql.bin.000002 # analyzes the newly opened binlog log file, and shows the starting and ending positions of the time of misoperation. Just skip this period of time.

Restore 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

View recovery status

Select * from t1.tab1; # at this time, there are three items of data in the table that have been successfully restored.

The manual backup and recovery process has been completed. Next time, talk about how to script this process.