Shulou(Shulou.com)05/31 Report--

Editor to share with you what to do after the mysql database is mistakenly deleted. I hope you will get something after reading this article. Let's discuss it together.

First, make sure that mysql turns on binlog logging

In the [mysqld] block in the / etc/my.cnf file, add:

Log-bin=mysql-bin

Then restart the mysql service

(1) create a table customers under the ops library

Mysql > use ops

Mysql > create table customers (

-> id int not null auto_increment

-> name char (20) not null

-> age int not null

-> primary key (id)

->) engine=InnoDB

Query OK, 0 rows affected (0.09 sec)

Mysql > show tables

+-+

| | Tables_in_ops |

+-+

| | customers |

+-+

1 row in set (0.00 sec)

Mysql > desc customers

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | NULL | auto_increment |

| | name | char (20) | NO | | NULL |

| | age | int (11) | NO | | NULL |

+-+ +

3 rows in set (0.02 sec)

Mysql > insert into customers values (1, "wangbo", "24")

Query OK, 1 row affected (0.06 sec)

Mysql > insert into customers values (2, "guohui", "22")

Query OK, 1 row affected (0.06 sec)

Mysql > insert into customers values (3, "zhangheng", "27")

Query OK, 1 row affected (0.09 sec)

Mysql > select * from customers

+-- +

| | id | name | age | |

+-- +

| | 1 | wangbo | 24 | |

| | 2 | guohui | 22 | |

| | 3 | zhangheng | 27 | |

+-- +

3 rows in set (0.00 sec)

(2) full backup now

[root@vm-002] # mysqldump-uroot-p-B-F-R-x-- master-data=2 ops | gzip > / opt/backup/ops_$ (date +% F) .sql.gz

Enter password:

[root@vm-002 ~] # ls / opt/backup/

Ops_2016-09-25.sql.gz

Parameter description:

-B: specify the database

-F: refresh the log

-R: backup stored procedures, etc.

-x: lock the table

-- master-data: add CHANGE MASTER statement, binlog file and location information to the backup statement

(3) insert the data again

Mysql > insert into customers values (4, "liupeng", "21")

Query OK, 1 row affected (0.06 sec)

Mysql > insert into customers values (5, "xiaoda", "31")

Query OK, 1 row affected (0.07 sec)

Mysql > insert into customers values (6, "fuaiai", "26")

Query OK, 1 row affected (0.06 sec)

Mysql > select * from customers

+-- +

| | id | name | age | |

+-- +

| | 1 | wangbo | 24 | |

| | 2 | guohui | 22 | |

| | 3 | zhangheng | 27 | |

| | 4 | liupeng | 21 | |

| | 5 | xiaoda | 31 | |

| | 6 | fuaiai | 26 | |

+-- +

6 rows in set (0.00 sec)

(4) erroneous operation at this time and deletion of test database

Mysql > drop database ops

Query OK, 1 row affected (0.04 sec)

At this point, between complete and misoperation, the data written by the user is in the binlog and needs to be recovered!

(5) check the newly added binlog file after it is complete.

[root@vm-002 ~] # cd / opt/backup/

[root@vm-002 backup] # ls

Ops_2016-09-25.sql.gz

[root@vm-002 backup] # gzip-d ops_2016-09-25.sql.gz

[root@vm-002 backup] # ls

Ops_2016-09-25.sql

[root@vm-002 backup] # grep CHANGE ops_2016-09-25.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106

This is the location of the binlog file for the full time

That is, line 106 of mysql-bin.000002, so the data in the binlog file prior to this file is already contained in this complete sql file.

After reading this article, I believe you have a certain understanding of "what to do after the mysql database is deleted by mistake". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.