[转帖]tidb 修改root密码

转载

jinanxiaolaohu6 2024-01-12 10:49:24

文章标签 mysql MySQL Database 文章分类 Python 后端开发

通过 {pd-ip}:{pd-port}/dashboard 登录 TiDB Dashboard，登录用户和口令为 TiDB 数据库 root 用户和口令。如果你修改过数据库的 root 密码，则以修改后的密码为准，默认密码为空。

1，传统修改密码方式，引发的问题

集群有3个tidb节点tidb1，tidb2，tidb3

往tidb1连接，执行命令

USE mysql;
UPDATE user SET Password = PASSWORD('password') WHERE user = 'root';
FLUSH PRIVILEGES;

在tidb1连接，需要连接使用-uroot -P4000 -p'password'，在tidb2，和tidb3使用上面的命令不能连接，不带-ppassword则可以连接

这个问题本人没有去验证，只是看到官方的github上面有说人问。得到的回复是，按照官方文档来

2，修改root账号

1. [tidb@jiankong ~]$ mysql -u root -p -P 4000 -h 10.0.10.18  
2. Enter password:  
3. Welcome to the MySQL monitor.  Commands end with ; or \g.  
4. Your MySQL connection id is 69  
5. Server version: 5.7.25-TiDB-v4.0.8 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible  
6.   
7. Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.  
8.   
9. Oracle is a registered trademark of Oracle Corporation and/or its  
10. affiliates. Other names may be trademarks of their respective  
11. owners.  
12.   
13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
14.   
15. mysql> show databases;  
16. +--------------------+  
17. | Database           |  
18. +--------------------+  
19. | INFORMATION_SCHEMA |  
20. | METRICS_SCHEMA     |  
21. | PERFORMANCE_SCHEMA |  
22. | mysql              |  
23. | test               |  
24. +--------------------+  
25. 5 rows in set (0.00 sec)  
26.   
27. mysql> use mysql  
28. Reading table information for completion of table and column names  
29. You can turn off this feature to get a quicker startup with -A  
30.   
31. Database changed  
32. mysql> select * from user\G;  
33. *************************** 1. row ***************************  
34.                  Host: %  
35.                  User: root  
36. authentication_string:  
37.           Select_priv: Y  
38.           Insert_priv: Y  
39.           Update_priv: Y  
40.           Delete_priv: Y  
41.           Create_priv: Y  
42.             Drop_priv: Y  
43.          Process_priv: Y  
44.            Grant_priv: Y  
45.       References_priv: Y  
46.            Alter_priv: Y  
47.          Show_db_priv: Y  
48.            Super_priv: Y  
49. Create_tmp_table_priv: Y  
50.      Lock_tables_priv: Y  
51.          Execute_priv: Y  
52.      Create_view_priv: Y  
53.        Show_view_priv: Y  
54.   Create_routine_priv: Y  
55.    Alter_routine_priv: Y  
56.            Index_priv: Y  
57.      Create_user_priv: Y  
58.            Event_priv: Y  
59.          Trigger_priv: Y  
60.      Create_role_priv: Y  
61.        Drop_role_priv: Y  
62.        Account_locked: N  
63.         Shutdown_priv: Y  
64.           Reload_priv: Y  
65.             FILE_priv: Y  
66.           Config_priv: Y  
67. 1 row in set (0.00 sec)  
68.   
69. ERROR:  
70. No query specified  
71.   
72. mysql> set password for 'root'@'%' = '************';  //参考官方文档  
73. Query OK, 0 rows affected (0.02 sec)  
74.   
75. mysql> flush privileges;  
76. Query OK, 0 rows affected (0.01 sec)

各个tidb节点都是可以登录的

3，添加用户，分配权限

1. mysql> create database `tank_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;  
2. Query OK, 0 rows affected (1.52 sec)  
3.   
4. mysql> use tank_test;  
5. Database changed  
6.   
7. mysql> CREATE TABLE `test` (  
8. 'ID',  
9. '' COMMENT '名称'  
10. '测试';  
11. Query OK, 0 rows affected (1.52 sec)  
12.   
13. mysql> CREATE USER tank@"%" IDENTIFIED BY 'tank';  
14. Query OK, 0 rows affected (0.05 sec)  
15.   
16. mysql> GRANT ALL PRIVILEGES ON tank_test.* TO tank@'%';  
17. Query OK, 0 rows affected (0.03 sec)  
18.   
19. mysql> flush privileges;  
20. Query OK, 0 rows affected (0.00 sec)

作者:海底苍鹰