忘記 root 密碼

1. 先關閉 mariadb 服務。

   [root@kvm3 ~]# systemctl stop mariadb.service
   

2. mysqld_safe 跳過 grant tables，因為接著要下命令，所以讓其背景執行。

   [root@kvm3 ~]# mysqld_safe --skip-grant-tables &
   [1] 4804
   [root@kvm3 ~]# 251013 18:31:59 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
   251013 18:31:59 mysqld_safe Starting mariadbd daemon with databases from /var/lib/mysql
   

3. 接著就可以免密碼登入 mariadb 了。

   [root@kvm3 ~]# mariadb -uroot
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 4
   Server version: 10.5.16-MariaDB MariaDB Server
   
   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY '123qwe';
   ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
   

4. 使用 mysql 資料庫。

   MariaDB [(none)]> 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
   

5. Mariadb 10.4 以後版本，表格 mysql.global_priv 取代 mysql.user，之前以變更 mysql.user 的方法，在 mysqld_safe --skip-grant-tables 下都無法執行。
   1. 方法一：update 所有 root 帳號。

      MariaDB [mysql]> update user set password=password("123123") where user='root';
      ERROR 1348 (HY000): Column 'Password' is not updatable
      

   2. 方法二：alter 變更帳號 root@localhost 的密碼。

      MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY '123123';
      ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables 
      option so it cannot execute this statement
      

   3. 方法三：update 變更帳號 root 的密碼。

      MariaDB [mysql]> update user set authentication_string=PASSWORD("123123") where User='root';
      ERROR 1348 (HY000): Column 'authentication_string' is not updatable
      

6. 退出 mariadb，查詢 mariadb-secure-installation 腳本，update mysql.global_priv 表格的方式更新 root 密碼。

   [root@kvm3 ~]# grep esc_pass /usr/bin/mariadb-secure-installation
       esc_pass=`basic_single_escape "$rootpass"`
       echo "password='$esc_pass'" >>$config
       esc_pass=`basic_single_escape "$password1"`
       do_query "UPDATE mysql.global_priv SET priv=json_set(priv, '$.plugin', 'mysql_native_password', '$.authentication_string', PASSWORD('$esc_pass')) WHERE User='root';"
   

7. 再登入 mariadb ，update mysql.global_priv 表格的方式，更新 root 密碼為 123123。

   [root@kvm3 ~]# mariadb -uroot
   Welcome to the MariaDB monitor.  Commands end with ; or \g.
   Your MariaDB connection id is 7
   Server version: 10.11.11-MariaDB MariaDB Server
   
   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   MariaDB [(none)]> UPDATE mysql.global_priv SET priv=json_set(priv, '$.plugin',
       -> 'mysql_native_password', '$.authentication_string', PASSWORD('123123'));
   Query OK, 3 rows affected (0.005 sec)
   Rows matched: 3  Changed: 3  Warnings: 0
   

8. 更新權限。

   MariaDB [(none)]> flush privileges;
   Query OK, 0 rows affected (0.003 sec)
   

9. 退出。

   MariaDB [(none)]> quit
   Bye
   

10. mysql_safe 還在背景執行，先刪除。

    [root@kvm3 ~]# jobs 
    [1]+  Running                 mysqld_safe --skip-grant-tables &
    [root@kvm3 ~]# kill %1
    

11. 再啟動 mariadb 服務。

    [root@kvm3 ~]# systemctl start mariadb.service 
    [1]+  Done                    mysqld_safe --skip-grant-tables
    

12. 再用免密碼方式，已無法登入 mariadb。

    [root@kvm3 ~]# mariadb -uroot
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    

13. 使用新設定的密碼，成功登入 mariadb。

    [root@kvm3 ~]# mariadb -uroot -p123123
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 10.11.11-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]> quit
    Bye