Stuck after Mysql upgrade from 5.1 to 5.7

jonaskellens · 06-26-2019, 07:32 AM

Hello

I upgraded mysql server to version 5.7

Code:

yum localinstall https://dev.mysql.com/get/mysql57-community-release-el6-10.noarch.rpm
yum install mysql-community-client mysql-community-server

After this I was not able to restart mysql. I found solution (on internet) :

Code:

vi /etc/my.cnf
skip-grant-tables
innodb_data_file_path = ibdata1:10M:autoextend

But I am not able to log in as root user :

Code:

mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Doing mysql_upgrade to try resolve this gives :

Code:

mysql_upgrade
mysql_upgrade: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) while connecting to the MySQL server
Upgrade process encountered error and will not continue.

I also read on the internet about "grep 'temporary password' /var/log/mysqld.log", but this gives no result for me.

The following also fails :

Code:

mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)

So, I am stuck.

dc.901 · 06-26-2019, 09:27 AM

Did you check to see if you can upgrade from 5.1 to 5.7? That is a big jump and major changes...
http://mysqlserverteam.com/upgrading...ith-mysqldump/

I am hoping you have backups of the DBs, then revert back to 5.1 and perform upgrade as per documentation...

jonaskellens · 06-26-2019, 09:57 AM

Reverting to 5.1 is failing with major errors in /var/log/mysqld.log.

I did not check that document. It seems a huge version jump indeed.

By now I am able to log in again with following steps :
1 "skip-grant-tables" in /etc/my.cnf
2 "service mysqld restart"
3 "use mysql; update user set authentication_string=PASSWORD("NewPasswd") where User='root'; flush privileges;"

Code:

mysql --version
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

I now have the strange thing that in PhpMyAdmin the tab "Users" is emtpy. It does not load.
Also PhpMyAdmin gives strange info :

Code:

Server version: 5.1.73 - Source distribution
Client version: libmysql - 5.1.72

It seems it does not see the correct MySQL version.

dc.901 · 06-27-2019, 07:19 AM

Quote:

Originally Posted by jonaskellens

I did not check that document. It seems a huge version jump indeed.

By now I am able to log in again with following steps :
1 "skip-grant-tables" in /etc/my.cnf
2 "service mysqld restart"
3 "use mysql; update user set authentication_string=PASSWORD("NewPasswd") where User='root'; flush privileges;"

Code:

mysql --version
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Good!

Quote:

Originally Posted by jonaskellens

I now have the strange thing that in PhpMyAdmin the tab "Users" is emtpy. It does not load.
Also PhpMyAdmin gives strange info :

Code:

Server version: 5.1.73 - Source distribution
Client version: libmysql - 5.1.72

It seems it does not see the correct MySQL version.

From command line, login to mysql, then run this:

Code:

select * from mysql.user;

Do you see all the users from before upgrade?

jonaskellens · 06-27-2019, 07:35 AM

Quote:

Originally Posted by dc.901

Good!
From command line, login to mysql, then run this:

Code:

select * from mysql.user;

Do you see all the users from before upgrade?

Yes they are present.

dc.901 · 06-27-2019, 08:53 AM

Quote:

Originally Posted by jonaskellens

Yes they are present.

Great. Now, as for your applications (that use databases on this MySQL instance), are all of them functioning?
If so, may be reinstalling phpmyadmin will resolve its issues. I do not use phpmyadmin, so can't help you much with it

jonaskellens · 06-27-2019, 12:49 PM

Quote:

Originally Posted by dc.901

Great. Now, as for your applications (that use databases on this MySQL instance), are all of them functioning?
If so, may be reinstalling phpmyadmin will resolve its issues. I do not use phpmyadmin, so can't help you much with it

An application of mine that uses a particular mysql user is unable to connect. Mysql err log says :

Code:

2019-06-27T17:47:33.263349Z 68465 [Note] Access denied for user 'MyUser'@'localhost' (using password: YES)

PhpMyAdmin latest version got installed together with mysql-community-server-5.7.26-1 :

Code:

Package phpMyAdmin-4.0.10.20-1.el6.noarch already installed and latest version