--
--
匯出資料庫 - 一個資料庫
|
1 |
# mysqldump -u 使用者名稱 -p 資料庫名稱 > db.sql |
--
匯出資料庫 - 從資料庫挑選資料表
有時候匯出資料庫並不需要全部的資料表，可以手動或是根據條件式來挑選
備份 1 2 3 資料表
|
1 |
mysqldump -u user -p database 1 2 3 > backup.sql |
使用 like 過濾，挑選 user 開頭資料表，--login-path=root 是身份驗證方式請根據 MySQL 或 MariaDB 自行替換
|
1 |
mysqldump --login-path=root --databases $(mysql --login-path=root -Bse "use database; show tables like 'user%'") > backup.sql |
使用 not like 過濾，不想備份 log 又剛好資料表都是 _log 結尾就可以使用
|
1 |
"use database; show tables where Tables_in_bsb not like '%_log';" |
--
匯出不包含視圖 view
雖然可以使用 --ignore-table 來排除 "指定" 資料表，可是當有多個資料庫並且都有多個資料表需要排除時，沒有人會人工輸入。這時可以到 information_schema 資料庫的 TABLES 資料表來查詢
查詢所有 TABLE_TYPE 為 BASE TABLE 的資料表，view 的 TYPE 是 VIEW
|
1 |
SELECT GROUP_CONCAT(TABLE_NAME SEPARATOR ' ') FROM information_schema.TABLES WHERE TABLE_SCHEMA='table name' AND TABLE_TYPE='BASE TABLE' |
應用
|
1 2 |
MYSQLDUMP_SCHEMA="$MYSQLDUMP --skip-lock-tables --single-transaction --max_allowed_packet=512M --no-data" $MYSQLDUMP_SCHEMA question_db $($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse "SELECT GROUP_CONCAT(TABLE_NAME SEPARATOR ' ') FROM information_schema.TABLES WHERE TABLE_SCHEMA='table name' AND TABLE_TYPE='BASE TABLE'") > question_db_schema.sql |
--
備份所有資料庫，並且一個資料庫一個檔案
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
#!/bin/sh db_user="root" db_passwd="root" db_host="192.168.6.100" MYSQL="docker exec mariadb55 mysql" MYSQLDUMP="docker exec mariadb55 mysqldump" # get all databases all_db="$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse 'show databases')" for db in $all_db do $MYSQLDUMP -u $db_user -h $db_host -p$db_passwd $db > $db.sql done |
--
匯出不鎖定資料庫
預設備份資料庫會鎖住資料庫，這時就無法做任何操作，如果不需要一致性的備份資料庫，可以設定不鎖定
|
1 |
--skip-lock-tables --single-transaction |
網路上說可以加上 --quick
--
只匯出結構(包含預存程序、事件、觸發器)
|
1 |
# mysqldump -u root -p --no-data --no-create-info --no-create-db --skip-opt --routines --triggers --events bsb > schema.sql |
--
只匯出資料
|
1 |
# mysqldump --no-create-info |
--
正確的備份、復原資料庫方式
你可以直接整個把資料庫匯出，但是卻無法正確的匯入！
因為資料建立是有順序的，為什麼可以建立 view 是因為資料表結構已經存在，routine 和 trigger 也是，所以按照預設的 mysqldump 會根據字母順序匯出，只要 view 有使用到後面的資料表那匯入就會出錯，重複匯入可以解決不過你不會想浪費好幾倍的時間。
我們需要分開備份
- 結構
- 視圖
- 預存程序、觸發器、事件
- 資料
匯入的時候再按照順序就不會看到一個錯誤
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
#!/bin/sh db_user="root" db_passwd="password" db_host="192.168.6.100" MYSQL="docker exec mariadb55 mysql" MYSQLDUMP="docker exec mariadb55 mysqldump -u $db_user -h $db_host -p$db_passwd --skip-lock-tables --single-transaction --max_allowed_packet=512M" MYSQLDUMP_SCHEMA="$MYSQLDUMP --no-data" MYSQLDUMP_ROUTE_TRIGGER_EVENT="$MYSQLDUMP --no-create-info --no-data --no-create-db --skip-opt --routines --triggers --events" $MYSQLDUMP_SCHEMA database_name $($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse "SELECT GROUP_CONCAT(table_name SEPARATOR ' ') FROM information_schema.tables WHERE table_schema='question_db' AND engine IS NOT NULL") > question_db_schema.sql $MYSQLDUMP_ROUTE_TRIGGER_EVENT database_name$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse "SELECT GROUP_CONCAT(table_name SEPARATOR ' ') FROM information_schema.tables WHERE table_schema='question_db' AND engine IS NOT NULL") > question_db_route_trigger_event.sql $MYSQLDUMP database_name$($MYSQL -u $db_user -h $db_host -p$db_passwd -Bse "SELECT GROUP_CONCAT(table_name SEPARATOR ' ') FROM information_schema.tables WHERE table_schema='question_db' AND engine IS NULL") > question_db_view.sql $MYSQLDUMP --no-create-info database_name> question_db_data.sql |
--
使用 .sql 匯入資料庫
加上 -f 可以跳過錯誤繼續匯入，例如重複建立資料表
|
1 |
# mysql -f -u username -p database_name < file.sql |
使用 mysql shell
|
1 2 3 |
mysql -u root -p mysql> use database; mysql [database]> source import_file.sql; |
--
使用 gz 匯入資料庫
|
1 |
zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database |
--
匯入出現 MySql server has gone away
|
1 2 |
wait_timeout = 28800 max_allowed_packet = 128M |
--
使用 phpMyAdmin 匯入有 foreign key 的資料表處理方案
匯入 fk 資料表時，就算所有關聯資料表一起匯入也會因為順序不一致而發生
|
1 |
Cannot add or update a child row: a foreign key constraint fails |
這個錯誤
從網站上可以查詢到必須設定 SET FOREIGN_KEY_CHECKS=0; ，但是使用 SQL 指令執行後再使用 SELECT @@FOREIGN_KEY_CHECKS; 查看會發現還是 1 ，也就是沒有被關閉
解決的方法是修改匯入 .sql 檔案，在最開頭加上 SET FOREIGN_KEY_CHECKS=0; 以及結尾加上 SET FOREIGN_KEY_CHECKS=1;
類似像這樣
|
1 2 3 4 5 6 7 |
SET FOREIGN_KEY_CHECKS=0; -- -- 原來的 SQL 指令 -- SET FOREIGN_KEY_CHECKS=1; |
--
使用 LOAD DATA 匯入 CSV 檔案
直接匯入 CSV 檔案到 MySQL 資料庫
--
實際範例
|
1 |
LOAD DATA LOCAL INFILE '/tmp/a.csv' INTO TABLE UserTemp FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ( id, Name, position, `group`, recommend, IDNumber, Email, phone, city, province, JoinDate, ServerNo, status, status2 ); |
需要注意指令順序
--
效能
如果可以使用 LOAD DATA 一定會比使用程式拆解後逐筆插入快，五萬筆的匯入在小白 MacBook 上也只需不到 3 秒
|
1 2 3 |
mysql> LOAD DATA LOCAL INFILE '/tmp/b.csv' INTO TABLE UserTemp FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES ( id, Name, position, `group`, recommend, IDNumber, Email, phone, city, province, JoinDate, ServerNo, status, status2 ); Query OK, 57378 rows affected, 3 warnings (2.75 sec) Records: 57378 Deleted: 0 Skipped: 0 Warnings: 0 |
--
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet'
|
1 |
mysqldump --max_allowed_packet=512M |
--
Using a password on the command line interface can be insecure.
MySQL 在 cli 要自動執行必須使用 mysql_config_editor 設定
|
1 2 |
$ mysql_config_editor set --login-path=dbname --host=127.0.0.1 --user=root --password $ /usr/bin/mysqldump --login-path=root --databases HOYO_Web >/tmp/web$filename.sql |
--
ERROR 1045 (28000): Access denied for user 'XXXXX'@'%' (using password: YES)
將 LOAD DATA INFILE 改成 LOAD DATA LOCAL INFILE
--
21,354 total views, 2 views today