Complete simulation implementation of deletion and recovery of mysql and mariadb
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)
