Back Back

How to Allow Remote Access to MySQL

By default, MySQL only accepts local connections from the server itself. In this tutorial, you’ll allow connections from another computer or application, such as a website or game server.

What you need before you start

  • A VPS running Linux with MySQL or MariaDB installed.
  • A user with sudo privileges.
  • The public IP address of the computer or server you’ll connect from.

Enabling remote access

  1. Connect to the server via SSH

    Connect to your VPS via SSH. More: How to use PuTTY.

  2. Change the bind-address setting

    Open the database configuration file. The path depends on what you use:

    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

    Press Ctrl + W and search for bind-address. By default it only allows local connections:

    bind-address = 127.0.0.1

    Change the value so the server accepts external connections:

    bind-address = 0.0.0.0

    If there’s no bind-address line in the file, add it below the [mysqld] line.

    How to save

    Ctrl + O and Enter saves the file, Ctrl + X exits the editor.

  3. Restart the database service

    Restart the service to apply the changes:

    sudo systemctl restart mysql

  4. Prepare a user for remote access

    Open the MySQL console:

    sudo mysql

    If you see ERROR 1698 (28000): Access denied, password authentication is enabled for root. In that case, use:

    mysql -u root -p

    Replace 1.2.3.4 with the IP address of the computer or server you’ll connect from. Then choose:

    Create a user who can only connect from the given IP:

    CREATE USER 'username'@'1.2.3.4' IDENTIFIED BY 'password';
    Avoid % unless necessary

    Using '%' instead of an IP lets the user connect from anywhere. It’s convenient, but automated bots will constantly try to guess the password.

  5. Grant privileges

    Grant the user privileges only on the database it needs:

    GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'1.2.3.4';

    Exit the MySQL console. You don’t need to run FLUSH PRIVILEGES after GRANT.

    exit

  6. Open port 3306 in the firewall

    If the server runs the UFW firewall, open the MySQL port for your IP address only:

    sudo ufw allow from 1.2.3.4 to any port 3306
    Don’t open the port to everyone

    The sudo ufw allow 3306 command exposes the database to the entire internet. Always specify an IP.

Did it work?

On the computer you’ll connect from, run this command, replacing server_ip with your VPS IP address:

mysql -u username -h server_ip -p

If the MySQL console opens after you enter the password, remote access works. The MySQL client must be installed on that computer.

FAQ

“ERROR 2003: Can’t connect to MySQL server”

The server isn’t accepting the connection. Check that you changed bind-address, restarted the service and opened port 3306 for your IP.

“Host ... is not allowed to connect”

The user was created for a different IP. Check with SELECT user, host FROM mysql.user; and make sure the host column contains your IP.

Connection fails with PHP or phpMyAdmin

Very old PHP versions don’t support the default caching_sha2_password method. The best fix is updating PHP. mysql_native_password is disabled in MySQL 8.4 and removed in 9.0.

Is there a more secure option?

Yes, an SSH tunnel. Then you don’t need to change bind-address or open the port: ssh -L 3306:127.0.0.1:3306 [email protected]. After that, connect to 127.0.0.1 on your computer.

What’s next

If you run into trouble, contact us via live chat or email.

VPS plans All VPS tutorials

Similar articles

VPS Tutorials