笔记：mysql升级 5.6至5.7

说明

一台已有数据的机器，停机升级，从MySQL Server5.6.48，升级到 5.7.38。

环境介绍

10.24.10.247，Mysql 5.6.48
CentOS Linux release 7.9.2009 (Core)
32G内存、500G数据盘/home；
实际数据量约120M，2个logfile共8G；
basedir = /home/mysql/mysql-5.6.33
datadir = /home/mysql/mysql-5.6.33/data

升级至 5.7.38
MySQL数据与软件分离：
basedir = /usr/local/mysql
datadir = /home/mysql/data
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

操作流程

以下步骤如无特殊说明，均使用root用户操作。

• 事前检查：两个节点上，操作系统版本、位数；防火墙、selinux；磁盘容量；
• 记录与备份信息：MYSQL软件与数据路径；my.cnf，/etc/init.d/mysqld，etc/profile
• 获取并测试my.cnf，确认不兼容参数；确认这台机器上是否还有其他的应用或者服务，是否受升级影响；
• 逻辑与物理备份，停止MYSQL 5.6服务；
• 准备配置文件；
• 安装MYSQL5.7；
• 将5.6/data复制回来5.7，升级；
• 检查进程与状态。

实施步骤

事前检查

• 两个节点上，操作系统版本、位数；
• 防火墙、selinux；
• 磁盘容量；
• 记录与备份信息；

操作系统版本、位数：

[root@CentOS79 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@CentOS79 ~]# getconf LONG_BIT
64

关闭防火墙、selinux

[root@CentOS79 ~]# setenforce 0
[root@CentOS79 ~]# vi /etc/sysconfig/selinux
SELINUX=disabled
[root@CentOS79 ~]# systemctl stop firewalld
[root@CentOS79 ~]# systemctl disable firewalld

查看磁盘容量

[root@CentOS79 ~]# df -h
文件系统                 容量  已用  可用 已用% 挂载点
devtmpfs                  16G     0   16G    0% /dev
tmpfs                     16G  200K   16G    1% /dev/shm
tmpfs                     16G  1.6G   15G   10% /run
tmpfs                     16G     0   16G    0% /sys/fs/cgroup
/dev/mapper/centos-root  450G  1.1G  449G    1% /
/dev/mapper/centos-usr    50G  8.1G   42G   17% /usr
/dev/sda1               1014M  232M  783M   23% /boot
/dev/sda2                300M  7.9M  292M    3% /boot/efi
/dev/mapper/centos-home  465G   14G  452G    3% /home
/dev/mapper/centos-var    10G  907M  9.2G    9% /var
tmpfs                    3.2G   12K  3.2G    1% /run/user/42
tmpfs                    3.2G     0  3.2G    0% /run/user/0

记录与备份信息
MYSQL软件与数据路径；

my.cnf，/etc/init.d/mysqld，etc/profile
basedir = /home/mysql/mysql-5.6.33
datadir = /home/mysql/mysql-5.6.33/data

获取并测试my.cnf，确认不兼容参数；
mysql5.6 /etc/my.cnf
相关参数调整见后续步骤。

[client]
port = 3306
socket = /home/mysql/mysql-5.6.33/mysql.sock[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
socket = /home/mysql/mysql-5.6.33/mysql.sock[mysqld]
########basic settings########
server_id = 1
port = 3306
basedir = /home/mysql/mysql-5.6.33
datadir = /home/mysql/mysql-5.6.33/data
socket = /home/mysql/mysql-5.6.33/mysql.sock
pid_file = /home/mysql/mysql-5.6.33/mysql.pid
autocommit = 1
character_set_server=utf8mb4
skip_name_resolve
max_connections = 2000
max_connect_errors = 2000
lower_case_table_names = 1
back_log = 200
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /home/mysql/mysql-5.6.33/tmp
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER"
interactive_timeout = 28800
wait_timeout = 28800
max_user_connections = 0
skip-external-locking
read_buffer_size = 64M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
#########log settings########
log_output = FILE
log_error = error.log
slow_query_log = 1
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 15
long_query_time = 2
min_examined_row_limit = 100
#########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
log_bin = master_bin
#binlog-ignore-db=mysql
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
binlog_format = row
#relay_log = relay3306.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors#########innodb settings########
#按服务器实际内存大小调整
innodb_buffer_pool_size = 8G
#innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 600
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /home/mysql/mysql-5.6.33/data/
innodb_undo_directory = /home/mysql/mysql-5.6.33/data/
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 4G
innodb_log_buffer_size = 16M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_max_dirty_pages_pct = 75
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1

逻辑与物理备份，停止MYSQL 5.6服务
逻辑与物理备份:

mysqldump -u root -p -S /home/mysql/mysql-5.6.33/mysql.sock --add-drop-table --routines --events --all-databases --force > /home/mysql/bc/mysql56.sql
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

停止MYSQL 5.6服务:

systemctl stop mysqld
systemctl disable mysqld
mv /etc/init.d/mysqld /home/bc/

4.4准备配置文件
/etc/my.cnf
修改

basedir =/usr/local/mysql 和其他dir;
log_error = /home/mysql/error.log
slow_query_log_file = /home/mysql/slow.log
注释：
pid_file = /home/mysql/mysql.pid
tmpdir = /home/mysql/tmp
innodb_additional_mem_pool_size = 32M：

4.5安装MYSQL5.7

创建数据目录

mkdir -p /home/mysql/data
chown -R mysql:mysql /home/mysql
chmod 750 /home/mysql

tar -zxvf /software/mysql-5.7.38-linux-glibc2.12-x86_64 -C /usr/local
cd /usr/local
ln -s mysql-5.7.38-linux-glibc2.12-x86_64 mysql
chown -R mysql:mysql mysql
chmod -R 750 mysql

安装MYSQL：

cd /usr/local/mysql
./bin/mysqld --initialize --user=mysql

记录error.log中的初始密码；
添加路径：

vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
source /etc/profile

启动MYSQL:

bin/mysqld_safe --user=mysql & 

确认配置文件中存在以下参数：

[mysqld]
federated
skip-external-locking
skip-name-resolve

修改root密码：

set password for root@localhost = password('A_isino#888'); 
flush privileges;
shutdown;

4.6将5.6/data复制回来5.7，升级

cp -rp /home/bc/mysql-5.6.33/data/* /home/mysql/data/

删除/home/mysql/data/中的error.log\slow.log；
后台启动MYSQL

cd /usr/local/mysql
nohup ./bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql >/dev/null 2>&1 &

./bin/mysql_upgrade -u root -p -S /home/mysql/mysql.sock