"Best" way to back up MySQL to a remote VPS

GoWilkes

Well-Known Member
Sep 26, 2006
589
22
168
cPanel Access Level
Root Administrator
I'm setting up a second VPS with my server provider that will primarily act as a remote backup to MySQL. This way, in case of catastrophic hard drive failure (again) I won't be left empty handed.

The question is, what's the "best" way to do this? "Best" being subjective, the priorities are reliability, speed, and limiting any down time of the live database while it's being backed up.

Until now I've just been storing backups in a /backup/ directory on the same drive. WHM's backup is kind of a pain, though, and wants to back up everything, not just MySQL. If I can use WHM's backup tool to do this, how do I make it JUST do MySQL, and without taking the database offline while it does it?
 

GoWilkes

Well-Known Member
Sep 26, 2006
589
22
168
cPanel Access Level
Root Administrator
I've done some poking around, and I THINK that I can do this with WHM's backup... under WHM > Backup Configuration, go to Additional Destinations and choose Rsync, then "Create New Destination".

I haven't tried it yet, but it looks like this will create a backup locally, then when it's done it will use rsync to copy it to a remove server.

The only thing is that I still can't figure out how to get it to ONLY backup MySQL... I don't need the PHP scripts or user-submitted images to be saved, and if it backs up the entire account then it's gonna be over 100G! That's way too much storage and bandwidth to pay for if I don't need it.
 

GoWilkes

Well-Known Member
Sep 26, 2006
589
22
168
cPanel Access Level
Root Administrator
After more research, I can theoretically do it using a cron on the new VPS with:

Code:
rsync -a [email protected]_host:/var/lib/mysql/example_data backup
I'm not quite sure how to send the FTP password for "[email protected]_host" via cron, though.

And if I do it this way, are there any issues when/if a user writes something to MySQL while it's being copied?
 

andrew.n

Well-Known Member
Jun 9, 2020
607
171
43
EU
cPanel Access Level
Root Administrator
It is not recommended to backup the MySQL files while the MySQL server is running as they could be corrupted and you wouldn't be able to recover per databases anyway just the full MySQL server if needed of course as long as you provide the same environment as before (same OS, same MySQL version etc etc.-.). Usually the lock up periods are seconds when backing up the databases with mysqldump and such so I wouldn't worry about it but if you don't want that at all then a remote slave MySQL server would be the solution which you can back it up as you like. This would be a custom solution though.

The closest cPanel could provide iare remote MySQL profiles with which you can offload databases to a remote MySQL server:

 

GoWilkes

Well-Known Member
Sep 26, 2006
589
22
168
cPanel Access Level
Root Administrator
Usually the lock up periods are seconds when backing up the databases with mysqldump and such
@andrew.n , my database is about 12G, and the last backup took well over an hour :-O The site is usually active until around 2am, but even after that there's some traffic. I can handle being offline for a few seconds, but an hour or more is pretty major.


if you don't want that at all then a remote slave MySQL server would be the solution which you can back it up as you like.
That's an interesting one, I hadn't heard of that before. I did some research and found this how-to:


Do you know how this affects performance of the site in general?
 

andrew.n

Well-Known Member
Jun 9, 2020
607
171
43
EU
cPanel Access Level
Root Administrator
You should really test it how long it takes if you only backup the db and what performance impact it has. It goes the same to the master-slave setup as well, it depends on a lot of factors (drives, network connection, distance etc etc..)
 
Last edited:

ffeingol

Well-Known Member
PartnerNOC
Nov 9, 2001
619
202
343
cPanel Access Level
DataCenter Provider
Typically mysqldump dos not lock the tables (unless you add lock options) as it's just reading the database.

As far as replication goes, it's reading the binary log files (which you have to enable to have replication) so it does not affect the database performance per-se.
 

andrew.n

Well-Known Member
Jun 9, 2020
607
171
43
EU
cPanel Access Level
Root Administrator