Results 1 to 7 of 7

Thread: Rsync and MySQL

  1. #1
    Join Date
    Aug 2011
    Beans
    2

    Rsync and MySQL

    Hi

    I am intending to backup my MySQL server using rsync. I know if the mysql server is shut down, and I rsync the entire /var/lib/mysql, I don't get any problems. The problem is my database file itsself.

    Rsync does not copy the changed data, it copies the entire database file, so rather than copying a few hundred MB, I end up copying an entire 37GB file, which is not efficient.

    Is there anyway that rsync can be configured to only copy the changed data rather than an entire database file?
    I have looked at rdiff-backup as it will work, but I've been instructed to use rsync only.

    Thanks

  2. #2
    Join Date
    Feb 2011
    Location
    Maryland
    Beans
    2,243

    Re: Rsync and MySQL

    Well the database file is constantly changed with MySQL. I would assume that rsync copies the entire file because the system assumes the entire file is being changed?

    How do you know its actually copying 37gb at a time? Maybe the file is just being listed as 37gb because that is the actual size.

  3. #3
    Join Date
    Nov 2008
    Location
    Metro Boston
    Beans
    8,062
    Distro
    Kubuntu Development Release

    Re: Rsync and MySQL

    Rather than transferring the binary image, why not dump the file to text with mysqldump, then compress that with gzip or bzip2 and copy the compressed file to backup? It'll be a lot smaller than 37 GB is my guess.

    rsync always copies only changes whenever possible. This is much harder for binary files. Any change to the database will create a binary blob that differs from the backup copy. I have the same issue with VirtualBox VM images in my home directory. Launching the image almost guarantees the entire big file will need to be backed up again.
    Last edited by SeijiSensei; August 19th, 2011 at 02:31 PM.

  4. #4
    Join Date
    May 2009
    Beans
    118
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: Rsync and MySQL

    Im no expert but isnt taking a filesystem backup of a database like pulling the plug?

    You have no idee what the database engine has stored in memory or is writing to the filesystem.

    Use dumps instead to make sure you dont get a corrupt backup.

    You can do incremental backups of Mysql using binary logfiles.
    Link below might be of help,

    http://www.crnatural.net/mysql-backup

  5. #5
    Join Date
    Mar 2007
    Location
    Wenatchee, WA
    Beans
    Hidden!
    Distro
    Ubuntu

    Re: Rsync and MySQL

    Quote Originally Posted by kgatan View Post
    Im no expert but isnt taking a filesystem backup of a database like pulling the plug?

    You have no idee what the database engine has stored in memory or is writing to the filesystem.
    Read the first post again...

    ...mysql server is shut down...
    Pretty sure if the mysql server is shut down, it's done written everything to disk.

  6. #6
    Join Date
    May 2009
    Beans
    118
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: Rsync and MySQL

    Ah, sorry. Missed that part

  7. #7
    Join Date
    Aug 2011
    Beans
    2

    Re: Rsync and MySQL

    Quote Originally Posted by kgatan View Post
    You can do incremental backups of Mysql using binary logfiles.
    Link below might be of help,

    http://www.crnatural.net/mysql-backup
    Thank you, this looks like it might do the trick. A major reason for not going near mysqldump is the speed to reinsert all the data, and I needed a solution that could provide minimal downtime. I only need this for backup in my current configuration: master <=> master => slave.

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •