MySQL从5.6升级到5.7的多种实战经验总结

MySQL从5.6 升级到5.7主要有2种办法，第一种逻辑导出导入(mysqldump)进行升级，第二种是直接在老库的数据文件备份基础上升级。

扩展阅读：利用innobackupex 进行MySQL从库搭建

逻辑导出导入(mysqldump)的优缺点：

优点：

• 比较适合小库升级，简单
• 适合跨越多个版本进行升级，比如从5.5到5.7
• 能够减少数据库的碎片

缺点：

• 方法测试的时候麻烦，需要对大表进行手动拆分节约升级时间，容易出错
• 对于大库(100G)，升级时间耗费巨大
• 导出的时候如果选错字符集，将导致升级后数据库出现乱码

在老库的数据文件备份基础上升级

优点：

• 速度快，直接取决于备份速度，可以采用innobackup的并行备份
• 没有字符集的困恼
• 易于整库升级测试

缺点：

• 无法跨越大版本，笔者测试从5.6.20到5.7.15是没有问题, 建议事先测试。

一、逻辑导出导入(mysqldump)方法介绍

1、搭建新的环境，解压二进制文件，进行初始化，并拉起

直接指定basedir 和 datadir
./bin/mysqld --initialize-insecure --user=mysql --basedir=/app/mysql-5.7.15-linux --datadir=/app/mysql-5.7.15-linux/data
或者直接指定my.cnf 文件
./bin/mysqld --defaults-file=my.cnf --initialize-insecure --user=mysql 
启动数据库
./bin/mysqld --defaults-file=my.cnf &

注意：这里如果用cnf文件进行初始化的时候必须将defaults-file写在最前面，否则将导致初始化到/usr/local/mysql下面和其他报错。

如果出现Can’t find messagefile ‘/usr/share/mysql/errmsg.sys的时候，在my.cnf 文件指定 –lc_messages_dir=/app/mysql-5.7.15-linux/share/ –lc_messages=en_US

注意新环境的端口需要和老环境不同

innodb_additional_mem_pool_size 5.7不支持

2、确定老数据库的字符集并导出

(–default-character-set –确认是数据库的字符集)

./bin/mysqldump -uroot -p --default-character-set=utf8 --triggers  -R -E \
--add-drop-table  --lock-all-tables --master-data=2  \
 --all-databases  >  mysql_full_backup_$(date +%Y%m%d).sql

注意：master-data =2 是用注释的方式指定change master 的 file 和position ,在从库恢复的时候有用，如果没有开binlog需要去掉该参数

3、将数据进行导入，有两种办法：

直接导入

 ./bin/mysql -uroot -p -S ./mysql.sock < ./mysql_full_backup...

登入到数据库里面(推荐，方便分析)

tee /home/mysql/import.log
source mysql_full_backup...
notee
然后检查导入的日志。
cat /home/mysql/import.log | grep -v "Query OK" | grep -v ^$ | more

4、将MySQL进行upgrade，然后进行重启，确认后台无报错

利用mysql_upgrade进行升级，注意该处是否是全部OK
./bin/mysql_upgrade -u root -p  -S ./mysql.sock --force
然后关闭数据库
./bin/mysqladmin -u root -p  shutdown
启动数据库
./bin/mysqld_safe  -defaults-file=my.cnf &
检查error日志是否有报错

5、环境变量

用软连接将mysql 连接到新的数据库 ，配置环境变量

export MYSQL_HOME=/app/mysql 
export MYSQL_PS1="\\u@\\h [\\d] \\r:\\m:\\s>>>" 
export MYSQL_UNIX_PORT=/data/mysqldata/mysql.sock 
#export MYSQL_PWD 
#export MYSQL_TCP_PORT=3306

二、在老库的数据文件备份基础上升级方法介绍

1、环境初始化步奏和逻辑导出导入方式一致，注意端口即可。

2、利用innobackup进行备份，然后进行prepare

backup:
innobackupex --defaults-file=/app/mysql/my.cnf  --user=root --password=myroot321  \
--use-memory=1024M --no-timestamp --backup  /data/backup/full
 
prepare:
innobackupex --apply-log  /data/backup/full

3、将备份文件移动到新初始化的数据库数据文件目录

4、将MySQL进行upgrade，然后进行重启，确认后台无报错

利用mysql_upgrade进行升级，注意此处是否全部OK
./bin/mysql_upgrade -u root -p  -S ./mysql.sock --force
然后关闭数据库
./bin/mysqladmin -u root -p  shutdown
启动数据库
./bin/mysqld_safe  -defaults-file=my.cnf &
检查error日志是否有报错

5、环境变量，和逻辑导出导入一致