MySQL Create a user that can only access a specific database, why can't I connect remotely?

Question

I have remote access to root user.

The statement to create the user is as follows:

CREATE USER 'auser'@'%' IDENTIFIED BY 'auser';
GRANT ALL ON `MyDB`.* TO 'auser'@'%';
flush privileges;

This auser account cannot log in remotely, what is the reason?

PS. Use navicat to log in to the database prompt: 2013 - Lost connection to MySQL server during query

`This auser account cannot log in remotely` if the user would have trouble log in the error should be something like Permission denied not `013 - Lost connection to MySQL server during query`. As per the current error check https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query — Ergest Basha, Sep 09 '22 at 07:14
@Ergest Basha The OP actually has a problem at login , not afterwards. And since his remote root access has no issue, I wonder if something else other than network problem is to blame. — blabla_bingo, Sep 09 '22 at 07:24

score 0 · Accepted Answer · answered Sep 15 '22 at 09:42

I login the user on Linux, mysql prompt init_connect command failed after execute USE DatabaseName .

I found the answer online. Change /etc/my.cnf from

init_connect=‘SET collation_connection = utf8_unicode_ci’
init_connect=‘SET NAMES utf8’

to

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'

Note the single quotes.

Then restart mysql, the problem is solved.

1 Answers1