MySQL 還原轉儲需要很長時間才能完成，DBA 如何處理/複製大型數據庫？

由于冠狀病毒，我的公司被迫縮減規模並削減成本。長話短說，我走的是中斷最少的路線，我將原始數據庫伺服器（將在 2 週內永久關閉）設置為 MASTER 複製器，鎖定所有表以供讀取，轉儲數據導致 34GB 。 sql 文件並解鎖表。之後，我將文件複製到另一台伺服器並使用以下程式碼通過 mysql-client 執行 mysql import

cat default-start-import.sql newdatabase.sql default-end-import.sql | mysql -uroot -p newdatabase

default-start-import.sql 和 default-end-import.sql 的內容如下

預設開始import.sql

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

default-end-import.sql

COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

問題是，這個 34GB 的轉儲已經執行了 2 天，通過查看程序列表和我擁有的表的數量，我會說它的導入率約為 50%。而這個所謂的從伺服器目前沒有做任何其他事情。是空轉。

我的 MySQL 的 CPU 使用率最差為 9%，記憶體使用率最差為 44%。查看查詢大約需要 1-2 秒才能插入一行。我有數百萬行的表。如果每一行需要一秒鐘，則此導入將需要數年才能完成。我在 sysctl 中將 vm.swappiness 設置為 10 以減少硬碟交換，並且伺服器以穩定的 2.4 負載執行，這在很大程度上 - 很好。我不會說它負擔過重。

我已禁用慢查詢日誌

我innodb_flush_method = O_DIRECT已經給 innodb 設置了 20 GB 的緩衝區

我還能做些什麼來加快這個過程。這感覺像是在浪費大量時間。它只有 34GB，我敢肯定有些組織會立即啟動具有數百 GB 數據庫的從屬伺服器，而我在 30GB 數據庫上苦苦掙扎。如何？！？

大型數據庫應該物理備份（即通過複製物理數據庫文件）而不是 邏輯備份（即通過 SQL 或類似的方式導出）。如果您使用 Oracle MySQL 社區版，則應使用xtrabackup。如果您使用 MariaDB，則在最新版本中有一個集成功能：https ://mariadb.com/kb/en/mariabackup/ 。

為您的 my.cnf 考慮的建議

$$ mysqld $$部分

performance_schema=0  # from ON to conserve CPU cycles
key_buffer_size=200M  # from 2G to support 201M of MyISAM indexes
query_cache_size=0  # from 256M because query_cache_type = OFF conserve RAM
thread_concurrency=6  # from 16, you only have 4 cores/8 threads, leave 2 free
innodb_thread_concurrency=6  # to match thread_concurrency
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function every SECOND
innodb_log_file_size=1G  # from 50M to support ~ 1 HR logging
innodb_log_buffer_size=500M  # from 8M to support ~ 30 minutes in RAM
innodb_io_capacity=900  # from 200 to use more of IOPS capacity
innodb_change_buffer_max_size=50  # from 25 (percent) for higher INSERT rate per second

查看我的個人資料，免費下載實用程序腳本的網路配置文件和聯繫資訊，有更多機會提高日常使用的性能。

引用自：https://dba.stackexchange.com/questions/264240