Centos6.5 reset the mysql password and set to allow remote connection

Centos6.5 reset the mysql password and set to allow remote connection

Forget the root password. Reset the root password of mysql:

1. Modify the mysql configuration file my. cnf

1. Add the following sentence to the [mysqld] section: skip-grant-tables

[Mysqld]
Datadir =/var/lib/mysql
Socket =/var/lib/mysql. sock
Skip-name-resolve
Skip-grant-tables
Save and exit vi.

(Or execute mysqld_safe -- skip-grant-tables &)

2. Restart mysqld
#/Etc/init. d/mysqld restart
Stopping MySQL: [OK]
Starting MySQL: [OK]

3. log on to and modify the MySQL root Password
#/Usr/bin/mysql
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 3 to server version: 3.23.56
Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> UPDATE user SET Password = password ('new-password') WHERE User = 'root ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

Mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Mysql> quit
Bye
4. Modify the MySQL Logon Settings
# Vi/etc/my. cnf
Delete the skip-grant-tables added to the [mysqld] section.
Save and exit vi.

5. Restart mysqld
#/Etc/init. d/mysqld restart
Stopping MySQL: [OK]
Starting MySQL: [OK]

II,

Stop mysql Service

/Etc/init. d/mysqld stop

Sudo mysqld_safe -- skip-grant-table &

Mysql

Use mysql;

Update user set password = password ('yourpasswd') where user = 'root ';

Flush privileges;

Restart the mysql service to log on with a new password.

/Etc/init. d/mysqld restart

Allow remote connection to mysql

This error occurs when you connect to MySQL through navicat.
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 %

I,

Log on to MySQL and 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:

MySQL> flush privileges;

II,

Mysql> grant all privileges on *. * to 'root' @ '%' withgrant option;

Query OK, 0 rows affected (0.02 sec)

Mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Mysql> selectuser. host from user;

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

| User | host |

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

| Root | % |

| Root | 127.0.0.1 |

| Repl_user | 192.168.1.52 |

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

3 rows in set (0.00 sec)

Allow specified IP addresses to access mysql

Mysql> grant all privileges on *. * to 'root' @ ***. *** identifiedby '123 ';
QueryOK, 0 rows affected( 0.00 sec)