MySQL數據導入導出（用于個人學習與回顧）

該實例將練習MySQL的導入導出操作

實例：

1. 將/etc/passwd文件導入userdb庫user表并給每條記錄加編號

2. 將userdb庫user表中UID小于100的前10條記錄導出，存為/mydata/user1.txt文件

• 新建userdb庫，切換到userdb庫，并且設置如下字段；

mysql>?create?database?userdb;
Query?OK,?1?row?affected?(0.00?sec)
mysql>?use?userdb;
Database?changed
mysql>?create?table?user(
????->?username?varchar(24)?not?null,
????->?password?varchar(48)?default?'x',
????->?uid?int(5)?not?null,
????->?gid?int(5)?not?null,
????->?fullname?varchar(48),
????->?homedir?varchar(64)?not?null,
????->?shell?varchar(24)?not?null
????->?);
Query?OK,?0?rows?affected?(0.01?sec)
mysql>?desc?user;
+----------+-------------+------+-----+---------+-------+
|?Field????|?Type????????|?Null?|?Key?|?Default?|?Extra?|
+----------+-------------+------+-----+---------+-------+
|?username?|?varchar(24)?|?NO???|?????|?NULL????|???????|
|?password?|?varchar(48)?|?YES??|?????|?x???????|???????|
|?uid??????|?int(5)??????|?NO???|?????|?NULL????|???????|
|?gid??????|?int(5)??????|?NO???|?????|?NULL????|???????|
|?fullname?|?varchar(48)?|?YES??|?????|?NULL????|???????|
|?homedir??|?varchar(64)?|?NO???|?????|?NULL????|???????|
|?shell????|?varchar(24)?|?NO???|?????|?NULL????|???????|
+----------+-------------+------+-----+---------+-------+
7?rows?in?set?(0.01?sec)

• 注：在MySQL 5.7.6版本之后，導入文件只能在secure_file_priv指定的文件夾下。如果直接導入會報錯。執行show variables like '%secure%'命令顯示文件目錄：

mysql>?load?data?infile?'/etc/passwd'?into?table?user?fields?terminated?by?':';
ERROR?1290?(HY000):?The?MySQL?server?is?running?with?the?--secure-file-priv?option?so?it?cannot?execute?this?statement
mysql>?show?variables?like?'%secure%';
+--------------------------+-----------------------+
|?Variable_name????????????|?Value?????????????????|
+--------------------------+-----------------------+
|?require_secure_transport?|?OFF???????????????????|
|?secure_auth??????????????|?ON????????????????????|
|?secure_file_priv?????????|?/var/lib/mysql-files/?|
+--------------------------+-----------------------+
3?rows?in?set?(0.00?sec)

• ????執行導入操作

-將/etc/passwd文件復制到/var/lib/mysql-files/目錄下，

-讀取/var/lib/mysql-files/passwd文件內容，以“:”為分隔，導入到user表中：

[root@host50?~]#cp?/etc/passwd?/var/lib/mysql-files/
mysql>?LOAD?DATA?INFILE?'/var/lib/mysql-files/passwd'
->?INTO?TABLE?user
->?FIELDS?TERMINATED?BY?':';
Query?OK,?42?rows?affected?(0.11?sec)
Records:?42??Deleted:?0??Skipped:?0??Warnings:?0

注：上述操作中省略了行分隔 LINES TERMINATED BY '\n'，因為這是默認的情況（每行一條原始記錄），除非需要以其他字符分割行，才需要用到這個。

• 確認導入結果

mysql>?select?count(*)?from?user;
+----------+
|?count(*)?|
+----------+
|???????42?|
+----------+
1?row?in?set?(0.00?sec)

mysql>?select?*?from?user?limit?10;
+----------+----------+-----+-----+----------+-----------------+----------------+
|?username?|?password?|?uid?|?gid?|?fullname?|?homedir?????????|?shell??????????|
+----------+----------+-----+-----+----------+-----------------+----------------+
|?root?????|?x????????|???0?|???0?|?root?????|?/root???????????|?/bin/bash??????|
|?bin??????|?x????????|???1?|???1?|?bin??????|?/bin????????????|?/sbin/nologin??|
|?daemon???|?x????????|???2?|???2?|?daemon???|?/sbin???????????|?/sbin/nologin??|
|?adm??????|?x????????|???3?|???4?|?adm??????|?/var/adm????????|?/sbin/nologin??|
|?lp???????|?x????????|???4?|???7?|?lp???????|?/var/spool/lpd??|?/sbin/nologin??|
|?sync?????|?x????????|???5?|???0?|?sync?????|?/sbin???????????|?/bin/sync??????|
|?shutdown?|?x????????|???6?|???0?|?shutdown?|?/sbin???????????|?/sbin/shutdown?|
|?halt?????|?x????????|???7?|???0?|?halt?????|?/sbin???????????|?/sbin/halt?????|
|?mail?????|?x????????|???8?|??12?|?mail?????|?/var/spool/mail?|?/sbin/nologin??|
|?operator?|?x????????|??11?|???0?|?operator?|?/root???????????|?/sbin/nologin??|
+----------+----------+-----+-----+----------+-----------------+----------------+
10?rows?in?set?(0.00?sec)

• 為user表中的每條記錄添加自動編號，并驗證自動編寫結果

mysql>?alter?table?user?add?sn?int(4)?auto_increment?primary?key?first;
Query?OK,?0?rows?affected?(0.03?sec)
Records:?0??Duplicates:?0??Warnings:?0

mysql>?select?*?from?user?limit?10;
+----+----------+----------+-----+-----+----------+-----------------+----------------+
|?sn?|?username?|?password?|?uid?|?gid?|?fullname?|?homedir?????????|?shell??????????|
+----+----------+----------+-----+-----+----------+-----------------+----------------+
|??1?|?root?????|?x????????|???0?|???0?|?root?????|?/root???????????|?/bin/bash??????|
|??2?|?bin??????|?x????????|???1?|???1?|?bin??????|?/bin????????????|?/sbin/nologin??|
|??3?|?daemon???|?x????????|???2?|???2?|?daemon???|?/sbin???????????|?/sbin/nologin??|
|??4?|?adm??????|?x????????|???3?|???4?|?adm??????|?/var/adm????????|?/sbin/nologin??|
|??5?|?lp???????|?x????????|???4?|???7?|?lp???????|?/var/spool/lpd??|?/sbin/nologin??|
|??6?|?sync?????|?x????????|???5?|???0?|?sync?????|?/sbin???????????|?/bin/sync??????|
|??7?|?shutdown?|?x????????|???6?|???0?|?shutdown?|?/sbin???????????|?/sbin/shutdown?|
|??8?|?halt?????|?x????????|???7?|???0?|?halt?????|?/sbin???????????|?/sbin/halt?????|
|??9?|?mail?????|?x????????|???8?|??12?|?mail?????|?/var/spool/mail?|?/sbin/nologin??|
|?10?|?operator?|?x????????|??11?|???0?|?operator?|?/root???????????|?/sbin/nologin??|
+----+----------+----------+-----+-----+----------+-----------------+----------------+
10?rows?in?set?(0.00?sec)

• 從MySQL數據庫中導出查詢結果

? ? ? ? ?-以將userdb庫user表中UID小于100的前10條記錄導出為/myload/user2.txt文件為例

? ? ? ?首先，修改配置文件中存放導出導入目錄及查看修改結果

[root@host50?~]#?mkdir??/myload??;??chown??mysql??/myload
[root@host50?~]#?vim??/etc/my.cnf
[mysqld]
secure_file_priv="/myload"
[root@dbsvr1?~]#?systemctl??restart?mysqld
mysql>?show?variables?like?"secure_file_priv";
+------------------+----------+
|?Variable_name????|?Value???????|
+------------------+----------+
|?secure_file_priv???|?/myload/?|

? ? ? 導出user表中UID小于100的前十條記錄

mysql>?select?*?from?userdb.user?where?uid<100
????->?into?outfile?'/myload/user.txt'
????->?fields?terminated?by?":";
Query?OK,?26?rows?affected?(0.00?sec)

? ? ? 確認導出結果

[root@host50?~]#?wc?-l?/myload/user.txt
26?/myload/user.txt
[root@host50?~]#?tail?/myload/user.txt?
25:tss:x:59:59:Account?used?by?the?trousers?package?to?sandbox?the?tcsd?daemon:/dev/null:/sbin/nologin
29:rpcuser:x:29:29:RPC?Service?User:/var/lib/nfs:/sbin/nologin
33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin
35:sshd:x:74:74:Privilege-separated?SSH:/var/empty/sshd:/sbin/nologin
36:avahi:x:70:70:Avahi?mDNS/DNS-SD?Stack:/var/run/avahi-daemon:/sbin/nologin
37:postfix:x:89:89::/var/spool/postfix:/sbin/nologin
38:ntp:x:38:38::/etc/ntp:/sbin/nologin
39:tcpdump:x:72:72::/:/sbin/nologin
41:apache:x:48:48:Apache:/usr/share/httpd:/sbin/nologin
42:mysql:x:27:27:MySQL?Server:/var/lib/mysql:/bin/false