搞定linux上MySQL編程（五）：資料匯入匯出和備份

標籤：mysql

       【著作權聲明：尊重原創，轉載請保留出處：blog.csdn.net/shallnet，文章僅供學習交流，請勿用於商業用途】         在MySQL中提供多種資料匯入方法，比如mysqlinport、sql語句匯入以及編寫專門匯入程式等。通常情況下，資料匯入基本步驟科分成3步:1. 確定匯入的資料來源，按固定格式儲存的文字檔或者SQL檔案。
2. 依照匯入的檔案格式，確定目標資料表，這個資料表如果沒有，可以依照匯入的文字檔格式，建立一個相對應的資料表。
3. 執行匯入命令，將資料匯入資料表中。

        下面分別介紹MySQL提供的各種匯入資料方法，此處設計一張表，一個一個要匯入的資料，已文本格式儲存。
1.資料來源
建立一個如下文字檔，各欄位已tab鍵隔開：

# cat myuser.txt zhao    25      8       2015-1-1qian    22      4       2014-5-6sun     31      1       2013-12-7li      40      6       2014-12-12zhou    45      3       2015-2-8wu      18      1       2014-9-12zheng   44      9       2012-10-12wang    29      12      2015-3-6

2. 然後建立一張目標資料庫表，表的資料結構要和文字檔一一對應，如下：
[[email protected] db_bak]# mysql -u root -p 
Enter password: 

mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table user_import ( name varchar(32) not null primary key, age int, level int, login_date date );Query OK, 0 rows affected (0.02 sec)mysql> desc user_import;+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| name       | varchar(32) | NO   | PRI | NULL    |       || age        | int(11)     | YES  |     | NULL    |       || level      | int(11)     | YES  |     | NULL    |       || login_date | date        | YES  |     | NULL    |       |+------------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> 

3.匯入資料，mysqlimport是MySQL提供的匯入工具，該工具可以把文字檔匯入到使用者指定的資料表中。
mysqlimport 使用格式為：mysqlimport [-d/f...] tablename data.txt
其中[-d/f...] 是選擇性參數。tablename用於表示資料庫名稱。data.txt用於表示記錄的文字檔，通常要匯入的資料表名預設是與文字檔同名的。例如下面運行mysqlimport命令將文本資料匯入到MySQL中：

# cp /home/allen/user_import.txt /var/lib/mysql/db_users/# mysqlimport -uroot -pxxx  db_users user_import.txtdb_users.user_import: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0# mysqlimport -uroot -p  db_users user_import.txt        Enter password: mysqlimport: Error: 1062, Duplicate entry 'zhao' for key 'PRIMARY', when using table: user_import# mysqlimport -d -uroot -p  db_users user_import.txtEnter password: db_users.user_import: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

然後查看資料匯入後的情況如下：

# mysql -u root -pEnter password:......mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from user_import;+-------+------+-------+------------+| name  | age  | level | login_date |+-------+------+-------+------------+| zhao  |   25 |     8 | 2015-01-01 || qian  |   22 |     4 | 2014-05-06 || sun   |   31 |     1 | 2013-12-07 || li    |   40 |     6 | 2014-12-12 || zhou  |   45 |     3 | 2015-02-08 || wu    |   18 |     1 | 2014-09-12 || zheng |   44 |     9 | 2012-10-12 || wang  |   29 |    12 | 2015-03-06 |+-------+------+-------+------------+8 rows in set (0.00 sec)

有時候資料來源的間隔符可能不是預設tab鍵，有可能是逗號，這時可以加入參數--field-terminatied-by=str， 匯入的命令為：

# mysqlimport -uroot -pxxx --fields-terminated-by=, db_users user_import2.txtdb_users.user_import2: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

資料匯出是將資料庫中已存的資料匯出到固定文本記錄，mysqldump是MySQL中專門僅資料匯出服務的工具，它可以將一個資料庫、表，設定預存程序一SQL語句的形式匯出，另外在資料備份中也會使用該工具。其使用格式為:mysqldump [-r/...] databsesname < data.sql[-r/...]為選擇性參數；
databsesname為資料庫名稱；
data.sql表示亞匯出的SQL的指令碼。

# mysqldump -u root -p db_users  > db_userr.sql        Enter password: # ls -al db_userr.sql-rw-r--r--. 1 root root 6366 Jun 10 22:52 db_userr.sql# vim db_userr.sql-- MySQL dump 10.13  Distrib 5.1.66, for redhat-linux-gnu (i386)---- Host: localhost    Database: db_users-- -------------------------------------------------------- Server version       5.1.66/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;/*!40101 SET @[email protected]@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @[email protected]@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `password`--DROP TABLE IF EXISTS `password`;/*!40101 SET @saved_cs_client     = @@character_set_client */;"db_userr.sql" 163L, 6366C                                                1,1           Top-- MySQL dump 10.13  Distrib 5.1.66, for redhat-linux-gnu (i386)---- Host: localhost    Database: db_users-- -------------------------------------------------------- Server version       5.1.66......

資料匯出成功。資料庫匯出檔案可以通過如下幾種方式來恢複到資料庫中：1 利用mysql命令執行資料的恢複操作：

# mysql -u root db_users2 < db_userr.sql -p        Enter password: [[email protected] allen]# mysql -u root -pEnter password: mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db_users           || db_users2          || mysql              || test               |+--------------------+5 rows in set (0.00 sec)mysql> 

2. sql語句source 來匯入。

mysql> create database db_users3;Query OK, 1 row affected (0.00 sec)mysql> use db_users3;Database changedmysql> source db_userr.sqlQuery 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)

對於單個表示的資料備份可以採用select into ... outfile ... 進行資料匯出，利用load data ... 方式進行資料匯入。資料表備份執行如下：

mysql> select  * into outfile 'tbbk_users' from tb_users;        Query OK, 13 rows affected (0.00 sec)

資料表執行如下：

mysql> load data infile 'tbbk_users' into table tb_users;                                  Query OK, 13 rows affected, 13 warnings (0.00 sec)Records: 13  Deleted: 0  Skipped: 0  Warnings: 13

搞定linux上MySQL編程（五）：資料匯入匯出和備份