MySQL數據庫的數據備份與恢復學習

C:mysqlbin>mysql -u root mysql
mysql> show databases;
+----------+
| Database |
+----------+
|   mysql  |
|   test   |
+----------+
2 rows in set (0.00 sec)
#就用默認的庫，test，或者自己create database database_name;來建立一個。
mysql> use test
Database changed#選擇一個庫。

mysql> create table test(
-> id int,
-> name varchar(40),
-> age tinyint
-> );
#創建了一個test表。show tables;來看到他。
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
|      test      |
+----------------+
1 row in set (0.02 sec)
#看看表的結構。
mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| FIEld |     Type    | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
|   id  |    int(11)  |  YES |     |   NULL  |       |
|  name | varchar(40) |  YES |     |   NULL  |       |
|   age |  tinyint(4) |  YES |     |   NULL  |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#插入三條記錄。
mysql> insert into test(id,name,age)
-> values(5101,'chifeng',23);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id,name,age)
-> values(5102,'phpchina',18);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id,name,age)
-> values(5103,'admin',40);
Query OK, 1 row affected (0.00 sec)
#看看結果
mysql> select * from test;
+------+----------+------+
|  id  |    name  |  age |
+------+----------+------+
| 5101 |  chifeng |  23  |
| 5102 | phpchina |  18  |
| 5103 |   admin  |  40  |
+------+----------+------+
3 rows in set (0.02 sec)
#備份到一個文件夾。這裡備份到c盤的根下，也可以建立一個文件夾比如：data。'c:data'
mysql> backup table test to 'c:';
+-----------+--------+----------+----------+
|     Table |   Op   | Msg_type | Msg_text |
+-----------+--------+----------+----------+
| test.test | backup |  status  |    OK    |
+-----------+--------+----------+----------+
1 row in set (0.00 sec)

#可以看到這兩個文件：test.frm格式文件，test.myd數據文件。不能保存索引文件。:(
#刪除test表。
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)
#恢復test
mysql> restore table test from 'c:';
+-----------+---------+----------+----------+
|   Table   |   Op    | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.test | restore |  status  |    OK    |
+-----------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
|      test      |
+----------------+
1 row in set (0.00 sec)

MySQL> select * from test;
+------+----------+------+
|  id  |   name   |  age |
+------+----------+------+
| 5101 |  chifeng |  23  |
| 5102 | PHPchina |  18  |
| 5103 |   admin  |  40  |
+------+----------+------+
3 rows in set (0.00 sec)

mysql> select * from test into outfile 'f:mysql_copy est.dat';
Query OK, 3 rows affected (0.00 sec)
#現在可以delete from test;一下。
#再恢復test。

mysql> load data infile 'f:MySQL_copy est.dat' into table test;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

#mysqldump工具備份
C:mysqlbin>mysqldump test>test.sql
#恢復
C:mysqlbin>MySQL -u root -p test