mysql数据备份－导入与导出数据

数据导入：

把系统文件的内容存储到数据库服务器的表里。

命令格式： LOAD DATA INFILE “目录名/文件名” INTO TABLE 库.表名 FIELDS TERMINATED BY “分隔符” LINES TERMINATED BY “\n”;

把系统文件/etc/passwd的内容存储到userdb库下的user表里。

（/etc/passwd的内容格式：root:x:uid:gid:root:/root:/bin/bash） ＞create database userdb; ＞create table userdb.user( 　name char(25), 　password char(1), 　uid int(2), 　gid int(2), 　comment varchar(50), 　homedir char(30), 　shell char(25), 　index(name) 　);

＞desc userdb.user; ＞select * from userdb.user; > show variables like "secure_file_priv"; >system cp /etc/passwd /var/lib/mysql-files/ > load data infile "/var/lib/mysql-files/passwd" into table 　userdb.user

　fields terminated by ":" 　lines terminated by "\n"; > select * from userdb.user; > alter table userdb.user add 　id int(2) primary key 　auto_increment first; ＞select * from userdb.user where id=25; ＞select * from userdb.user where id<=10;

导入数据时，设置搜索文件路径

#vim /etc/my.cnf [mysqld] secure_file_priv="/mydata" #mkdir /mydata #chown mysql /mydata #setenforce 0 #systemctl restart mysqld

> show variables like "secure_file_priv";

数据导出

把表记录存储到系统文件里。 语法格式 sql查询 into outfile "目录/文件名"； SELECT查询.. .. INTO OUTFILE “目录名/文件名”

FIELDS TERMINATED BY “分隔符” LINES TERMINATED BY “\n”;

>select user,host from mysql.user into outfile "/mydata/a.txt"; >system cat /mydata/a.txt