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

The following mainly brings you how to restore MySQL database backup by erroneous deletion. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit and operate MySQL database backup by mistake. All right, don't talk too much nonsense, let's just read the following.

Example: on June 1, 2013, the database of a website was mistakenly deleted at about 10:00, resulting in the inaccessibility of the website, because the database has backed up the data before 0: 00 a.m. but there is no backup data for the data from 0 o'clock to 10:00, so you are required to restore all the data in the database.

One: introduction to data records

The data before 0: 00 on June 1st are

| | 1 | oldboy | | 2 | oldgirl | | 3 | inca | | 4 | zuma | | 5 | kaka | data written after 0: 00 on June 1 and around 10:00 | 6 | oldgirl | | 7 | test | | 8 | laowang |

Second: simulate the preparation for the occurrence of accidents

1. In order not to affect this experiment, we first put the timing task of the system.

[root@db01] # crontab-lump hammer 5 * / usr/sbin/ntpdate time.nist.gov > / dev/null 2 > & 1

two。 First, adjust the usage time to 0: 00 in the morning, and backup the database at 00:00 every day.

[root@db01 backup] # date-s "2013-06-01" Mon Aug 29 00:00:00 CST 2016

3. Check what data is in the database first.

[root@db01 ~] # mysql-uroot-poldboy123-S / data/3306/mysql.sockmysql > use oldboy

Create a test test

CREATE TABLE `test` (`id` int (4) NOT NULL AUTO_INCREMENT, `name` char (20) NOT NULL,PRIMARY KEY (`id`))

Insert data into the table

Insert into test values (1), (2) (2), (3), (3), (4), (5); mysql > select * from test +-1 | oldboy | 2 | oldgirl | 3 | inca | 4 | zuma | | 5 | kaka | +-+-+ 5 rows in set (0.00 sec)

4. First, make a full backup of the oldboy database

The following parameter-- master-data=2 needs to enable binlog log

[root@db02] # mysqldump-uroot-poldboy123-S / data/3306/mysql.sock-B-F-x-- master-data=2 oldboy | gzip > / server/backup/oldboy_$ (date +% F). Sql.gz # use oldboy;mysql > insert into test values +-oldboy | 2 | oldgirl | 3 | inca | 4 | zuma | 5 | kaka | 6 | oldgirl | 7 | test | 8 | laowang | +-- +-- + 8 rows in set (0.00 sec)

Third, manufacturing failure occurs.

Suppose 10:00:

something happened,

Some leader deleted the database oldboy by mistake

7. Now the simulation fails at 10:00 in the morning, and the database oldboy is deleted. Modify the time first.

[root@db02] # date-s "10:00:00" Mon Aug 29 10:00:00 CST 2016 [root@db02] # mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "drop database oldboy;" Query OK, 1 row affected, 1 warning (0.01sec)

# check whether the database has been deleted successfully

Mysql-uroot-poldboy123-S / data/3306/mysql.sock-e "show databases;"

Four: solve the problem and restore the data

10:10, operation and maintenance staff, website customers, find problems, find developers or operators

Finally, the problem was determined, the database of the website could not be connected, and an error was reported. Login to the database occurs. The database no longer exists.

8. Now let's solve the problem and restore the database.

First of all, we need to know the binlog logs generated after the backup, which can be judged by the backup time.

[root@db02 ~] # ll / data/3306/-rw-rw---- 1 mysql mysql 107 Jun 1 00:02 mysql-bin.000070

9. If you can't see it, look at the information in the backup file.

[root@db02 ~] # cd / server/backup/ [root@db02 backup] # gzip-d oldboy_2013-06-01.sql.gz [root@db02 backup] # grep "CHANGE" oldboy_2013-06-01.sql-CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000070', MASTER_LOG_POS=107

10. Back up the binlog log of mysql-bin.000070 first

[root@db02 ~] # cp / data/3306/mysql-bin.000070 / server/backup/ [root@db02 ~] # ll / server/backup/total 8After root root rwmurr-1 root root 415 Jun 1 10:04 mysql-bin.000070 #

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.