[2] Recovery method after the database is modified
The database is modified, and there may be many reasons, such as being hacked, bugs in the corresponding program, etc., so we will not introduce it in detail here. Here we will only introduce the method of restoring to the state before the database is modified.
Specifically, it is similar to the "recovery method after the database is deleted" mentioned above. Here, the test database is then used to use the test that was just used in the previous section. Here, in order to prevent the understanding of friends who have just been exposed to the database, we log in to the MySQL server again to confirm the relevant information of the test database test just established.
| [root@CentOS ~]# mysql -u root -p ← Log in to the MySQL server with root Enter password: ← Enter MySQL root user password Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 to server version: 4.1.20 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show databases; ← View the currently existing database +-------------+ | Database | +-------------+ | mysql | | test | +------------+ 2 rows in set (0.00 sec) mysql> use test ← Connect to test database Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; ← View tables that exist in the test database +-------------------+ | Tables_in_test | +-------------------+ | test | +-------------------+ 1 row in set (0.00 sec) mysql> select * from test; ← View content in the database +------+--------------------+ | num | name | +------+--------------------+ | 1 | Hello,CentOS| +------+--------------------+ 1 row in set (0.01 sec) mysql> exit ← Exit MySQL server Bye |
Then, we run the database backup script again and back up the database in the current state.
| [root@CentOS ~]# cd ← Back to the root directory of the root user where the script is located [root@CentOS ~]# ./ ← Run the script for database backup |
Next, we log in to the MySQL server again and make some modifications to the test database test to facilitate the test whether the data recovery can be successful.
| [root@sample ~]# mysql -u root -p ← Log in to the MySQL server with root Enter password: ← Enter MySQL root user password Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 to server version: 4.1.20 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test ← Connect to test database Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update test set name='Shit,Windows'; ← Then redefine the value of the table in the test as "Shit,Windows" (formerly "Hello,CentOS") Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; ← Confirm the value defined in the table in test +------+--------------------+ | num | name | +------+-------------------+ | 1 | Shit,Windows | ← Confirm that the value in the original test database table has been modified to the new value "Shit,Windows" +------+-------------------+ 1 row in set (0.00 sec) mysql> exit ← Exit MySQL server Bye |
By the above, we simulate the process of the database being tampered with. Next, it is the method of restoring the database after it is "tampered with" and restored with backup.
| [root@CentOS ~]# /bin/cp -Rf /backup/mysql/test/ /var/lib/mysql/ ← Copy the backup database test to the corresponding directory |
Then, log in to the MySQL server again to see if the database has been restored to its state before it was "tampered with".
| [root@CentOS ~]# mysql -u root -p ← Log in to the MySQL server with root Enter password: ← Enter MySQL root user password Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 to server version: 4.1.20 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test ← Connect to test database Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from test; ← View content in the database +------+----------------+ | num | name | +------+----------------+ | 1| Hello,CentOS|← Confirm that the content in the data table is the same as the "Hello,CentOS" defined before it was modified! +------+----------------+ 1 row in set (0.01 sec) mysql> exit ← Exit MySQL server Bye |
The above results show that after the database is modified, the backup database is successfully restored to the state before it was "tampered".
After testing...
After the test is completed, delete the legacy information used by the test.
| [root@CentOS ~]# mysql -u root -p ← Log in to the MySQL server with root Enter password: ← Enter MySQL root user password Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.1.20 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test ← Connect to test database Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table test; ← Delete the table in the test database Query OK, 0 rows affected (0.01 sec) mysql> drop database test; ← Delete the test database test Query OK, 0 rows affected (0.00 sec) mysql> show databases; ← View the currently existing database +-------------+ | Database | +-------------+ | mysql | ← Confirm that the test database test does not exist and has been deleted +-------------+ 1 row in set (0.00 sec) mysql> exit ← Exit MySQL server Bye |
The above introduces the method of backing up the database using a shell script we created ourselves through mysqlhotcopy.
For many individual enthusiasts, building a server may not be very considering the data being corrupted and the recovery work after it is corrupted. But it must be said that for servers, the recovery efficiency after data corruption is also one of the factors that distinguish between amateur and professionalism. Therefore, the author suggests that when you have configured a web server, MySQL server, etc., don’t rush to apply it, but think about the application issue after making it "indestructible" under limited (hardware, software) conditions.
Moreover, although the database automatic backup script mentioned in the above method is set to run regularly every day, when you run some programs associated with MySQL (forums, communities, etc.) and do some operations that may endanger data security, it is also very helpful to run the database backup script to instantly backup the current state database. At least it can ensure the recovery of the database after problems occur.