Solution MySQL does not allow remote access to the method

Source: Internet
Author: User
Tags flush web services

Solution MySQL does not allow remote access to the method

Workaround:
1. Change Table method. Your account may not be allowed to log in remotely, only in localhost. This time as long as the computer in localhost, login mysql, change the "MySQL" Database in the "User" table in the "host" item, from "localhost" renamed "%"

Mysql-u root-pvmwaremysql>use MySQL;
Mysql>update User Set host = '% ' where user = ' root ';
Mysql>select host, user from user;

2. Authorization law. For example, you want to myuser use MyPassword to connect to a MySQL server from any host.

GRANT all privileges on *.* to ' myuser ' @ '% ' identified by ' MyPassword ' WI
TH GRANT OPTION;

If you want to allow the user to connect to the MySQL server from the IP-192.168.1.6 host, and use MyPassword as the password myuser

GRANT all privileges on *.* to ' myuser ' @ ' 192.168.1.3′identified by
' MyPassword ' with GRANT OPTION;

I used the first method, just started to find no, online check, less execution of a statement mysql>flush privileges
Make the changes take effect. It's okay.


X:>mysql-u Root-pvmware

mysql> use MySQL;
mysql> Update user Set host = '% ' where user = ' root ';
Mysql> Select Host, user from user;

mysql> flush Privileges;


Note:mysql> flush privileges; Make the changes take effect.


Remote connection to MySQL needs to be done

1. Enter MySQL, create a new user xuys:

Format: Grant permission on database name. Table name user @ Login host identified by "User password";
Grant Select,update,insert,delete on *.* to xuys@192.168.88.234 identified by "xuys1234";


View the results and execute:
Use MySQL;
Select Host,user,password from user;


You can see that the Xuys user that you just created is already in the user table. The host field represents the hosts that are logged on, their values can be IP, or the host name is available, and changing the value of the host field to% means that the Xuys user can log on to the MySQL server on any client machine, and it is recommended that you set it to% at development time.
Update user Set host = '% ' where user = ' Xuys ';

2.
./mysqladmin-u root-p pwd Reload
./mysqladmin-u root-p pwd shutdown

3.
./mysqld_safe--user=root &

Remember: Any changes to the authorization table need to be reload, that is, step 3rd.

If you cannot connect from the client through the above 3 steps, insert a record in the DB table of the MySQL database by doing the following:
Use MySQL;
INSERT into DB values (' 192.168.88.234 ', '% ', ' xuys ', ' y ', ' y ', ' y ', ' y ', ' y ' ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y-axis ', ' y ', ' y ';
Update db Set host = '% ' where user = ' Xuys ';


Repeat the 2nd, 3 steps above.


A2:

The separation of the web from the MySQL database is a good choice to avoid the lack of web resources due to the large number of database queries consuming the CPU, while allowing the Web server's resources to do its utmost to provide browsing services, while the database server handles only database transactions alone.

I am not very familiar with the principle of this aspect, my practice is actually the following to say, very simple. We have better experience and skills to share.

Scope of application: have independent host permissions
Hardware configuration: Two servers, as for the specific server hardware configuration is not within the scope of this article
Where: A for the Web server (assuming IP is: 192.192.192.192), b for MySQL data server (assuming IP: 168.168.168.168)

To proceed to action:

1. Configure Web Services in Web Server A. There are a lot of articles on this. Suppose the IP of the Web server is: 192.192.192.192
2. Install MySQL service on database server B
3. Now the new version of the MySQL general default does not allow remote connection, the need to establish a remote connection account can be


Use the root account on the command line to enter MySQL
Mysql-u Root-p Pass

Choose to enter MySQL database
Use MySQL;

View all existing accounts and addresses
SELECT ' Host ', ' user ' from ' user ';

For example, mine is:

+------------+-------+
| Host | User |
+------------+-------+
|          localhost | |
| localhost | PMA |
| localhost | Root |
+------------+-------+
3 Rows in Set (0.00 sec)


In other words, there are three (localhost) accounts that allow only local connections, respectively, ROOT,PMA, and empty users.

Now that you decide to have root have the permissions of the remote link on the Web server A above, that's it:
UPDATE ' user ' SET ' Host ' = ' 192.192.192.192 ' WHERE ' user ' = ' root ' LIMIT 1;

So 192.192.192.192 this Web server can be remotely connected to this database server, if you want any remote machine can connect to this database, you will change 192.192.192.192 to%, but do not recommend this, because you know!

If you want to create a new user New_user have access to remote links, that's it:
INSERT into ' user ' (' Host ', ' user ', ' Password ', ' select_priv ', ' insert_priv ', ' update_priv ', ' delete_priv ', ' creat E_priv ', ' drop_priv ', ' reload_priv ', ' shutdown_priv ', ' process_priv ', ' file_priv ', ' grant_priv ', ' References_priv ' , ' Index_priv ', ' alter_priv ', ' show_db_priv ', ' super_priv ', ' create_tmp_table_priv ', ' lock_tables_priv ', ' EXECUTE_PR IV ', ' repl_slave_priv ', ' repl_client_priv ', ' ssl_type ', ' ssl_cipher ', ' x509_issuer ', ' x509_subject ', ' max_questions ', ' max_updates ', ' max_connections ') VALUES (' 192.192.192.192 ', ' New_user ', PASSWORD (' New_user_password '), ' y ', ' y ', ' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ', ', ', ', ', ', ', ', ', ' 0 ', ' 0 ', ' 0 ');

Change the New_user to the name you want, and the password is: New_user_password, of course, you can set it at your discretion.

When your database can be connected remotely, you can set the $dbhost variable for your MySQL database Server B IP in your Web server's forum config.inc.php:
$dbhost = ' 168.168.168.168 ';

In practice, it is best to have two machines in the same network section/firewall within the same computer room. Of course, if possible, it would be better to place the database server in a local area network on the Web server.


Q3:

It's a little more concise:
Grant all on yourdb.* to Yourusername@yourhost identified by "YourPassword";
Flush privileges; Make permission effective immediately

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.