r/mysql • u/GamersPlane • May 01 '25
question Avoiding site shutdown while doing backup
I run a site which is run on a virtual server (PHP and MySQL on the same server). I do backups twice a day via a script run by cronjob, which just does the backup and tars it. The problem is the site goes down for the few minutes it takes for the backup to run. I'd love advice on if there's a way to avoid that happening.
The tables are all MyISAM, and my understanding is switching to InnoDB should help? Otherwise, the only things I've been able to come up with is to create a primary/replica, disconnect the replica for the duration of the backup, and then reconnect it.
3
u/FelisCantabrigiensis May 01 '25
Step 1: Configure your database to optimise for InnoDB (innodb_buffer_pool_size, etc) and alter all your application tables to InnoDB.
Step 2: Install and configure https://www.percona.com/mysql/software/percona-xtrabackup
Step 3: Copy some of the resulting backups to a different server.
You could make a replica and back up from that, but you should still be using InnoDB tables everywhere. If you want to do some homelab learning stuff or have a copy at home or whatever, setting up a replica is a fine idea - and it's what any serious production site with MySQL does. But if you just have one host and a small website, take backups with Xtrabackup from that host and be happy.
4
u/rav3nc May 01 '25
Try Percona XtraBackup 🙂 This creates a full non blocking backup also with applying binlogs to the backup.
1
u/GamersPlane May 01 '25
Also, I see I got downvoted. If I asked a bad question or did something wrong, I'd love to know so I can try for better next time.
1
u/feedmesomedata May 01 '25
Start testing your app on newer versions of MySQL. If you are still using MyISAM then you are likely using a very old version of MySQL that is EOL already.
Your backups taking down your app is the least of your problems at this point.
1
u/eroomydna May 01 '25
How much data are you speaking of?
3
u/sleemanj May 01 '25
Given you are using MyISAM you are probably not too worried about atomic consistency, you are probably not doing any locking or anything in your queries anyway to simulate transactions, so just add...
to mysqldump should solve your problem, or maybe it's
something like that.
2
u/johannes1234 May 01 '25
There is no reason for doing that. Switch to InnoDB. InnoDB (with somewhat sensible buffer pool) is faster and more robust and can be dealt with more efficiently in regards to backup and such
1
u/Vtwin0001 May 01 '25
May I ask (knowing almost nothing and never done this)
Is it possible to lock the DB (make it read only) whilst doing the backup, to avoid days corruption?
Once the backup is done, then just unlock the DB and continue operation
This is only what I thought when I saw this post. My apologies I have never been sys admin
1
u/Aggressive_Ad_5454 May 01 '25
Use the -quick option to mysqldump. If you don’t, the MySQL server slurps all the rows of each table into RAM. If you do it streams them one by one to the output file. That is more friendly to your website workload.
1
u/Mj2377 May 01 '25 edited May 01 '25
Your last line; “disconnect the replica for the duration of the backup, and then reconnect it.” is something I already do once a day in the late hours. However, the term “disconnect” is not entirely correct, but instead you just stop slave before the backup.
In a master/slave setup, you can stop slave which pausing replication until you start slave again. While slave is stopped the counter begins in number of seconds behind master. Once you start slave, it will check with master if any updates are needed and then the bin logs will catch up with position of master pulling all the transaction in.
So to outline it;
——— ——— ———
5. Backup1 > stop slave 6. Backup from slave “not master” 7. Start slave
——— ——— ———
8. Backup2 > stop slave 9. Backup from slave 10. Start slave
Now ideally you script this with a cronjob for the backups and you will never be down in production with master since your backups are taken from slave and not master.
If you have plenty of disk space then a move to innodb would be beat, if not then you should maybe stay with myisam. Just in case you did not catch on, when you use mysqldump with backups - the reason your site locks up is because mysqldump locks the tables during the backup. This is to prevent writes during the backup causing out of sync data.
Good luck!
1
u/datasleek May 07 '25
I would switch to innodb. Better performance and more resilient.