浅析MySQL数据库常用管理

来源：作者：IT168 周卫丰出处：巧巧读书 2008-02-08 进入讨论组

一个简单的恢复测试

做全库备份：

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -uroot -p 
   --single-transaction --flush-logs --master-data=2 mytest > backup_mytest.sql
Enter password: ******

备份后创建新的测试表并INSERT测试数据：

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.27-community-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| aaa              |
| customer         |
| mytable          |
| sys_tests        |
| test             |
| test1            |
| test_isam        |
+------------------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE NEW_TABLE(ID VARCHAR(20),MC VARCHAR(60));
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO NEW_TABLE VALUES('1','111');
Query OK, 1 row affected (0.23 sec)

mysql> SELECT * FROM NEW_TABLE;
+------+------+
| ID   | MC   |
+------+------+
| 1    | 111  |
+------+------+
1 row in set (0.00 sec)

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.53 sec)

删除所有TABLE后：

mysql> SHOW TABLES;
Empty set (0.00 sec)

我们从全库备份文件执行恢复操作:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -p mytest < backup_mytest.sql
Enter password: ******

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 5.0.27-community-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| aaa              |
| customer         |
| mytable          |
| sys_tests        |
| test             |
| test1            |
| test_isam        |
+------------------+
7 rows in set (0.01 sec)

这时我们发现新创建的测试表丢失,我们将应用增量备份来恢复最新数据。

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog -uroot -p
  E:\MySqlData\mochasof-8ed6b1-bin.000003 | mysql -uTEST -p mytest
Enter password: Enter password: ******
****

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24 to server version: 5.0.27-community-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| aaa              |
| customer         |
| mytable          |
| new_table        |
| sys_tests        |
| test             |
| test1            |
| test_isam        |
+------------------+
8 rows in set (0.02 sec)

mysql> SELECT * FROM NEW_TABLE;
+------+------+
| ID   | MC   |
+------+------+
| 1    | 111  |
+------+------+
1 row in set (0.00 sec)

至此新创建的表NEW_TABLE恢复完成。MYSQL还支持基于时间点的恢复及设置自动恢复等，我们在这里不做太多讲述。

保留：： http://www.qqread.com/mysql/r396485.html