MySQL5.6升级到5.7

1.1 升级说明

MySQL升级:MySQL中不存在打补丁的说法,MySQL的所谓的补丁就是升级到一个新版本,然后把原始数据拉起来进行升级。

1.2 升级方式

1.2.1 inplace就地升级

在一台服务器上,进行原版本升级到新版本,风险较大。如果是主从环境可以先就地升级从库,然后再升级主库,滚动方式升级。

1.2.2 逻辑迁移升级

准备新的服务器,然后将数据从源库中逻辑导出,然后再导入到新的数据库中,数据量大的时候,会很慢。例如:
如果是一主一从(主->从1),在有条件的情况下,可以新准备一台服务器,采用物理备份的方式将数据备份恢复到新的服务中,然后构建从库的从库(主->从1->从2),最后将从2进行inplace方式升级,然后进行业务验证,验证完成后构建主->从2。升级从1,将(主->从1)的从1断开,从1升级完成后,构建(主->从1,主->从2),此时可以升级主库,主库停止写业务,主从关系变更为(从1->从2)原从1承担写库,待主库完成升级后重新加入主从即可。

1.2.3 前提建议

1.不管哪种方式升级,都先冷备全量数据,包括日志,便于回退。
2.升级之前一定要充分的测试验证,包含业务验证。

1.2.4 升级注意事项

1.支持GA版本之间的升级,不支持跨版本升级。
2.5.6升级到5.7时,先将5.6升级到最新版本,然后再升级到5.7
3.5.6升级5.7时,先将5.5升级到最新版本,然后从5.5的最新版本升级到5.6最新版本,最后从5.6最新版本升级到5.7最新版本
4.回退方案提前准备好,做好充足的演练;墙裂建议做好备份,尤其是升级到8.0最新版本时
5.降低停机时间,在业务不繁忙的月黑风高的后半夜升级。
6.升级过程中需要重启节点,

1.3 inplace升级过程(生产思路)

1.部署新版本的数据库软件
2. 设置参数:innodb_fast_shutdown=1,然后关闭数据库。 #表示不干净的关闭数据库,建议设置0,表示干净的关闭,该落盘的数据都落盘
3.冷备份数据库数据
4.使用新版本数据库,拉起旧版本的数据进行启动,参数(--skip-grant-tables --skip-networking)
5.启动完成后,验证业务功能
6.恢复业务,升级完成。

1.4 5.6inplace升级到5.7

5.6.50升级到5.7.32。
[root@localhost ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.50, for linux-glibc2.12 (x86_64) using EditLine wrapper

1.4.1 部署要新版本的MySQL(5.7)

1.解压文件

tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz
ln -s  /usr/local/mysql-5.7.32-linux-glibc2.12-x86_64 mysql57
1.4.2 停服务

1停止业务发起新连接到数据库
停止前先断开业务,show processlist; 查看当前连接情况,如果连接比较多,需要手工杀掉,可以拼接语句批量杀掉
拼接语句:
select concat("kill ",id,";") from information_schema.processlist;
2.调整快速关闭参数

set global innodb_fast_shutdown=0;

例如:

mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@innodb_fast_shutdown;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

3.关闭服务
如果是多实例,可以使用该关闭方法:mysqladmin -S /tmp/mysql56.sock shutdown
单实例关闭:/etc/init.d/mysqld stop
关闭服务:

[root@localhost ~]# netstat -lntup	
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      14827/sshd          
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      6526/master         
tcp6       0      0 :::3306                 :::*                    LISTEN      16824/mysqld        
tcp6       0      0 :::22                   :::*                    LISTEN      14827/sshd          
tcp6       0      0 ::1:25                  :::*                    LISTEN      6526/master         
[root@localhost ~]# ps -ef|grep mysql
root      16646      1  0 15:43 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/localhost.localdomain.pid
mysql     16824  16646  0 15:43 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/data/3306/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
root      16855  14763  0 15:52 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# /etc/init.d/mysqld stop  # 停止服务
Shutting down MySQL.. SUCCESS! 
[root@localhost ~]# ps -ef|grep mysql
root      16878  14763  0 15:53 pts/0    00:00:00 grep --color=auto mysql
[root@localhost ~]# 
[root@localhost ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      14827/sshd          
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      6526/master         
tcp6       0      0 :::22                   :::*                    LISTEN      14827/sshd          
tcp6       0      0 ::1:25                  :::*                    LISTEN      6526/master         
[root@localhost ~]# 
1.4.3 备份数据

1.冷备份
如果数据和日志都需要备份

[root@localhost data]# cp -r 3306 3306_bak # 生产环境中建议把数据单独其他盘
1.4.4 升级
1.4.4.1 升级前

1.使用5.7服务,拉起5.6的数据,然后升级

/usr/local/mysql57/bin/mysqld_safe --/defaults-files=/etc/my.cnf --skip-grant-tables --skip-networking &

--/defaults-files=/etc/my.cnf f # 为了读取5.6的数据

[root@localhost ~]# mysql   # mysql 登录验证  此时启动成功,可以登录,但并未完成升级
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> show databases;  # 升级前5.6没有sys库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sysbenchdb         |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> 
1.4.4.2 升级

升级:

/usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql.sock --force  # 该操作是升级系统表

一堆ok
例如:

[root@localhost ~]# /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql.sock --force 
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
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
sysbenchdb.sbtest1                                 OK
sysbenchdb.sbtest10                                OK
sysbenchdb.sbtest11                                OK
sysbenchdb.sbtest12                                OK
sysbenchdb.sbtest13                                OK
sysbenchdb.sbtest14                                OK
sysbenchdb.sbtest15                                OK
sysbenchdb.sbtest16                                OK
sysbenchdb.sbtest17                                OK
sysbenchdb.sbtest18                                OK
sysbenchdb.sbtest19                                OK
sysbenchdb.sbtest2                                 OK
sysbenchdb.sbtest20                                OK
sysbenchdb.sbtest3                                 OK
sysbenchdb.sbtest4                                 OK
sysbenchdb.sbtest5                                 OK
sysbenchdb.sbtest6                                 OK
sysbenchdb.sbtest7                                 OK
sysbenchdb.sbtest8                                 OK
sysbenchdb.sbtest9                                 OK   # 应用表
Upgrade process completed successfully.
Checking if update is needed.
1.4.4.3 升级后

show databases;
查看是否有sys库。查看user表是否有authentication_string字段

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |		# 此时有sys库
| sysbenchdb         |
| test               |
+--------------------+
6 rows in set (0.00 sec)
1.4.5 重启服务

重启数据库:
升级完成后,还处理安全模式,因为在拉起数据的库的时候加了参数“--skip-grant-tables --skip-networking”,此时需要重启数据库
此时配置文件中的basedir可以调整成新版本的
修改配置:

 [root@localhost ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql57 # 由之前的56版本改成57
datadir=/data/3306/data
server_id=56
port=3306
socket=/tmp/mysql.sock
lower_case_table_names=1
 重启服务
[root@localhost ~]# /etc/init.d/mysqld restart # 重启服务
Shutting down MySQL..2022-01-03T08:28:21.739297Z mysqld_safe mysqld from pid file /data/3306/data/localhost.localdomain.pid ended
 SUCCESS! 
Starting MySQL. SUCCESS! 
[1]+  Done                    /usr/local/mysql57/bin/mysqld_safe --/defaults-files=/etc/my.cnf --skip-grant-tables --skip-networking
[root@localhost ~]#
[root@localhost ~]# netstat -lntup	
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      14827/sshd          
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      6526/master         
tcp6       0      0 :::3306                 :::*                    LISTEN      16824/mysqld        
tcp6       0      0 :::22                   :::*                    LISTEN      14827/sshd          
tcp6       0      0 ::1:25                

以下方式启动也可以:
/usr/local/mysql57/bin/mysqld_safe --/defaults-files=/etc/my.cnf &

1.4.6 验证
启动完成后,验证业务

[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@localhost ~]# mysql -uroot -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.32 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> show databses;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databses' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| sysbenchdb         |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mysql> use sysbenchdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from sbtest1 limit 3;
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k    | c                                                                                                                       | pad                                                         |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|  1 | 3231 | 68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441 | 22195207048-70116052123-74140395089-76317954521-98694025897 |
|  2 |  557 | 13241531885-45658403807-79170748828-69419634012-13605813761-77983377181-01582588137-21344716829-87370944992-02457486289 | 28733802923-10548894641-11867531929-71265603657-36546888392 |
|  3 | 2758 | 16516882386-05118562259-88939752278-62800788230-55205008755-06868633780-74894238647-69655573455-70526404237-73358617781 | 73198647949-50059256035-48039302709-77824424754-93913530645 |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

注意:升级只是升级了系统表,和数据量没有关系。

1.5小结

1.升级前也可以单独把所有的表结构进行备份
2.生产环境中建议把数据单独其他盘
生产环境中操作步骤建议复制粘贴执行,手敲容易出错
5.重启时,不建议直接杀进程!!
4.5.6中没有sys库,升级后检查库中是否由sys库

推荐这些技术文章:

mysql-终端链接mysql报错

报错信息如下:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解决方案如下:

alter user 'root'@'localhost' identified by '新密码';返回信息如下代表成功:Query OK, ...

mysql教程(上)

MySQL 创建数据库

MySQL 删除数据库

MySQL 选择数据库

MySQL 数据类型

MySQL 创建数据表

MySQL 删除数据表

MySQL 插入数据

MySQL 查询数据

MySQL WHERE 子句

MySQL UPDATE 更新

MySQL DELETE 语句

MySQL LIKE 子句

MySQL UNION 操作符

M...

mysql半同步复制

目录半同步复制增强半同步复制配置
半同步复制
同步复制:当主库执行完客户端提交的事务后,需要等到所有从库也都执行完这一事务后,才返回给客户端执行成功。因为要等到所有从库都执行完,执行过程中会被阻塞,等待返回结果,所以性能上会有很严重的影响。一般不使用。
半同步复制:半同步复制模式,可以说是介于异步和同步之间的一种复制模式,主库在执行完客户端提交的事务后,要等待至少一个从库接收到binlog并将数...

mysql 8 修改账号密码

### my.cnf [mysqld] 下配置skip-grant-tables
flush privileges;

###方案一,直接修改
alter user 'root'@'localhost' identified by '123456';
###方案二,删除原来的用户重新创建用户
#删除用户
DROP USER 'root'@'localhost';#localhost,默认本地访问...

mysql并行复制

目录1.为什么需要并行复制2.并行复制配置2.1配置2.2检验并行复制2.3监控3.参数详解binlog_group_commit_sync_delaybinlog_group_commit_sync_no_delay_countslave_parallel_type=logical_clockslave_parallel_workers=8slave_preserve_commit_order...

文章标题:MySQL5.6升级到5.7
文章链接:https://www.dianjilingqu.com/4353.html
本文章来源于网络,版权归原作者所有,如果本站文章侵犯了您的权益,请联系我们删除,联系邮箱:saisai#email.cn,感谢支持理解。
THE END
< <上一篇
下一篇>>