MySQL cannot be remotely logged on _ User Creation and authorization _ grantallprivilegeson

Source: Internet
Author: User
By default, the root account configured for MySQL can only be logged on locally. The following error occurs during Remote Logon: 1103hostxxx. xxx. xxx. xxisnotallowedtoconnectothismysqlserver. Or 1045Accessdeniedforuserroot@10.163.225.155 (usingpassword: YES) because MySQL specified an account only

By default, the root account configured for MySQL can only be logged on locally. The following message is displayed during Remote Logon: 1103 host xxx. xxx. xxx. xx is not allowed to connec to this mysql server. or 1045 Access denied for user root@10.163.225.155 (using password: YES ). the reason is that MySQL can only specify an account

By default, the root account configured for MySQL can only be logged on locally. The following message is displayed during Remote Logon:

1103-host xxx. xx is not allowed to connec to this mysql server.

Or

1045-Access denied for user 'root' @ '10. 163.225.155 '(using password: YES ).

The reason is that MySQL specifies that an account can only log on to the server somewhere; for example, the root@192.168.1.155 means that the account root can only log on to the MySQL server on a machine with the IP address 19.168.1.155.

There are two solutions:

1) modify the root account to log on to an address (the address can be an IP address, machine name, domain name, or % represents any address) to solve the problem. Run Command Line Client locally, log on as a root user and run the following command:

Mysql> use mysql;

Mysql> update user set host = '%' where user = 'root ';

Mysql> flush privileges;-refresh the MySQL system permission table;

This is done. If you still cannot log on, try to restart MySQL;

2) create an account for remote logon. Run Command Line Client locally and Log On As root to execute the Command:

Mysql> grant permission 1, permission 2 ,... Permission n on database name. Table name to user name @ user address identified by 'Connection password ';

Permission 1, permission 2 ,... Permission n indicates 14 permissions, including select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, and file.
If the permission is replaced by all privileges or all, all permissions are granted to the user.
When the database name. Table name is replaced by *. *, it grants the user the permission to operate all the tables in the database on the server.
The user address can be localhost, IP address, machine name, or domain name. You can also use '%' to connect from any address.
The 'Connection password' cannot be blank; otherwise, creation fails.

Mysql> grant select, insert, update, delete, create, drop on vtdc. employee to joe@10.163.225.87 identified by '123 ′;
Assign the user joe from 10.163.225.87 the permission to perform select, insert, update, delete, create, drop, and other operations on the database's vtdc employee table, and set the password to 123. If no joe User exists, the user is automatically created.

Mysql> grant all privileges on vtdc. * to joe@10.163.225.87 identified by '000000 ′;
Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in the database vtdc and set the password to 123.

Mysql> grant all privileges on *. * to joe@10.163.225.87 identified by '000000 ′;
Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in all databases and set the password to 123.

Mysql> grant all privileges on *. * to joe @ localhost identified by '000000 ′;
Grant the local user joe the permission to perform all operations on all tables in all databases and set the password to 123.

Original article address: MySQL cannot be remotely logged on _ User Creation and authorization _ grant all privileges on. Thank you for sharing your original article.

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.