如何允许远程mysql登录？

Question

I have simple vagrant multi-machine config:

Vagrant.configure(2) do |config|
  config.vm.box = "bento/ubuntu-16.04"
  config.vm.provision :shell, path: "vagrant/ubuntu-16.04/provision.sh"
  config.vm.synced_folder "ed", "/var/www/html"
  config.vm.provider "virtualbox" do |v|
    v.memory = 1024
    v.cpus = 2
  end
  config.vm.define "node1", primary: true do |node|
    node.vm.hostname = 'node1'
    node.vm.network :private_network, ip: "192.168.56.101"
    node.vm.network :forwarded_port, guest: 22, host: 10122, id: "ssh"
    node.vm.provision :hosts, :sync_hosts => true
  end
  config.vm.define "node2" do |node|
    node.vm.hostname = "node2"
    node.vm.network :private_network, ip: "192.168.56.102"
    node.vm.network :forwarded_port, guest: 22, host: 10123, id: "ssh"
    node.vm.provision :hosts, :sync_hosts => true
  end
end

On node1 I have php and on node2 I have mysql.
When I run mysql -uuser -ppass -P3306 -hlocalhost from vagrant@node2 it works,
but when I try run mysql -uuser -ppass -P3306 -h192.168.56.102 from vagrant@node1 it won't work, I get error:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.56.102' (111)

I can ping node1 from node2 and vice versa.

Answer 1

In mysql, remote user login is disabled by default. It only allow localhost for security reason.

For cross verification, login to mysql and run command:

mysql> select user,host from mysql.user;
+------+--------------------------------+
| user | host                           |
+------+--------------------------------+
| root | localhost                      | 
| root | ::1                            |
+------+--------------------------------+
2 rows in set (0.00 sec)

If it is giving localhost only. it means you are only allowed to login from localhost and user as root(in this example).

If you want to allow login from remote system then you have to add like:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

This command will allow login from all remote IP and give access to all database to username user.

If you want to allow from specific IP address and allow access only specific database then you can run like:

GRANT ALL PRIVILEGES ON databasename.* TO 'user'@'ipaddress' IDENTIFIED BY 'password' WITH GRANT OPTION;

Note: Don't forget to change bind-address in /etc/mysql/my.cnf.

Open file /etc/mysql/my.cnf and change bind-address from 127.0.0.1 to 0.0.0.0 or comment out bind-address and restart mysql service.