笔记：mysql 5.7 小版本升级

说明

环境：CentOS Linux release 7.9.2009 (Core)
数据库版本：Mysql 5.7.38
升级版本：Mysql 5.7.44

升级步骤

1，安装新版本数据库
在需要升级的数据库服务器上安装Mysql 5.7.44，并制定新的数据库端口，如源数据库使用3306，新数据库使用3307（升级完成后再改）

安装包：mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

tar -xvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.44-linux-glibc2.12-x86_64 /data/mysql-5.7.44
mkdir /data/mysql-5.7.44/data
mkdir /data/mysql-5.7.44/log
chown -R mysql.mysql mysql-5.7.44

2，创建配置文件

[mysqld]
port=3307
server-id=1
basedir=/data/mysql-5.7.44
datadir=/data/mysql-5.7.44/data
socket=/data/mysql-5.7.44/log/mysql.sock
max_connections = 1000
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
interactive_timeout=86400
wait_timeout=86400

skip-external-locking
key_buffer_size= 128M
max_allowed_packet=32M
query_cache_size=32M
read_buffer_size=2M
sort_buffer_size=1M
join_buffer_size= 128M

innodb_file_per_table= 1
innodb_open_files= 5000
innodb_buffer_pool_size= 8G ##物理内存的80%
innodb_write_io_threads= 16
innodb_read_io_threads= 16
innodb_thread_concurrency = 0
innodb_purge_threads= 1
innodb_flush_log_at_trx_commit= 2
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group= 5
innodb_max_dirty_pages_pct= 90
innodb_lock_wait_timeout= 120
bulk_insert_buffer_size= 64M
myisam_sort_buffer_size=64M
myisam_max_sort_file_size= 10G
myisam_repair_threads= 1 ##5.7.39 5.7.42已经没有该参数
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
binlog_format=row
log-bin=/data/mysql-5.7.44/log/binlog.bin
slow_query_log=on
slow_query_log_file=/data/mysql-5.7.44/log/slow-query.log
long_query_time=1

log_queries_not_using_indexes=on
log-error=/data/mysql-5.7.44/log/mysql-err.log
binlog_cache_size=4MB
skip-host-cache
skip-name-resolve
expire_logs_days=15
skip-slave-start
relay-log-index=/data/mysql-5.7.44/log/slave-relay-bin.index
relay-log=/data/mysql-5.7.44/log/relaylog-binlog
replicate-ignore-db=information_schema,performance_schema,sys
slave_net_timeout=60
early-plugin-load=""
explicit_defaults_for_timestamp=true
log_slave_updates=1
gtid_mode=ON
enforce_gtid_consistency = ON
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

##secure_file_priv参数
secure_file_priv=/data/mysql-5.7.44/secure_file

##启用ssl连接，客户端需要下载如下三个文件才能通过ssl连接
ssl-ca=/data/mysql-5.7.44/ca/ca.pem
ssl-cert=/data/mysql-5.7.44/ca/client-cert.pem
ssl-key=/data/mysql-5.7.44/ca/client-key.pem

##审计,需要安装审计插件才可用
server_audit_logging=on
server_audit_file_path =/data/mysql-5.7.44/audit
server_audit_file_rotate_size=524288000
server_audit_file_rotations=256
server_audit_file_rotate_now=ON
server_audit_events='connect,table,query_ddl,query_dcl,query_dml_no_select';

##开启使用dblink
federated

  ##关闭ssl,默认是开启的
  skip_ssl




[client]
port = 13306
socket=/data/mysql-5.7.44/log/mysql.sock
default-character-set = utf8mb4

[mysqldump]
quick
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

3，初始化数据库*

./mysqld --initialize --user=mysql --basedir=/data/mysql-5.7.44 --datadir=/data/mysql-5.7.44/data --lc_messages_dir=/data/mysql-5.7.44/share --lc_messages=en_US

4，启动数据库服务*

mysqld_safe --defaults-file=/data/mysql-5.7.44/my_3307.cnf --user=mysql &

5，关闭新库

mysqladmin -h localhost -uroot -P123456 -p shutdown -S /data/mysql-5.7.44/log/mysql.sock

6，删除新库data目录

cd /data/mysql-5.7.44
mv data data_bak
mkdir data

7，停老库并拷贝data文件到新库data下，注意权限
8，启动新库

mysqld_safe --defaults-file=/data/mysql-5.7.44/my_3307.cnf --user=mysql &

9，升级

mysql_upgrade -s -h localhost -p123456 -P3307 -S /data/mysql-5.7.44/log/mysql.sock

10，新库信息更新

• 端口号
• 配置文件
• binlog.index