How to Change Database Password

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何修改数据库密码

| username: TiDBer_kMTvTIEv

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact]
After installing the cluster, how to change the password when logging into the MySQL database

image
image971×212 10.1 KB

image
image991×380 14.2 KB

[Attachment: Screenshot/Log/Monitoring]

2
| username: zhanggame1 | Original post link

Execute this

alter user root@'%' identified by 'XXXXXX'

Note that those are single quotes; the ones copied from the community might turn into Chinese quotes.

3
| username: 普罗米修斯 | Original post link

use mysql;

SET PASSWORD FOR ‘root’@‘%’ = ‘xxxx’;

update user set authentication_string=“xxx” where user=“root”; (Choose one of the commands to set the root password)

4
| username: 春风十里 | Original post link 
mysql> create user 'test'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.02 sec)

mysql>  select User,authentication_string from mysql.user where user='test';
+------+-------------------------------------------+
| User | authentication_string                     |
+------+-------------------------------------------+
| test | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password AS '111111';
ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() funct
ion.mysql> ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.0 |
+--------------------+
1 row in set (0.00 sec)
5
| username: 烂番薯0 | Original post link

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘rock’;

6
| username: kelvin | Original post link

use mysql;
ALTER USER ‘root’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;
flush privileges;

7
| username: zhanggame1 | Original post link

flush privileges; is not necessary.

8
| username: dba远航 | Original post link

There are many methods: set password; alter user; update user

9
| username: TiDBer_lBAxWjWQ | Original post link

The method is the same as changing the password in MySQL.

10
| username: TIDB-Learner | Original post link

MySQL is almost the same.

11
| username: Kongdom | Original post link

You can refer to the official documentation for settings:

image
image755×466 20 KB

12
| username: dba远航 | Original post link

Read the MySQL manual more.

13
| username: Kongdom | Original post link

:yum: You can also take a look at the TiDB documentation, there are descriptions in the official documentation as well. Check the reply above.

14
| username: TiDBer_rvITcue9 | Original post link

Exactly the same as MySQL.

15
| username: linnana | Original post link

High MySQL compatibility

16
| username: 哈喽沃德 | Original post link

The same as MySQL.

17
| username: 江湖故人 | Original post link

The correct order is to search the official documentation first before asking questions.

18
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.