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

Scene one

I made a full backup at 2 o'clock yesterday morning and used it normally during the day. What a coincidence when it's time to get off work! The hard drive is broken. Fortunately, however, it has been backed up and the binary log is stored separately from the database.

1. Set up the database and open the binary log

Create a folder for storing binary logs, and modify the folder group owner so that mysql users can read it.

Make a full backup at 02:00. After the backup, see if the backup is successful.

Mysqldump-A-F-single-transaction-master-data=2 > / backup/ fullbak`date +% F`.sql

Less / backup/fullbak2019-07-11.sql #-the master-data option automatically records the location of the bin-log during backup

Simulate data modification from 2 o'clock to 18:00

2. The hard disk storing the database is damaged and all databases are lost.

3. Restore

During fault recovery, users can suspend access to the database.

Vi / etc/my.cnf # add a line

Systemctl restart mariadb

View the location of the current binary log

Get 2: 00 to 18:00 data from the binary log

[root@swh ~] mysqlbinlog-- start-position=245 / data/logbin/mysql-bin.000003 > / backup/inc.sql [root@swh ~] # mysqlbinlog / data/logbin/mysql-bin.000004 > > / backup/inc.sql [root@swh ~] # mysqlbinlog / data/logbin/mysql-bin.000005 > > / backup/inc.sql [root@swh ~] # mysqlbinlog / data/logbin/mysql-bin.000006 > > / backup/inc.sql

Since restoring the database will generate a large number of logs, you can temporarily close the binary log

MariaDB [(none)] > set sql_log_bin=off;MariaDB [(none)] > source / backup/fullbak2019-07-11.sql # Import full backup MariaDB [(none)] > source / backup/inc.sql # Import incremental backup MariaDB [(none)] > set sql_log_bin=on; check the database after recovery and restore access to MariaDB [hellodb] > select * from students

Vi / etc/my.cnf # remove skip-networking

Scene two

A full backup was made at 2: 00 in the morning. during the day, a colleague mistakenly deleted the database, but he did not find it at that time, until there was a failure at the end of the day.

1. Full backup of the database

Mysqldump-A-F-single-transaction-master-data=2 > / backup/ fullbak`date +% F`.sql

Less / backup/fullbak2019-07-11.sql #-the master-data option automatically records the location of the bin-log during backup

2. The table was deleted, but it was not found. Continue to use it and find the problem at 18:00.

3. Restore

During fault recovery, users can suspend access to the database.

Vi / etc/my.cnf # add a line

Systemctl restart mariadb

View the location of the current log

Get increments from binary logs based on the log location recorded in the backup file

[root@swh ~] # mysqlbinlog-- start-position=245 / data/logbin/mysql-bin.000013 > / backup/inc.sql [root@swh ~] # cp / backup/inc.sql / backup/inc.sql.bak # backup under modified backup [root@swh ~] # vi / backup/inc.sql # find the table deletion statement from the binary log and delete it. The rest of the log is the log used.

[root@swh ~] # rm-rf / var/lib/mysql/* # can choose to delete all database restores, or directly restore incremental backups [root@swh ~] # systemctl restart mariadbMariaDB [(none)] > set sql_log_bin=off;MariaDB [(none)] > source / backup/fullbak2019-07-11.sqlMariaDB [(none)] > source / backup/inc.sqlMariaDB [(none)] > set sql_log_bin=on

Check the data

Vi / etc/my.cnf # remove skip-networking

Although it can be recovered, the time cost may be quite large, and the production environment needs to be cautious.

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.