Linux – 預設的資料庫 mysql

Loading

測試環境為 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
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料