Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to forget the root password of the mysql user name. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

When it comes to users, let's first talk about the user rights of mysql. 5.7 from the beginning, there have been a lot of changes:

1. The length of the user has changed. the length of users after 5.7.8 is 32 characters, and that of users before 5.7.8 is 16 characters. 5.6, I think it's 8? I forgot. Cough

2. The mysql_native_password plug-in is enabled by default, and password will be slowly removed later. There is no password field in mysql.user. Instead, the encrypted characters of the password are stored in the authentication_string field through the encryption algorithm of the plugin field.

3.5.7.4 the user's password can be set for a period of validity. After the expiration date, the password will be reset.

4. 5. 7. 6 from now on, create and alter user can lock down users, but after locking, users will not be able to access mysql. Such as: ALTER USER 'jeffrey'@'localhost' ACCOUNT LOCK

5.5.7.5 from now on, mysql provides ssl and RSA communication certificates, auto_generate_certs and sha256_password_auto_generate_rsa_keys parameter control, but must be set before startup

The initialization of 6.5.7 has also changed from the previous one. Delete anonymous users and initialize only root@'localhost' and mysql.sys@'localhost' users. And there is no test library.

7.5.7.8 an super_read_only parameter has been added to make supper users read-only except for root users.

8. In the past, grant can directly create and authorize new users who do not have them. Under the default mode (sql_mode), it is not possible to add new users through grant. You must first create user and then use grant permissions.

Refer to the official manual: http://dev.mysql.com/doc/refman/5.7/en/security.html

If ordinary users modify it, they will not go into details. If you log in and own super users, you will directly modify the following statement:

Alter user user_name@'host' identified by 'password'

Mainly talk about the repair of root password, two methods:

1. General methods:

Add the skip-grant-tables parameter to my.cnf at startup, or start the option-- skip-grant-tables, then enter mysql to change the mysql password, and restart the mysql service. There are many such posts on the Internet, so we won't go into details.

two。 Cool techs (modify without downtime):

This method is not recommended, but it is absolutely easy to use. I learned it from a Daniel.

Mysql to the current 5.7 mysql.user table is the myisam engine (it is said that after 8.0 will be innodb). When it comes to the myisam engine, we all know that the data of myisam tables are stored in .MYD. So, here comes cool techs.

1. Go to the data/mysql directory and copy the three files of the user table to another library, cp-a user*.. / databasename1/

two。 Users who need to have permission to operate the tables in the databasename1 library can connect to the databasename1 library and directly change the root user password to null: update user set entication_string='' where user='root'; finally check select user,host,entication_string from databasename1 where user='root'

3. Overwrite the 3 files of the user of the databasename1 directory under the original mysql library. Cp-a data/databasename1/user* data/mysql/

4. You can't log in directly at this time, because after changing the user's password, you need flush privileges to brush the new password into memory. We no longer have super users to flush, and we can't stop the phone. What should we do? Here we go, kill-HUP pid, execute this command in the operating system. Note that pid is the pid of mysql, not ppid. Don't suspend the parent process. The specific function of HUP, Baidu. Don't go into details.

This is done, at this time you can use root to log in without a password and change the password of root. OK. Call it a day!

The knowledge point of this method: first of all, the data of myisam is stored in the file, and the table structure, data, and index can be directly used by copy through replication, so this is also a security risk point, and the permissions of the mysql directory must be well controlled. The second is that kill-HUP pid initiates a hang signal, and then mysqld_safe will monitor the suspension and reload the configuration file. There is no task impact on the online business, and there will be no flash breakage.

This is the end of the article on "how to forget the mysql user name and root password". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.