利用mysqldump導出資料庫的子集

一、引言

假如：

1. 你有一個非常大的正式資料庫，和一個空的測試資料庫
2. 需要把正式資料庫中的部分資料導到測試資料庫中用來測試，同時，
3. 希望導入的資料盡可能的保持一緻性、完備性

比如，有一個商城的資料庫，你希望導出其中的部分資料到測試資料庫中，導入的兩個主要依據是：

• 使用者表

  user

  ：購買了商品的部分使用者和沒有購買商品的部分使用者
• 商品表

  goods

  ：部分已經有過購買的商品和部分沒人購買的商品

其他表（如訂單

order

、浏覽曆史

view_history

則依據已經導出的

user

表和

goods

表導出，而諸如文章

articles

這類屬于系統的表則可以獨立地部分導出，有些系統配置的表（如

shop_config

）則需要全部導出。另外一些表你隻希望導出空表，因為擔心其中的記錄會影響測試。

通過使用

mysqldump

工具的一些選項，通過管道配合

mysql

，利用

shell

腳本把這些指令組織起來，可以做到。

mysqldump

相關選項：

• -w

  選項

  指定導出的條件，相當于查詢中指定的

  WHERE

  語句

• -d`選項

  不導出資料，隻導出表的定義。用于導出空表

• -t

  選項

  與

  -d

  選項相反，隻導出資料，不導出表的定義。用來追加記錄到已經存在的表。

  mysqldump預設會同時導出表的定義和資料

其它：

• msyql

  的

  -e

  選項

  用來執行一個sql語句組成的字元串，必須要用雙引号包含起來

• 管道操作符

  |

  用來把前一個指令的輸出作為後一個指令的輸入

二、實作

導入資料的源資料庫和目的資料庫相關配置如下（已經寫在shell腳本中）：

#!/bin/bash

REMOTE_DBHOST=a.remote.com
REMOTE_DBNAME=shop
REMOTE_DBUSER=root
REMOTE_DBPASS=root

LOCAL_DBHOST=localhost
LOCAL_DBNAME=shop
LOCAL_DBUSER=root
LOCAL_DBPASS=root

REMOTE_ARGS=" -h${REMOTE_DBHOST} -u${REMOTE_DBUSER} -p${REMOTE_DBPASS} ${REMOTE_DBNAME}"
LOCAL_ARGS=" -h${LOCAL_DBHOST} -u${LOCAL_DBUSER} -p${LOCAL_DBPASS} ${LOCAL_DBNAME}"
           

第一步，篩選相關的使用者id。

function selectUserId() {
  echo "select users ..."
  # 建立一個臨時表來存儲使用者id
  mysql ${REMOTE_ARGS} -e "DROP TABLE IF EXISTS tmp_user_ids; CREATE TABLE tmp_user_ids(id INT NOT NULL PRIMARY KEY);"
  # 選擇購買了商品的部分使用者，最多導入100個哈
  mysql ${REMOTE_ARGS} -e "INSERT INTO tmp_user_ids SELECT DISTINCT user_id FROM `order` WHERE `status`= LIMIT "
  # 選擇沒有購買商品的部分使用者，最多20個
  mysql ${REMOTE_ARGS} -e "INSERT INTO tmp_user_ids SELECT id FROM user WHERE id NOT IN (SELECT user_id FROM `order`) LIMIT "
}
           

第二步，導入使用者表

user

以及使用者的位址表

address

。

function dumpUserTable() {
  mysqldump ${REMOTE_ARGS} user -w 'id IN (SELECT id FROM tmp_user_ids)' | mysql ${LOCAL_ARGS}
  mysqldump ${REMOTE_ARGS} address -w 'user_id IN (SELECT id FROM tmp_user_ids)' | mysql ${LOCAL_ARGS} 
}
           

第三步，導入使用者的訂單表

order

以及訂單商品表

order_goods

。

function dumpOrderTable() {
  mysqldump ${REMOTE_ARGS} order -w 'user_id IN (SELECT id FROM tmp_user_ids)' | mysql ${LOCAL_ARGS}
  # order的視圖
  mysql ${REMOTE_ARGS} -e "CREATE VIEW view_order_tmp as SELECT * FROM `order` WHERE user_id IN (SELECT id FROM tmp_user_ids)"
  mysqldump ${REMOTE_ARGS} order_goods -w 'order_id IN (SELECT id view_order_tmp)' | mysql
  # order_goods的視圖
  mysql ${REMOTE_ARGS} -e "CREATE VIEW view_order_goods_tmp as SELECT * FROM `order_goods` WHERE order_id IN (SELECT id view_order_tmp)"
}
           

第四步，導入商品表

goods

。

function dumpGoodsTable() {
  # 導出出售過的商品
  mysqldump ${REMOTE_ARGS} goods -w 'id IN (SELECT goods_id FROM view_order_goods_tmp)' | mysql ${LOCAL_ARGS}
  # 導出未出售過的商品，限10個。因為追加，是以用-t選項
  mysqldump ${REMOTE_ARGS} goods -t -w 'id NOT IN (SELECT goods_id FROM view_order_goods_tmp) LIMIT 10' | mysql ${LOCAL_ARGS}
  # 未出售過商品的視圖 
  mysql {$REMOTE_ARGS} -e "CREATE VIEW view_goods_tmp AS (SELECT * FROM goods WHERE id NOT IN (SELECT goods_id FROM view_order_goods_tmp) LIMIT 10)"
  # 導出商品的圖檔
  mysqldump ${REMOTE_ARGS} goods_images -w 'goods_id IN (SELECT goods_id FROM view_order_goods_tmp)' | mysql ${LOCAL_ARGS}
  mysqldump ${REMOTE_ARGS} goods_images -t -w 'goods_id IN (SELECT goods_id FROM view_goods_tmp)' | mysql ${LOCAL_ARGS}
}
           

第五步，導入其它表。

function dumpOtherTables() {
  # 導出10篇文章
  mysqldump ${REMOTE_ARGS} articles -w '1 LIMIT 50' | mysql ${LOCAL_ARGS}
  # 商城配置，全部導出
  mysqldump ${REMOTE_ARGS} shop_config | mysql ${LOCAL_ARGS}
  # 評論，導出空表
  mysqldump ${REMOTE_ARGS} comment -d | mysql ${LOCAL_ARGS}
}
           

第六步，清除遠端伺服器上的臨時表和視圖

function cleanTmpTableAndView() {
  mysql ${REMOTE_ARGS} -e "DROP TABLE tmp_user_ids;DROP VIEW view_goods_tmp;DROP VIEW view_order_goods_tmp;DROP VIEW view_order_tmp"
}
           

最後，把以上函數組織起來，運作

function main() {
  selectUsers;
  dumpUserTable;
  dumpOrderTable;
  dumpGoodsTable;
  dumpOtherTables;
  cleanTmpTableAndView;
}

# 運作
main
           

注意：

• 編寫指令時必須清楚到底是指令針對的是遠端的資料庫還是在本地的資料庫中操作，以免把本地資料庫中的表與遠端資料庫中的表混在一起操作。
• 若要避免過多的嵌套SELECT查詢，可以在源資料庫中先建立一個視圖來輔助

三、總結

mysqldump

還有其他很有用處的選項，可用

mysqldump --help

列印出幫助研究下。

說明：本篇文章的例子是根據真實用例改編而來的，真實的用例因為與工作相關，當然不可能原原本本的搬出來哈。真實的用例比這個要簡單，不需要建立視圖，不過表比較多，相同類型操作的表寫在一個檔案裡，然後逐行讀取操作。