Allow remote access to MySQL 8 server

On this page

Login as root

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 caching_sha2_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 (optional)

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

From your local machine:

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

Tagged: MySQL