MySQL5.6.25升级MySQL5.7.15

170次阅读

共计 7854 个字符，预计需要花费 20 分钟才能阅读完成。

MySQL5.6 升级 MySQL5.7
环境介绍
Part1: 写在最前
提到 MySQL 升级，网上文章数之不尽，但大多数为老的版本，诸如 5.1 升级到 5.5、5.5 升级到 5.6，今天给大家介绍下 MySQL5.6 升级到 MySQL5.7 版本的方法和注意事项。

Part2: 升级方法
升级的方法一般有两类：
1. 利用 mysqldump 来直接导出 sql 文件，导入到新库中，这种方法是最省事儿的，也是最保险的，缺点的话，也显而易见，大库的 mysqldump 费时费力。
2. 直接替换掉 mysql 的安装目录和 my.cnf，利用 mysql_upgrade 来完成系统表的升级，这种方法需要备份原有的文件，但属于物理拷贝，速度较快。缺点的话，跨版本升级不推荐这么做，比如 mysql5.1 升级到 mysql5.6,mysql5.5 升级到 mysql5.7 等。
本文采用的是第二种方法升级。

Part3: 环境
数据库软件目录：/usr/local/mysql
my.cnf 位置：/etc/my.cnf
数据库 data 目录：/data/mysql
慢日志目录：/data/slowlog
准备工作
Part1:MySQL5.6.25
[root@HE3 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select version();
+————+
| version() |
+————+
| 5.6.25-log |
+————+
1 row in set (0.00 sec)
mysql> use helei;
Database changed
mysql> show tables;
+—————–+
| Tables_in_helei |
+—————–+
| helei |
+—————–+
1 row in set (0.00 sec)

我这里用的是 5.6.25 版本作为待升级库，库中已经模拟和创建了数据库 helei 和表 helei，用以之后验证升级。

Part2: 备份
备份整体需要备份的是 my.cnf、数据库安装目录、还有数据目录。
[root@HE3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@HE3 etc]# cp -rp my.cnf my_56_old.cnf
[root@HE3 local]# cp -rp mysql mysql_56_old
[root@HE3 ~]# rm -rf /usr/local/mysql
[root@HE3 data]# cp -rp mysql/ mysql_56_old
这里我直接采用 cp 的方式来进行。

Part3: 替换
1. 替换 /etc/my.cnf 为 mysql5.7 的 my.cnf
[root@HE3 ~]#vi /etc/my.cnf
[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8
[mysql]
no-auto-rehash
default-character-set=utf8
[mysqld]
port=3306
character-set-server=utf8
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql
explicit_defaults_for_timestamp=true
lower_case_table_names=1
back_log=103
max_connections=3000
max_connect_errors=100000
table_open_cache=512
external-locking=FALSE
max_allowed_packet=32M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size=51
query_cache_size=32M
#query_cache_limit=4M
transaction_isolation=REPEATABLE-READ
tmp_table_size=96M
max_heap_table_size=96M
###***slowqueryparameters
long_query_time=1
slow_query_log = 1
slow_query_log_file=/data/slowlog/slow.log
###***binlogparameters
log-bin=mysql-bin
binlog_cache_size=4M
max_binlog_cache_size=8M
max_binlog_size=1024M
binlog_format=MIXED
expire_logs_days=7
###***relay-logparameters
#relay-log=/data/3307/relay-bin
#relay-log-info-file=/data/3307/relay-log.info
#master-info-repository=table
#relay-log-info-repository=table
#relay-log-recovery=1
#***MyISAMparameters
key_buffer_size=16M
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=1M
#skip-name-resolve
###***master-slavereplicationparameters
server-id=$SERVERID
slave-skip-errors=all
#***Innodbstorageengineparameters
innodb_buffer_pool_size=512M
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_file_io_threads=8
innodb_thread_concurrency=16
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=2
innodb_max_dirty_pages_pct=75
innodb_buffer_pool_dump_pct=50
innodb_lock_wait_timeout=50
innodb_file_per_table=on
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqldump]
quick
max_allowed_packet=32M
[myisamchk]
key_buffer=16M
sort_buffer_size=16M
read_buffer=8M
write_buffer=8M
[mysqld_safe]
open-files-limit=8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

2. 解压新版 mysql
[root@HE3 ~]# tar xvf mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz
[root@HE3 ~]# mv mysql-5.7.15-linux-glibc2.5-x86_64 /usr/local/mysql
[root@HE3 ~]# chown -R mysql. /usr/local/mysql

3. 替换新版 mysqld 启动脚本
[root@HE3 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
cp: overwrite `/etc/init.d/mysqld’? y

开始升级
Part1: 启动
[root@HE3 ~]# /etc/init.d/mysqld start
Starting MySQL….. SUCCESS!
[root@HE3 ~]# ps -ef|grep mysql
root 26467 1 0 20:30 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –datadir=/data/mysql –pid-file=/data/mysql/HE3.pid
mysql 27197 26467 4 20:30 pts/2 00:00:01 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/data/mysql –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –log-error=/data/mysql/error.log –open-files-limit=8192 –pid-file=/data/mysql/HE3.pid –socket=/tmp/mysql.sock –port=3306
root 27235 25656 0 20:31 pts/2 00:00:00 grep mysql

这里虽然启动成功了，但 error 日志里能捕捉到很多错误信息，因为没有升级字典，诸如：
2016-10-20T03:30:27.375466Z 0 [ERROR] Native table ‘performance_schema’.’events_statements_summary_by_program’ has the wrong structure
2016-10-20T03:30:27.375506Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_current’ has the wrong structure
2016-10-20T03:30:27.375536Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_history’ has the wrong structure
2016-10-20T03:30:27.375568Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_history_long’ has the wrong structure
2016-10-20T03:30:27.375598Z 0 [ERROR] Native table ‘performance_schema’.’events_transactions_summary_by_thread_by_event_name’ has the wrong structure

Part2: 系统表升级
1. 升级数据字典
[root@HE3 ~]# /usr/local/mysql/bin/mysql_upgrade -uroot -pMANAGER
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
helei.helei OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.

2. 重启实例再次检查 error 日志
[root@HE3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

3. 请保证一些路径的设置和以前的 my.cnf 一致，如果设置错误，很可能导致启动不了数据库
[ERROR] Could not use /data/slowlog/slow.log for logging
我这里是由于慢日志的位置和老的 mysql5.6 路径不一致，创建 /data/slowlog 即可，这里要仔细看报错，然后根据报错来发现和解决问题。

验证结果
Part1: 输入用户名密码检查结果
[root@HE3 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.15-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select version();
+————+
| version() |
+————+
| 5.7.15-log |
+————+
1 row in set (0.00 sec)