I would like to backup my mysql server remotely. I have three problems, and one requirement.
- I do not have enough space in my server. I can only keep one copy of backup in the local server
- I do not have good connection. I can only upload 1MB per second.
- My database size is 9 GB.
- If my remote server gets hacked, they shouldn't be able to harm the main server.
I have tried to use automysqlbackup script, but it is locking tables and since the connection is slow, it is taking ages and site goes down.
I don't want to use replication, my second server has also its own application and there are many writes in the other server. I don't have enough resources.
I don't want to use xtrabackup (it is not solving the main problem other than locking problem anyway) I had a bad experience.
Backup should be stored locally first, then GZIPed and then transfered.
Main server should only keep one copy (or preferably file size limit, so if any of the backups fail, it will still keep the old one) Automysqlbackup script doesn't have limitation that I can set. (So, I could use automysqlbackup for local backup and limit total allocated disk space)
I can use SFTP and chroot the user and retrieve the backup.
Backup server should keep unlimited (or 100) number of files. It shouldn't delete the files like the main server.
Do I have to write a couple of script? Or are there any pre-made solution? (and possibly with more features)