Mysql database, remote access _ MySQL

Mysql database, remote access to the current mysql database on the server, remote access, do not want to publish the root account, so, created a demo account, allow the demo account to access the shandong database in the mysql database anywhere.
Solution 1:
Run the following command on the machine where mysql is installed:
1: Create a user

The code is as follows:

Create user demo identified by 123456"

2,

The code is as follows:

Mysql> grant all privileges on shandong. * TO 'demo' @ '%' WITH GRANT OPTION
// Grant the data access permission to any host. you can also perform the following operations:
Grant all privileges on shandong. * TO 'demo' @ '%' identified by '000000' with grant option;

3,

The code is as follows:

Mysql> FLUSH PRIVILEGES
// The modification takes effect.

4,

The code is as follows:

Mysql> EXIT
// Exit the MySQL server so that you can log on to any other host as a demo.

Reference
In addition, when a client is used to connect to mysql, the connection fails. it seems that you need to re-authorize the user. The procedure is as follows:
[Root @ cicro108 mysql] # bin/mysql-uroot-p-h 127.0.0.1-A cws3
Enter password:
Welcome to the MySQL monitor. Commands end with or/g.
Your MySQL connection id is 1863 to server version: 4.1.20-standard
Type 'help; 'or'/h' for help. type'/C' to clear the buffer.
Mysql> grant all privileges on *. * to root @ "%" identified by "mysql ";
Query OK, 0 rows affected (0.17 sec)
After the permission is changed, the remote connection is still not available, but you can use the following operations.
Mysql> grant all privileges on *. * to root @ "%" identified by "mysql" with grant option;
Query OK, 0 rows affected (0.17 sec)
At this moment, the root user can be remotely connected. of course, other non-root users can also be remotely connected here.

Solution 2:
MySQL 1130 error solution:
This error occurs when you connect to MySQL through MySQL-Front or MySQL administrator.
ERROR 1130: Host ***. *** is not allowed to connect to this MySQL server
This indicates that the connected user account does not have the permission for remote connection and can only log on to the local machine (localhost.
You need to change the host entry in the user table of the MySQL database.
Rename localhost as %

Specific steps: log on to MySQL
Use MySQL first;
An error occurs when the update method is provided by others.
MySQL> update user set host = '%' where user = 'root ';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'primary'
Then, you can view the host information of the database as follows:
MySQL> select host from user where user = 'root ';
+ ----------------------- +
| Host |
+ ----------------------- +
| % |
| 127.0.0.1 |
| Localhost. localdomain |
+ ----------------------- +
3 rows in set (0.00 sec)
The host already has the value %, so run the following command:

The code is as follows:

MySQL> flush privileges;

Connect to MySQL administrator !!

----------------------------------------------- The dividing line of all evil -------------------------------------------------------------

ERROR 2003 (HY00

The reason is that for security considerations, the default configuration of MySQL only allows local login

Open the/etc/mysql/my. cnf file and find bind-address = 127.0.0.1 and change it to bind-address = 0.0.0.0.

Restart mysql: sudo/etc/init. d/mysql restart

Reconnect, error 1045

ERROR 1045 (28000): Access denied for user 'test' @ 'X. x. x. X' (using password: NO)

A: The reason is that you have not set the remote host logon permission for the logon username. Another possibility is that you need to reset the password... it may be caused by the authorization operation ..

Log on locally with root: mysql-u root-p

Modify the Host field of the username in the user table of MySQL database and change localhost to %

Use mysql;

Update user set Host = '%' where User = 'username ';

To set this permission, you must log on as the ROOT user. Unfortunately, the ROOT password does not exist.

Retrieve the ROOT password and set remote logon

Mysqld_safe -- skip-grant-tables &

Mysql-u root mysql

Mysql> UPDATE user SET Password = PASSWORD ('newpassword') where USER = 'root ';

Mysql> flush privileges;

Set the ROOT remote connection to update user set host = '%' where user = 'root ';

View the process. you can see the MYSQLD_SAFE and MYSQL processes. MYSQL can be used normally at this time. However, you can see the -- skip-grant-tab when you view the parameters.

Enter the mysqld_safe command line and immediately enter mysql-u root mysql. no error is allowed. or you can open a new window.

Error 1062 occurred during UPDATE execution.

RROR 1062 (23000): Duplicate entry '%-root' for key 'primary'

If ERROR 1062 (23000): Duplicate entry '%-root' for key 'primary' occurs when executing the update statement, multiple root users are recorded in the USER table.

Select host from user where user = 'root ';

Check whether the value of % already exists in the host.

Mysql> select host, user from user where user = 'root ';

+ ----------------------- + ------ +

| Host | user |

+ ----------------------- + ------ +

| % | Root |

| 127.0.0.1 | root |

|: 1 | root |

| Localhost. localdomain | root |

Then, use the ROOT user to log on and change the remote connection permission of the user account. the following ERROR occurs: ERROR 1044 (42000): Access denied for user ''@ 'localhost' to database 'mysql '.

ERROR 1044 (42000): Access denied for user ''@ 'localhost' to database 'mysql '.

The reason is that the user table of the mysql database contains an account with an empty username, that is, an anonymous account. as a result, the root account is used for logon, but the logon is actually anonymous, you can see the '@ 'localhost' in the error message.

First shut down the MYSQL process ..

Then

# Mysqld_safe -- skip-grant-table

The screen displays: Starting demo from .....

Remember, and enter

# Mysql-u root mysql

Mysql> delete from user where USER = '';

Mysql> flush privileges;

If Starting demo from... is displayed, input other commands, and then use mysql-u root mysql. This error occurs again.

KILL the MYSQL process and restart the normal process ..

Set remote host connection permissions

Update user set host = '%' where user = 'fanzkcom _ fanzk ';

Flush privileges;

However, in the actual connection, although it can be connected, it does not have the permission to access the database.

You have to set permissions next.

Set user and Database permissions

Grant all privileges on fanzkcom_fanzk. * to fanzkcom_fanzk @ '%' identified by '123 ';

Flush privileges;

Single quotes must be placed on both sides of the percent sign. Otherwise, the syntax is incorrect.

Then, the system prompts 1045 error. dizzy. after thinking about it for a long time, try again with the password.

Update mysql. user set password = password ('XXX') where User = "fanzkcom_fanzk"

Flush privileges;