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

[GoWilkes]

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?

 

[ffeingol]

The cPanel backup won't take the database offline, it just uses mysqldump. You could also look at JetBackup.

 

[GoWilkes]

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]

After more research, I can theoretically do it using a cron on the new VPS with:

rsync -a username@remote_host:/var/lib/mysql/example_data backup

I'm not quite sure how to send the FTP password for "username@remote_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]

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:

Manage MySQL® Profiles | cPanel & WHM Documentation

This interface allows you to manage multiple MySQL® profiles.

docs.cpanel.net

 

[cPRex]

Thanks for the good replies here!

 

[GoWilkes]

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:

How To Set Up Replication in MySQL | DigitalOcean

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slav…

www.digitalocean.com

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

 

[andrew.n]

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..)

 

[cPRex]

How long does a good old-fashioned mysqldump take on that database?

 

[ffeingol]

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]

It does lock the tables by default:

"mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 8.0.21) PROCESS if the --no-tablespaces option is not used. Certain options might require other privileges as noted in the option descriptions."

MySQL :: MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program

dev.mysql.com