mysql 大表mysqldump遷移方案

zero_and_one發表於2020-06-09

場景

 一張歷史表product_history 500萬資料,凌晨的才會將正式表的資料遷移到歷史表,此次需求將歷史表遷移到一個更便宜的資料庫例項進行儲存。

條件

  1.此表不是實時寫,凌晨才會更新

  2.誇資料庫例項進行遷移

  3.此表對資料準確性有要求,資料必須準確

選型

  1.navicat 匯出資料(轉儲存僅結構和資料)

  2.重新命名錶,建立一張新表(適合同一個例項)

  3.mysqldump 導資料

操作對比 

  navicat 支援兩個資料庫之間直接導資料,不需要先匯出到本地再從本地匯入到另外的例項

  mysqldump 支援兩個資料庫之間直接導資料,不需要先匯出到本地再從本地匯入到另外的例項,支援匯出壓縮。例子:匯出的5M的資料壓縮只有700k

效能對比

  Navicat匯出的資料是一條條的insert語句,每一行一條插入語句。

  mysqldump 匯出的資料,多行資料合併成一行插入。批插入減少sql 語法詞法解析,減少插事務(最大的開銷),較少資料的傳輸

思考準備

  大表資料遷移需思考的問題:

  1.大量資料讀取與插入是否會造成表的死鎖。

  2.對自己的伺服器或者雲上的例項的IO,頻寬,記憶體佔用有多大,會不會造成記憶體溢位,cpu 100%

  3.遷移的資料特殊型別例如(blob)會不會在匯入的時候丟失。

  4.不同的引擎之間是否會對匯入資料有影響

 

開始:

  資料庫mysql 5.7,官方mysqldump文件https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#mysqldump-performance-options

  匯出表資料和結構 mysqldump -h例項ip/域名 -P埠號(大寫P表示埠號區別於小寫p密碼) -u使用者名稱 -p密碼 庫名 表名|gizp(表示壓縮) > 輸出地址 xxx.sql

  eg:

mysqldump -uroot -p'123456'  -h127.0.0.1 -P3306  mydb order_product|gizp > /Users/llj/order_product.sql

匯出的格式

  刪除表

  建立相同的表結構

  給表加寫鎖

    批量插入資料(insert into)

  釋放表鎖

這種帶來的隱患

1.刪除表:若匯出是增量模式,不是全量覆蓋則會將原來的表資料刪除,只會有增量的資料

 

  將資料導回例項

  mysql -h例項ip/域名 -p埠號 -u使用者名稱 -p'密碼' 庫名<Documents/sql/order_product.sql 

 

 

 

 

  

 

相關文章