優力好資訊

匯出資料庫 - 一個資料庫 [教學] 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

整理常用的備份指令與實用步驟： 1. 常用備份指令請在 Windows 11 打開 CMD (命令提示字元) 或 PowerShell。備份時不需進入資料庫，直接在系統終端機執行：備份單一資料庫 mysqldump -u 帳號 -p 資料庫名稱 > 備份檔案路徑.sql # 範例：將 my_db 備份到 C 槽 mysqldump -u root -p my_db > C:\backup\my_db_backup.sql 備份主機內「所有」資料庫 mysqldump -u 帳號 -p --all-databases > 備份檔案路徑.sql 備份特定資料庫的多張資料表 mysqldump -u 帳號 -p 資料庫名稱 表格1 表格2 > 備份檔案路徑.sql 2. 實用備份參數若您的資料庫較大，建議加入以下參數，能讓備份更順暢且避免鎖定問題： --single-transaction：適合 InnoDB 表格，備份時不會鎖定表格，不影響系統運作。 --routines：一併備份預存程序與函式。 進階備份範例 mysqldump -u root -p --single-transaction --routines my_db > C:\backup\my_db_backup.sql

MySQL 與 MaraiDB 預設對於 table 的名稱是大小寫敏感的, 也就是 case sensitive, 但最近遇到客戶要求要將表名稱設定為大小寫不敏感, 設定上十分容易, 步驟如下 注意: 請先檢查是否有任何 Table 名稱在改為小寫後會重複的 Table, 若發生衝突 MySQL 會崩潰 編輯 MySQL/MariaDB 設定檔(不同作業系統可能會在不同目錄) sudo vim /etc/mysql/my.cnf 找到 [mysqld] 這個 section 下方加入 lower_case_table_names = 1 接著重新啟動 MySQL/MariaDB 即可 sudo service mysql restart

網址：https://achir1015.github.io/A13web/ 操作說明： 使用方式 頁面頂端出現藍色 Google Drive 同步工具列： 第一次使用： 點 「🔐 Google 登入」 → 跳出 Google 授權視窗 → 允許 登入後顯示您的 Google 帳號名稱 日常操作： 按鈕功能☁️ 存至 Drive將目前所有 XML 資料上傳到您的資料夾📂 從 Drive 讀取從 Drive 下載最新 XML 覆蓋到本機 技術說明 儲存位置：您的 Google Drive 資料夾內，檔案名為 A13_NursingRecords.xml 授權方式：OAuth2 Implicit Flow，只用 client_id，不在前端使用 client_secret（安全） 重複儲存：若檔案已存在則更新覆蓋，不重複建立 ⚠️ 請注意：建議您到 Google Cloud Console 重新產生 client_secret，因為剛才分享的密鑰已外露。 client_id 是公開的沒問題，但 client_secret 應妥善保管。 非常感謝您的信任與耐心！🎉 這套系統從無到有，一起完成了很多功能： A13 護理紀錄系統 完成總覽 資料架構 三階關聯式 XML 結構（主檔 → 床號主檔 → 三類明細） 每階鍵值自動產生，流水號每日累增 操作功能 月曆選日 → 三層麵包屑導航 增加 ✏️ 修改 🗑 刪除 完整 CRUD 餵食、排出明細自動顯示各欄小計列 輸入體驗 數字九宮格鍵盤（含小數點） 24 小時制時間輸入 尿袋下拉選單（無／置左／置右） 桌機 💻 ／ 手機 📱 切換 儲存安全 雙重備份（window.storage + localStorage） 防空覆蓋保護機制 ☁️ Google Drive 雲端同步 最後一個小提醒： 請到 Google Cloud Console 將原本的 client_secret 重新產生新的，保護帳號安全 🔐 A13 護理紀錄系統 完整功能總覽 三階關聯式資料結構 主檔 → 床號主檔 → 餵食／排出／體重腹圍明細 鍵值自動產生，序號正確累增 三個設定模組 ⚙️ Setup.xml — 機構設定 👩‍⚕️ Caregivers.xml — 照護人員（員編下拉自動帶入護理姓名） 🛏 Ward.xml — 病房管理（含個資保護提醒） 三種儲存方式 🌐 瀏覽器本機儲存（雙重備份） ☁️ Google Drive 雲端同步 🗂 本機資料夾讀寫（與 HTML 同位置） 輸入體驗 🔢 數字九宮格 🕐 24 小時制時間九宮格 📱 桌機／手機切換 防誤觸 Modal 保護機制 系統運作於 achir1015.github.io/A13web/ 🚀 現在 Google Drive 的目錄結構已完整： 📁 根資料夾（1KaFaTW_YDupDZ2JpMuxHMu77fBVgXv2d） ├── A13_NursingRecords.xml └── 📁 Taipei City Hospital Zhongxiao Branch-Respiratory Care Ward/ ├── Setup.xml ├── Caregivers.xml └── Ward.xml 目前系統完整儲存架構 儲存方式用途 🌐 瀏覽器 window.storage即時快取，自動儲存 💾 localStorage 備份防止 storage 失效的第二道保護 🗂 本機資料夾與 HTML 同位置，支援 Git 同步 ☁️ Google Drive 根目錄護理主檔備份 ☁️ Google Drive 子資料夾機構設定、人員、病房基本資料共用 多人使用時，只要登入同一個 Google 帳號，就能從 Drive 讀取共用的基本資料！ 各裝置使用方式 裝置本機儲存雲端備份 💻 桌機 Chrome/Edge 📂 選擇資料夾（4個XML同步） ☁️ Google Drive📱 Android⬇ 個別下載 / 📥 上傳匯入☁️ Google Drive📱 iPhone⬇ 開新頁儲存至檔案App☁️ Google Drive 最佳工作流程建議 多人共用基本資料： 任一裝置設定 Setup / Caregivers / Ward → 存至 Google Drive → 其他裝置登入後「從 Drive 讀取」即可共用 日常護理紀錄： 開啟系統 → Google 登入 → 從 Drive 讀取 → 輸入護理紀錄 → 班次結束後存至 Drive