Shulou(Shulou.com)06/01 Report--

Below, I would like to tell you briefly about the specific operation of using mysqlbinlog to recover data. Have you learned about similar topics before? If you are interested, let's take a look at this article. I believe it will be of some help to you after reading the specific operations of using mysqlbinlog to recover data.

First, turn on bin-log

1. Install mysql

[root@zhen ~] # yum-y install mysql mysql-devel mysql-server

2. Enable the bin-log function

[root@zhen ~] # vi / etc/my.cnf 1 [mysqld] 2 datadir=/var/lib/mysql 3 socket=/var/lib/mysql/mysql.sock 4 user=mysql 5 # Disabling symbolic-links is recommended to prevent assorted security risks 6 symbolic-links=0 7 log_bin=mysql_bin

Restart mysqld after the configuration is saved.

Bin-log is the log file generated when SQL is executed, except that the log is binary. There are two functions:

① data recovery

② configuration database master-slave, used for slave to synchronize data from master.

3. Confirm whether bin-log is enabled.

Log in to the database and execute:

Mysql > show variables like 'log_bin'

See that it is indeed turned on, which generates a log file in the format mysql_bin.000001 in the datadir directory of the database:

Second, view the generated log files

Bin-log is a binary file and cannot be opened with a text editor such as vim. Mysql provides two ways to view. Before viewing, execute some SQL to make a record in the file.

Mysql > use test;mysql > create table students (id int (10) primary key auto_increment,name varchar); mysql > insert into students (id,name) values (1memorialzhangsan'), (2memoriallisi`); mysql > select * from students

The first way to view: the mysqlbinlog command that comes with mysql:

[root@zhen] # / usr/bin/mysqlbinlog / var/lib/mysql/mysql_bin.000001

Taking inserting data into a table as an example, look at the format of the log:

# at 248pm 160711 17:29:00 server id 1 end_log_pos 373TIMESTAMP 1468272540 * / insert into students (id,name) values (1 meme Zhangsan') / *! / /

"at 248" indicates the starting position of this record (position)

"160711 17:29:00" indicates the time when the record was produced.

"server id 1" indicates which CVM generated the record.

"end_log_pos 373" indicates where this record ends.

Finally, you can see the executed SQL.

The data can be recovered according to the starting position and time of the record.

The second way to view:

Mysql > show binlog events in "mysql_bin.000001"

It is simple and clear to look at it in this way. You can also see the starting position of the record, the SQL executed, and so on.

Third, use bin-log to recover data

First delete the table students that you just created in the test library:

Mysql > drop table students;Query OK, 0 rows affected (0.01 sec)

There are two ways to restore this table after deletion:

① recovers according to the time of operation.

Using mysqlbinlog to view the log file, it is found in the record that the time when the table was created is 160711 17:24:00, and the last time to delete the table is 160711 19:10:38. Use these two time points to restore the table.

[root@zhen] # / usr/bin/mysqlbinlog-- start-date= "2016-07-11 17:24:00"-- stop-date= "2016-07-11 19:10:38" / var/lib/mysql/mysql_bin.000001 | / usr/bin/mysql-uroot-p

If no database password is set, / usr/bin/mysql

You can also export as a sql file and then import the sql file into the database:

[root@zhen] # / usr/bin/mysqlbinlog-- start-date= "2016-07-11 17:24:00"-- stop-date= "2016-07-11 19:10:38" / var/lib/mysql/mysql_bin.000001 > / tmp/1.sqlmysql > source / tmp/1.sql

② recovers according to the start and end position of the operation.

[root@zhen ~] # / usr/bin/mysqlbinlog-- start-position=106-- stop-position=373 / var/lib/mysql/mysql_bin.000001 | / usr/bin/mysql-uroot-p

IV. Summary

1. In order to recover the database in the event of a disaster, add log_bin=mysql_bin to the configuration file now. "mysql_bin" is the format of the log file, which can be customized.

2. Use "mysql > show master status;" to determine which log file the database is currently using.

What do you think of this article about how to use mysqlbinlog to recover data and whether it has gained anything? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.