Connect remotely to Linode mysql server

In order to allow remote MySQL connections, you'll need update your MySQL configuration that currently binds to your localhost, as well as update your user permissions or create a new user. Default options are read from the following files in the given order:

/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

Start with editing mysql config file:

$ vim /etc/mysql/my.cnf

Comment out the following line that binds your MySQL to your localhost:

#bind-address = 127.0.0.1

Restart your mysql server:

$ service mysql restart

To grant access to a database user, log in to the database server. Connect to the MySQL database as the root user.

Run a command like below to access from all machines:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Run a command like below to give access from specific IP, replacing 1.2.3.4 with your remote IP:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

Finally, you may also need to run:

mysql> FLUSH PRIVILEGES;

Then you are ready to test your connection. If you encounter issues during this process, please share them here along with any errors you encounter so we can help further.

To add to this, if you are using MariaDB there are some additional files you might want to check. Luckily, MariaDB includes very helpful comments in their files which points can point you in the right direction. This is contained in /etc/mysql/mariadb.cnf:

# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.

In my test case, I commented the "bind-address" line from /etc/mysql/mariadb.conf.d/50-client.cnf. Then, I had to add port 3306 to my ufw firewall rules:

ufw allow 3306

MariaDB has their own documentation on this very subject if you'd like to read further. Also, if you're like me and use ufw, we have a good article in our Docs site that can get you started.

I could not able to find the #bind-address = 127.0.0.1 in my.cnf file.Can anyone please help me? Thanks

techsoldxb, Look at /etc/mysql/mysql.conf.d/mysqld.cnf

@evaldez writes:

Then, I had to add port 3306 to my ufw firewall rules:
 

ufw allow 3306

 

More specifically, this needs to be tcp/3306 and this port needs to be open on BOTH the Linode and the AWS VM.

-- sw