5

MySQL的备份网络优化

 7 months ago
source link: https://bajie.dev/posts/20240123-mysql_backup_optimize/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

MySQL的备份网络优化

2024-01-23 1 分钟阅读

公司的MySQL服务器定时在凌晨4:00准时开始备份。

结果是会触发报警，net流量增高，cpu增高，磁盘io增高，这个是属于正常的，如何避免触发警报呢？

有4种方法：

一、优化io和cpu，让备份写磁盘的速度降下来，平稳的写入

nice -n 10 ionice -c2 -n 7 /usr/bin/mysqldump -S /var/lib/mysql/mysql.sock -uroot --single-transaction --quick\  
  --triggers -R --hex-blob --log-error=$db.log --databases $db > $basedir/$backdir/$db.sql

二、加快备份速度，多线程，让报警触发之前就结束备份

COMMIT_COUNT=0  
COMMIT_LIMIT=10  
for DB in `cat ListOfDatabases.txt`  
do  
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done  
if [ ${COMMIT_COUNT} -gt 0 ]  
then  
    wait
fi

三、用cstream来控制流速

-t = throughput in bytes/sec 1000000是一兆，每秒只允许写一兆

mysqldump --single-transaction --quick -u <USER> -p<PASS> <Database> | cstream -t 1000000 > backup.sql

四、用pv来控制流速

–rate-limit Limit the transfer to a maximum of RATE bytes per second. 单位可以是：1k 1m 1g 1t

mysqldump --single-transaction --quick -u -p | pv --rate-limit 1m > db.sql

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK