4.6 MySQL数据库导入与导出攻略
4.6.1 Linux下MySQL数据库导入与导出
1. MySQL数据库的导出命令参数
主要是通过两个mysql和mysqldump命令来执行
(1) MySQL连接参数
-u $USER: 用户名
-p $PASSWD: 密码
-h 127.0.0.1 主机IP
-P 3306 端口
--default-character-set=utf-8 指定字符集
--skip-column-names 不显示数据列的名字
-B 以批处理的方式运行MySQL程序，查询结果将显示为制表符间隔格式
-e 执行命令后，退出
(2) mysqldump参数
-A 全库备份
--routines 备份存储过程和函数
--default-character-set=utf-8 指定字符集
--lock-all-tables 全局以执行锁
--add-drop-database 在每次执行建表语句前，先执行DROP TABLE IF EXIST语句
--no-create-db 不输出CREATE DATABASE 语句
--no-create-info 不输出CREATE TABLE 语句
--databases 将后面的参数都解析为库名
--tables 第一个参数为库名，后续为表名
2. MySQL数据库的常见导出命令
(1) 导出全库备份到本地目录
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf-8 --lock-all-tables --add-drop-database -A > bmfxdb.all.sql
(2) 导出指定库到本地的目录
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf-8 --databases bmfxtest > bmfxdb.sql
(3) 导出某个库的表到本地目录
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf-8 --tables bmfxtest user > bmfxuser.db.sql
(4) 导出指定库的表(仅数据) 到本地的目录
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf-8 --no-create-db --no-create-info --tables mysql user --where="host='localhost'" > bmfxdb.table.sql
(5) 导出某个库的所有表结构
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf-8 --no-data --databases mysql > bmfxdb.nodata.sql
(6) 导出某个查询SQL的数据为.txt格式文件到本地的目录
'select host,user,password from mysql.user;'
mysqldump -u$USER -p$PASSWD -h127.0.0.1 -P3306 --routines --default-character-set=utf-8 --skip-column-names -B -e 'select host,user,password from mysql.user;' > mysql_user.txt
(7) 导出某个查询SQL的数据为.csv格式文件到MySQL服务器
MySQL需要有写的权限，用tmp目录最好
select host,user,password from mysql.user into outfile '/tmp/mysql_user.csv' FIELDS TERMINATED BY ', ';
3. 加快MySQL数据库导出速度的技巧
--max_allowed_packed=xxx 客户端/服务端之间通信缓存区的最大值
--net-buffer_length=xxx TCP/IP和套接字层通信缓冲区大小，创建长度达net_buffer_length的行
上面的参数设置值的时候，不能比目标数据库设置的数值大
show variables like 'max_allowed_packed';
show variables like 'net-buffer_length';
mysqldump -uroot -pbmfx bmfxtest -e --max_allowed_packet=8388608 --net_buffer_length=8192 > bmfx.sql
当然最快的方法是直接复制数据库目录，前提是停掉MySQL数据库服务
4. Linux下MySQL数据库导入常见命令
导入完成的时候需要执行 flush privileges
(1) mysql -u$USER -p#PASSWD -h127.0.0.1 -P3306 --default-character-set=utf-8 < bmfxdb.all.sql
(2) 使用source命令导入
前提是登录到mysql数据库里面，然后执行source命令，需要导入的文件名要么在当前登录数据库的路径，要么是绝对路径
mysql> source /tmp/bmfxdb.all.sql
(3) 使用mysql命令恢复某个库的数据
mysql -u$USER -p#PASSWD -h127.0.0.1 -P3306 --default-character-set=utf-8 bmfxtest < bmfx.user.sql
(4) 使用source恢复某个库的数据
mysql -u$USER -p#PASSWD -h127.0.0.1 -P3306 --default-character-set=utf-8
mysql> use bmfxtest;
mysql> source /tmp/bmfx.db.sql
(5) 恢复MySQL服务器上面的.txt格式文件
mysql -u$USER -p#PASSWD -h127.0.0.1 -P3306 --default-character-set=utf-8
mysql> use mysql;
mysql> LOAD DATA INFILE '/tmp/mysql_user.txt' INTO TABLE user;
(6) 恢复MySQL服务器上面的.csv格式文件，需要FILE权限 ，各个数据直接用逗号分隔
mysql -u$USER -p#PASSWD -h127.0.0.1 -P3306 --default-character-set=utf-8
mysql> use mysql;
mysql> LOAD DATA INFILE '/tmp/mysql_user.csv' INTO TABLE user FIELDS TERMINATED BY ', ';
(7) 恢复本地的.txt或.csv文件到MySQL
mysql -u$USER -p#PASSWD -h127.0.0.1 -P3306 --default-character-set=utf-8
mysql> use mysql;
mysql> LOAD DATA INFILE '/tmp/mysql_user.txt' INTO TABLE user; //.txt情况
mysql -u$USER -p#PASSWD -h127.0.0.1 -P3306 --default-character-set=utf-8
mysql> use mysql;
mysql> LOAD DATA INFILE '/tmp/mysql_user.csv' INTO TABLE user FIELDS TERMINATED BY ', '; //.csv情况
4.6.2 Windows下MySQL数据库导入与导出
1. mysqldump命令导入和导出
导出数据库
mysqldump -u root -p root bmfxdb > D:ackup20200701.sql
导入数据库
mysqldump -u root -p root bmfxdb < D:ackup20200701.sql
2. mysql命令导入导出
(1) 将数据库bmfxdb导出到D盘根目录bmfx.sql
mysql -uroot -proot -hlocalhost bmfxdb > D:mfx.sql
(2) 将数据库D盘文件下的bmfx.sql文件导入到数据库bmfxtest中
mysql -uroot -proot -hlocalhost bmfxtest < D:mfx.sql
可以在登录的情况下使用source命令
source D:mfx.sql
4.6.3 HTML文件导入MySQL数据库
1. 选择导入类型
通过研究发下Navicat可以导入多种类型，可以使用此工具选择导入HTML文件的格式进行导入
2. 查看文件的编码格式
在导入前一定要知道文件是以何种格式进行编码，查看编码的方式可以使用各种出名的编辑器工具进行查看，作者这里使用的Notepad++ , 大家可以根据自己喜欢的编辑器工具使用，这里大家一定要注意，否则导入会显示乱码
3. 选择编码方式
使用工具的时候选择对应的编码，一般推荐大家都是有UTF-8编码
4. 设置栏名称和起始数据行
5. 设置目标表名称
6. 设置目标表的列名
7. 查看导入日志
8. 查看导入数据
4.6.4 MSSQL数据库导入MySQL数据库
MSSQL数据库导入与HTML导入MySQL的操作基本相同，导入类型-ODBC 在数据链接属性的窗口中选择 Microsoft OLE DB Provider for SQL Server
4.6.5 XLS或者XLSX文件导入MySQL数据库
还是使用工具Navicat，方式跟上面一样，注意选中的Sheet有几个即可
4.6.6 Navicat for MySQL导入XML数据
有的数据存在的文件后缀是.txt但是里面的内容结构是XML语法格式，那么这个时候是可以同将txt后缀改成XML格式然后使用Navicat工具进行导入，还是得注意在导入的时候记得选中UTF-8编码
1. 选择编码方式
2. 选择表字段
3. 设置数据行
4. 设置目标表名称
5. 设置导入的栏位名称
6. 选择导入模式
7. 导入数据库
8. 后续处理
导入成功之后会有一些无用的垃圾数据，可以清楚掉
delete from log where appid isnull
4.6.7 Navicat 代理导入数据
首先要在Navicat的安装目录下找到文件ntunnel_mysql.php
(1) 在常规中设置，新建-连接，设置一个连接的名称，主机名设置为localhost，然后设置好账号和密码
(2) 使用HTTP通道，选择HTTP选项卡，使用HTTP通道，然后输入http://www.xx.com/ntunnel_mysql.php ，前提是这个文件已经上传到目标站点了
(3) 上述操作完成了，就可以开始连接了
4.6.8 导入技巧和出错处理
1. 进行转码处理
使用Notepad++或者其他编辑器工具进行转码，一般将其转换为UTF-8编码格式
2. 选择出错继续
需要勾选这个选项卡
3.错误信息再处理
有的时候数据格式不全，或者编码中有多余的特殊字符，将会导致数据导入失败，没有成功导入的数据会在日志上显示，可以将日志中出错的信息复制到记事本中进行查看，修改错误的地方之后，再查询在查询器中进行查询导入
实际操作的SQL语句
mysqldump -uroot -proot -hlocalhost -P3306 --routines --default-character-set=utf-8 --lock-all-tables --add-drop-database -A > bmfxdb.all.sql
mysqldump -uroot -proot -hlocalhost -P3306 --routines --databases mysql > mysql.sql
mysqldump -uroot -proot -hlocalhost -P3306 --routines --tables mysql user > bmfxuser.db.sql
mysqldump -uroot -proot -hlocalhost -P3306 --routines --no-create-db --no-create-info --tables mysql user --where="host='localhost'" > bmfxdb.table.sql
mysqldump -uroot -proot -hlocalhost -P3306 --routines --no-data --databases mysql > bmfxdb.nodata.sql
mysqldump -uroot -proot -hlocalhost -P3306 --routines --default-character-set=utf-8 --skip-column-names -B -e 'select host,user,password from mysql.user;' > mysql_user.txt
mysql -uroot -proot -hlocalhost -P3306 --default-character-set=utf-8 < bmfxdb.all.sql
