--
--
匯出資料庫 - 一個資料庫
# mysqldump -u 使用者名稱 -p 資料庫名稱 > db.sql
--
匯出資料庫 - 從資料庫挑選資料表
有時候匯出資料庫並不需要全部的資料表，可以手動或是根據條件式來挑選
備份 1 2 3 資料表
mysqldump - u user - p database 1 2 3 > backup . sql
使用 like 過濾，挑選 user 開頭資料表，--login-path=root 是身份驗證方式請根據 MySQL 或 MariaDB 自行替換
mysqldump -- login - path = root -- databases $ ( mysql -- login - path = root - Bse "use database; show tables like 'user%'" ) > backup . sql
使用 not like 過濾，不想備份 log 又剛好資料表都是 _log 結尾就可以使用
"use database; show tables where Tables_in_bsb not like '%_log';"
--
匯出不包含視圖 view
雖然可以使用 --ignore-table 來排除 "指定" 資料表，可是當有多個資料庫並且都有多個資料表需要排除時，沒有人會人工輸入。這時可以到 information_schema 資料庫的 TABLES 資料表來查詢
查詢所有 TABLE_TYPE 為 BASE TABLE 的資料表，view 的 TYPE 是 VIEW
SELECT GROUP_CONCAT ( TABLE_NAME SEPARATOR ' ' ) FROM information_schema . TABLES WHERE TABLE_SCHEMA = 'table name' AND TABLE_TYPE = 'BASE TABLE'
應用
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
--
備份所有資料庫，並且一個資料庫一個檔案
#!/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
--
匯出不鎖定資料庫
預設備份資料庫會鎖住資料庫，這時就無法做任何操作，如果不需要一致性的備份資料庫，可以設定不鎖定
-- skip - lock - tables -- single - transaction
網路上說可以加上 --quick
--
只匯出結構(包含預存程序、事件、觸發器)
# mysqldump -u root -p --no-data --no-create-info --no-create-db --skip-opt --routines --triggers --events bsb > schema.sql
--
只匯出資料
# 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 可以跳過錯誤繼續匯入，例如重複建立資料表
# mysql -f -u username -p database_name < file.sql
使用 mysql shell
mysql - u root - p
mysql > use database ;
mysql [ database ] > source import_file . sql ;
--
使用 gz 匯入資料庫
zcat / path / to / file . sql . gz | mysql - u 'root' - p your_database
--
匯入出現 MySql server has gone away
wait_timeout = 28800
max_allowed_packet = 128M
--
使用 phpMyAdmin 匯入有 foreign key 的資料表處理方案
匯入 fk 資料表時，就算所有關聯資料表一起匯入也會因為順序不一致而發生
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;
類似像這樣
SET FOREIGN_KEY_CHECKS= 0;
--
-- 原來的 SQL 指令
--
SET FOREIGN_KEY_CHECKS= 1;
--
使用 LOAD DATA 匯入 CSV 檔案
直接匯入 CSV 檔案到 MySQL 資料庫
--
實際範例
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 秒
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'
mysqldump -- max_allowed_packet = 512M
--
Using a password on the command line interface can be insecure.
MySQL 在 cli 要自動執行必須使用 mysql_config_editor 設定
$ 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
--
文章導覽