Allow remote access to MySQL 8 server

Login as root

Login as root (however you’ve configured root login).

sudo mysql

Create a user

You’ll typically want to create a separate user for each application so consider naming the user the same as the application. Use a password generator to create a strong password.

CREATE USER 'example_app'@'%' IDENTIFIED WITH mysql_native_password BY 'some-strong-password';

Grant privileges

GRANT ALL ON example_app.* TO 'example_app'@'%';
FLUSH PRIVILEGES;

Verify new user was created

SELECT host, user, plugin FROM mysql.user;

This should output something like:

+----------------------------+-----------+-----------------------+
| User                       | Host      | plugin                |
+----------------------------+-----------+-----------------------+
| example_app                | %         | mysql_native_password |
| debian-sys-maint           | localhost | caching_sha2_password |
| mysql.infoschema           | localhost | caching_sha2_password |
| mysql.session              | localhost | caching_sha2_password |
| mysql.sys                  | localhost | caching_sha2_password |
| root                       | localhost | auth_socket           |
+----------------------------+-----------+---------------------

Exit MySQL.

mysql> exit

Allow remote access

Edit /etc/mysql/mysql.conf.d/mysqld.cnf and comment out the following line:

bind-address          = 127.0.0.1

This ensures that users can login from other IP addresses. Ensure your firewall (if you have one) allows remote access to port 3306.

Restart MySQL server

sudo systemctl restart mysql.service

Login with new credentials

From your local machine:

mysql -h <server-ip> -u example_user -p