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

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-f 存放路径 /etc/my-f                     /etc/my-f

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-f | grep datadir

datadir = /database/mysql/data

# cd /database

# pwd

/database

2).开始备份数据文件:

# cp -r mysql mysql3306

3).备份参数文件:

# cp /etc/my-f /etc/5.6.27_my-f

四.正式修改my-f配置文件 # 主要修改basedir

将/etc/my-f文件中的“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-f &

启动报错:

[root@chengshuidai mysql]# /opt/app/mysql/5.7.18/bin/mysqld --defaults-file=/etc/my-f &

[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-f --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 havent 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-f 

log_bin=mysql-bin

server_id=58      --加入server_id内容

再次启动,成功:

[root@chengshuidai mysql]# /opt/app/mysql/5.7.18/bin/mysqld --defaults-file=/etc/my-f --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-f --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-f --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-f --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-f --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)

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

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。