MariaDB / MySQL 資料庫備份與還原

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

  • Server A (IP : 192.168.111.129)
  • Server B (IP : 192.168.111.157)

先在 Server A 建立一些測試用資料.

[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]> SELECT * FROM employee;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben1 | 2019-11-13 17:56:28 |
| Ben2 | 2019-11-13 17:56:32 |
| Ben3 | 2019-11-13 17:56:38 |
+------+---------------------+
3 rows in set (0.00 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)

MariaDB [testdb]> SELECT * FROM employee1;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben1 | 2019-11-13 17:57:16 |
| Ben2 | 2019-11-13 17:57:21 |
| Ben3 | 2019-11-13 17:57:27 |
+------+---------------------+
3 rows in set (0.00 sec)

All Database

  1. 備份 Server A (IP : 192.168.111.129) 所有的資料庫資料.
  2. 還原 Server A (IP : 192.168.111.129) 所有的資料庫資料到 Server B (IP : 192.168.111.157)
  • Server A
    需先把資料庫設定成為唯讀 (Read lock).

    [root@localhost ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 5.5.64-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 TABLES WITH READ LOCK;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> exit
    Bye
    

    透過 mysqldump 把所有資料備份下來並傳給 Slave (IP: 192.168.111.157)

    [root@localhost ~]# mysqldump --all-databases --user=root --password > database.sql
    [root@localhost ~]# scp database.sql 192.168.111.157:/root/
    root@192.168.111.157's password: 
    database.sql                                                             100%  503KB  66.5MB/s   00:00                   
    

    回到資料庫並取消唯讀.

    [root@localhost ~]# mysql -u root -p
    Enter password: 
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 4
    Server version: 5.5.64-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)]> UNLOCK TABLES;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> exit
    Bye
    
  • Server B
    匯入剛剛從 ServerA 得到所有資料庫的備份檔案並重啟資料庫服務.

    [root@localhost ~]# mysql -u root -p < database.sql 
    Enter password: 
    [root@localhost ~]# systemctl restart mariadb
    

    檢視一下資料得確已匯入.

    [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.
    
    MariaDB [testdb]> SELECT * FROM testdb.employee;
    +------+---------------------+
    | Name | ts                  |
    +------+---------------------+
    | Ben1 | 2019-11-13 17:56:28 |
    | Ben2 | 2019-11-13 17:56:32 |
    | Ben3 | 2019-11-13 17:56:38 |
    +------+---------------------+
    3 rows in set (0.00 sec)
    
    MariaDB [testdb]> SELECT * FROM testdb.employee1;
    +------+---------------------+
    | Name | ts                  |
    +------+---------------------+
    | Ben1 | 2019-11-13 17:57:16 |
    | Ben2 | 2019-11-13 17:57:21 |
    | Ben3 | 2019-11-13 17:57:27 |
    +------+---------------------+
    3 rows in set (0.00 sec)
    

Database

把 testdb 資料庫備份下來.

  • Server A
    [root@localhost ~]# mysqldump -u root -p testdb > testdb.sql
    Enter password: 
    [root@localhost ~]# scp testdb.sql 192.168.111.157:/root/
    root@192.168.111.159's password: 
    testdb.sql  
    
  • Server B
    資料無法直接還原,須先建立相同的資料庫後才能匯入.

    [root@localhost ~]# mysql -u root -p testdb < testdb.sql 
    Enter password: 
    ERROR 1049 (42000): Unknown database 'testdb'
    
    [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.
    
    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)
    
    MariaDB [(none)]> exit
    Bye
    

    資料庫建立好之後就可以匯入 ServerA 的資料庫備份檔案來還原 (如果有 Procedures & Function, Event 不需額外參數即會匯入).

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

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

    [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.
    
    MariaDB [(none)]> USE testdb;
    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 [testdb]> SHOW TABLES;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | employee         |
    | employee1        |
    +------------------+
    2 rows in set (0.00 sec)
    
    MariaDB [testdb]> SELECT * FROM employee;
    +------+---------------------+
    | Name | ts                  |
    +------+---------------------+
    | Ben1 | 2019-11-13 18:26:46 |
    | Ben2 | 2019-11-13 18:26:51 |
    | Ben3 | 2019-11-13 18:26:56 |
    +------+---------------------+
    3 rows in set (0.00 sec)
    
    MariaDB [testdb]> SELECT * FROM employee1;
    +------+---------------------+
    | Name | ts                  |
    +------+---------------------+
    | Ben1 | 2019-11-13 18:27:11 |
    | Ben2 | 2019-11-13 18:27:17 |
    | Ben3 | 2019-11-13 18:27:21 |
    +------+---------------------+
    3 rows in set (0.00 sec)
    

Table

針對 testdb 資料庫裡面的 employee 資料表備份下來.

  • Server A
    [root@localhost ~]# mysqldump -u root -p testdb employee > testdb.employee.sql
    Enter password: 
    [root@localhost ~]# scp testdb.employee.sql 192.168.111.157:/root/
    root@192.168.111.159's password: 
    testdb.sql  
    
  • Server B
    資料無法直接還原,須先建立相同的資料庫後才能匯入.

    [root@localhost ~]# mysql -u root -p testdb < testdb.employee.sql
    Enter password: 
    ERROR 1049 (42000): Unknown database 'testdb'
    
    [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.
    
    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)
    
    MariaDB [(none)]> exit
    Bye
    

    資料庫建立好之後就可以匯入 ServerA 的資料庫備份檔案來還原 (如果有 Procedures & Function, Event 不需額外參數即會匯入).

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

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

    [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.
    
    MariaDB [(none)]> USE testdb;
    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 [testdb]> SHOW TABLES;
    +------------------+
    | Tables_in_testdb |
    +------------------+
    | employee         |
    +------------------+
    2 rows in set (0.00 sec)
    
    MariaDB [testdb]> SELECT * FROM employee;
    +------+---------------------+
    | Name | ts                  |
    +------+---------------------+
    | Ben1 | 2019-11-13 18:26:46 |
    | Ben2 | 2019-11-13 18:26:51 |
    | Ben3 | 2019-11-13 18:26:56 |
    +------+---------------------+
    3 rows in set (0.00 sec)
    

User

使用者資料儲存在預設資料庫 mysql 的 user 資料表裡面,關於 mysql 資料表的其他備份,請參考 – http://benjr.tw/1164 .

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

發佈留言

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

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