Shulou(Shulou.com)06/01 Report--

This example will practice the import and export operation of MySQL

Example:

Import the / etc/passwd file into the userdb library user table and number each record

Export the first 10 records with UID less than 100in the user table of userdb library and save them as / mydata/user1.txt file

Create a new userdb library, switch to the userdb library, and set the following fields

Mysql > create database userdb;Query OK, 1 row affected (0.00 sec) mysql > use userdb;Database changedmysql > 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 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.01sec) |

Note: after MySQL version 5.7.6, the import file can only be under the folder specified by secure_file_priv. An error will be reported if it is imported directly. Execute the show variables like'% secure%' command to display the file directory:

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 statementmysql > 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)

Perform import operation

-copy the / etc/passwd file to the / var/lib/mysql-files/ directory

-read the contents of the / var/lib/mysql-files/passwd file, separated by ":", and import them into the user table:

[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

Note: line separation LINES TERMINATED BY'\ nrecords is omitted in the above operation, because this is the default (one original record per line) and is not needed unless you need to split lines with other characters.

Confirm the import result

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 | root | / root | / bin/bash | | bin | x | 1 | 1 | bin | / bin | | / sbin/nologin | | daemon | x | 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)

Add automatic numbering for each record in the user table and verify the result of automatic writing

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: 0mysql > 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 | 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)

Export query results from MySQL database

-take exporting the first 10 records in the user table of userdb library with UID less than 100 as / myload/user2.txt file as an example

First of all, modify the configuration file to store the export import directory and view the modification results

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

Export the first ten records with UID less than 100 in the user table

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

Confirm the export result

[root@host50 ~] # wc-l / myload/user.txt26 / 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/nologin29:rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin33:gdm:x:42:42::/var/lib/gdm:/sbin/nologin35:sshd:x:74:74 : Privilege-separated SSH:/var/empty/sshd:/sbin/nologin36:avahi:x:70:70:Avahi mDNS/DNS-SD Stack:/var/run/avahi-daemon:/sbin/nologin37:postfix:x:89:89::/var/spool/postfix:/sbin/nologin38:ntp:x:38:38::/etc/ntp:/sbin/nologin39:tcpdump:x:72:72::/:/sbin/nologin41:apache:x:48:48:Apache:/usr/share/httpd : / sbin/nologin42:mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.