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

Background of the problem:

Before using a set of mysql 5.6.25, for some reason I carried out a clearance. As you know, the second time we execute mysql_install_db to initialize the database, we do not store the root temporary password.

To access the mysql database, introduce the following article.

Case Process:

0, start database without password authentication

[root@standbygtid mysql]#nohup mysqld_safe --user=mysql --skip-grant-tables&

1. Configure the password of root user

mysql> set password for 'root'@'localhost'='system';

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

2. Refresh the operation of modifying user password to mysql.user table.

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3, modify the database user password, prompt password needs 41 bytes

mysql> set password for 'root'@'localhost'='system';

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

4. The password function is used to modify the user password successfully.

mysql> set password for 'root'@'localhost'=password('system');

Query OK, 0 rows affected (0.00 sec)

5. Close the database

[root@standbygtid mysql]#mysqladmin -uroot -psystem shutdown

6. Start the database

[root@standbygtid mysql]# nohup mysqld_safe --user=mysql&

7. You can log in with the database user password configured above.

[root@standbygtid mysql]# mysql -uroot -psystem

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.25-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

8, password function No matter how many bits of the string used, the resulting byte width is all 41

mysql> select password('system'),length(password('system'));

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

| password('system') | length(password('system')) |

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

| *576EE5B74C20E68F2A5A240F3E408E6DE43DD73F | 41 |

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

1 row in set (0.00 sec)

mysql> select password('syste'),length(password('syste'));

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

| password('syste') | length(password('syste')) |

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

| *795AF4045357E7DA27CBF22263514F881880C4AC | 41 |

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

1 row in set (0.00 sec)

9. What is the meaning of the above 41 bytes? Check the official mysql 5.6 document. The above 41 bytes are related to the method of password authentication.

The method of password authentication is controlled by the system variable old_passwords.

10, meaning of old_passwords system variable

old_passwords controls the password authentication method used by the password function, which affects how the create user and grant operations generate database user passwords.

Several values of old_passwords

Value Password Hash Method Plug-in Used by Password Hash Method Default

0 mysql version 4.1 hash mysql_native_password Yes

1 mysql 4.1 hash method sha256_password

2 sha-256 hash method sha256_password

11, you can see that different values of old_password affect the width of the value generated by the password function

mysql> set old_passwords=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select password('syste'),length(password('syste'));

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

| password('syste') | length(password('syste')) |

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

| *795AF4045357E7DA27CBF22263514F881880C4AC | 41 |

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

1 row in set (0.00 sec)

mysql> set old_passwords=1;

Query OK, 0 rows affected (0.00 sec)

mysql> select password('syste'),length(password('syste'));

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

| password('syste') | length(password('syste')) |

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

| 46acda267e174d58 | 16 |

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

1 row in set (0.00 sec)

mysql> set old_passwords=2;

Query OK, 0 rows affected (0.00 sec)

mysql> select password('syste'),length(password('syste'));

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

| password('syste') | length(password('syste')) |

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

| $5$\]^D8P`aMt\vp2Pu$lDCzMsToPBl56CdhB2pjetpvqEiL9BMhdt1TDbMbzL6 | 68 |

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

1 row in set (0.01 sec)

12, the above different values of old_passwords correspond to the following different plugins

mysql> show plugins;

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

| Name | Status | Type | Library | License |

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

| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |

| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |

| sha256_password | ACTIVE | AUTHENTICATION | NULL | PROPRIETARY |

If you want to specify the default user password authentication plug-in for database startup or creation

This plug-in is the value corresponding to the above show plugins, and there is no corresponding system variable, only options, see below

--default-authentication-plugin

Optional values:

mysql_native_password Default

sha256_password

This option can be specified on the command line when starting the database or in the configuration file

14, If upgrading mysql, Be sure to consider the value of old_password or--default-authentication-plugin, Otherwise the old version of mysql.

The client may not log in to the upgraded mysql database.

Thinking Summary:

1, there is a problem, according to the error prompt for literal analysis

2. If there is a problem, query the syntax of the corresponding error sentence in the official manual to obtain further diagnostic information or clues.

3, the question raised must be placed in a large context, that is, to serve the business, what is the goal of your business, such as: the reliability and security of database users, the database to be upgraded immediately, the performance of the database, and so on.

4, database learning is an iterative process, some knowledge, not all at once can be fully understood, need to be further mastered from the literal or other technical reverse

You can pay attention to my Weixin Official Accounts and regularly send some database-related articles every day. Welcome to communicate.

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.