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

Simulate the scene:

Sunday full backup-delete the library at 10:00 on Monday, db1,-- 10:10 found that some pages are inaccessible, how to restore the data?

Note:

Because there is more than one library, so db1 is deleted, users may access db2 normally, but there is often a relationship between multiple databases, such as commodity database and order database, so in order to avoid data inconsistency, it often leads to logical problems. The service should be stopped at this time. That is, to suspend the access of users, which is a big problem, which requires careful choice, whether the service is important or the data is more important, it is best to report to the superior.

Processing logic:

1. Stop service: disconnect the network connection between the database and the remote application, so that only local users can manage

2. Logic carding:

Full backup restore: full backup on Sunday

Data between 10:00 on Sunday and Monday; the deleted SQL command

Data from 10:00 to 10:10 on Monday

If the business data is not very important, then restore the full backup on Sunday and lose the data that was fully backed up to 10:00 on Monday. [root@centos7x ~] $mysqldump-A-F-- master-data=2-- single-transaction > all_ `date +% Flying% T`.sql [root@centos7x ~] $less all_2018-02-24021\: 46\: 13.sql-- Position to start replication or point-in-time recovery from---- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000010', MASTER_LOG_POS=245 -Current Database: `hellodb` then for the test environment, the binary log data before 'mariadb-bin.000010', MASTER_LOG_POS=245 can be deleted. For stand-alone backup,-- master-data=1 or 2 is OK, but if it is active replication, it is better to be 1.

Simulated failure:

Simulate modify and delete operation

MariaDB [hellodb] > select * from students

+-+ +

| | StuID | Name | Age | Gender | ClassID | TeacherID | |

+-+ +

| | 1 | Shi Zhongyu | 22 | M | 2 | 3 | |

| | 2 | Shi Potian | 22 | M | 1 | 7 | |

| | 3 | Xie Yanke | 53 | M | 2 | 16 |

| | 4 | Ding Dian | 32 | M | 4 | 4 |

| | 5 | Yu Yutong | 26 | M | 3 | 1 | |

| | 6 | Shi Qing | 46 | M | 5 | NULL |

| | 7 | Xi Ren | 19 | F | 3 | NULL |

| | 8 | Lin Daiyu | 17 | F | 7 | NULL |

| | 9 | Ren Yingying | 20 | F | 6 | NULL |

| | 10 | Yue Lingshan | 19 | F | 3 | NULL |

| | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| | 12 | Wen Qingqing | 19 | F | 1 | NULL |

| | 13 | Tian Boguang | 33 | M | 2 | NULL |

| | 14 | Lu Wushuang | 17 | F | 3 | NULL |

| | 15 | Duan Yu | 19 | M | 4 | NULL |

| | 16 | Xu Zhu | 21 | M | 1 | NULL |

| | 17 | Lin Chong | 25 | M | 4 | NULL |

| | 18 | Hua Rong | 23 | M | 7 | NULL |

| | 19 | Xue Baochai | 18 | F | 6 | NULL |

| | 20 | Diao Chan | 19 | F | 7 | NULL |

| | 21 | Huang Yueying | 22 | F | 6 | NULL |

| | 22 | Xiao Qiao | 20 | F | 1 | NULL |

| | 23 | Ma Chao | 23 | M | 4 | NULL |

| | 24 | Xu Xian | 27 | M | NULL | NULL | |

| | 25 | Sun Dasheng | 100 | M | NULL | NULL | |

+-+ +

25 rows in set (0.00 sec)

MariaDB [hellodb] > insert into students (name,age,gender) values; Query OK, 1 row affected (0.01 sec) MariaDB [hellodb] > select * from students +-+ | StuID | Name | Age | Gender | ClassID | TeacherID | +- -- + | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | 3 | Xie Yanke | 53 | M | 2 | 16 | 4 | Ding Dian | 32 | M | | 4 | 4 | 5 | Yu Yutong | 26 | M | 3 | 1 | 6 | Shi Qing | 46 | M | 5 | NULL | 7 | Xi Ren | 19 | F | 3 | NULL | 8 | Lin Daiyu | 17 | F | 7 | NULL | | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 12 | Wen Qingqing | 19 | F | 1 | NULL | 13 | Tian Boguang | 33 | M | 2 | NULL | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | 16 | Xu Zhu | 21 | M | 1 | NULL | 17 | Lin Chong | 25 | M | 4 | NULL | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | 20 | Diao Chan | 19 | F | 7 | NULL | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | 26 | a | 29 | F | NULL | NULL | +- -+-+ 26 rows in set (0.00 sec) MariaDB [hellodb] >

MariaDB [hellodb] > drop database hellodb

Query OK, 7 rows affected (0.01sec)

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

MariaDB [(none)] > use test

Database changed

MariaDB [test] > create table T1 (id int)

Query OK, 0 rows affected (0.01 sec)

MariaDB [test] > select * from test

ERROR 1146 (42S02): Table 'test.test' doesn''t exist

MariaDB [test] >

MariaDB [test] > show tables

+-+

| | Tables_in_test |

+-+

| | T1 |

+-+

1 row in set (0.00 sec)

MariaDB [test] > flush tables with read lock

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > select * from T1

Empty set (0.00 sec)

MariaDB [test] > insert T1 values (1)

ERROR 1223 (HY000): Can''t execute the query because you have a conflicting read lock

MariaDB [test] >

Data recovery operations:

MariaDB [test] > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mariadb-bin.000001 | 7700 | |

| | mariadb-bin.000002 | 290 |

| | mariadb-bin.000003 | 290 |

| | mariadb-bin.000004 | 290 |

| | mariadb-bin.000005 | 290 |

| | mariadb-bin.000006 | 290 |

| | mariadb-bin.000007 | 290 |

| | mariadb-bin.000008 | 290 |

| | mariadb-bin.000009 | 290 |

| | mariadb-bin.000010 | 669 | |

+-+ +

10 rows in set (0.00 sec)

Refresh the log:

Then the data before mariadb-bin.000010', MASTER_LOG_POS=245 is restored by full backup.

'mariadb-bin.000010', MASTER_LOG_POS=245 and' mariadb-bin.000011', MASTER_LOG_POS=245

Use binary logs to recover the data between

MariaDB [test] > flush logs

Query OK, 0 rows affected (0.01 sec)

MariaDB [test] > show master logs

+-+ +

| | Log_name | File_size |

+-+ +

| | mariadb-bin.000001 | 7700 | |

| | mariadb-bin.000002 | 290 |

| | mariadb-bin.000003 | 290 |

| | mariadb-bin.000004 | 290 |

| | mariadb-bin.000005 | 290 |

| | mariadb-bin.000006 | 290 |

| | mariadb-bin.000007 | 290 |

| | mariadb-bin.000008 | 290 |

| | mariadb-bin.000009 | 290 |

| | mariadb-bin.000010 | 714 | |

| | mariadb-bin.000011 | 245 |

+-+ +

11 rows in set (0.01 sec)

If there are multiple binary logs after a full backup, they all need to be copied and merged.

[root@centos7x] $cp / var/lib/mysql/mariadb-bin.000010.

[root@centos7x ~] $ls

All_2018-02-24_21:46:13.sql all.sql anaconda-ks.cfg hellodb_InnoDB.sql initial-setup-ks.cfg mariadb-bin.000010

[root@centos7x] $mysqlbinlog-- start-position=245 mariadb-bin.000010

[root@centos7x ~] $mysqlbinlog-- start-position=245 mariadb-bin.000010 > binlog.sql

Delete the SQL statement that deletes the database in the binary log

[root@centos7x ~] $vim binlog.sql

Drop database hellodb

The function of recording binary logs needs to be temporarily turned off before restoring the data.

MariaDB [test] > show variables like 'sql_log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | sql_log_bin | ON |

+-+ +

1 row in set (0.00 sec)

MariaDB [test] > set sql_log_bin=0

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > show variables like 'sql_log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | sql_log_bin | OFF |

+-+ +

1 row in set (0.00 sec)

The log_bin options in sql_log_bin and / etc/my.cnf are both options that can affect binary records, but if one of them is turned off, then

The binary logging function is disabled. If log_bin is in the configuration file, the MySQL service needs to be restarted if modified.

Sql_log_bin is at the reply level, effective immediately.

Unlock the read lock of the database

MariaDB [test] > unlock tables

Query OK, 0 rows affected (0.01 sec)

Restore a full backup:

MariaDB [test] > source all_2018-02-24_21:46:13.sql

MariaDB [(none)] > select * from hellodb.students

Binary log recovery:

Finally, the binary log is recorded to restore the user's access.

MariaDB [(none)] > source binlog.sql

MariaDB [(none)] > select * from hellodb.students

| | 25 | Sun Dasheng | 100 | M | NULL | NULL | |

| | 26 | a | 29 | F | NULL | NULL | |

+-+ +

MariaDB [test] > set sql_log_bin=1

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > show variables like 'sql_log_bin'

+-+ +

| | Variable_name | Value |

+-+ +

| | sql_log_bin | ON |

+-+ +

1 row in set (0.00 sec)

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.