mysql8.0 数据导入导出

命令方式

导出

语法：
mysql -u your_username -ppasswrod -h your_host -P  your_port -D your_database > /home/my.sql
说明：
your_username:用户名
passwrod:密码
your_host:主机
your_port:端口
your_database:数据库
my.sql:保存sql文件路径

导入

mysql> create database a;      # 创建数据库
mysql> use a;                          # 使用已创建的数据库 
mysql> source /home/my.sql   # 导入备份数据

文件方式

• csv 方式
  权限设置

'/home/test/team.csv' 文件路径必须有读、写、执行权限
chmod -R 777 /home/test   设置权限

mysql参数设置

my.cnf 文件的配置，必须设置 secure_file_priv 参数
[mysqld]
secure_file_priv=""
设置完成后必须重启mysql

执行导入导出

# 导出csv
SELECT name,url,up_and_down,morning_and_afternoon,question_type,question_name,level,type
INTO OUTFILE '/home/test/team.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM rk_cate;

# 导入csv
LOAD DATA INFILE '/home/test/team.csv'
INTO TABLE rk_cate
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,url,up_and_down,morning_and_afternoon,question_type,question_name,level,type);

• txt 方式

• csv 方式
  权限设置

'/home/test/team.csv' 文件路径必须有读、写、执行权限
chmod -R 777 /home/test   设置权限

mysql参数设置

my.cnf 文件的配置，必须设置 secure_file_priv 参数
[mysqld]
secure_file_priv=""
设置完成后必须重启mysql

执行导入导出

# 导出csv
SELECT name,url,up_and_down,morning_and_afternoon,question_type,question_name,level,type
INTO OUTFILE '/home/test/team.txt'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM rk_cate;

# 导入csv
LOAD DATA INFILE '/home/test/team.txt'
INTO TABLE rk_cate
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,url,up_and_down,morning_and_afternoon,question_type,question_name,level,type);