1

I would like to backup my mysql server remotely. I have three problems, and one requirement.

  1. I do not have enough space in my server. I can only keep one copy of backup in the local server
  2. I do not have good connection. I can only upload 1MB per second.
  3. My database size is 9 GB.
  4. 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)

4 Answers 4

4

It sounds like your requirements are sufficiently custom that you'll need to write your own script. A couple of notes on that subject:

  • You don't have to dump everything to disk and then compress it. Use a dump/gzip pipe and it'll save a pile of diskspace:

    mysqldump ... | gzip -c >/var/backups/mysqldump-$(date +%Y-%m%-d).sql.gz
    
  • You can make most of your locking problems disappear if you use all InnoDB tables and dump in a transaction, with the --single-transaction option to mysqldump.

The whole dump/push script should be about 5 lines. It's pretty trivial.

1

You will have to write your own script.

Considering that you do not want extended locking, and it looks like you have space on the main server, I recommend you start a second mysql instance for replication on the main server and do the backup from replication slave.

It is not recommended to use only a single copy. There are a lot of services that allow you backup space for very low cost. For example, you can use amazon AWS for backup, or find other space provider. It will cost you below $10/month, but allow multiple backups (a week or more), and as a result you will have none of the problems you have now.

Other variants: get hetzner.de or another low cost VPS and setup replication+backup on it.

0

If most of your data is in innodb then look into Xtrabackup

The benefit to using it if you have a innodb database is that it won't lock the tables while you are doing a backup.

0

Seas.

I make my mysql-backups with

  • xtrabackup
  • rdiff-backup

If you really don't like xtrabackup, then use mysqldump (perhaps mk-parallel-dump from the maatkit suite) or whatever. After that, you could throw your backups through rdiff-backup, which should just copy changed parts of files - also, it can be used over network.

So on the 'small' machine, you could just keep your nightly dumps, and on the remote 'big' machine you can keep all rdiff-backups, updated nightly after your mysql-dumps being taken.

It schould be easy to script (== write your dump-command and the rdiff-backup call into your crontab) this ...

Ciao

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .