mysql版本升级导致的一些错误

在项目过程遇到一些因为数据库版本原因发生的一些错误

mysql服务器升级数据还原后表结构不能显示

mysql服务器升级后,数据库数据还原后,在使用navicat进行desc表结构是出现了

“1558 – Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50018, now running 50146. Please use mysql_upgrade to fix this error.”这样的错误内容

我的mysql服务器是从5.1升级到了5.5版本,而原数据库表的备份与恢复是直接copy mysql data目录;数据都是完好的。搜索查明

解决方案就是:

mysql.proc
这个是5.1里面的系统表来的,用来记录存储过程或函数的信息.你的数据库肯定作过升级或用不同的版本进行备份迁移恢复.

使用命令:mysql_upgrade 就可以解决

mysql_upgrade -u root –datadir=/opt/mysql –basedir=/opt/mysql/data –password=123456

MySQL utility for upgrading databases to new MySQL versions.

5.6.10 mysqldump: Couldn’t execute ‘SELECT @@GTID_MODE’ 执行错误

mysqldump: Couldn’t execute ‘SELECT @@GTID_MODE’: Unknown system variable ‘GTID_MODE’ (1193)

不知道系统变量GTID_MODE,google搜索在mysql官方找到一片bug报告

mysqldump –set-gtid-purged=AUTO does not detect if mysqld has GTIDs

5.6.5 introduced GTIDs. mysqldump 5.6 was made
compatible  to use GTIDs
2. In versions < 5.6, where GTIDs are not
even defined, mysqldump 5.6 fails as it cannot
execute ‘SELECT @@GTID_MODE’
3.  using –set-gtid-purged=AUTO, should detect whether
the server has GTIDs enabled or not.  However, this
option works only for 5.6 versions.

WORKAROUND: if you are using mysqldump 5.6 to backup mysql-5.5  and old database
versions, use –set-gtid-purged=OFF option.

Fix:  THe fix is to check for the server version before executing ‘select @@gtid_mode’

5.6.5版本引入GTIDs;如果mysql version 小于5.6,GTIDs是没有定义的,mysqldump5.6不能执行’SELECT @@GTID_MODE’ ;应该使用–set-gtid-purged=AUTO用来检测GTIDs是否启用,但是这个选项只能在5.6版本mysql上工作

我要备份的远程服务器是

原先按正常的mysqldump语法如下则会报错

所以应该用mysqldump 5.6 对mysql server版本小于5.6版本或者是更老的版本进行数据备份时,会首先执行“SELECT @@GTID_MODE”这样的语句(尽管select @@gtid_mode 得到的属性值它是off)

则要有加上 –set-gtid-purged=OFF 属性了,导出成功

 

但是对于这个gtid_mode属性是什么意思,我不是很明白,查阅了一下与事物数据同步有关想了解的请看16.1.4.5. Global Transaction ID Options and Variables

“这是因为在启用GTID模式下,如果对非事务表更新时,无法保证事务一致性,因此设置 disable-gtid-unsafe-statements = 1 确保主从数据一致性。”[MySQL FAQ]系列 — 启用GTID & binlog新安装完的MySQL提示无法登录

 

扩展阅读

从mysql5.6导出的sql备份文件要还原到小于5.6版本或更老版本时可能会出现如下的问题

GTID_PURGED variable makes mysql dump non backward compatible

A dump produced from 5.6 server by 5.6 mysqldump is not supposed to be restorable on all or some old server and client versions,
Last version for which we had a compatibility option in mysqldump was 4.0 !!!
Hence, what you ask for would be some 5.5 compatibility version.
We have also asked our Documentation team to further document this limitation in clear terms.

mysqldump 5.6导出的sql备份文件不支持老版本的数据恢复

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*