測試環境為 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)
Note: 以下匯出成 .sql 皆為 SQL 指令(匯入時會依據這些指令來重建資料)
All Database
- 備份 Server A (IP : 192.168.111.129) 所有的資料庫資料.
- 還原 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 A without specify DB
如果有些資料庫不須備份起來(如果只有資料表可以使用–ignore-table=database.table_name) ,可以使用以下方式來指定 (如不備份 mysql , performance_schema , information_schema ).[root@localhost ~]# candidates=$(echo "show databases" | mysql --user=root --password | grep -Ev "^(Database|mysql|performance_schema|information_schema)$") Enter password: [root@localhost ~]# echo $candidates phpmyadmin
[root@localhost ~]# mysqldump --user=root --password --databases $candidates > db2.sql Enter password:
- 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)
columns
要指定欄位匯出可以使用以下指令,但缺點是匯出後 不是 SQL 語法而是單純資料表而已.
SELECT ((just those columns)) INTO OUTFILE ...
匯入時可以使用 load data
load data local infile 'c.csv' into table mytable (@dummy, col1, col2);
如果想要匯成 SQL 語法時,可以透過 phpMyAdmin 的功能(先使用 SQL 的 SELECT 指定欄位後,在一次匯出成 SQL 指令).
User
使用者資料儲存在預設資料庫 mysql 的 user 資料表裡面,關於 mysql 資料表的其他備份,請參考 – https://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
沒有解決問題,試試搜尋本站其他內容