Automatic backup of MySQL database and recovery after database corruption (3)

[2] Recovery method when the database is modified

The database has been modified, there may be many reasons for the intrusion, and the corresponding program has a bug, and so on, not described in detail here. This will only show you how to revert to the pre-modified state after the database has been modified.

This is similar to the "recovery method after the database has been deleted" described above. Here, the test database then uses the test that you just used earlier. In order to keep our friends in touch with the database from confusing, we log on to the MySQL server again to confirm the information about the database test that we just created.

[[email protected] ~]# mysql-u root-p← log in to MySQL server with root Enter password:← the root user password for MySQL Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is a 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← connection to the test database Reading table information for completion of table and column names Can turn off this feature to get a quicker startup with-a Database changed Mysql> Show tables; ← Viewing tables that exist in the test database +-------------------+ | Tables_in_test | +-------------------+ | Test | +-------------------+ 1 row in Set (0.00 sec) Mysql> select * from test; ← Viewing content in a 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, the current state of the database, and then do a backup.

[[email protected] ~]# cd← back to the root directory of the root user where the script is located [[email protected] ~]#./mysql-backup.sh← Run script for database backup

Next, we log in to the MySQL server again, making some modifications to the test database tests to make it easier to test whether data recovery is successful.

[[email protected] ~]# mysql-u root-p← log in to MySQL server with root Enter password:← the root user password for MySQL Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is the server version:4.1.20 Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> use test← connection to the test database Reading table information for completion of table and column names 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 test to "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 that the table in test is defined value +------+--------------------+ | num | name | +------+-------------------+ | 1 |　 Shit,windows | ← Confirm that the values in the original test database table have been modified to the new value "Shit,windows" +------+-------------------+ 1 row in Set (0.00 sec) mysql> exit← exit MySQL server Bye

Above, we are equivalent to simulating the process of database tampering. Next, the database is "tampered" with the method of restoring with a backup.

[[email protected] ~]#/bin/cp-rf/backup/mysql/test//var/lib/mysql/← Copy the backed up database test to the appropriate directory

Then, log on to the MySQL server again to see if the database was restored to the state before it was "tampered with".

[[email protected] ~]# mysql-u root-p← log in to MySQL server with root Enter password:← the root user password for MySQL Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is a to server version:4.1.20 Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> use test← connection to the test database Reading table information for completion of table and column names Can turn off this feature to get a quicker startup with-a Database changed Mysql> select * from test; ← Viewing content in a database +------+----------------+ | num | name | +------+----------------+ | 1|　Hello,centos | ← Confirm that the content in the datasheet is the same as the "Hello,centos" defined before it is modified! +------+----------------+ 1 row in Set (0.01 sec) mysql> exit← exit MySQL server Bye

The above results indicate that after the database has been modified, the database has been successfully restored to the state before it was "tampered".

After the test ...

After the test is completed, the test used legacy information is removed.

[[email protected] ~]# mysql-u root-p← log in to MySQL server with root Enter password:← the root user password for MySQL Welcome to the MySQL Monitor. Commands End With; or \g. Your MySQL Connection ID is server version:4.1.20 Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer. mysql> use test← connection to the test database Reading table information for completion of table and column names Can turn off this feature to get a quicker startup with-a Database changed mysql> drop table test; ← Delete a table from the test database Query OK, 0 rows affected (0.01 sec) mysql> drop database test; ← Delete Test with database test Query OK, 0 rows Affected (0.00 sec) mysql> show databases; ← View the currently existing database +-------------+ | Database | +-------------+ |　 MySQL | ← Verify that the test data database test does not exist, has been deleted +-------------+ 1 row in Set (0.00 sec) mysql> exit← exit MySQL server Bye

The above describes the way to back up a database using mysqlhotcopy, a shell script that we built ourselves.

For many enthusiasts, building a server may not be a consideration for data corruption and recovery after data corruption. However, for the server, the recovery efficiency after data corruption is also one of the differences between amateur and professional factors. So I recommend that you configure the Web server and MySQL server and so on, do not rush to apply it, and to find ways to make it "indestructible" under Limited (hardware, software) conditions, and then consider the application of the problem.

Also, the database automatic backup script mentioned in the above method is set to run on a daily basis, but when you run certain programs associated with MySQL (forums, communities, etc.), run a database backup script to immediately back up the current state database when you are doing something that might compromise data security. is also very helpful, at least to ensure recoverability of the database in the event of a problem.