Remote connection to MySQL on Raspberry Pi Error:ERROR 2003 (HY000): Can’t connect to MySQL…10061

When finishing the installation of MySQL on raspberry pi, the first we commonly do is run the configuration command

sudo mysql_secure_installation

After the process, when establishing a remote connection to MySQL, we may see a error:

$ mysql -h 192.168.1.100 -u [USERNAME] -p[PASSWORD]
  ERROR 2003 (HY000): Can't connect to MySQL server on...10061

The main reasons which cause this error are:

1. You may use ‘root’ identification to login directely. Under normal conditions, you can not use‘root’ to not only login raspberry, but also MySQL;
2. The MySQL haven’t be correctly configured. If you search this error code in Google, they will give you an instruction about commenting the ‘bind-address’ line in /etc/mysql/my.cnf, but in raspberry pi you won’t see a line which contains ‘bind-address’, the target line exists in /etc/mysql/mariadb.conf.d/50-server.cnf.

Solutions

1. Create a new user, for example: pi

create user pi identified by 'password';

the ‘pi’ is your specific username and ‘password’ is your password. And you can user the new identification to login.

2. Comment the line which contains ‘bind-address’ in /etc/mysql/mariadb.conf.d/50-server.cnf

...
# bind-address = 127.0.0.1
...

That’s all done.

Addition

If you want to give normal user permission to create database or other operation, login as root, and input

GRANT ALL PRIVILEGES ON *.* TO '[USERNAME]'@'%';

Reference:

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-18-04