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

61次阅读
没有评论

共计 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)

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)

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

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

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-19发表,共计15174字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)