MySQL5.6.26升级到MySQL5.7.9实战方案

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

MySQL5.6.26升级到MySQL5.7.9实战方案

前言

         某大公司升级方案,由于公司经常安全扫描,每个版本都有自身的BUG,为了安全到一定时间都会升级到新的版本。本案例采用单机环境升级方案,在单机中搭建主从方案。

 

1.单机环境

IP:172.16.0.111

系统:SUSE  11

MySQL旧版本:5.6.26

MySQL新版本:5.7.9

2.升级前准备

注意:升级之前查看磁盘空间充足,能在本机搭建从库

wKiom1ZqVHOAyALRAABSaxQcDB4983.png

3.安装新版本

旧版本目录

软件目录: /app/mysql5.6/

数据目录:/data/mysql3306/

配置文件:/app/mysql5.6/my.cnf

[client]

socket=/app/mysql5.6/mysql.sock

default-character-set=utf8

#innodb_buffer_pool_size

port=3306

[mysql]

prompt=\\u@\\d \\r:\\m:\\s>

no-auto-rehash

[mysqld_safe]

pid-file=/app/mysql5.6/mysqld.pid

[mysqld]

socket=/app/mysql5.6/mysql.sock

port=3306

 

character-set-server=utf8

transaction-isolation=READ-COMMITTED

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp=TRUE

lower_case_table_names=1

local-infile=1

skip-name-resolve

 

table_definition_cache=500

table_open_cache=500

max_connections=1024

max_user_connections=1000

max_connect_errors=65536

max_allowed_packet=16M

 

query_cache_type=0

 

basedir=/app/mysql5.6

datadir=/data/mysql3306

slow_query_log=1

long_query_time=1

log-queries-not-using-indexes

#############

event_scheduler=ON

#innodb_buffer_pool_size=20G

innodb_flush_log_at_trx_commit=2

innodb_strict_mode=1

innodb_flush_method=O_DIRECT

innodb_log_files_in_group=3

innodb_file_per_table

 

log-bin=mysql-bin

binlog-format=ROW

server_id=111

log-slave-updates=1

skip-slave-start

#read_only=1

expire_logs_days=7

sync_binlog=1

新版本目录

软件目录:/app/mysql5.7/

数据目录:/data/mysqldata/

配置文件:/app/mysql5.7/my.cnf

 

3.1.解压文件

   mysql01:~# tar zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz

3.2.移动解压文件

mysql01:~ # mvmysql-5.7.9-linux-glibc2.5-x86_64 /app/mysql5.7

mysql01:~ # chown -R mysql:app/app/mysql5.7

3.3.创建数据目录

mysql01:~ # mkdir /data/mysqldata

mysql01:~ # chown -R mysql:app/data/mysqldata

3.4.初始化数据

注意:初始化和之前版本不一样了,初始化时有初始化密码的

mysql01:/data/mysqldata# /app/mysql5.7/bin/mysqld--initialize --user=mysql --basedir=/app/mysql5.7--datadir=/data/mysqldata 

2015-12-03T23:34:12.688857Z0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use--explicit_defaults_for_timestamp server option (see documentation for moredetails).

2015-12-03T23:34:12.927838Z0 [Warning] InnoDB: New log files created, LSN=45790

2015-12-03T23:34:12.968656Z0 [Warning] InnoDB: Creating foreign key constraint system tables.

2015-12-03T23:34:13.025810Z0 [Warning] No existing UUID has been found, so we assume that this is thefirst time that this server has been started. Generating a new UUID:5c20785f-9a16-11e5-a525-000c294cec8f.

2015-12-03T23:34:13.027457Z0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed'cannot be opened.

2015-12-03T23:34:13.028709Z1 [Note] A temporary password is generated for root@localhost: O-FxRJjl_1Yi   #初始化密码

3.5.修改配置文件

mysql01:~ # cp /app/mysql5.6/my.cnf  /app/mysql5.7/my.cnf

 

mysql01:/data/mysqldata # vi /app/mysql5.7/my.cnf

[client]

socket=/app/mysql5.7/mysql.sock

default-character-set=utf8

port=3307

[mysql]

prompt=\\u@\\d \\r:\\m:\\s>

no-auto-rehash

[mysqld_safe]

pid-file=/app/mysql5.7/mysqld.pid

[mysqld]

socket=/app/mysql5.7/mysql.sock

port=3307

 

character-set-server=utf8

transaction-isolation=READ-COMMITTED

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp=TRUE

lower_case_table_names=1

local-infile=1

skip-name-resolve

 

table_definition_cache=500

table_open_cache=500

max_connections=1024

max_user_connections=1000

max_connect_errors=65536

max_allowed_packet=16M

 

query_cache_type=0

 

basedir=/app/mysql5.7

datadir=/data/mysqldata

slow_query_log=1

long_query_time=1

log-queries-not-using-indexes

###################

 

#############

event_scheduler=ON

#innodb_buffer_pool_size=20G

innodb_flush_log_at_trx_commit=2

innodb_strict_mode=1

innodb_flush_method=O_DIRECT

innodb_log_files_in_group=3

innodb_file_per_table

 

log-bin=mysql-bin

binlog-format=ROW

server_id=112

log-slave-updates=1

skip-slave-start

#read_only=1

expire_logs_days=7

sync_binlog=1

 

3.6修改启动脚本

mysql01:/data/mysqldata # cp/app/mysql5.7/support-files/mysql.server /etc/init.d/mysql3307

mysql01:/data/mysqldata # vi/etc/init.d/mysql3307

 

修改之处

basedir=/app/mysql5.7

datadir=/data/mysqldata

 

 

mysql01:/data/mysqldata # chmod +x/etc/init.d/mysql3307

 

启动新版本MYSQL

mysql01:/data/mysqldata # /etc/init.d/mysql3307 start

Starting MySQL..                                                    done

 

进入MYSQL

mysql01:/data/mysqldata #/app/mysql5.7/bin/mysql-uroot  -pO-FxRJjl_1Yi  -P3307 --socket=/app/mysql5.7/mysql.sock

 

 

4.导出与导入数据

导出数据

mysql@mysql01 ~ $mysqldump -uroot -p  --default-character-set=utf8 --all-databases--single-transaction --routines --triggers --events  --master-data=2  --socket=/app/mysql5.6/mysql.sock >mysql20151211.sql

 

注解:

--all-databases   导出所有数据库

 

--single-transaction

该选项在导出数据之前提交一个BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎,仅InnoDB。本选项和--lock-tables 选项是互斥的,因为LOCK  TABLES 会使任何挂起的事务隐含提交。要想导出大表的话,应结合使用--quick 选项。

--routines, -R

导出存储过程以及自定义函数。

--triggers

导出触发器。该选项默认启用,用--skip-triggers禁用它。

--events, -E

导出事件。

 

--master-data

该选项将binlog的位置和文件名追加到输出文件中。如果为1,将会输出CHANGE MASTER 命令;如果为2,输出的CHANGE  MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项,除非--single-transaction也被指定(在这种情况下,全局读锁在开始导出时获得很短的时间;其他内容参考下面的--single-transaction选项)。该选项自动关闭--lock-tables选项。

 

 

mysql@mysql01 ~ $more mysql20151211.sql

-- MySQL dump 10.13  Distrib 5.6.26, for linux-glibc2.5 (x86_64)

--

-- Host: localhost    Database:

-- ------------------------------------------------------

-- Server version       5.6.26-log

 

/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,FOREIGN_KEY_CHECKS=0

*/;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0 */;

 

--

-- Position to start replication orpoint-in-time recovery from

--

 

-- CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000049', MASTER_LOG_POS=120;

#主从搭建时需要的

 

 

把备份数据导入新版本

 

mysql@mysql01 ~ $/app/mysql5.7/bin/mysql-uroot  -pO-FxRJjl_1Yi -P3307 --socket=/app/mysql5.7/mysql.sock  < mysql20151211.sql

 

查看导入成功

mysql@mysql01 ~ $/app/mysql5.7/bin/mysql-uroot  -pO-FxRJjl_1Yi -P3307 --socket=/app/mysql5.7/mysql.sock

mysql: [Warning] Using a password on thecommand line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.9-log MySQL CommunityServer (GPL)

 

Copyright (c) 2000, 2015, Oracle and/or itsaffiliates. All rights reserved.

 

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarksof their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.

 

root@(none) 07:51:16>show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| dataaudit          |

| fanrenjie          |

| huizhe             |

| mysql              |

| performance_schema |

| sys                |

| test               |

| test02             |

| test08             |

+--------------------+

10 rows in set (0.00 sec)

 

root@(none) 07:51:20>

 

5.主从搭建

Master上增加一个用于复制的账号:

 

root@(none) 07:52:47>GRANT REPLICATIONSLAVE ON *.*  TO 'repl'@'172.16.0.%'  IDENTIFIED BY '123456';

Query OK, 0 rows affected (1.01 sec)

 

 

SLAVE上执行如下命令

mysql@mysql01 ~ $/app/mysql5.7/bin/mysql-uroot  -pO-FxRJjl_1Yi -P3307 --socket=/app/mysql5.7/mysql.sock

mysql: [Warning] Using a password on thecommand line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.7.9-log MySQL CommunityServer (GPL)

 

Copyright (c) 2000, 2015, Oracle and/or itsaffiliates. All rights reserved.

 

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarksof their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.

 

 

root@(none) 07:51:20>CHANGE MASTER TOMASTER_HOST='172.16.0.111',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000049',MASTER_LOG_POS=120;

 #注意MASTER_LOG_FILE='mysql-bin.000049',MASTER_LOG_POS=120;与备份数据里的要一样,为了保证数据一致性

Query OK, 0 rows affected, 2 warnings (0.01sec)

 

 

root@(none) 08:00:17>start slave;  #开启主从

Query OK, 0 rows affected (0.00 sec)

 

 

root@(none) 08:03:42>show slavestatus\G; #查看复制

*************************** 1. row***************************

               Slave_IO_State: Waiting formaster to send event

                  Master_Host: 172.16.0.111

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000049

         Read_Master_Log_Pos: 327

               Relay_Log_File:mysql01-relay-bin.000002

                Relay_Log_Pos: 524

       Relay_Master_Log_File: mysql-bin.000049

            Slave_IO_Running: Yes

           Slave_SQL_Running: Yes

             Replicate_Do_DB:

         Replicate_Ignore_DB:

          Replicate_Do_Table:

      Replicate_Ignore_Table:

     Replicate_Wild_Do_Table:

 Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

         Exec_Master_Log_Pos: 327

              Relay_Log_Space: 733

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

          Master_SSL_Allowed: No

          Master_SSL_CA_File:

          Master_SSL_CA_Path:

              Master_SSL_Cert:

           Master_SSL_Cipher:

               Master_SSL_Key:

       Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

 Replicate_Ignore_Server_Ids:

            Master_Server_Id: 111

                  Master_UUID:279f439b-5d2f-11e5-ad29-000c294cec8f

            Master_Info_File: /data/mysqldata/master.info

                    SQL_Delay: 0

         SQL_Remaining_Delay: NULL

     Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates

          Master_Retry_Count: 86400

                  Master_Bind:

     Last_IO_Error_Timestamp:

    Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

          Master_SSL_Crlpath:

          Retrieved_Gtid_Set:

           Executed_Gtid_Set:

                Auto_Position: 0

        Replicate_Rewrite_DB:

                 Channel_Name:

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

root@(none) 08:03:55>

 

 

6.执行mysql_upgrade命令

  官方提到执行这命令可以让原数据启用到新版本的特性,注意:GTID复制不要开启

wKioL1ZqVGDANeo6AAB9dyXcKAQ413.png

mysql@mysql01 ~$/app/mysql5.7/bin/mysql_upgrade -uroot -p123456 -P3307 --socket=/app/mysql5.7/mysql.sock

mysql_upgrade: [Warning] Using a passwordon the command line interface can be insecure.

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

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

The sys schema is already up to date(version 1.5.0).

Found 0 sys functions, but expected 21.Re-installing the sys schema.

Upgrading the sys schema.

Checking databases.

dataaudit.t_dataaudit_amount_7                     OK

fanrenjie.hz_admin                                 OK

fanrenjie.hz_advertisement                         OK

fanrenjie.hz_car                                   OK

fanrenjie.hz_education                             OK

fanrenjie.hz_entertainment                         OK

fanrenjie.hz_food                                  OK

fanrenjie.hz_health                                OK

fanrenjie.hz_house                                 OK

fanrenjie.hz_it                                    OK

fanrenjie.hz_literature                            OK

fanrenjie.hz_manager_menu                          OK

fanrenjie.hz_message                               OK

fanrenjie.hz_news                                  OK

fanrenjie.hz_sports                                OK

fanrenjie.hz_subclass_menu                         OK

fanrenjie.hz_travel                                OK

huizhe.auth_urllink                                OK

huizhe.auth_user                                   OK

huizhe.data_baoyou                                 OK

huizhe.data_huazhuangpin                           OK

huizhe.data_muying                                 OK

huizhe.data_nanzhuang                              OK

huizhe.data_nvzhuang                               OK

huizhe.data_products                               OK

huizhe.data_shipin                                 OK

huizhe.data_tour                                   OK

huizhe.data_tuan                                   OK

huizhe.data_xiebao                                 OK

sys.sys_config                                     OK

Upgrade process completed successfully.

Checking if update is needed.

 

 

 

本文转自 jxzhfei  51CTO博客,原文链接:http://blog.51cto.com/jxzhfei/1721983


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
21天前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
168 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
21天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
1月前
|
监控 关系型数据库 MySQL
zabbix agent集成percona监控MySQL的插件实战案例
这篇文章是关于如何使用Percona监控插件集成Zabbix agent来监控MySQL的实战案例。
31 2
zabbix agent集成percona监控MySQL的插件实战案例
|
22天前
|
监控 关系型数据库 MySQL
如何升级mysql的版本
如何升级mysql的版本
48 2
|
22天前
|
存储 监控 关系型数据库
如何升级MySQL版本?
如何升级MySQL版本?
32 2
|
2月前
|
SQL 关系型数据库 MySQL
干货!python与MySQL数据库的交互实战
干货!python与MySQL数据库的交互实战
|
2月前
|
存储 关系型数据库 MySQL
实战!MySQL主从复制一键搭建脚本分享
实战!MySQL主从复制一键搭建脚本分享
31 2
|
2月前
|
运维 容灾 关系型数据库
MySQL高可用方案--Xenon全解
MySQL高可用方案--Xenon全解
|
2月前
|
SQL 存储 关系型数据库
MySQL备份:mydumper 备份恢复工具生产实战
MySQL备份:mydumper 备份恢复工具生产实战
|
2月前
|
安全 关系型数据库 MySQL
【MySQL】Orchestrator最简单的 mysql 高可用方案最细细细细~
【MySQL】Orchestrator最简单的 mysql 高可用方案最细细细细~
下一篇
无影云桌面