Home / server / Questions / 434212
In Process
Geo
Asked: 2008-12-19 08:09:35 +0800 CST

What is the best way to backup mysql in s3?

  • 772

mysqldump is probably not the best idea on running backups every x hours but is the one that we are currently using. The backups are around 150 Megs each so sending it to other machines could waste many gigs of bandwidth.

Is there any tool or application or script that could automate the creation of the backup and the upload to s3 amazon service or any similar.

Thanks.

backup scripting mysql amazon-s3

3 Answers

  1. Zmanda Enterprise offers backup to S3.

    • 1

  2. Either you set up the LVM and/or a back-up slave.

    On the target machine:
mysql> STOP SLAVE -- if a back-up slave is used
mysql> FLUSH TABLES READ LOCK
^Z
% lvm_snapshot_blah blah-blah on /mnt/backup_image  
% fg
mysql> UNLOCK TABLES
mysql> START SLAVE -- if a back-up slave is used
^Z
% mount /mnt/backup_image /mnt/backup
% s3upload_recursively /mnt/backup/mysql_data https://s3.amazon.com/blah_blah
    • 1

  3. A good way to save space and bandwidth is to only backup the tables that were updated.

    I found this by accident, but it looks like a good starting point for a backup script:

    SELECT TABLE_NAME, UPDATE_TIME, CREATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'database_name' AND UPDATE_TIME > CREATE_TIME;
    • 0