MySQL - Enable Remote Access | VIKHOST
Enable remote access to MySQL database server
If you try to connect to server mysql from remote server, you will get an error. Initially, mysql uses the standard localhost connection (127.0.0.1). It needs to be fixed this in the configuration file my.cnf (usually in Linux the file is located /etc/mysql/my.cnf)
In my.cnf we need to find line:
bind-address = 127.0.0.1
It needs to be changed to:
bind-address = 0.0.0.0
Now after service mysql restart we can connect to mysql from any IP (if the user is created). At the same time, for the root user, remote access remains under the blank password.
Next step, needs to create user, under which we will connect remotely to mysql.
Need to create user: username with password: pass.
CREATE USER 'username'@'%' IDENTIFIED BY 'pass';
We give for user name: "username" all the rights to work with the database: "database".
GRANT ALL PRIVILEGES ON database.* TO 'username'@'%';
Apply new rights.
FLUSH PRIVILEGES;
Also you can also give access for work with all databases
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%';
For command CREATE USER we created a user that can connect to mysql from all ip. We can allow only connection with certain ip, for example:
We create user: test with password: pass which can be connected only from IP - 10.10.50.50
CREATE USER 'test'@'10.10.50.50' IDENTIFIED BY 'pass';
Also we can allow connecting from specific network:
Create user: test with password: pass which can be connected only from subnet 10.10.50.0/24. Can be used mask '10.10.50.%' or special syntax '10.10.50.0/255.255.255.0'
CREATE USER 'test'@'10.10.50.%' IDENTIFIED BY 'pass';
Test Connection
From terminal/command-line:
mysql -h HOST -u USERNAME -pPASSWORD
If you get a mysql shell, don’t forget to run show databases; to check if you have right privileges from remote machines.
Revoke Access
If you accidentally grant access to a user, then better have revoking option handy.
Following will revoke all options for USERNAME from all machines:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%';
Following will revoke all options for USERNAME from particular IP:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'10.50.50.50';
Its better to check information_schema.user_privileges table after running REVOKE command.
More information you can find on the official MySQL website.