河豚随心记（三）腾讯云mysql数据库迁移至CVM自建数据库

买了个腾讯云的云MYSQL数据库，业务不断发展，数据库上塞的项目越来越多了，性能逐渐顶不住，但是提配又太贵，刚好有台Windows系统的CVM云服务器，就想着在上面装个MYSQL数据库，把云数据库里面没那么重要的项目库迁过来。

看起来没什么难度，实际做起来还是有不少细节没有考虑到，折腾了三天，在这分享下整个过程。

几个特殊点：

1.有个项目数据库里有存放图片base64格式数据，数据长度很长，无法直接通过navicat进行传输；

2.云数据库仅内网可访问，且由于线上正在使用，无法关停操作；

首先想到几个方案：

1.在云服务器上，装个navicat，分别连接云数据库和本地数据库，直接数据传输，但是失败了，有个库里面数据长度比较大。

2.在云数据库上，把data文件直接搞下来，拷贝到服务器的数据库data目录中，但是由于云数据库只能内网访问，且无法关停，实际上只能在腾讯云控制台下载数据库备份文件（.xb文件）

3.通过mysql命令行操作转移

第一个方案折腾了下直接pass掉，数据丢失严重；第二个方案，.xb文件的提取仅支持在linux系统的服务器下通过xb工具操作，我的服务器是windows，也pass了；

就剩下第三个方案了，也就是mysql命令行操作转移，参考腾讯云的操作文档：云数据库 MySQL 离线迁移数据-操作指南-文档中心-腾讯云

简单的导出导入操作，实际上操作却不顺利，导出的话参考MySQL :: MySQL 5.6 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

mysqldump -h 原主机地址 -P 端口 -u 用户名 -p 密码 --databases 数据库名 > C:/data.sql

一步到位了，sql文件导出成功，但是在目标数据库导入就不行了， 

第一个报错：@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_MODE = ON

这个原因是原数据库开了gtid，需要在导出语句上加：--set-gtid-purged=OFF（看到网上一大堆文章写的是ON，基本都是互抄也不知道怎么想的），导出时把导出脚本里的gtid关闭，接着在导入语句上加个参数：-f。

于是导出命令改为：mysqldump -h 主机地址 -P 端口 -u 用户名 -p 密码 --set-gtid-purged=OFF --databases 数据库名 > C:/data.sql ，重新导出一份。然后导入命令：mysql -u  用户名 -p 密码 -f < C:/data.sql。

第二个报错：ERROR at line : Unknown command ‘\‘‘.

编码规则的问题，也是找了好几个办法尝试，包括修改character变量等，依旧不行，最终解决是在导入命令上加了一句： --default-character-set=utf8mb4 搞定，于是导入命令改为：mysql -u  用户名 -p 密码 --default-character-set=utf8mb4 -f < C:/data.sql。

第三个报错：Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table

这个报错最终确定是导出的数据库里有字段值长度很长，且表里数据长度也很长，单个SQL修改的数据产生的binlog超过了max_allowed_packet参数。这个解决方法也简单，直接修改my.ini里面的max_allowed_packet参数，然后导出语句加：--max-allowed-packet=512M就行（这里的大小不一定要是512M）

mysqldump -h 主机地址 -P 端口 -u 用户名 -p 密码 --set-gtid-purged=OFF --max-allowed-packet=512M --databases 数据库名 > C:/data.sql

第四个报错：MySQL server has gone away

这个报错其实和第三个报错是一个问题，sql语句太长了，修改完max_allowed_packet还不够，最终我这边是修改了my.ini2个变量，提高超时时间：

wait_timeout=2880000

interactive_timeout = 2880000

第五个报错：innodb_log_file_size大小不够，具体报错信息就不找了，总之就是innodb_log_file_size不够大，5.6版mysql的默认是48M，之前版本是5M,具体概念参考：MySQL :: MySQL 5.6 Reference Manual :: 14.8.1 InnoDB Startup Configuration

用命令改是不行的，这个参数是只读权限的，好在5.6和5.6以后的mysql，可以直接在my.ini里面修改这个参数值，我改成256M了，innodb_log_file_size=256M。

然后就可以成功导入导出了，完整的数据库迁移。

数据库导出语句：

mysqldump -h 主机地址 -P 端口 -u 用户名 -p 密码 --set-gtid-purged=OFF --max-allowed-packet=512M --databases 数据库名 > C:/data.sql

数据库导入语句：

mysql -u  用户名 -p 密码 --default-character-set=utf8mb4 -f < C:/data.sql

注意：1.my.ini文件路径不要搞错了，我在改配置的过程中就改错了，搞半天参数不生效，吐血。

2.在修改配置文件前关闭mysql服务，修改完后重启