MySQL5.6.26升级到MySQL5.7.9实战方案
MySQL5.6.26 升级到 MySQL5.7.9 实战方案
前言
某大公司升级方案，由于公司经常安全扫描，每个版本都有自身的 BUG ，为了安全到一定时间都会升级到新的版本。本案例采用单机环境升级方案，在单机中搭建主从方案。
1. 单机 环境
IP:172.16.0.111
系统： SUSE 11
MySQL 旧版本： 5.6.26
MySQL 新版本： 5.7.9
2. 升级前准备
注意： 升级之前查看磁盘空间充足，能在本机搭建从库 。
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 -p O-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 -p O-FxRJjl_1Yi -P3307 --socket=/app/mysql5.7/mysql.sock < mysql20151211.sql
查看导入成功
mysql@mysql01 ~ $/app/mysql5.7/bin/mysql-uroot -p O-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 -p O-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 复制不要开启
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.
7. 升级切换。
把主库停止，把从库停止后，端口修改成 3306 ，因为为了对生产库没有影响。在停止时必须和应用联系。
停止主库
mysql01:/data/mysqldata #/etc/init.d/mysqld stop
Shutting down MySQL.... done
停止从库
mysql01:/data/mysqldata #/etc/init.d/mysql3307 stop
Shutting down MySQL.... done
修改启动
mysql01:/data/mysqldata # cd /etc/init.d/
mysql01:/etc/init.d # mv mysqld mysqlold
mysql01:/etc/init.d # mv mysql3307 mysqld
修改配置文件
mysql01:/etc/init.d # vi/app/mysql5.7/my.cnf
[client]
….
port=3306
…
[mysqld]
…………
port=3306
……
最后启动新版本
mysql01:/etc/init.d # /etc/init.d/mysqldstart
Starting MySQL. Done
清除同步
mysql> resetslave all;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status/G;
Empty set (0.00 sec)
ERROR:
No query specified
把环境变量修改如下
mysql@mysql01 ~ $vi .profile
export MYSQL_HOME=/app/mysql5.7
export MY_BASEDIR_VERSION=/app/mysql5.7
export PATH=/app/mysql5.7/bin:$PATH
export LD_LIBRARY_PATH=/app/mysql5.7/lib
export PS1='/u@/H /w /$'
让环境变量生效
mysql@mysql01 ~ $source .profile
注：过几天如果应用一切正常。
最后清理旧版本的数据与软件
本文出自 “�w��沓�}” 博客，转载请与作者联系！
正文到此结束
- 本文标签： src 备份 Travel 配置 空间 SQL Server root tar 安装 注释 同步 lib rewrite sql 时间 锁 dist 2015 update ip 安全 NSA 软件 client ACE 目录 db Slaves Menu value schema Word mysql 端口 cat IDE key MySQL5 博客 linux tab 数据库 数据 Document Connection http ssl Oracle UI App mysql-bin.0000
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
