Recently I have to backup mysql database on quite heavily loaded server and I wanted to make backup daily on one hand and I wanted them not to affect production on the other. DB size is more than 150 Gb, about 50k users daily.
First approach was ty try to simply run musqldump and see what would happen. Site was down right after the backup has begun. Then I have tried to nice the mysqldump with
/usr/bin/nice -n 10 ionice -c2 -n 7The result was better, site was down at about 10% of the job. Next I add –single-transaction, that have solved about 95% of problems, still some time site was down, so I had to add -q option. The final command was:
/usr/bin/nice -n 10 ionice -c2 -n 7 /usr/bin/mysqldump --plugin-dir=/usr/lib/mysql/plugin --user=dbuser -h bd.lxc --port=3306 --all-databases --single-transaction -q|/bin/gzip > /var/bcp/backup-db-`/bin/date -I`.sql.gz
Leave a Reply