1.升级调研
#基本特点
默认字符集由latin1变为utf8mb4。
MyISAM系统表全部换成InnoDB表。
JSON特性增强。
支持不可见索引,支持直方图。
sql_mode参数默认值变化。
默认密码策略变更。
新增角色管理。
支持窗口函数,支持Hash join。
###对应方式
1)数据库字典升级
schema,mysql,information_schema,performance_schema,sys
ps:密码测试 mysql_native_password → caching_sha2_password
2)关键词是不是兼容[(官网确认)](https://dev.mysql.com/doc/refman/8.0/en/keywords.html)
关键词 added in查询
3)sql是不是兼容(Group by处理上的不兼容,触发器,存储过程)
mysql5.6 可以执行select id,count(*)from group by name;
mysql5.7,8.0是不是允许的 sql_mode控制
4)数据文件存储格式是不是可以直接升级
Perconal 和 mysql 存储引擎一直,可以完全兼容
5)现有应用的兼容性是否满足
自定义函数,一些不规范的SQL语句等等
6)MySQL 5.7 系统数据库中不得有与 MySQL 8.0 数据字典使用的表同名的表
7)密码策略等
2.升级wbs
1. mysql测试库升级,局部应用验证
2. mysql数据库升级,末知问题发生验证
3. 数据库my.cnf主配置文件信息调整
4. 不兼容的操作方法,影响复制
5. 平稳的过滤,数据库主从,可先升级一个从库,然后到主库
6. 停机时间评估,生产数据恢复到环境,模拟升级测试
7. 数据验证:行数,表的数量,库,等
8. 回滚方案计划
9. 数据库备份
3.升级检查器
升级检查器实用程序不支持检查MySQL 5.7之前版本的MySQL服务器实例
【升级信息检查mysql-shell】
升级检查器,以下内容都不会存在提示:
1. basedir,
2. sql_mode ,
3. 半同步配置,
4. 密码策略:default_authentication_plugin = mysql_native_password
4.测试升级内容
# 二进制安装,安装包上传到之前安装的目录下 ,一般是/usr/local/
cd /usr/local/
#解压安装包
xz -d mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
tar -xvf mysql-8.0.20-linux-glibc2.12-x86_64.tar
# 文件夹重命名为mysql8.0
mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql8
# 目录文件授权
chown -R mysql.mysql /usr/local/mysql8.0/
配置参数调整:
注意sql_mode、basedir、密码认证插件及字符集设置
# 配置参数修改
[mysqld]
user = mysql
datadir = /data/mysql/data
port = 3306
socket = /data/mysql/tmp/mysql.sock
pid-file = /data/mysql/tmp/mysqld.pid
tmpdir = /data/mysql/tmp
skip_name_resolve = 1
max_connections = 2000
group_concat_max_len = 1024000
lower_case_table_names = 1
log_timestamps=SYSTEM
max_allowed_packet = 32M
binlog_cache_size = 4M
sort_buffer_size = 2M
read_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 96M
max_heap_table_size = 96M
max_length_for_sort_data = 8096
default_time_zone = '+8:00'
#logs
server-id = 1003306
log-error = /data/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 3
log-bin = /data/mysql/logs/binlog
binlog_format = row
log_bin_trust_function_creators = 1
gtid_mode = ON
enforce_gtid_consistency = ON
#for8.0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
character-set-server = utf8
collation_server = utf8_general_ci
basedir = /usr/local/mysql8
skip_ssl
default_authentication_plugin=mysql_native_password
数据库备份
数据库升级
# 查看数据库版本(进入mysql5.7)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
# 确保数据都刷到硬盘上,更改成0
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
#关闭数据库
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
# mysql8.0.20客户端启动
/usr/local/mysql8.0/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
# 确认日志,登录测试
[root@centos ~]# mysql -uroot -pzeny
mysql: [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 17
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2018, 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() |
+-----------+
| 8.0.20 |
+-----------+
1 row in set (0.00 sec)
系统启动环境变量配置
# 修改mysql服务启动项配置
vim /etc/init.d/mysql
# 修改basedir目录
basedir=/usr/local/mysql8.0
# 修改PATH变量
vim /etc/profile
# 将PATH中的/usr/local/mysql/bin改为/usr/local/mysql8.0/bin
# 重载配置生效
source /etc/profile
#路径查看
which mysql
#版本确认
mysql -V
添加system管理文件
cat >/etc/systemd/system/mysqld8.0.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql8.0/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql
LimitNOFILE = 5000
EOF