posts - 104,  comments - 34,  trackbacks - 0
           

          隨著數據的增多,數據的備份顯得日益重要,下面是mysql常用的數據導入導出命令。
          1.導出整個數據庫

          格式:mysqldump -u 用戶名 -p 數據庫名 > 導出的文件名
          舉例:
          C:\Documents and Settings\Owner>mysql -u root -p
          Enter password:
          Welcome to the MySQL monitor.  Commands end with ; or \g.
          Your MySQL connection id is 5 to server version: 5.0.7-beta-nt

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

          mysql> use testdb
          Database changed
          mysql> select * from user;
          +--------+----------+----------+---------------+
          | userid | username | password | email         |
          +--------+----------+----------+---------------+
          |      1 | aaa      | aaa      | aaaa          |
          |      2 | bbb      | bbbb     |
          bbbb@sina.com|
          +--------+----------+----------+---------------+
          2 rows in set (0.00 sec)

          mysql> select * from user1;
          +--------+----------+----------+---------------+
          | userid | username | password | email         |
          +--------+----------+----------+---------------+
          |      1 | cccc     | cccc     | cccc          |
          |      2 | cccc     | cccc     |
          cccc@sina.com|
          +--------+----------+----------+---------------+
          2 rows in set (0.00 sec)

          在mysql的bin目錄里面執行如下命令
          C:\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p testdb > testdb.sql
          Enter password:

          C:\MySQL\MySQL Server 5.0\bin>

          testdb.sql內容如下:

          -- MySQL dump 10.10
          --
          -- Host: localhost    Database: testdb
          -- ------------------------------------------------------
          -- Server version 5.0.7-beta-nt

          /*!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 */;
          /*!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__ON_ZERO' */;
          /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

          --
          -- Table structure for table `user`
          --

          DROP TABLE IF EXISTS `user`;
          CREATE TABLE `user` (
            `userid` int(11) NOT NULL,
            `username` varchar(20) NOT NULL,
            `password` varchar(50) NOT NULL,
            `email` varchar(50) default NULL,
            PRIMARY KEY  (`userid`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          --
          -- Dumping data for table `user`
          --


          /*!40000 ALTER TABLE `user` DISABLE KEYS */;
          LOCK TABLES `user` WRITE;
          INSERT INTO `user` S (1,'aaa','aaa','aaaa'),(2,'bbb','bbbb','bbbb@sina.com');
          UNLOCK TABLES;
          /*!40000 ALTER TABLE `user` ENABLE KEYS */;

          --
          -- Table structure for table `user1`
          --

          DROP TABLE IF EXISTS `user1`;
          CREATE TABLE `user1` (
            `userid` int(11) NOT NULL,
            `username` varchar(20) NOT NULL,
            `password` varchar(50) NOT NULL,
            `email` varchar(50) default NULL,
            PRIMARY KEY  (`userid`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          --
          -- Dumping data for table `user1`
          --


          /*!40000 ALTER TABLE `user1` DISABLE KEYS */;
          LOCK TABLES `user1` WRITE;
          INSERT INTO `user1` S (1,'cccc','cccc','cccc'),(2,'cccc','cccc','cccc@sina.com');
          UNLOCK TABLES;
          /*!40000 ALTER TABLE `user1` ENABLE KEYS */;

          /*!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*/;


          2.導出一個表
          格式:mysqldump -u 用戶名 -p 數據庫名 表名> 導出的文件名
          舉例:表結構與上面的相同,命令如下:
          C:\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p testdb user > user.sql
          Enter password:

          C:\MySQL\MySQL Server 5.0\bin>

          user.sql內容如下

          -- MySQL dump 10.10
          --
          -- Host: localhost    Database: testdb
          -- ------------------------------------------------------
          -- Server version 5.0.7-beta-nt

          /*!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 */;
          /*!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__ON_ZERO' */;
          /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

          --
          -- Table structure for table `user`
          --

          DROP TABLE IF EXISTS `user`;
          CREATE TABLE `user` (
            `userid` int(11) NOT NULL,
            `username` varchar(20) NOT NULL,
            `password` varchar(50) NOT NULL,
            `email` varchar(50) default NULL,
            PRIMARY KEY  (`userid`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          --
          -- Dumping data for table `user`
          --


          /*!40000 ALTER TABLE `user` DISABLE KEYS */;
          LOCK TABLES `user` WRITE;
          INSERT INTO `user` S (1,'aaa','aaa','aaaa'),(2,'bbb','bbbb','bbbb@sina.com');
          UNLOCK TABLES;
          /*!40000 ALTER TABLE `user` ENABLE KEYS */;

          /*!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*/;

          3.導出一個數據庫結構
          格式:mysqldump -u 用戶名 -p -d --add-drop-table 數據庫 > 導出的文件名
          說明:-d 選項表示沒有數據       
                --add-drop-table 選項說明在每個create語句之前增加一個drop table
          舉例:表結構與上面的相同

          C:\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p -d --add-drop-table testdb > testdbstruct.sql
          Enter password:

          C:\MySQL\MySQL Server 5.0\bin>

          testdbstruct.sql內容如下
          -- MySQL dump 10.10
          --
          -- Host: localhost    Database: testdb
          -- ------------------------------------------------------
          -- Server version 5.0.7-beta-nt

          /*!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 */;
          /*!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__ON_ZERO' */;
          /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

          --
          -- Table structure for table `user`
          --

          DROP TABLE IF EXISTS `user`;
          CREATE TABLE `user` (
            `userid` int(11) NOT NULL,
            `username` varchar(20) NOT NULL,
            `password` varchar(50) NOT NULL,
            `email` varchar(50) default NULL,
            PRIMARY KEY  (`userid`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          --
          -- Table structure for table `user1`
          --

          DROP TABLE IF EXISTS `user1`;
          CREATE TABLE `user1` (
            `userid` int(11) NOT NULL,
            `username` varchar(20) NOT NULL,
            `password` varchar(50) NOT NULL,
            `email` varchar(50) default NULL,
            PRIMARY KEY  (`userid`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          /*!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*/;


          4.導入數據庫
          格式:常用source 命令

          進入mysql數據庫控制臺:
          C:\MySQL\MySQL Server 5.0\bin>mysql -u root -p
          Enter password:
          Welcome to the MySQL monitor.  Commands end with ; or \g.
          Your MySQL connection id is 10 to server version: 5.0.7-beta-nt

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

          mysql> use testdb;
          Database changed
          mysql> source testdb.sql;
          Query OK, 0 rows affected (0.05 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.13 sec)

          Query OK, 0 rows affected (0.10 sec)

          Query OK, 0 rows affected (0.01 sec)

          Query OK, 0 rows affected (0.00 sec)

          Query OK, 2 rows affected (0.04 sec)
          Records: 2  Duplicates: 0  Warnings: 0

          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.03 sec)

          Query OK, 0 rows affected (0.09 sec)

          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.01 sec)

          Query OK, 2 rows affected (0.03 sec)
          Records: 2  Duplicates: 0  Warnings: 0

          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.00 sec)

          Query OK, 0 rows affected (0.01 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>

          其中testdb.sql放在C:\MySQL\MySQL Server 5.0\bin目錄下,如果不在這個目錄下則要指定路徑

          posted on 2008-01-23 09:54 末日風情 閱讀(1537) 評論(0)  編輯  收藏 所屬分類: oracle
          <2008年1月>
          303112345
          6789101112
          13141516171819
          20212223242526
          272829303112
          3456789

          常用鏈接

          留言簿(4)

          隨筆分類

          隨筆檔案

          搜索

          •  

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 扶沟县| 磐安县| 松阳县| 瑞金市| 新竹市| 贵南县| 马鞍山市| 浦江县| 中阳县| 西青区| 东方市| 辽宁省| 成安县| 佛教| 广饶县| 武强县| 屏山县| 翁牛特旗| 忻城县| 黄大仙区| 永吉县| 平远县| 手游| 南昌市| 汉川市| 叙永县| 板桥市| 邯郸市| 抚顺市| 杭锦后旗| 上虞市| 扬中市| 武城县| 调兵山市| 海盐县| 海原县| 南乐县| 兴安县| 巴彦淖尔市| 肥城市| 育儿|