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

one。 Using binlog to recover database table

Strategy:

1. Enable binlog Log

two。 Create db1 library tb1 table and insert 3 records

3. Delete 3 records that have just been inserted in the tb1 table

4. Use mysqlbinlog to recover 3 deleted records

Step 1: enable binlog Log

1) adjust / etc/my.cnf configuration and restart the service

[mysqld]

.

Log_bin=logdir

Server_id=50

Binlog_format=mixed

.

[root@MySQL50 ~] # systemctl restart mysqld

2) confirm the binlog log file

When binlog is newly enabled, a new log file is generated each time the MySQl service is started:

[root@MySQL50 ~] # ls / var/lib/mysql/logdir.*

/ var/lib/mysql/logdir.000001 / var/lib/mysql/logdir.index

Where the logdir.index file records the list of binaries currently held:

Restart the MySQL service program, or execute the SQL operation "FLUSH LOGS;" to generate a new log:

[root@MySQL50 ~] # cat / var/lib/mysql/logdir.index

. / logdir.000001

[root@MySQL50 ~] # systemctl restart mysqld

[root@MySQL50 ~] # cat / var/lib/mysql/logdir.index

. / logdir.000001

. / logdir.000002

Step 2: redo database operations using binlog logs

1) perform database table add operation

Create db1 ·Library tb1 table with custom table structure:

Mysql > create database db1

Query OK, 1 row affected (0.11 sec)

Mysql > create table db1.tb1 (

-> id tinyint

Name char (18)

->)

Query OK, 0 rows affected (0.68 sec

Mysql > insert into db1.tb1 values (

-> 1, "yangmi")

-> (2, "tangyuan")

-> (3, "liuyan")

Query OK, 3 rows affected (0.13 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > select * from db1.tb1

+-+ +

| | id | name |

+-+ +

| | 1 | yangmi |

| | 2 | tangyuan |

| | 3 | liuyan |

+-+ +

3 rows in set (0.00 sec)

2) Delete the 3 table records added in the previous step

Perform a delete all table records operation:

Mysql > delete from db1.tb1

Query OK, 3 rows affected (0.14 sec)

Mysql > select * from db1.tb1

Empty set (0.00 sec)

Step 3: restore table records through binlog logs

Binlog records all database and table changes, so it can re-perform some of the previous data operations when necessary, but it will not apply to libraries and table data that already exist before binlog is enabled.

According to the above requirements of "restoring the three deleted table records", you should check the relevant log files through the mysqlbinlog tool to find the time to delete these table records, as long as you resume the previous SQL operation (mainly inserting the three records).

[root@MySQL50 ~] # cat / var/lib/mysql/logdir.index

. / logdir.000001

. / logdir.000002

Mysql > show variables like "binlog_format"

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | MIXED |

+-+ +

1 row in set (0.01 sec)

2) execute the sql command within the specified range of Pos nodes to recover data

Mysql > create table db1.tb2 (id tinyint, name char (12))

Query OK, 0 rows affected (0.40 sec)

Alert user root@localhost identified by "Wjc_2018"

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.