Today I decided to migrate the website from my old home server that had MySQL installed to a newer web server with MariaDB running on it.
Did it by doing the regular mysqldump and import procedure, which all went fine up to the point when I actually tried to access the site again. Then I got the following error message “Error establishing a database connection“. To see what’s going on I tried logging in to the database using the websites credentials in commandline and it also failed. After that logged in as root and saw that the user was imported, but it had no permissions.
To check what users exist in the database You can use the following SQL statement:
SELECT User, Host, Password FROM mysql.user;
To see what privileges a user has you can use the following SQL statement:
show grants for 'user_name'@'localhost';
In my case it showed the following out put stating that that the user has no privileges:
ERROR 1141 (42000): There is no such grant defined for user 'user_name' on host '%'
After seeing that I tried just re-applying the users rights by using the regular grant command to re-grant the user it’s privileges on the database using the following command:
MariaDB [(none)]> GRANT ALL PRIVILEGES ON database_name.* to 'user_name'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
Then I looked at the users permissions again and unfortunately I got the same result as before, “no such grant defined for the user..”. After that I tried just flushing privileges, to force the server to reload them by issuing the following command:
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
It didn’t get any better. Finally ended up revoking, flushing and resetting the permissions by doing the following:
MariaDB [(none)]> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user_name'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT ALL ON database_name.* TO 'user_name'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for 'user_name'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for user_name@localhost |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_name'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxx' |
| GRANT ALL PRIVILEGES ON `database_name`.* TO 'user_name'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
That finally helped and the site is up on the new server.
