MySQL 5.6.27升级MySQL 5.7.18版本的升级步骤是怎么样的

31次阅读

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

MySQL 5.6.27 升级 MySQL 5.7.18 版本的升级步骤是怎么样的，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

名称升级前升级后备注
OS 版本 CentOS release 6.5 CentOS release 6.5
MySQL 版本 5.6.27 社区版 64bit 5.7.13 社区版 64bit Linux Generic Binaries
MySQL 安装路径 /usr/local/mysql /opt/app/mysql/5.7.13
My.cnf 存放路径 /etc/my.cnf /etc/my.cnf
Mysql basedir /usr/local/mysql /opt/app/mysql/5.7.13
Mysql Datadir /database/mysql3306/data /database/mysql/data

一. 下载 MySQL 5.7.18
从官网下载 mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

官网地址： http://dev.mysql.com/downloads/mysql/

# mkdir -p /opt/app/mysql

# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /opt/app/mysql

# mv /opt/app/mysql/mysql-5.7.18-linux-glibc2.5-x86_64 /opt/app/mysql/5.7.18

chown -R mysql.mysql /opt/app/mysql/5.7.18/

二. 关闭 MySQL 5.6.27 实例
[root@chengshuidai ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!

确认不存在 mysql 进程:
[root@chengshuidai ~]# ps -ef | grep mysqld | grep -v grep

三. 备份 MySQL 5.6.27 数据库:
1). 确认数据文件路径:
# cat /etc/my.cnf | grep datadir
datadir = /database/mysql/data
# cd /database
# pwd
/database
2). 开始备份数据文件:
# cp -r mysql mysql3306
3). 备份参数文件:
# cp /etc/my.cnf /etc/5.6.27_my.cnf

四. 正式修改 my.cnf 配置文件 # 主要修改 basedir
将 /etc/my.cnf 文件中的“basedir = /usr/local/mysql”替换成“basedir = /opt/app/mysql/5.7.18”，
然后保存所做的修改！

1). 启动 MySQL 5.7.18
/opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf
启动报错:
[root@chengshuidai mysql]# /opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf
[1] 12747
[root@chengshuidai mysql]# 2017-04-27T05:48:11.328215Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-27T05:48:11.328365Z 0 [Warning] NO_ZERO_DATE , NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-04-27T05:48:11.328374Z 0 [Warning] NO_AUTO_CREATE_USER sql mode was not set.
2017-04-27T05:48:11.328430Z 0 [Note] –secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-04-27T05:48:11.328483Z 0 [Note] /opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12747 …
2017-04-27T05:48:11.335101Z 0 [ERROR] Fatal error: Please read Security section of the manual to find out how to run mysqld as root!

2017-04-27T05:48:11.335153Z 0 [ERROR] Aborting

2017-04-27T05:48:11.335200Z 0 [Note] Binlog end
2017-04-27T05:48:11.335278Z 0 [Note] /opt/app/mysql/5.7.18/bin/mysqld: Shutdown complete

加 –user=root 后启动依然报错:
[root@chengshuidai mysql]# /opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf –user=root
[1] 12754
[root@chengshuidai mysql]# 2017-04-27T05:50:00.048115Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-27T05:50:00.048278Z 0 [Warning] NO_ZERO_DATE , NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-04-27T05:50:00.048288Z 0 [Warning] NO_AUTO_CREATE_USER sql mode was not set.
2017-04-27T05:50:00.048359Z 0 [Note] –secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-04-27T05:50:00.048413Z 0 [Note] /opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12754 …
2017-04-27T05:50:00.054466Z 0 [ERROR] You have enabled the binary log, but you haven t provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
2017-04-27T05:50:00.054518Z 0 [ERROR] Aborting

解决方法：
# vi /etc/my.cnf
log_bin=mysql-bin
server_id=58 – 加入 server_id 内容

再次启动, 成功:
[root@chengshuidai mysql]# /opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf –user=root
[1] 12768
[root@chengshuidai mysql]# 2017-04-27T05:54:10.228092Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-27T05:54:10.228188Z 0 [Warning] NO_ZERO_DATE , NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-04-27T05:54:10.228192Z 0 [Warning] NO_AUTO_CREATE_USER sql mode was not set.
2017-04-27T05:54:10.228232Z 0 [Note] –secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-04-27T05:54:10.228266Z 0 [Note] /opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12768 …
2017-04-27T05:54:10.233042Z 0 [Note] InnoDB: PUNCH HOLE support not available
2017-04-27T05:54:10.233072Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-27T05:54:10.233077Z 0 [Note] InnoDB: Uses event mutexes
2017-04-27T05:54:10.233081Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017-04-27T05:54:10.233085Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
……………………………………………………………………………………………………..
……………………………………………………………………………………………………..

2). 确认是否启动成功:
[root@chengshuidai mysql]# ps -ef |grep mysqld | grep -v grep
root 12768 12577 1 13:54 pts/2 00:00:00 /opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf –user=root

四. 升级数据库(升级数据字典)
1). 运行 mysql_upgrade 升级数据字典
[root@chengshuidai mysql]# /opt/app/mysql/5.7.18/bin/mysql_upgrade -uroot -p123456
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.
2017-04-27T05:57:08.942356Z 3 [Warning] System table servers is expected to be transactional.
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.
sys.sys_config OK
test.chenfeng OK
test.duansf OK
Upgrade process completed successfully.
Checking if update is needed.

2). 重启 MySQL 5.7.18 实例
关闭 MySQL 实例:
[root@chengshuidai tmp]# /opt/app/mysql/5.7.18/bin/mysqladmin -uroot -p123456 -S /tmp/mysql.sock shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2017-04-27T05:59:23.778618Z 0 [Note] Giving 0 client threads a chance to die gracefully
2017-04-27T05:59:23.778648Z 0 [Note] Shutting down slave threads
2017-04-27T05:59:23.778665Z 0 [Note] Forcefully disconnecting 0 remaining clients
[root@chengshuidai tmp]# 2017-04-27T05:59:23.778799Z 0 [Note] Binlog end
2017-04-27T05:59:23.782195Z 0 [Note] Shutting down plugin ngram
2017-04-27T05:59:23.782234Z 0 [Note] Shutting down plugin partition
2017-04-27T05:59:23.782242Z 0 [Note] Shutting down plugin ARCHIVE
2017-04-27T05:59:23.782247Z 0 [Note] Shutting down plugin BLACKHOLE
2017-04-27T05:59:23.782254Z 0 [Note] Shutting down plugin MRG_MYISAM
2017-04-27T05:59:23.782263Z 0 [Note] Shutting down plugin MyISAM
2017-04-27T05:59:23.782276Z 0 [Note] Shutting down plugin INNODB_SYS_VIRTUAL
2017-04-27T05:59:23.782283Z 0 [Note] Shutting down plugin INNODB_SYS_DATAFILES
2017-04-27T05:59:23.782288Z 0 [Note] Shutting down plugin INNODB_SYS_TABLESPACES
2017-04-27T05:59:23.782293Z 0 [Note] Shutting down plugin INNODB_SYS_FOREIGN_COLS
2017-04-27T05:59:23.782299Z 0 [Note] Shutting down plugin INNODB_SYS_FOREIGN
2017-04-27T05:59:23.782304Z 0 [Note] Shutting down plugin INNODB_SYS_FIELDS
2017-04-27T05:59:23.782309Z 0 [Note] Shutting down plugin INNODB_SYS_COLUMNS
2017-04-27T05:59:23.782314Z 0 [Note] Shutting down plugin INNODB_SYS_INDEXES
2017-04-27T05:59:23.782319Z 0 [Note] Shutting down plugin INNODB_SYS_TABLESTATS
2017-04-27T05:59:23.782324Z 0 [Note] Shutting down plugin INNODB_SYS_TABLES
2017-04-27T05:59:23.782329Z 0 [Note] Shutting down plugin INNODB_FT_INDEX_TABLE
2017-04-27T05:59:23.782334Z 0 [Note] Shutting down plugin INNODB_FT_INDEX_CACHE
2017-04-27T05:59:23.782339Z 0 [Note] Shutting down plugin INNODB_FT_CONFIG
2017-04-27T05:59:23.782345Z 0 [Note] Shutting down plugin INNODB_FT_BEING_DELETED
2017-04-27T05:59:23.782350Z 0 [Note] Shutting down plugin INNODB_FT_DELETED
2017-04-27T05:59:23.782355Z 0 [Note] Shutting down plugin INNODB_FT_DEFAULT_STOPWORD
2017-04-27T05:59:23.782360Z 0 [Note] Shutting down plugin INNODB_METRICS
2017-04-27T05:59:23.782366Z 0 [Note] Shutting down plugin INNODB_TEMP_TABLE_INFO
2017-04-27T05:59:23.782370Z 0 [Note] Shutting down plugin INNODB_BUFFER_POOL_STATS
2017-04-27T05:59:23.782384Z 0 [Note] Shutting down plugin INNODB_BUFFER_PAGE_LRU
2017-04-27T05:59:23.782390Z 0 [Note] Shutting down plugin INNODB_BUFFER_PAGE
2017-04-27T05:59:23.782395Z 0 [Note] Shutting down plugin INNODB_CMP_PER_INDEX_RESET
2017-04-27T05:59:23.782400Z 0 [Note] Shutting down plugin INNODB_CMP_PER_INDEX
2017-04-27T05:59:23.782406Z 0 [Note] Shutting down plugin INNODB_CMPMEM_RESET
2017-04-27T05:59:23.782410Z 0 [Note] Shutting down plugin INNODB_CMPMEM
2017-04-27T05:59:23.782416Z 0 [Note] Shutting down plugin INNODB_CMP_RESET
2017-04-27T05:59:23.782421Z 0 [Note] Shutting down plugin INNODB_CMP
2017-04-27T05:59:23.782441Z 0 [Note] Shutting down plugin INNODB_LOCK_WAITS
2017-04-27T05:59:23.782446Z 0 [Note] Shutting down plugin INNODB_LOCKS
2017-04-27T05:59:23.782452Z 0 [Note] Shutting down plugin INNODB_TRX
2017-04-27T05:59:23.782457Z 0 [Note] Shutting down plugin InnoDB
2017-04-27T05:59:23.782547Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-04-27T05:59:23.782702Z 0 [Note] InnoDB: Starting shutdown…
2017-04-27T05:59:23.883069Z 0 [Note] InnoDB: Dumping buffer pool(s) to /database/mysql/data/ib_buffer_pool
2017-04-27T05:59:23.884131Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 170427 13:59:23
2017-04-27T05:59:25.101878Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2874061
2017-04-27T05:59:25.106229Z 0 [Note] InnoDB: Removed temporary tablespace data file: ibtmp1
2017-04-27T05:59:25.106254Z 0 [Note] Shutting down plugin PERFORMANCE_SCHEMA
2017-04-27T05:59:25.106308Z 0 [Note] Shutting down plugin MEMORY
2017-04-27T05:59:25.106316Z 0 [Note] Shutting down plugin CSV
2017-04-27T05:59:25.106324Z 0 [Note] Shutting down plugin sha256_password
2017-04-27T05:59:25.106330Z 0 [Note] Shutting down plugin mysql_native_password
2017-04-27T05:59:25.106661Z 0 [Note] Shutting down plugin binlog
2017-04-27T05:59:25.107913Z 0 [Note] /opt/app/mysql/5.7.18/bin/mysqld: Shutdown complete

[1]+ Done /opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf –user=root (wd: /opt/app/mysql)

再次启动 MySQL 5.7.18
[root@chengshuidai tmp]# /opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf –user=root
[1] 12823
[root@chengshuidai tmp]# 2017-04-27T06:00:40.884043Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-27T06:00:40.884128Z 0 [Warning] NO_ZERO_DATE , NO_ZERO_IN_DATE and ERROR_FOR_DIVISION_BY_ZERO sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-04-27T06:00:40.884133Z 0 [Warning] NO_AUTO_CREATE_USER sql mode was not set.
2017-04-27T06:00:40.884168Z 0 [Note] –secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-04-27T06:00:40.884200Z 0 [Note] /opt/app/mysql/5.7.18/bin/mysqld (mysqld 5.7.18-log) starting as process 12823 …
2017-04-27T06:00:40.889079Z 0 [Note] InnoDB: PUNCH HOLE support not available
2017-04-27T06:00:40.889111Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-27T06:00:40.889116Z 0 [Note] InnoDB: Uses event mutexes
2017-04-27T06:00:40.889120Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2017-04-27T06:00:40.889124Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-04-27T06:00:40.889128Z 0 [Note] InnoDB: Using Linux native AIO
2017-04-27T06:00:40.889401Z 0 [Note] InnoDB: Number of pools: 1
2017-04-27T06:00:40.889516Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-04-27T06:00:40.890941Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-04-27T06:00:40.899250Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-04-27T06:00:40.901561Z 0 [Note] InnoDB: page_cleaner coordinator priority: -20
2017-04-27T06:00:40.914701Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-04-27T06:00:40.945405Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-04-27T06:00:40.945496Z 0 [Note] InnoDB: Setting file ./ibtmp1 size to 12 MB. Physically writing the file full; Please wait …
2017-04-27T06:00:40.987464Z 0 [Note] InnoDB: File ./ibtmp1 size is now 12 MB.
2017-04-27T06:00:40.989695Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-04-27T06:00:40.989715Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-04-27T06:00:40.991635Z 0 [Note] InnoDB: Waiting for purge to start
2017-04-27T06:00:41.041879Z 0 [Note] InnoDB: 5.7.18 started; log sequence number 2874061
2017-04-27T06:00:41.042155Z 0 [Note] InnoDB: Loading buffer pool(s) from /database/mysql/data/ib_buffer_pool
2017-04-27T06:00:41.042846Z 0 [Note] Plugin FEDERATED is disabled.
2017-04-27T06:00:41.044000Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170427 14:00:41
2017-04-27T06:00:41.058759Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-04-27T06:00:41.059402Z 0 [Note] Server hostname (bind-address): * port: 3306
2017-04-27T06:00:41.059537Z 0 [Note] IPv6 is available.
2017-04-27T06:00:41.059557Z 0 [Note] – :: resolves to ::
2017-04-27T06:00:41.059599Z 0 [Note] Server socket created on IP: :: .
2017-04-27T06:00:41.079802Z 0 [Note] Event Scheduler: Loaded 0 events
2017-04-27T06:00:41.080095Z 0 [Note] /opt/app/mysql/5.7.18/bin/mysqld: ready for connections.
Version: 5.7.18-log socket: /tmp/mysql.sock port: 3306 MySQL Community Server (GPL)
2017-04-27T06:00:41.080121Z 0 [Note] Executing SELECT * FROM INFORMATION_SCHEMA.TABLES; to get a list of tables using the deprecated partition engine. You may use the startup option –disable-partition-engine-check to skip this check.
2017-04-27T06:00:41.080128Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-04-27T06:00:41.100591Z 0 [Note] End of list of non-natively partitioned tables

[root@chengshuidai tmp]# ps -ef | grep mysqld | grep -v grep
root 12823 12577 1 14:00 pts/2 00:00:00 /opt/app/mysql/5.7.18/bin/mysqld –defaults-file=/etc/my.cnf –user=root

五、确认升级是否成功:
[root@chengshuidai tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)

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.18-log |
+————+
1 row in set (0.00 sec)