导航：首页 > 数据库 >

MySQL5.6.25升级MySQL5.7.15

发表于：2025-01-31 作者：千家信息网编辑

千家信息网最后更新 2025年01月31日，MySQL5.6升级MySQL5.7环境介绍Part1:写在最前提到MySQL升级，网上文章数之不尽，但大多数为老的版本，诸如5.1升级到5.5、5.5升级到5.6，今天给大家介绍下MySQL5.6升

千家信息网最后更新 2025年01月31日MySQL5.6.25升级MySQL5.7.15

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 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 changedmysql> 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=3306socket=/tmp/mysql.sockdefault-character-set=utf8[mysql]no-auto-rehashdefault-character-set=utf8[mysqld]port=3306character-set-server=utf8socket=/tmp/mysql.sockbasedir=/usr/local/mysqldatadir=/data/mysqlexplicit_defaults_for_timestamp=truelower_case_table_names=1back_log=103max_connections=3000max_connect_errors=100000table_open_cache=512external-locking=FALSEmax_allowed_packet=32Msort_buffer_size=2Mjoin_buffer_size=2Mthread_cache_size=51query_cache_size=32M#query_cache_limit=4Mtransaction_isolation=REPEATABLE-READtmp_table_size=96Mmax_heap_table_size=96M###***slowqueryparameterslong_query_time=1slow_query_log = 1slow_query_log_file=/data/slowlog/slow.log###***binlogparameterslog-bin=mysql-binbinlog_cache_size=4Mmax_binlog_cache_size=8Mmax_binlog_size=1024Mbinlog_format=MIXEDexpire_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#***MyISAMparameterskey_buffer_size=16Mread_buffer_size=1Mread_rnd_buffer_size=16Mbulk_insert_buffer_size=1M#skip-name-resolve###***master-slavereplicationparametersserver-id=$SERVERIDslave-skip-errors=all#***Innodbstorageengineparametersinnodb_buffer_pool_size=512Minnodb_data_file_path=ibdata1:10M:autoextend#innodb_file_io_threads=8innodb_thread_concurrency=16innodb_flush_log_at_trx_commit=1innodb_log_buffer_size=16Minnodb_log_file_size=512Minnodb_log_files_in_group=2innodb_max_dirty_pages_pct=75innodb_buffer_pool_dump_pct=50innodb_lock_wait_timeout=50innodb_file_per_table=oninnodb_buffer_pool_dump_at_shutdown=1innodb_buffer_pool_load_at_startup=1sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION[mysqldump]quickmax_allowed_packet=32M[myisamchk]key_buffer=16Msort_buffer_size=16Mread_buffer=8Mwrite_buffer=8M[mysqld_safe]open-files-limit=8192log-error=/data/mysql/error.logpid-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 startStarting MySQL..... SUCCESS! [root@HE3 ~]# ps -ef|grep mysqlroot     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.pidmysql    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=3306root     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 structure2016-10-20T03:30:27.375506Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_current' has the wrong structure2016-10-20T03:30:27.375536Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history' has the wrong structure2016-10-20T03:30:27.375568Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history_long' has the wrong structure2016-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 -pMANAGERmysql_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                                 OKmysql.db                                           OKmysql.engine_cost                                  OKmysql.event                                        OKmysql.func                                         OKmysql.general_log                                  OKmysql.gtid_executed                                OKmysql.help_category                                OKmysql.help_keyword                                 OKmysql.help_relation                                OKmysql.help_topic                                   OKmysql.innodb_index_stats                           OKmysql.innodb_table_stats                           OKmysql.ndb_binlog_index                             OKmysql.plugin                                       OKmysql.proc                                         OKmysql.procs_priv                                   OKmysql.proxies_priv                                 OKmysql.server_cost                                  OKmysql.servers                                      OKmysql.slave_master_info                            OKmysql.slave_relay_log_info                         OKmysql.slave_worker_info                            OKmysql.slow_log                                     OKmysql.tables_priv                                  OKmysql.time_zone                                    OKmysql.time_zone_leap_second                        OKmysql.time_zone_name                               OKmysql.time_zone_transition                         OKmysql.time_zone_transition_type                    OKmysql.user                                         OKUpgrading the sys schema.Checking databases.helei.helei                                        OKsys.sys_config                                     OKUpgrade 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 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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)

Part2:验证库和表是否有丢失

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || helei              || mysql              || performance_schema || sys                || test               |+--------------------+6 rows in set (0.00 sec)

升级5.7后，我们会发现多了一个sys库，这个库有很多的功能，以后再给大家慢慢介绍~

--总结--

MySQL升级有多种方式，但无论采用何种方式，都要做好备份和回滚的准备，以避免升级失败所带来的损失。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

很赞哦！