系统表不存在执行升级(mysql_upgrade)操作报错误的解决办法(5.6升级到5.7)
环境:
OS:Centos 7
原db:5.6
新db:5.7
执行升级命令报如下错误
[root@hadoop-slave1 mysql]# /home/middle/mysql57/bin/mysql_upgrade -s -h localhost -pyeemiao3040 -P13306 -S /home/middle/mysql57/data/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1050: Table '`mysql`.`innodb_index_stats`' already exists
1.查看当前目录下是否有以下表的frm文件和ibd文件
innodb_table_stats
innodb_index_stats
slave_master_info
slave_relay_log_info
slave_worker_info
[root@hadoop-slave1 mysql]# pwd
/home/middle/mysql57/data/mysql
[root@hadoop-slave1 mysql]# ls -al *innodb_table_stats*
-rw-r----- 1 mysql mysql 65536 2月 21 08:57 innodb_table_stats.ibd
[root@hadoop-slave1 mysql]# ls -al *innodb_index_stats*
ls: 无法访问*innodb_index_stats*: 没有那个文件或目录
[root@hadoop-slave1 mysql]# ls -al *slave_master_info*
ls: 无法访问*slave_master_info*: 没有那个文件或目录
[root@hadoop-slave1 mysql]# ls -al *slave_relay_log_info*
ls: 无法访问*slave_relay_log_info*: 没有那个文件或目录
[root@hadoop-slave1 mysql]# ls -al *slave_worker_info*
ls: 无法访问*slave_worker_info*: 没有那个文件或目录
针对innodb_table_stats表,只有ibd文件,但是没有frm文件,我们将ibd文件删除掉,这5个表的ibd和frm文件全部从其他正常的节点拷贝过来.
[root@hadoop-slave1 mysql]# rm innodb_table_stats.ibd
2.拷贝正常机器(正常机器的mysql建议关闭)的文件到当前的机器
找一台版本一致的数据库,好像版本不一致也可以
我这里从5.6版本的拷贝到5.7(以5.7启动,但是未执行mysql_upgrade)
发现从其中的机器拷贝过来启动报错误
2024-03-18T06:22:04.275071Z 0 [ERROR] [FATAL] InnoDB: Tablespace id is 4 in the data dictionary but in file ./mysql/slave_master_info.ibd it is 1254!
[FATAL] InnoDB: Tablespace id is 3 in the data dictionary but in file ./mysql/slave_relay_log_info.ibd it is 7663!
我这里是从192.168.1.92(mysql5.6)机器上拷贝的
scp /var/lib/mysql/mysql/innodb_table_stats.frm root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/innodb_table_stats.ibd root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/innodb_index_stats.frm root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/innodb_index_stats.ibd root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/slave_master_info.frm root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/slave_master_info.ibd root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/slave_relay_log_info.frm root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/slave_relay_log_info.ibd root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/slave_worker_info.frm root@192.168.1.63:/tmp/
scp /var/lib/mysql/mysql/slave_worker_info.ibd root@192.168.1.63:/tmp
3.停掉数据库
/home/middle/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -pyeemiao3040 -S /home/middle/mysql57/data/mysql.sock shutdown
4.拷贝文件到相应目录
cp /tmp/innodb_index_stats.frm /home/middle/mysql57/data/mysql/
cp /tmp/innodb_index_stats.ibd /home/middle/mysql57/data/mysql/
5.修改权限
chown -R mysql:mysql /home/middle/mysql57
6.启动
/home/middle/mysql57/bin/mysqld_safe --defaults-file=/home/middle/mysql57/conf/my.cnf --user=mysql &
7.登录查看
/home/middle/mysql57/bin/mysql -h localhost -uroot -P13306 -pyeemiao3040 -S /home/middle/mysql57/data/mysql.sock
mysql> desc innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(64) | NO | PRI | NULL | |
| index_name | varchar(64) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name | varchar(64) | NO | PRI | NULL | |
| stat_value | bigint(20) unsigned | NO | | NULL | |
| sample_size | bigint(20) unsigned | YES | | NULL | |
| stat_description | varchar(1024) | NO | | NULL | |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.01 sec)
mysql> select * from innodb_index_stats limit 1;
+----------------+------------+------------+---------------------+--------------+------------+-------------+------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+----------------+------------+------------+---------------------+--------------+------------+-------------+------------------+
| ApolloConfigDB | App | AppId | 2019-05-29 13:35:36 | n_diff_pfx01 | 8 | 1 | AppId |
+----------------+------------+------------+---------------------+--------------+------------+-------------+------------------+
1 row in set (0.00 sec)
8.继续执行升级操作
[root@hadoop-slave1 mysql]# /home/middle/mysql57/bin/mysql_upgrade -s -h localhost -pyeemiao3040 -P13306 -S /home/middle/mysql57/data/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1050: Table '`mysql`.`slave_relay_log_info`' already exists
按照上面的步骤继续处理 slave_relay_log_info 表
9.最后升级成功
[root@hadoop-slave1 mysql]# /home/middle/mysql57/bin/mysql_upgrade -s -h localhost -pyeemiao3040 -P13306 -S /home/middle/mysql57/data/mysql.sock
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.
