1、升级前准备

1.1、检查主机软件依赖环境正常

检查主机软件依赖环境，如果缺少依赖，需要先解决，不然在升级Mysql的过程中可能会由于缺少依赖而无法升级。
/usr/bin/yum -d 0 -e 0 check dependencies

1.2、查看当前Mysql版本

mysql -V
mysql -uroot -p123456 -e “select version();”
rpm -qa | grep -i mysql
slave：

master：

1.3、准备Mysql版本RPM包

在两台Mysql Ha主机上执行
解压mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar到/home/ocdc/upgrade/mysql
目录下
mkdir -p /home/ocdc/upgrade/mysql && tar -xf mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar -C /home/ocdc/upgrade/mysql

2、升级备节点

2.1、停止备节点Slave

备节点查看同步情况：
show slave status\G;

备节点停止slave
stop slave;

主节点slave状态查看：

2.2、停止备节点keepalived

停止keepalived
sudo systemctl stop keepalived

2.3、备份数据库及配置文件

mysqldump -h10.25.176.90 -uroot -p123456 —set-gtid-purged=OFF —databases ambari —triggers —routines —events > /home/ocdc/upgrade/ambari_20201125.sql
mysqldump -h10.25.176.90 -uroot -p123456 —set-gtid-purged=OFF —databases hive_test —triggers —routines —events > /home/ocdc/upgrade/hive_test_20201125.sql
mysqldump -h10.25.176.90 -uroot -p123456 —set-gtid-purged=OFF —databases ranger —triggers —routines —events > /home/ocdc/upgrade/ranger_20201125.sql
sudo cp /etc/my.cnf /etc/my.cnf.bak
sudo cp /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service.bak

2.4、记录数据库记录

记录ambari库下部分表记录，用于升级后记录数核对：
select count() from ambari.execution_command;
select count() from ambari.alert_history;
select count() from ambari.host_role_command;
select count() from ambari.serviceconfigmapping;
select count() from ambari.clusterconfig;
select count() from ambari.stage;

2.5、设置清空缓存参数

设置参数使数据库关闭时会清空缓存避免版本间缓存格式差异：
mysql -u root -p123456 —execute=”SET GLOBAL innodb_fast_shutdown=0”

2.6、停止Mysql

sudo service mysqld stop && sudo service mysqld status

2.7、升级RPM包

cd /home/ocdc/upgrade/mysql && rm -f mysql-community-test-5.7.31-1.el7.x86_64.rpm
sudo rpm -Uvh mysql-community-*.rpm

2.8、启动Mysql

检查配置文件内容：
cat /etc/my.cnf
启动Mysql：
sudo service mysqld start
sudo service mysqld status

2.9、更新Mysql

sudo mysql_upgrade -uroot -p123456

2.10、重启Mysql

sudo service mysqld restart
sudo service mysqld status
重启后发现slave自动被拉起来：
show slave status\G;

2.11、启动keepalived

sudo systemctl start keepalived
sudo systemctl status keepalived

2.12、验证Mysql升级完成

rpm -qa |grep -i mysql
mysql -V
mysql -uroot -p123456 -e “select version();”

2.13、核对数据

记录ambari库下部分表记录，和master及升级前的记录数进行核对：
select count() from ambari.execution_command;
select count() from ambari.alert_history;
select count() from ambari.host_role_command;
select count() from ambari.serviceconfigmapping;
select count() from ambari.clusterconfig;
select count() from ambari.stage;
变更的数据库内容自动同步成功：

2.14、数据写入测试

通过备节点写入表数据，查看主表的同步情况：
备节点：

主节点查看数据被同步

3、升级主节点

3.1、停止主节点keepalived

停止主节点keepalived，让浮动IP进行切换
sudo systemctl stop keepalived

3.2、停止主节点Slave

主节点查看同步情况：
show slave status\G;

主节点停止slave
stop slave;

切换后的主节点slave状态查看：

3.3、备份数据库及配置文件

mysqldump -h10.25.176.91 -uroot -p123456 —set-gtid-purged=OFF —databases ambari —triggers —routines —events > /home/ocdc/upgrade/ambari_20201125.sql
mysqldump -h10.25.176.91 -uroot -p123456 —set-gtid-purged=OFF —databases hive_test —triggers —routines —events > /home/ocdc/upgrade/hive_test_20201125.sql
mysqldump -h10.25.176.91 -uroot -p123456 —set-gtid-purged=OFF —databases ranger —triggers —routines —events > /home/ocdc/upgrade/ranger_20201125.sql
sudo cp /etc/my.cnf /etc/my.cnf.bak
sudo cp /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service.bak

3.4、记录数据库记录

记录ambari库下部分表记录，用于升级后记录数核对：
select count() from ambari.execution_command;
select count() from ambari.alert_history;
select count() from ambari.host_role_command;
select count() from ambari.serviceconfigmapping;
select count() from ambari.clusterconfig;
select count() from ambari.stage;

3.5、设置清空缓存参数

设置参数使数据库关闭时会清空缓存避免版本间缓存格式差异：
mysql -u root -p123456 —execute=”SET GLOBAL innodb_fast_shutdown=0”

3.6、停止Mysql

sudo service mysqld stop && sudo service mysqld status

3.7、升级RPM包

cd /home/ocdc/upgrade/mysql && rm -f mysql-community-test-5.7.31-1.el7.x86_64.rpm
sudo rpm -Uvh mysql-community-*.rpm

3.8、启动Mysql

检查配置文件内容：
cat /etc/my.cnf
启动Mysql：
sudo service mysqld start && sudo service mysqld status

3.9、更新Mysql

sudo mysql_upgrade -uroot -p123456

3.10、重启Mysql

sudo service mysqld restart && sudo service mysqld status
重启后发现slave自动被拉起来：
show slave status\G;

3.11、启动keepalived

sudo systemctl start keepalived && sudo systemctl status keepalived

3.12、验证Mysql升级完成

rpm -qa |grep -i mysql
mysql -V
mysql -uroot -p123456 -e “select version();”

3.13、核对数据

记录ambari库下部分表记录，和master及升级前的记录数进行核对：
select count() from ambari.execution_command;
select count() from ambari.alert_history;
select count() from ambari.host_role_command;
select count() from ambari.serviceconfigmapping;
select count() from ambari.clusterconfig;
select count() from ambari.stage;
变更的数据库内容自动同步成功：

3.14、数据写入测试

通过备节点写入表数据，查看主库的同步情况：
备节点：
insert into ambari.test values(66666,188888);
select from ambari.test where id=66666;

主节点查看数据被同步
select from ambari.test where id=66666;

4、切换虚拟IP测试

4.1、重启主节点keepalived，让浮动IP进行切换

sudo systemctl restart keepalived && sudo systemctl status keepalived

虚拟IP切换成功

4.2、数据写入测试

4.2.1、通过备节点写入表数据，查看主库的同步情况：
备节点：
insert into ambari.test values(123,999);
select from ambari.test where id=123;

主节点查看数据被同步
select from ambari.test where id=123;

4.2.2、通过主节点写入表数据，查看备库的同步情况：
主节点：
insert into ambari.test values(456,777);
select from ambari.test where id=456;

备节点查看数据被同步
select from ambari.test where id=456;

5、异常处理

如果是升级mysql版本出现报错：
ERROR 1682 (HY000): Native table ‘performance_schema’.’session_variables’ has the wrong structure
则需要执行以下：
sudo mysql_upgrade -u root -p123456 -force
sudo service mysqld restart && sudo service mysqld status