測試環境為 CentOS 7 x86_64 .
安裝好的 Mariadb 會有一個預設資料庫 mysql.
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
進入 mysql 資料庫看看裡面儲存了哪一些 tables.
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 MariaDB [mysql]> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 24 rows in set (0.00 sec)
User table
來看看 User table 的內容,主要儲存使用者的資料,包含 Host , User , Password , Select_priv , Insert_priv , Update_priv , Delete_priv , Create_priv , Drop_priv , Reload_priv , Shutdown_priv , Process_priv , File_priv , Grant_priv , References_priv , Index_priv , Alter_priv , Show_db_priv , Super_priv , Create_tmp_table_priv , Lock_tables_priv , Execute_priv , Repl_slave_priv , Repl_client_priv , Create_view_priv , Show_view_priv , Create_routine_priv , Alter_routine_priv , Create_user_priv , Event_priv , Trigger_priv , Create_tablespace_priv , ssl_type | ssl_cipher , x509_issuer , x509_subject , max_questions , max_updates , max_connections , max_user_connections , plugin , authentication_string .
MariaDB [mysql]> select Host,User,Password from user; +-----------+------+-------------------------------------------+ | Host | User | Password | +-----------+------+-------------------------------------------+ | localhost | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | | 127.0.0.1 | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | | ::1 | root | *FD571203974BA9AFE270FE62151AE967ECA5E0AA | +-----------+------+-------------------------------------------+ 3 rows in set (0.00 sec)
如果要修改使用者密碼,也是針對這 table 來設定.
MariaDB [mysql]> update user set password=password('111111') where user='root';
Query OK, 0 rows affected (0.10 sec)
Rows matched: 3 Changed: 0 Warnings: 0
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.02 sec)
因為密碼不是明碼儲存,所以需要呼叫 SQL 函數 password 將資料做 MD5 加密 (One-Way Hash 雜湊法)
備份
- user: 備份使用者其資料與相對應的權限 ( privileges ).
[root@localhost ~]# mysqldump -u root -p mysql user > mysql_user.sql
- db: 備份資料庫與其使用者的相對應的權限 ( privileges ).
[root@localhost ~]# mysqldump -u root -p mysql db > mysql_db.sql
還有針對 tables_priv ( Table-level privileges ) 與 columns_priv ( Column-level privileges ) .
- event: 備份 Event 相關訊息.
[root@localhost ~]# mysqldump -u root -p mysql event > mysql_event.sql
- proc: 備份 Function 與 Procedure 相關訊息.
[root@localhost ~]# mysqldump -u root -p mysql proc > mysql_proc.sql
還原
剛剛備份了 mysql.user 的資料,並匯到另外一台 Mariadb (MySQL),但卻無法登入剛剛匯入的帳號.
[root@localhost ~]# mysql -u root -p mysql < mysql_user.sql Enter password: [root@localhost ~]# mysql -u linux ERROR 1045 (28000): Access denied for user 'linux'@'localhost' (using password: NO)
需要做下 SQL 指令 – FLUSH PRIVILEGES 才會生效.
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 39 Server version: 10.3.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)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> exit Bye
[root@localhost ~]# mysql -u linux Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 40 Server version: 10.3.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)]> exit Bye