一般
管理員
MySQL - 備份、還原資料庫(匯出、匯入)
管理員
匯出資料庫 - 一個資料庫
[教學] MariaDB/MySQL備份 – 如何匯出、匯入資料庫或表格
# 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
Skip views when using mysqldump
雖然可以使用 --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
--
備份所有資料庫，並且一個資料庫一個檔案
MYSQL排程備份並達成異地備份－shell script筆記
maria db 備份錯誤
#!/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
--
匯出不鎖定資料庫
Run MySQLDump without Locking Tables
預設備份資料庫會鎖住資料庫，這時就無法做任何操作，如果不需要一致性的備份資料庫，可以設定不鎖定
--skip-lock-tables --single-transaction
網路上說可以加上 --quick
--
只匯出結構(包含預存程序、事件、觸發器)
Dumping MySQL Stored Procedures, Functions and Triggers
# mysqldump -u root -p --no-data --no-create-info --no-create-db --skip-opt --routines --triggers --events bsb > schema.sql
--
只匯出資料
Dump only the data with mysqldump without any table information?
# mysqldump --no-create-info
--
正確的備份、復原資料庫方式
你可以直接整個把資料庫匯出，但是卻無法正確的匯入！
因為資料建立是有順序的，為什麼可以建立 view 是因為資料表結構已經存在，routine 和 trigger 也是，所以按照預設的 mysqldump 會根據字母順序匯出，只要 view 有使用到後面的資料表那匯入就會出錯，重複匯入可以解決不過你不會想浪費好幾倍的時間。
我們需要分開備份
結構
視圖
預存程序、觸發器、事件
資料
匯入的時候再按照順序就不會看到一個錯誤
#!/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 匯入資料庫
How to import an SQL file using the command line in MySQL?
加上 -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 匯入資料庫
How do I load a sql.gz file to my database? (importing)
zcat /path/to/file.sql.gz | mysql -u 'root' -p your_database
--
匯入出現 MySql server has gone away
MySQL Server has gone away when importing large sql file
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 資料庫
Importing CSV data using PHP/MySQL
14.2.6 LOAD DATA INFILE Syntax
--
實際範例
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 error: Got packet bigger than max_allowed_packet'
mysqldump --max_allowed_packet=512M
--
Using a password on the command line interface can be insecure.
MySQL 執行 bash script 出現 Warning: 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)
access denied for load data infile in MySQL
將 LOAD DATA INFILE 改成 LOAD DATA LOCAL INFILE
💬 0 則回應
還沒有回應，歡迎留下您的見解！
請登入後才能回應
🔑 登入