mysql 大表mysqldump遷移方案
場景
一張歷史表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文檔//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