MariaDB / MySQL 備份

測試環境為 CentOS 7 x86_64 (虛擬機)

先來建立一些資料.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-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.

建立 testdb 資料庫與 employee , employee1 資料表,欄位為使用者 (Name) 與時間(ts 為 timestamp)

MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> create table employee (Name char(20),ts timestamp);
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben1' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.01 sec)
 
MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben2' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.01 sec)
 
MariaDB [testdb]> INSERT INTO employee (Name ,ts ) VALUES ('Ben3' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.01 sec)
 
MariaDB [testdb]> create table employee1 (Name char(20),ts timestamp);
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [testdb]> INSERT INTO employee1 (Name ,ts ) VALUES ('Ben1' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.01 sec)
 
MariaDB [testdb]> INSERT INTO employee1 (Name ,ts ) VALUES ('Ben2' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.01 sec)
 
MariaDB [testdb]> INSERT INTO employee1 (Name ,ts ) VALUES ('Ben3' , CURRENT_TIMESTAMP() );
Query OK, 1 row affected (0.01 sec)
 

備份

  • Database
    把 testdb 整個資料庫備份下來.

    [root@localhost ~]# mysqldump -u root -p testdb > testdb.sql
    Enter password: 
    
  • Table
    針對 testdb 資料庫裡面的 employee 資料表備份下來.

    [root@localhost ~]# mysqldump -u root -p testdb employee > testdb.employee.sql
    Enter password: 
    
  • User
    使用者資料儲存在預設資料庫 mysql 的 user 資料表裡面.

    [root@localhost ~]# mysqldump -u root -p mysql user > user_table_dump.sql
    
  • Procedures & Function
    在備份資料庫的同時需要增加 –routines 參數,才能把 procedures 與 function 備份下來.

    [root@localhost ~]# mysqldump -u root -p --routines testdb > testdb.sql.fun
    Enter password: 
    
  • Event
    在備份資料庫的同時需要增加 –events 參數,才能把 event 備份下來.

    [root@localhost ~]# mysqldump -u root -p --events testdb > testdb.sq
    

還原

先把 testdb 資料庫刪除.

MariaDB [testdb]> drop database testdb;
Query OK, 2 rows affected (0.01 sec)

資料無法直接還原,須先建立相同的資料庫後才能匯入.

[root@localhost ~]# mysql -u root -p test < testdb.sql 
Enter password: 
ERROR 1049 (42000): Unknown database 'test'
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all privileges on testdb.* to root@localhost identified by '111111' ;
Query OK, 0 rows affected (0.03 sec)

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

資料庫建立好之後就可以匯入資料了 (Procedures & Function, Event 不需額外參數即會匯入).

[root@localhost ~]# mysql -u root -p testdb < testdb.sql
Enter password: 

檢視一下資料表與資料是否匯入.

MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| employee         |
| employee1        |
+------------------+
2 rows in set (0.01 sec)

MariaDB [testdb]> select * from employee;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben1 | 2019-05-24 10:50:59 |
| Ben2 | 2019-05-24 10:51:05 |
| Ben3 | 2019-05-24 10:51:10 |
+------+---------------------+
3 rows in set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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