MySQL 5.6.27升级MySQL 5.7.18版本的升级步骤介绍

名称           升级前                                      升级后                                   备注
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)


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




# 升级成功后会多出一个sys库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)


至此，升级步骤全部完毕。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2138109/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2138109/