mysqldump 和还原

<pre name="code" class="sql">mysqldump 最常用于备份一个整个的数据库：shell> mysqldump db_name >backup-file.sql你可以这样将转储文件读回到服务器：shell> mysql db_name < backup-file.sql相应的，在目标服务器上导入数据的命令就是：shell> mysqladmin create target_db_name shell> mysql target_db_name < backup-file.sql 下面的例子是把replication_test库dump到D:/replication_test.sql文件，在生成的SQL中加入DROP TABLE IF EXISTS。mysqldump -u root -proot --add-drop-database --add-drop-table replication_test > D:/replication_test.sql以下是几个常用的简单地使用方法是：1.导出整个数据库   mysqldump -u 用户名 -p 数据库名 > 导出的文件名  2.导出一个表   mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名3.导出一个数据库结构   mysqldump --u 用户名 -p 数据库名 --no-data --add-drop-database --add-drop-table  smgp_apps_wcnc >d:/wcnc_db.sql   --no-data: 不dump数据库，--add-drop-database  创建数据库之前加一个drop database --add-drop-table 在每个create语句之前增加一个drop table mysql 备份和还原数据库:[mysql@master ~]$ mysqldump -utest -p123456 plmsdb >plmsdbWarning: Using a password on the command line interface can be insecure.mysql> SELECT table_name,table_schema  FROM information_schema.TABLES  where table_schema = 'plmsdb';+------------+--------------+| table_name | table_schema |+------------+--------------+| area       | plmsdb       || person     | plmsdb       || test       | plmsdb       |+------------+--------------+3 rows in set (0.00 sec)mysql> select * from area;Empty set (0.00 sec)mysql> select * from person;+--------+------+------------+| number | name | birthday   |+--------+------+------------+|      1 | aaa  | 2014-01-01 ||      2 | b    | 2015-05-01 |+--------+------+------------+2 rows in set (0.00 sec)mysql> select * from test;+------+| id   |+------+|    1 ||  100 |+------+2 rows in set (0.00 sec变更数据:mysql> select * from area;+------+| id   |+------+|    1 |+------+1 row in set (0.00 sec)mysql> select * from person;+--------+------+------------+| number | name | birthday   |+--------+------+------------+|      1 | aaa  | 2014-01-01 |+--------+------+------------+1 row in set (0.00 sec)还原数据库:[mysql@master ~]$ mysql -utest -p123456 -D plmsdb<plmsdb.sqlmysql> select * from area;Empty set (0.00 sec)mysql> select * from person;+--------+------+------------+| number | name | birthday   |+--------+------+------------+|      1 | aaa  | 2014-01-01 ||      2 | b    | 2015-05-01 |+--------+------+------------+2 rows in set (0.00 sec)mysql 备份表:[mysql@master ~]$ mysqldump -u test -p123456 test t1 >t1.sqlWarning: Using a password on the command line interface can be insecure.[mysql@master ~]$ cat t1.sql -- MySQL dump 10.13  Distrib 5.6.16, for Linux (x86_64)---- Host: localhost    Database: test-- -------------------------------------------------------- Server version5.6.16-log/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `t1`--DROP TABLE IF EXISTS `t1`;/*!40101 SET @saved_cs_client     = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t1` (  `id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Dumping data for table `t1`--LOCK TABLES `t1` WRITE;/*!40000 ALTER TABLE `t1` DISABLE KEYS */;INSERT INTO `t1` VALUES (1),(2),(3);/*!40000 ALTER TABLE `t1` ENABLE KEYS */;UNLOCK TABLES;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2015-03-26  7导回数据: mysql -utest -p123456 -Dtest <t1.sql