Connect remotely to Linode mysql server
I'm trying to connect remotely to my mysql server.
When I try to access remotely from another server (e.g. my aws server) using shell command "mysql -u username -ppasword -hhost) I get "Host xxx is not allowed to connect to this MySQL server"
I even tried using client like Toad but still same message
I added the ip and domain name of my aws server to the Linode "Remote MySQL" but still get above message.
What am I doing wrong?
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:
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 220.127.116.11 with your remote IP:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'18.104.22.168' 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
# 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
I could not able to find the #bind-address = 127.0.0.1 in my.cnf file.Can anyone please help me? Thanks