mysql命令（数据库备份与恢复)实践

动手实践：

A、备份mysql数据库

使用管理员打开命令行窗口：

1.进入mysql安装的路径

例：

C:\WINDOWS\system32>cd "c:\Program Files (x86)\MySQL\MySQL Server 5.1"

c:\Program Files (x86)\MySQL\MySQL Server 5.1>cd bin

 

2.使用mysqldump命令，备份数据库为一个sql文件

例：

c:\Program Files (x86)\MySQL\MySQL Server 5.1\bin>mysqldump -uroot -p test>test1.sql
Enter password: ****

 

3.查看得到的备份文件：在当前目录c:\Program Files (x86)\MySQL\MySQL Server 5.1\bin下，

c:\Program Files (x86)\MySQL\MySQL Server 5.1\bin>dir
驱动器 C 中的卷没有标签。
卷的序列号是 64E8-2E17

c:\Program Files (x86)\MySQL\MySQL Server 5.1\bin 的目录

2015/08/12 08:31 <DIR> .
2015/08/12 08:31 <DIR> ..
2010/11/04 17:31 2,359,296 libmySQL.dll
2010/11/04 17:31 2,084,864 myisamchk.exe
2010/11/04 17:31 1,974,272 myisamlog.exe
2010/11/04 17:31 2,007,040 myisampack.exe
2010/11/04 17:31 1,961,984 myisam_ftdump.exe
2010/11/04 17:31 2,342,912 mysql.exe
2010/11/04 17:31 5,681,152 mysql.pdb
2010/11/04 17:31 2,277,376 mysqladmin.exe
2010/11/04 17:31 5,476,352 mysqladmin.pdb
2010/11/04 17:31 2,367,488 mysqlbinlog.exe
2010/11/04 17:31 5,943,296 mysqlbinlog.pdb
2010/11/04 17:31 2,273,280 mysqlcheck.exe
2010/11/04 17:31 6,098,944 mysqld.exe
2010/11/04 17:31 5,759,185 mysqld.map
2010/11/04 17:31 21,524,480 mysqld.pdb
2010/11/04 17:31 2,330,624 mysqldump.exe
2010/11/04 17:31 5,623,808 mysqldump.pdb
2010/11/04 17:31 2,269,184 mysqlimport.exe
2010/11/04 17:31 5,451,776 mysqlimport.pdb
2010/09/13 16:42 2,973,600 MySQLInstanceConfig.exe
2010/11/04 17:31 2,269,184 mysqlshow.exe
2010/11/04 17:31 5,443,584 mysqlshow.pdb
2010/11/04 17:31 1,798,144 mysql_upgrade.exe
2010/11/04 17:31 1,724,416 my_print_defaults.exe
2010/11/04 17:31 1,712,128 perror.exe
2010/11/04 17:31 1,703,936 resolveip.exe
2015/08/12 08:31 1,942 test01.sql
27 个文件 103,434,247 字节
2 个目录 71,895,162,880 可用字节

-------------------------------------------------------------

B、还原mysql数据库

方法一：进入MySQL Command Line Client，输入密码，进入到“mysql>”，输入命令"show databases；"，回车，看看有些什么数据库；

建立你要还原的数据库，输入"create database voice；"，回车；

切换到刚建立的数据库，输入"use voice；"，回车；

导入数据，输入"source voice.sql；"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。

方法二：在c:\Program Files (x86)\MySQL\MySQL Server 5.1\bin>命令提示符下,输入mysql,进入到数据库模式下面

c:\Program Files (x86)\MySQL\MySQL Server 5.1\bin>mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.53-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>create database voicedb;

Query OK, 1 row affected (0.00 sec)

mysql> use voicedb;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> source test01.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql>  

参考文章：

http://blog.knowsky.com/261411.htm