MySQL 升级最佳实践 --- MySQL 5.6升级至MySQL 8.0
背景介绍
MySQL 8.0 新特性
- 数据字典更改
- MySQL Server 8.0 合并了一个全局数据字典，其中包含有关事务表中数据库对象的信息。在mysql8之前的 MySQL 系列中，字典数据存储在元数据文件和非事务性系统表中
- 安全和帐户管理
- mysql系统数据库中的授权表现在是InnoDB(事务性)表。在MySQL8之前是MyISAM(非事务性)表
- 身份验证插件
- caching_sha2_password实现 SHA-256 密码哈希,使用缓存来解决连接时的延迟问题
- caching_sha2_password是MySQL8.0的首选身份验证插件，也是默认的身份验
- 配置更改
- 不兼容的更改：
- MySQL存储引擎现在负责提供自己的分区处理程序，MySQL服务器不再提供通用分区支持。InnoDB是唯一提供MySQL 8.0支持的原生分区处理程序的存储引擎。在升级服务器之前，必须修改使用任何其他存储引擎的分区表：要么将其转换为InnoDB，要么删除其分区否则以后就不能使用它
- 从MySQL 8.0.11开始，禁止使用与初始化服务器时不同的lower_case_table_names设置启动服务器
- 重要更改：
- 默认字符集已从 更改 latin1为utf8mb4。这些系统变量会受到影响：
- character_set_server 和 系统变量 的默认值 character_set_database 已从 latin1更改为utf8mb4
- collation_server和 系统变量 的默认值 collation_database 从latin1_swedish_ci更改为 utf8mb4_0900_ai_ci
- 服务器更改
- 在 MySQL 8.0.11 中，删除了与帐户管理相关的几个已弃用的功能，例如使用 GRANT语句修改用户帐户的非特权特性、 NO_AUTO_CREATE_USER的SQL_Mode、 PASSWORD()函数和 old_passwords系统变量
- InnoDB 更改
- Auto-Increment
- 服务器重新启动不再取消 AUTO_INCREMENT = N 表选项的影响
- 服务器在操作后立即重新启动 ROLLBACK 不再导致重用分配给回滚事务的自动增量值
- A new dynamic variable
- innodb_deadlock_detect可用于禁用死锁检测,在高并发系统上,当大量线程等待同一个锁时，死锁检测会导致速度减慢。innodb_lock_wait_timeout 有时禁用死锁检测并依赖发生死锁时事务回滚 的设置可能更有效
- InnoDB现在在共享临时表空间中创建了临时表， ibtmp1.
- SQL 更改
- 不兼容的更改:
- 从MySQL 8.0.13开始，已弃用的GROUP BY子句ASC或DESC等限定符已被删除。以前依赖于GROUP BY排序的查询可能产生与以前MySQL版本不同的结果。要生成给定的排序顺序
- JSON特性增强，支持窗口函数，支持Hash join
- 原子数据定义语句（Atomic DDL）
- 原子 DDL 语句将与 DDL 操作关联的数据字典更新、存储引擎操作和二进制日志写入组合到单个原子事务中
- 会话重用
- MySQL 服务器现在默认支持 SSL 会话重用，并通过超时设置来控制服务器维护会话缓存的时间，该会话缓存建立允许客户端请求新连接的会话重用的时间段，所有 MySQL 客户端程序都支持会话重用
MySQL 8.0 中弃用的选项和变量
- query_cache_type: 查询缓存类型。在 MySQL 8.0.3 中删除
- query_cache_size：分配的内存用于存储旧查询的结果。在 MySQL 8.0.3 中删除
- query_cache_limit：不要缓存大于此的结果。在 MySQL 8.0.3 中删除
- date_format：日期格式 在 MySQL 8.0.3 中删除
- datetime_format: DATETIME/TIMESTAMP 格式 在 MySQL 8.0.3 中删除
推荐升级路线：
- 支持从 MySQL 5.7 升级到 8.0。但是，仅在通用 (GA) 版本之间支持升级。对于 MySQL 8.0，需要从 MySQL 5.7 GA 版本（5.7.9 或更高版本）升级。不支持从 MySQL 5.7 的非 GA 版本升级。
- 建议在升级到下一个版本之前升级到最新版本。例如，在升级到 MySQL 8.0 之前升级到最新的 MySQL 5.7 版本。
- 不推荐：不支持跳过版本的升级(in-place)，例如：不支持直接从 MySQL 5.6 升级到 8.0
MySQL升级过程升级做了什么
- mysql系统模式：其中包含存储 MySQL 服务器运行时所需信息的表,mysql模式表分为两大类：
- 数据字典表，存储数据库对象元数据。
- 用于其他操作目的的系统表（即剩余的非数据字典表）。
- 其他模式：其中一些是内置的，可能被服务器视为“拥有”，另一些模式如下：
- 性能架构、 INFORMATION_SCHEMA、 ndbinfo和 sys架构。
- User schemas
升级前检查
- 不得存在以下问题：
- 不得有使用过时数据类型或函数的表。如果表包含 5.6.4 之前格式的 旧时间列（不支持小数秒精度的列）TIME 升级前进行修改
- 不能有孤立.frm文件
- 触发器不能有缺失的或空的定义器或无效的创建上下文( SHOW TRIGGERS，INFORMATION_SCHEMA TRIGGERS进行查看)
- 要检查以上这些问题，请执行以下命令： 返回结果如果全部都是OK, 就表示检查没问题
root@xgsdk-dev-mysql-1:~#mysqlcheck -u root -p --all-databases --check-upgrade 进行查看
- 检查是否有分区表存在并查看分区表的属性是否满足mysql8
- sql_mode支持问题：8.0版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中带有NO_AUTO_CREATE_USER
- 密码认证插件变更为caching_sha2_password，
- 注意:如果不想修改认证插件的话 需在配置文件中指定default_authentication_plugin=mysql_native_password
- 如果打算在升级时将lower_case_table_names设置更改为1，请在升级前确保模式名和表名为小写，否则的话 可能会由于模式或表名字母大小写不匹配而导致失败 ，查询表名字母大小写 命令如下:
1. [[email protected]][(none)]> select TABLE_NAME, if(sha(TABLE_NAME) !=sha(lower(TABLE_NAME)),'Yes','No') as UpperCase from information_schema.tables;
- 主从架构参数GTID
- 源库和目标库GTID得模式不一致得话 是不能进行部署主从同步得 ，start slave时报错如下：
- The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON (Master:关闭GTID，Slave：开启GTID)
- The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF. (Master:开启GTID，Slave：关闭GTID)
- Slave_IO_Running: No
- 查询源库存储过程，函数，event，view等
问题与注意
- 部署增量同步架构注意：
- mysql8.0和mysql8之前 部署主从的时候 在源库要按照mysql8.0的方式进行创建同步用户不然主从同步会报错数据不一致
- 程序链接 mysql8.0 时：
- 报错: Public Key Retrieval is not allowed 错误的时候，我们可以在连接数据库的配置文件中加上
- 处理: 推荐使用 第一种方式解决：
- 连接串加上 allowPublicKeyRetrieval=true 如下图
- 代码侧链接MySQL8时的驱动问题
- mysql8.0 需要驱动使用 8.0对应连接的驱动，springboot2.0+版本自动集成了8.0+版本，只需要对应依赖正确即可
- 高版本的默认jdbc驱动类从 com.mysql.jdbc.Driver 改成 com.mysql.cj.jdbc.Driver 虽然springboot 启动不会影响，但是会提示warning
- 加载驱动与连接数据库方式如下：
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?useSSL=false&allowPublicKeyRetrieval=true&serve- MySQL8配置文件修改lower_case_table_names 后 重启systemctl restart mysql值后无法登录
- 报错信息:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock' (2)
解决： 将lower_case_table_names 修改为在初始化时候设置的数值
原因：在mysql8进行初始化时 配置文件指定了lower_case_table_names=1后就不能在修改lower_case_table_names这个值了 因为各种数据字典表字段使用的排序规则基于 lower_case_table_names 服务器初始化时定义的设置，并且使用不同的设置重新启动服务器会在标识符的排序和比较方式方面引入不一致
升级方案：
MySQL升级中，基本有三种方案可供参考 如下图：
具体升级过程
业务需求：
- 下面以Linux系统为例，展示下具体升级过程。我的系统是Ubuntu 20.04.3 ，源端是MySQL5.6.29，需要升级到目标端MySQL8.0.23
- 因要把A，B 2个业务合并到一个MySQL实例中，需采用多源复制和在线过滤方式进行同步数据：其中在线过滤的方式(只同步业务库)进行增量数据的同步操作(information_schema|mysql|sys|performance_schema|test) 这些系统库不同步
升级方案
- 升级链路
- 采用 MySQL5.6 >>> MySQL5.7 >>> MySQL8.0 Logical Upgrade+级联复制 方式进行升级操作以便于更好的兼容
- 升级方式
- 连夸大版本升级推荐采用 Logical Upgrade 搭建高版本Slave角色的方式进行做增量同步
- mysqldump(如果数据量小可用这个)
- mydumper(如果数据量较大推荐使用)
升级链路图：
回滚方案
- 对比源库和新库的数据是否一致
- 记录切换到新slave的时间点，提前准备binlog 回滚脚本，进行binlog针对相应db分析出新增数据手动同步到源库。
下载解压安装包
下载transfer 机器的mysql5.7.39和目标端 mysql8.0.23 的安装包后在对应机器 进行自行安装MySQL
更改配置文件my.cnf
因8.0之前的版本与8.0版本参数有所不同，为了能顺利升级，我们需要更改部分配置参数。主要注意sql_mode、basedir、密码认证插件及字符集设置，其他参数最好还是按照原5.7的来，不需要做调整。下面展示下更改后的配置文件：
root@ubuntu20-171:mysql# cat my3306.cnf
[client]
port = 3306
socket = /data/mysql/mysql_3306/data/mysql.sock
[mysqld]
#--------dir--------------
port = 3306
socket = /data/mysql/mysql_3306/mysql.sock
pid-file = /data/mysql/mysql_3306/mysql.pid
datadir = /data/mysql/mysql_3306
tmpdir = /data/mysql/mysql_3306
lower_case_table_names = 1
#--------network--------------
back_log = 1000
max_connections = 16384
max_user_connections = 16000
wait_timeout = 86400
interactive_timeout = 86400
max_connect_errors = 1000000
max_allowed_packet = 64M
#--------character--------------
character-set-server = utf8mb4
collation_server = utf8mb4_unicode_ci
default_authentication_plugin=mysql_native_password
log_timestamps = "SYSTEM"
#--------Miscellaneous--------------
auto_increment_increment = 1
auto_increment_offset = 1
#sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
performance_schema = off
skip-external-locking
skip-name-resolve
key_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
join_buffer_size = 2M
table_open_cache = 16384
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M
#--------engine--------------
default-storage-engine = InnoDB
#--------innodb--------------
innodb_autoextend_increment = 256
innodb_data_file_path = ibdata1:128M:autoextend
innodb_lock_wait_timeout = 60
innodb_buffer_pool_size = 2G
innodb_max_dirty_pages_pct = 45
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 32
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_log_file_size = 1G #25% *buffer pool size (1G)
innodb_log_buffer_size = 96M
#--------log--------------
long_query_time = 1
log-error = /data/mysql/mysql_3306/err.log
slow_query_log = 1
slow_query_log_file = /data/mysql/mysql_3306/slow.log
log_queries_not_using_indexes = 0
#--------binlog--------------
log_slave_updates = 1
expire_logs_days = 7
binlog_format = row
log-bin = /data/mysql/mysql_3306/MySql-bin
relay-log = /data/mysql/mysql_3306/relay-bin
max-binlog-size = 1024M
log_bin_trust_function_creators = 1
sync_binlog = 600
server-id = 983306
gtid-mode = on
enforce-gtid-consistency = true
binlog_cache_size = 2M
#--------replication--------------
#replicate-ignore-db = test
#replicate-ignore-db = information_schema
master-info-repository = TABLE
relay-log-info-repository = TABLE
slave-parallel-workers = 4
slave_preserve_commit_order = 1
slave_parallel_type = logical_clock
binlog-checksum = CRC32
master-verify-checksum = 1
slave-sql-verify-checksum = 1
binlog-rows-query-log-events = 1
#suppression of duplicate-key and no-key-found errors
#slave_exec_mode = IDEMPOTENT
# perforamnce_schema settings
performance_schema=on
performance-schema-instrument = 'memory/%=COUNTED'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096
[mysqldump]
quick
max_allowed_packet = 64M
[myisamchk]
key_buffer = 40M
sort_buffer_size = 40M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_multi]
mysqld=mysqld_safe
[mysql]
prompt = [\u@\p][\d]>\_
no-auto-rehash
搭建高版本主从同步进行实时同步操作
- mysql56 >>>mysql57 多源复制+在线过滤操作：
在mysql5.7 transfer机器上远程备份源端mysql56数据
root@mysql57-transfer-01:# mydumper -u root -p 123456 -h 10.96.0.198 -P 3307 -t 8 -e --regex '^(?!(information_schema|mysql|sys|performance_schema|test))' -o /data/backup/all_back --logfile=/data/backup/a-bak.log --compress --verbose=3 --skip-tz-utc >/data/backup/a-all.log
在mysql5.7 transfer机器上 进行恢复操作：
root@mysql57-transfer-01:/data/backup# cat metadata
Started dump at: 2023-02-03 15:24:07
SHOW MASTER STATUS:
Log: MySql-bin.000106
Pos: 690832633
GTID:17b49cbc-3417-11ed-8bdc-fa163e7b0b14:1-4651395,
92153826-4cf8-11e7-8e1b-fa163e10dbc0:617883504-618181406
登录mysql57-transfer 初始化GTID
root@mysql57-transfer-01:# mysql --user=root --password=123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] option 'port': value --prompt=\u@ \d> adjusted to 0
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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.
[[email protected]][(none)]> show master status \G;
[[email protected]][(none)]> show master status\G;
*************************** 1. row ***************************
File: mysql_bin.000001
Position: 3919
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
设置最新的GTID 编号
[[email protected]][(none)]> reset master;
[[email protected]][(none)]> SET @@GLOBAL.GTID_PURGED='17b49cbc-3417-11ed-8bdc-fa163e7b0b14:1-4651395,92153826-4cf8-11e7-8e1b-fa163e10dbc0:617883504-618181406'
[[email protected]][(none)]> show master status\G;
*************************** 1. row ***************************
File: MySql-bin.000005
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 17b49cbc-3417-11ed-8bdc-fa163e7b0b14:1-4651395,
92153826-4cf8-11e7-8e1b-fa163e10dbc0:617883504-618181406
执行恢复操作：
root@mysql57-transfer-01:# myloader -u root -p 123456 -P 3306 -t 8 -o -d /data/backup/all_back -v 3 >>/data/backup/dumper.log
执行成功后 进行master>>slave 关系构建
使用多源复制+在线过滤复制进行DB的合并操作：
[[email protected]][(none)]> CHANGE MASTER TO
-> MASTER_HOST='127.x.x.x',
-> MASTER_PORT=3307,
-> MASTER_USER='repl_user',
-> MASTER_PASSWORD='repl_user',
-> master_auto_position=1
-> for channel 'a';
设置过滤复制忽略的dbname
[[email protected]][(none)]> CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(mysql,information_schema,performance_schema,sys,test);
[[email protected]][(none)]> change replication filter Replicate_Wild_Ignore_Table=('mysql.%','information_schema.%','performance_schema.%','sys.%','test.%');
[[email protected]][(none)]> start slave; ## 启动
[[email protected]][(none)]> show slave status \G; #### 查看主从状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.x.x.x
Master_User: repl_user
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 204082752
Relay_Log_File: relay-bin-3307.000003
Relay_Log_Pos: 17979
Relay_Master_Log_File: mysql-bin.000023
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys,test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,sys.%,test.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 204082752
Relay_Log_Space: 18447
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: 200107
Master_UUID: 86179ae1-3b26-11ed-870c-52540071e14c
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 86179ae1-3b26-11ed-870c-52540071e14c:133090-133112
Executed_Gtid_Set: 6066ae53-6405-11ed-9554-525400ad92bb:1-3,
86179ae1-3b26-11ed-870c-52540071e14c:1-133112,
8f6fc968-6499-11ed-a9b1-52540071e14c:1-22
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: 3307
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
- mysql57-transfer >>> mysql8 同步数据: 和以上操作同理进行操作
切换迁移
在源库MySQL56进行 查询 没有process后操作 锁库切换
[[email protected]][(none)]> show processlist;
+----+-----------------+-----------------+------+---------+---------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+---------+--------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 7344390 | Waiting on empty queue | NULL |
| 57 | system user | connecting host | NULL | Connect | 7261228 | Waiting for master to send event | NULL |
| 58 | system user | | NULL | Query | 6655951 | Slave has read all relay log; waiting for more updates | NULL |
| 59 | system user | | NULL | Connect | 7261228 | Waiting for an event from Coordinator | NULL |
| 60 | system user | | NULL | Connect | 6656238 | Waiting for an event from Coordinator | NULL |
| 66 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------------+------+---------+---------+--------------------------------------------------------+------------------+
6 rows in set (0.00 sec)
mysql>
全局锁：
[[email protected]][(none)]> flush tables with read lock;
解锁
[[email protected]][(none)]> unlock tables; ### 程序切换到mysql8.0后 在源库 解锁
总结：
至此，我们的数据库由5.6成功升级至8.0。 其实升级过程并不复杂，复杂的是升级前的检查以及升级后的验证及兼容测试，特别是对于复杂的业务库，MySQL版本升级还是要小心的。真实环境 一定要在dev环境验证无误后再逐步对生产环境业务库进行升级！