Solution: mysql cannot connect to the database remotely

In general, mysql does not enable the remote access permission, which is relatively safer. But today I will introduce it to my friends who need the remote access permission.

In general, mysql does not enable the remote access permission, which is relatively safer. But today I will introduce it to my friends who need the remote access permission.

1. Login

Mysql-u root-p

View user table

The Code is as follows:

Mysql> use mysql; Database changed Mysql> host, user, password from user; + -------------- + ------ + --------------------------------------------- + | Host | user | password | + -------------- + ------ + --------------------------------------------- + | Localhost | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | | 192.168.1.1 | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | + -------------- + ------ + --------------------------------------------- + 2 rows in set (0.00 sec)

You can see the created root user in the user table. The host field indicates the host to log on to. The value can be an IP address or a host name,

(1) If you want to log on with a local IP address, you can change the Host value to your own Ip address.

2. implement remote connection (authorization)

Changing the value of the host field to % indicates that you can log on to the mysql server as a root user on any client machine. We recommend that you set the value to % during development.
Update user set host = '%' where user = 'root ';

Change the permission to all privileges.

The Code is as follows:

Mysql> use mysql; Database changed Mysql> grant all privileges on *. * to root @ '%' identified by "root "; Query OK, 0 rows affected (0.00 sec) Mysql> select host, user, password from user; + -------------- + ------ + --------------------------------------------- + | Host | user | password | + -------------- + ------ + --------------------------------------------- + | Localhost | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | | 192.168.1.1 | root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | | % | Root | * A731AEBFB621E354CD41BAF207D884A609E81F5E | + -------------- + ------ + --------------------------------------------- + 3 rows in set (0.00 sec)

In this way, the machine can remotely access MySql on the machine with the username root Password root.

3. remote connection (Table change)

The Code is as follows:
Use mysql; Update user set host = '%' where user = 'root ';

In this way, you can access Mysql through the root user at the remote end.

Another method

By default, mysql cannot be accessed through a remote machine. You can enable remote access through the following configuration.

My mysql environment is ubuntu + mysql5

1. Modify/etc/mysql/my. conf, bind-address, and specify as the actual IP address of the Local Machine. After modifying your my. con, it looks like the following:

The Code is as follows:
[Mysqld] 　 # # * Basic settings # Default-character-set = utf8 Default-storage-engine = innodb Bind-address = 192.168.1.107 User = mysql Pid-file =/var/run/mysqld. pid Socket =/var/run/mysqld. sock Port = 3306 Basedir =/usr Datadir =/var/lib/mysql Tmpdir =/tmp Language =/usr/share/mysql/english Skip-external-locking

2. log on to mysql using root and execute the following command:

The Code is as follows:
Grant all on sonardb. * to sonar @ '%' identified by '123 '; Grant all on sonardb. * to sonar @ localhost identified by '20140901 ';

Sonardb replaces the name of the database you want to access. sonar is the username you want to use, and 123456 is replaced with your password. This enables remote access.

For more details, see: http://www.111cn.net/database/mysql/34989.htm

One of the two methods above is to use the root user to operate in cmd mode, and the other is to operate in the configuration file.