项目05(Mysql升级04Mysql5.1.73升级到Mysql5.5.62)

背景

《承接上文,项目05(Mysql升级01)》,上文中说要介绍一些主从切换，仅用于本次项目的测试(实际生产中还需切换VIP).文章项目05(Mysql升级01)》讨论的是Mysql5.0.95升级到Mysql5.1.73.本文讨论Mysql5.1.73升级到Mysql5.5.62.
写在前面

需要(考虑)检查和测试的层面很多，不限于以下内容。

Mysql5.1.73升级到Mysql5.5.62

环境

Mysql5.1.73主(66.190) 从(66.191)，使用逻辑升级Logical Upgrade(备份旧版本数据，还原到新版本的方式)

然后先升级从库，切换之后，再升级主库

原理是Mysqldump备份的sql文件，恢复到新版本中。(这里会重点说明执行中需要注意的地方,下文中会使用【重点】字样标注)

1.逻辑升级（从库）

以下命令均为见面知意，这里不再重复说明
1.
mysqlcheck --defaults-file=/etc/my.cnf --socket=/tmp/mysql5.1.73.sock -u root -p --all-databases --check-upgrade
2.
(升级到Mysql5.1.73检查没有--events选项,升级到5.5.62时需要添加,只备份业务库)
mysqldump --socket=/tmp/mysql5.1.73.sock -u root -p --add-drop-table --routines  --events --master-data=2 --single-transaction --databases  ceshi100 ceshi111 ceshi222 ceshi444 ceshi888 --force > data-for-upgrade.sql

3.
stop slave;
/etc/init.d/mysqld5.1.73 stop

4.
PATH=$PATH:/usr/local/mysql5.5.62/bin

5.
vim /etc/my.cnf【重点】(更新路径)其中#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend只有初始化时，使用。完成后请注释掉

6.
./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql5.5.62/data --defaults-file=/etc/my.cnf
【重点】初始化之后数据目录data需要修改权限【mysql5.5.62]# chown -R mysql. data/    (防止下面重启失败的问题)

重启失败的日志报错为：
210209 11:27:39 [ERROR] /usr/local/mysql5.5.62/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13)
210209 11:27:39 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13)'

7.
cp support-files/mysql.server /etc/init.d/mysqld5.5.62
vim /etc/init.d/mysqld5.5.62
basedir=/usr/local/mysql5.5.62
datadir=/usr/local/mysql5.5.62/data
/etc/init.d/mysqld5.5.62 start

8.
ln -s  /tmp/mysql5.5.62.sock /tmp/mysql.sock
./bin/mysql_secure_installation

9.
mysql -u root -p --force < data-for-upgrade.sql(推荐备份文件是主库传过来的)

10.
升级命令(5.1到5.5)【重点】((可选)并且在/etc/my.cnf [mysqld]下添加skip-grant-tables)
sudo /usr/local/mysql5.5.62/bin/mysql_upgrade

11.
/etc/init.d/mysqld5.5.62 restart

如果使用mysql_upgrade -u root -p会报错：
Running 'mysql_fix_privilege_tables'...
ERROR 1064 (42000) at line 47: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant'' at line 1
ERROR 1064 (42000) at line 49: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), Column_priv set('Select','Insert','Update','References') COLLATE utf8_gene' at line 1
ERROR 1064 (42000) at line 81: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14), PRIMARY KEY (Host,Db,User,Routine_name,Routine_type), KEY Grantor (Grantor' at line 1
ERROR 1064 (42000) at line 483: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14) AFTER Proc_priv' at line 2
FATAL ERROR: Upgrade failed

注意:mysql.proc表的第16列在5.1.73中是Field: comment  Type: char(64)；而在5.5.62中是  Field: comment Type: text。因为日志报错([ERROR]Incorrect definition)
备份时,报错：
mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'ceshi100'': Cannot load from mysql.proc. The table is probably corrupted (1548)[表损坏]
修复不行，可以通过sudo /usr/local/mysql5.5.62/bin/mysql_upgrade之后再次备份解决(是数据库从5.1升级到5.5的常见问题)

ln -s  /tmp/mysql5.5.62.sock /tmp/mysql.sock
在安装后第一次使用 mysql server 时切记要跳过 grants 表，原因有二：一是我们可能希望原来的用户和权限数据，二是新版的 grants 表结构有所变化。

1.只备份恢复业务库
2.【重点】(更新路径)其中#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend只有初始化时，使用。完成后请注释掉
3.【重点】初始化之后数据目录data需要修改权限
4.sudo /usr/local/mysql5.5.62/bin/mysql_upgrade 需要在升级命令前添加 sudo

2.主从切换之后，升级主库

操作步骤同上

补充

如果主库没有恢复mysql库，需要新建复制用户
CREATE USER 'repl'@'192.168.66.%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.66.%';
FLUSH PRIVILEGES;

3.重建主从

主从切换：
show slave status\G
show master status;

stop slave IO_THREAD;
stop slave;
注意要保证主从数据一致的情况下，才进行reset
reset slave; #5.1.73没有reset slave all;之后的版本有
reset master;

新从库：(还原点master_log_pos的值可以人为设置小一点)
change master to master_host='192.168.66.190',master_user='repl',master_port=3306,master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1;

主从一致性验证可以简化为增删测试库验证,下文《项目05(Mysql升级05)》，我们一起讨论一下Mysql5.5.62升级到Mysql5.6.51

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​