名稱升級前升級後備註
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)

至此，升級步驟全部完畢。

MySQL 5.6.27升級MySQL 5.7.18版本的升級步驟介紹

相關文章