mySQL之数据备份，迁移等操作

数据库迁移

务必保证在相同版本之间迁移
mysqldump -h 源IP -uroot -p123 –databases db1 | mysql -h 目标IP -uroot -p456

表的导入导出

SELECT… INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE ‘student1.txt’
FIELDS TERMINATED BY ‘,’ //定义字段分隔符
OPTIONALLY ENCLOSED BY ‘”’ //定义字符串使用什么符号括起来
LINES TERMINATED BY ‘\n’ ; //定义换行符

mysql 命令导出文本文件
示例:
mysql -u root -p123 -e ‘select * from student1.school’ > /tmp/student1.txt
mysql -u root -p123 –xml -e ‘select * from student1.school’ > /tmp/student1.xml
mysql -u root -p123 –html -e ‘select * from student1.school’ > /tmp/student1.html

windows平台下要使用-e后面需要双引号

LOAD DATA INFILE 导入文本文件
mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE ‘/tmp/student1.txt’
INTO TABLE school.student1
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”’
LINES TERMINATED BY ‘\n’;

MySQL数据备份

1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。

2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

使用mysqldump实现逻辑备份

语法：
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

示例：
单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

多库备份
mysqldump -uroot -p123 –databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

备份所有库
mysqldump -uroot -p123 –all-databases > all.sql

备份数据库中的表
mysqldump -uroot -p123 db1 user >d:\ db1-user.sql

3. 导出表： 将表导入到文本文件中。

SELECT… INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE ‘student1.txt’
FIELDS TERMINATED BY ‘,’ //定义字段分隔符
OPTIONALLY ENCLOSED BY ‘”’ //定义字符串使用什么符号括起来
LINES TERMINATED BY ‘\n’ ; //定义换行符

mysql 命令导出文本文件
示例:
mysql -u root -p123 -e ‘select * from student1.school’ > /tmp/student1.txt
mysql -u root -p123 –xml -e ‘select * from student1.school’ > /tmp/student1.xml
mysql -u root -p123 –html -e ‘select * from student1.school’ > /tmp/student1.html

windows平台下要使用-e后面需要双引号

LOAD DATA INFILE 导入文本文件
mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE ‘/tmp/student1.txt’
INTO TABLE school.student1
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”’
LINES TERMINATED BY ‘\n’;

如果没有权限，则需要：

然后在ini配置文件中修改mysqld的数据项secure_file_priv=d:\，
重启mysql服务，配置文件生效，现在可以将表中的数据导入指定的d盘。