命令方式
导出
语法:
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);