tidb 修改root密码

通过 {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账号

[tidb@jiankong ~]$ mysql -u root -p -P 4000 -h 10.0.10.18
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.7.25-TiDB-v4.0.8 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2020, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user\G;
*************************** 1. row ***************************
                 Host: %
                 User: root
authentication_string:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
         Process_priv: Y
           Grant_priv: Y
      References_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
           Index_priv: Y
     Create_user_priv: Y
           Event_priv: Y
         Trigger_priv: Y
     Create_role_priv: Y
       Drop_role_priv: Y
       Account_locked: N
        Shutdown_priv: Y
          Reload_priv: Y
            FILE_priv: Y
          Config_priv: Y
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> set password for 'root'@'%' = '************';  //参考官方文档
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

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

3，添加用户，分配权限

mysql> create database `tank_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (1.52 sec)

mysql> use tank_test;
Database changed

mysql> CREATE TABLE `test` (
    ->   `test_id` int(4) UNSIGNED NOT NULL COMMENT 'ID',
    ->   `test_name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试';
Query OK, 0 rows affected (1.52 sec)

mysql> CREATE USER tank@"%" IDENTIFIED BY 'tank';
Query OK, 0 rows affected (0.05 sec)

mysql> GRANT ALL PRIVILEGES ON tank_test.* TO tank@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)