If you recently installed MySQL server in DigitalOcean or Upcloud and you use TablePlus on connecting to the server. You probably get an error message that says “Driver Error, Can’t connect to MySQL server on ‘YOUR_IP_ADDRESS’ (10061)”. There are only two causes that cause this problem, either by a database user no remote access or a configuration file.
Granting Remote Access to MySQL Server
To grant a remote access to MySQL server, you need to create a database user with a host wildcard “%”. To do this, access your mysql via
mysql -u -root -p. Then enter the following commands, make sure to change the default data.
grant all privileges on *.* to 'YOUR_NEW_USER'@'%' identified by "YOUR_PASSWORD";
If you’re using a higher version of MySQL like the 8.0, you’ll get an error like “ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘identified by “Your_password”‘ at line 1“. Above code is depreciated and you can do this alternative approach.
CREATE USER 'YOUR_NEW_USER'@'%' ALTER USER 'YOUR_NEW_USER' identified by 'YOUR_PASSWORD'; GRANT ALL PRIVILEGES ON *.* TO 'YOUR_NEW_USER'@'%';
Then flush the privileges to update any records.
This should work now, but Ubuntu 18.04.1 comes with a MySQL configuration that has a default secure connection that we need to take care of.
You have to edit the mysqld.cnf file and uncomment the bind-address. You can use vim to edit this,
# # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 # # * Fine Tuning #
To make sure everything is good, restart mysql server.
sudo service mysql restart
Now, go back to TablePlus and access your database again.