Remote access to MySQL via Workbench
What is there:
- Installed Ubuntu 16 on VirtualBox.
- In the settings of the virtual machine (hereinafter referred to as Ubuntu), there is a NAT and a Network bridge, i.e. it sees the Internet over NAT and is part of the physical local network.
- The guest system has a static address (192.168.1.99), which is pinged on all other machines on the local network.
- Apache + PHP + MySQL +installed PhpMyAdmin + Nginx
- On any machine on the local network, you can enter in the browser 192.168.1.99/phpmyadmin and successfully log in to phpMyAdmin
But I'm used to it and I'm more comfortable with MySQL Workbench. I'm trying to add a new connection: - in hostname: 192.168.1.99 - port: 3306 - uresname: root - password: corresponding and I click Test, then it writes to me that it can not connect to the MySQL server at the address
192.168.1.99:3306
If I enter the netstat-tln command in the Ubuntu console, then I understand that
0.0.0.0:80 - это слушают все адреса и порт 80 для показа моего сайта, а
127.0.0.1:3306 - это скорее всего слушает MySQL.
I.e. I understand that MySQL uses port 3306 only on localhost.
And now the question: How to make it listen to other addresses besides localhost? In other words, how to connect to a MySQL server installed on VirtualBox remotely using MySQL Workbench?
1 answers
To configure remote access from other IP addresses:
sudo nano /etc/mysql/my.cnf
Looking for the [mysqld] section and changing the bind-address value to 0.0.0.0
My. cnf file was almost empty. There was a lot of text in the form of comments, then I just added:
[mysqld]
bind-address=0.0.0.0
Restarting the server:
sudo service mysql restart
Checking that the MySQL daemon is now listening:
netstat -tln
Must be 0.0.0.0:3306
Next, we try to connect via MySQL Workbench or another client-manager databases and get a message that our computer can not connect to the MySQL server.
host ‘192.168.1.83’ is not allowed to connect to this mysql server
Where 192.168.1.83 is the IP address of my PC.
The fact is that MySQL is not only able to restrict external access to the server itself, but also to identify users for remote access to certain databases or even tables.
You need to create a user and give him the necessary access. Let's take a closer look at the SQL query for creating users:
GRANT [тип прав] ON [имя базы данных].[имя таблицы] TO 'имя пользователя'@'его ip-адрес';
What would create a superuser, type root, with only your own name, USER_NAME, with full rights for all databases with access from any IP and with the password YOUR_PASSWORD:
GRANT ALL PRIVILEGES ON *.* TO 'USER_NAME'@'%' IDENTIFIED BY 'YOUR_PASSWORD' WITH GRANT OPTION;
Here are some more examples:
Create a user for a specific database:
GRANT ALL PRIVILEGES ON `my_db`.* TO USER_NAME@'%' IDENTIFIED BY 'YOUR_PASSWORD';
Create a user and restrict access only to a specific IP:
GRANT ALL PRIVILEGES ON `my_db`.* TO USER_NAME@'192.168.1.83' IDENTIFIED BY 'YOUR_PASSWORD';
Or even only for working from the server, for example, only through phpMyAdmin:
GRANT ALL PRIVILEGES ON *.* TO USER_NAME@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';
If you have decided on a choice SQL query, then it can be run in the same phpMyAdmin or in the console:
Go to MySQL:
mysql -u root mysql -p
We write the necessary SQL query, for example:
GRANT ALL PRIVILEGES ON *.* TO 'megaadmin'@'%' IDENTIFIED BY 'superparol' WITH GRANT OPTION;
In order for the changes to take effect, run an update request: FLUSH PRIVILEGES;
Exiting MySQL: EXIT;
You can also do this in the phpMyAdmin GUI without entering an SQL query. After logging in under the root in phpMyAdmin, go to the "Accounts" tab users". Everything is intuitive there. By lowering the screen below, you can select the necessary privileges for SQL queries (Data), for creating databases and tables (Structure) , and for server management (Administration), and you can also set a limit on resource usage.