linux系统比较老,glibc2.5版本,所以只能升级到5.6.36
用mysql的tar.gz软件包安装,可以不用删除旧的mysql就安装运行新的mysql,方便数据导入导出。遇到个问题就是账户权限的迁移
用的网上流行的账户迁移方式,一直迁移不成功,方法脚本如下
https://blog.csdn.net/weixin_42715413/article/details/81534163
报错ERROR 1827 (HY000): The password hash doesn’t have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
究其原因是版本升级后密码加密升级,不让用旧的加密方式。
通用解决方案
mysql> show variables like ‘old_passwords’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| old_passwords | 1 |
±--------------±------+
1 row in set (0.01 sec)
mysql> set old_passwords=0;
Query OK, 0 rows affected (0.00 sec)
设置成0加密40位,设置成1,16位,不管设置0还是1还是2,使用旧的加密后密码执行grant语句都不行
GRANT USAGE ON . TO ‘hadoop’@’%’ IDENTIFIED BY PASSWORD ‘7e28fbb90cc5ec20’;
报错报错报错
根据mysql网站说,
In some early development versions of MySQL 5.6 (5.6.6 to 5.6.10), the server could create accounts with a mismatched password hash and authentication plugin. For example, if the default authentication plugin is mysql_native_password, this sequence of statements results in an account with a plugin of mysql_native_password but a pre-4.1 password hash (the format used by mysql_old_password):
https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
就是说5.6.10以后版本再用old_passwords=1;不会让执行了,会报错。
咋办?一堆账号不能让用户重新建,所以必须把账号迁移过来
解决办法:
1、把所有的账号密码设置一个随意的40位的密码,先把账号建立起来,密码是与之前不同的,例如
GRANT USAGE ON . TO ‘hadoop’@’%’ IDENTIFIED BY PASSWORD *21D2FBD2CDDD99B7A328A4F13E63DD2C99C3163F’;
2、把原来mysql.user里的password直接update到新的mysql.user,用sql语句更新过去
3、连接试一下,连接成功。