Mysql – Allow remote connections and create a user

November 21st, 2013 by admin Leave a reply »

To enable logins from other locations you need to:

1) ssh to the server and edit the my.cnf (located in /etc/my.cnf or /etc/mysql/my.cnf
2) Add the line “bind-address=0.0.0.0”. This allows for localhost and remote connections.
3) Create or Modify a user to have permissions to connect to the server. Assuming the user already exists, connect to the mysql server as an admininistrator. Then type one of/or all of the following (depending on need)

GRANT ALL ON *.* TO username@'%';
GRANT ALL ON *.* TO username@'localhost';
GRANT ALL ON *.* TO username@'X.X.X.X';

Were % is ALL source IP address’s, localhost is only from the localhost, X.X.X.X is allow user from this source IP address.

This grants that user access to ALL databases on the server. To limit access to only certain databases then replace *.* with

databases.nameofdatabase.

You might also need to create/modify the user:

create user username@'X.X.X.X' IDENTIFIED BY 'userpassword';
create user username@'%' IDENTIFIED BY 'userpassword';
create user username@'localhost' IDENTIFIED BY 'userpassword';

Note: while each user is the same name and password, each is a separate entry in the mysql.users table because of the source of the connection for the user.

4) Make sure the firewall allows for remote connection to mysql on port 3306.

if you found this helpful feel free to donate to:
ZEC: t1NQp1UuqQbmnXzazbLTSreS2AbaZpRBuTM
LTC: LZyNF1qkBUA7XFz83m5xwzGgmmj1owQn9d
BTC: 1PY95KFPTEJTR7f2NnSgaB6xB9pwDJkcJz
Advertisement

Leave a Reply

You must be logged in to post a comment.