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

Data import and export

Set the directory used for data import / export

1. Check to see if the default usage directory exists.

Mysql > show variables like "secure_file_priv"

Secure_file_priv / var/lib/mysql-files/

two。 Modify the catalog and view the modification results.

# mkdir / abc

# chown mysql / abc

# vim / etc/my.cnf

[mysqld]

....

Secure_file_priv= "/ abc"

# systemctl restart mysqld

Mysql > show variables like "secure_file_priv"

Secure_file_priv / abc

SQL data Import

Basic usage

Format: load data infile directory name / file name used for import / export into table library. Table name fields terminated by "Field interval symbol" lines terminated by "\ n"

Note:

1. The characters should be consistent with the contents of the file by the delimiter.

two。 Specifies the absolute path to the import file.

3. The table field type of the imported data should match the file field.

4. Disable SElinux

Example: 1. Copy the contents of the file to the directory used by the import / export. # cp / etc/passwd / var/lib/mysql-files/2. Create a table field type to match the file field. 3. / etc/passwd field is divided into user name: password placeholder: uid: gid: description information: home directory: shellmysql > create database passwddb;mysql > create table passwddb.test (> user char (20), > password char (1), > uid int (2), > gid int (2), > comment varchat (50), > homedir char (30), > shell char (30), > index (user) >); mysql > desc passwddb.test;3. Import data into mysql > oad data infile "/ var/lib/mysql-files/passwd" into table passwddb.test fields terminated by ":" lines terminated by "\ n"; 4. You can add a line number to the table field. Mysql > alter table passwddb.test add id int (2) zerofill primary key auto_increment first; / / (zerofil with 0 complement) 5. Check mysql > select id,name,uid from passwddb.test

SQL data export

Basic usage

Format: result of select query into outfile "directory name used for import / export / take a file name" fields terminated by "delimiter" lines terminated by "\ n"

Note:

1. The exported content is determined by the sql query statement.

two。 Disable SElinux.

Example:

Export user records with uid less than 100 in the test table of the passwddb library.

Mysql > select * from passwddb.test where uid < 100 into outfile "/ var/lib/mysql-files/abc.txt"

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.