Whilst restoring a MariaDB Backup from one Virtualmin server to another, MySQL create and grant errors


On a legacy server with an older version of MariaDB, restoring to a newer Virtualmin server that run MySQL 8 might be problematic. The errors manifest themselves like so:

Restore failed : SQL grant all on `6009\_rpa\_data`.* to '6009'@'localhost' with grant option failed : You are not allowed to create a user with GRANT

You might also encounter this error at other places in the system:

The server requested authentication method unknown to the client [caching_sha2_password]

There is not a magic bullet for solving this, and the reason is that permissions on MySQL 8 is more strict so you probably have to fiddle.


Here are solutions that might work:

Update 20 September

After a particularly nasty system failure we noted that deleting the user from localhost, then adding then to, worked.

Do not backup database with complete backup

Sometimes the solution is to not backup the database and do it separately. So backup the files, then restore them. Then backup the database, and try to restore it.

Update 15 August 2021

As the this Stack article, MySQL has issues with grant and the sequence is different.

They summarize it as so in that article:

Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:

Notice the multi step process. Please note, this article pertains to root. If you’re working on a shared server, root will not be applicable.


One way to troubleshooted is by opening two SSH windows:

SSH Window #1:

mysql -uroot
Test grant statement.
It fails

mysql> grant all privileges on database.* to broken-user@localhost with grant option;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

SSH Window #2:

mysql -uroot
Create user, which also fails!

mysql> create user broken-user@localhost identified by 'secret';
ERROR 1396 (HY000): Operation CREATE USER failed for 'broken-user'@'localhost'
mysql> drop user broken-user@localhost;
Query OK, 0 rows affected (0.00 sec)

However, after dropping the user, and then doing grant again, it works!

mysql> create user broken-user@localhost identified by 'secret';
Query OK, 0 rows affected (0.00 sec)

Now grant all is working!

mysql> grant all privileges on broken-user.* to database@localhost with grant option;
Query OK, 0 rows affected (0.01 sec)

Here are the original solutions:

Grant all privileges

First login as root to MySQL. Then:

grant all privileges on database.* to database_user@'%' with grant option;

Be extremely careful of typos, as the resultant error completely throws one off.

Fiddle with Database Import

You might also want to try Edit Database / Import Database. It seems when backups are restored and MySQL Grant issues occur, these databases become orphaned and one way to re-establish the site link is to re-import them to the correct site.

Create User Again and Grant Again

In MySQL 8, user creation and grant was split. So what you could try is to create the user again, without a grant statement, and then try adding grant permissions.


Share this article

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top