場景
一張歷史表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