Shulou(Shulou.com)06/01 Report--

I do not know if you have any knowledge of similar articles on how to change the password of the mysql database, today I am here to give you a brief introduction. If you are interested, let's take a look at the body. I believe you will gain something after reading how to change the password of the mysql database.

Environment introduction:

Operating system: Red Hat linux6

Database version: Mysql5.7

Note: after the initial installation, MySQL5.7 will generate a random initial password, which is recorded in / var/log/mysqld.log. You can check it through the head command and find the password keyword to find the random initial password.

The password is set as follows:

Method 1: edit the user table directly with update

Mysql-u root / * enter mysql*/mysql > use mysql / * enter mysql user * / mysql > update user set authentication_string=password ('newpass'). Password_expired='N where user='root'; / this sql is applicable to the version after mysql5.7 (including 5.7). The version before 5.7 is not authentication_string, but Password, so change authentication_string to password to set the password. Mysql > FLUSH PRIVILEGES; / * tell the CVM to reload the authorization table * /

Exit mysql, edit the / etc/my.cnf file, comment on the contents of skip-grant-tables, and log in with the account password when restarting the service.

Method 2:mysqladmin command (of course, if your mysqladmin is not connected to mysql server, or if you have no way to execute mysqladmin, this method is invalid)

Mysqladmin-u root password "newpass" / * sets the password to newpass*/ if you want to change the username password

Method 3: assign a password to the account with the set password command

Mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD (' newpass')

Method 4: if you do not connect as an anonymous user, you can change your password by omitting the FOR clause:

Mysql > SET PASSWORD = PASSWORD ('newpass')

Method 5: you can also use GRANT USAGE statements at the global level (in.) To specify an account password without affecting the current permissions of the account:

Mysql > GRANT USAGE ON *. * TO 'root'@'%' IDENTIFIED BY' newpass'

Add: create a password when creating a new account

Mysql > INSERT INTO user (Host,User,Password) VALUES ('%', 'oldboy',PASSWORD (' newpass')); mysql > FLUSH PRIVILEGES

When you use SET PASSWORD, INSERT, or UPDATE to specify an account password, you must encrypt it with the PASSWORD () function. (the only exception is that you don't need to use PASSWORD () if the password is empty.) You need to use PASSWORD () because the user table holds the password in encrypted form, not in clear text.

Mysql > INSERT INTO user (Host,User,Password) VALUES ('%', 'oldboy','newpass'); mysql > FLUSH PRIVILEGES

The result is that the password 'newpass' is not encrypted after it is saved to the user table. When oldboy uses this password to connect to the cloud server, the value is encrypted and compared with what is stored in the user table. However, the saved value is the string 'newpass', so the comparison will fail, and the CVM rejects the connection:

Access denied

The method to change the password is as follows:

Method 1: if you forget the root password, you can change it through the first method of setting the password above.

1. First modify the configuration file / etc/my.cnf, add the skip-grant-tables parameter under "mysql", and restart the service. You can log in to mysql without entering a password at this time

two。 Enter the mysql library, enter use mysql, and modify the user table of mysql with update

3.FLUSH PRIVILEGES; / refresh permissions /, and then exit mysql

4. Log out or delete the skip-grant-tables parameter in the my.cnf configuration file and restart the mysql service

Method 2: use mysqladmin

Mysqladmin-u root-p password "newpass2" / * the password prompted by enter at this time is the old password * /

Mysqladmin-u root-pnewpass password "newpass2" / * if you remember the old password, use this way * /

When you use the navicat mysql tool to connect to mysql Services Times err1130 error, the reasons and solutions are as follows:

1. Check to see if the firewall is off service iptables stop

two。 Table change method: check whether it is because the account does not support remote, only localhost.

Log in to the local database and enter the mysql user use mysql

Just change the localhost in the host column of the corresponding user in the user data table to%.

SQL:

Mysql > select host,user from user order by host desc,user desc;-check the user's hostmysql > update user set host='%' where user='root';-change localhost to%

In MySQL,'% 'means that you can log in to the MySQL database on any host, so why do you need to explicitly create a user whose login host is localhost?

This involves initializing users, anonymous users and connection authentication policies during MySQL installation, which is analyzed in depth below.

Source of reprint:

1. Deep Analysis of MySQL ERROR 1045 (28000)

After reading this article on how to change the password of mysql database, what do you think? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.