Showing posts with label Backup. Show all posts
Showing posts with label Backup. Show all posts

Thursday, November 20, 2014

Sharing Shared folders files between Linux, FreeBSD and Windows

FreeBSD Windows Server 2008 Note
NFS Server Client Windows Server 2008 has a built-in NFS client.
NFS Client Server Windows Server 2008 has a built-in NFS server.
SSH Filesystem Server Client SSH Filesystem
SFTP Server Client SyncBack Pro, FileZilla.
SFTP Client Server Core FTP, Bitvise SSH Server WinSSHD, VanDyke Vshell.
SMB/CIFS Server Client
SMB/CIFS Client Server mount_smbfs
RSYNC Server Client
RSYNC Client Server
Samba Server Client
Samba Client Server

Note: The Server Message Block (SMB) Protocol is a network file sharing protocol, and as implemented in Microsoft Windows is known as Microsoft SMB Protocol. The set of message packets that defines a particular version of the protocol is called a dialect. The Common Internet File System (CIFS) Protocol is a dialect of SMB. Both SMB and CIFS are also available on VMS, several versions of Unix, and other operating systems.

Note: SSH Filesystem (簡單好用的 SSH 檔案系統) 工具,讓你只要有 SSH 登入權限,就可以輕易的將遠端資料夾直接掛載到本機,而且連一般使用者也可以使用,甚至於連在 Windows 下都可以自由掛載遠端 Linux 帳號下的任意一個資料夾,真的非常方便。

http://msdn.microsoft.com/en-us/library/windows/desktop/aa365233(v=vs.85).aspx
http://technet.microsoft.com/en-us/library/cc753302(v=ws.10).aspx

Saturday, June 28, 2014

Symantec Backup Exec 2012

When backing up a FreeBSD on a virtual machine on ESXi:

It took about 5 hours to finish with Exceptions:

Media Label: IMG000001
GRT backup set folder: E:\BEData\IMG000001
Transport mode 'nbd' was used for the disk 'WebProxy.vmdk'
Transport mode 'nbd' was used for the disk 'WebProxy_1.vmdk'
V-79-57344-38761 - Failed to mount one or more virtual disk images because they contained GPT style disks. Backups that were enabled for Granular Recovery Technology may not be available for restore.

Solution:

In the backup job deselect the option "Use Backup Exec Granular Recovery Technology (GRT) to enable the restore of individual files and folders from virtual machines" under Settings-VMware Virtual Infrastructure.

To simulate a virtual machine is gone completely, I build a fresh install virtual machine with Windows 7 and try to restore the system from the backup:

It took about 10 minutes to restore from the backup. However, the system could not be booted up and it shows the Blue Screen of Death. The solution and the proper procedure is to use a backup exec recovery disk (a .ISO image) to boot up the machine then restore from the Backup Exec server.

Thursday, July 21, 2011

Backup Options For FreeBSD

Backup Options For FreeBSD

Warren Block
<wblock@wonkity.com>
2011-05-24
Table of Contents
Introduction
Archives
dump(8)/restore(8)
 Useful dump Options
 restore(8)
 Simple dump
 dump With Compression
 dump Via SSH
 Copying Filesystems
 restore(8) Over A Live System
Clonezilla
dd
Simple dd Usage
dd With Compression
Other Programs
Conclusion
© 2011 Warren Block

Last updated 2011-05-24

Available in HTML or PDF. Links to all my articles here. Created with AsciiDoc.

Introduction

There are many ways of backing up a FreeBSD system. Which method to choose depends on your needs. Here we’ll describe the most common methods, and their benefits and disadvantages. The FreeBSD Handbook Backup Basics chapter is another source you should examine.

Be very careful about backups. Test the data from them. The more copies, the better. Consider the information here advisory and untested. No guarantee is expressed or implied that any of this works or will protect your data or will elicit anything more than loudly apologetic noises if data loss occurs.

Archives

Beware of backup methods that only keep one copy of data; there’s no way to retrace your steps. Deleted a file last month? It’s gone, because we only have a backup from last night. The backup methods below save to files, and it’s easy to put those files into a new yyyy-mm-dd directory each time. Keep backups as far back as possible.

dump(8)/restore(8)

dump(8) and restore(8) have been the standard FreeBSD backup utilities since vacuum tubes roamed the earth. They work at the filesystem level, and so only back up used space. restore can provide access to individual files in a backup, useful when your system is fine but you just need that one file out of a backup.

dump is not particularly fast, but because it only backs up actual data (filesystem blocks in use), can be faster than whole-disk backup methods.

Each filesystem must be dumped individually; this is sometimes expressed as "dump does not cross filesystems". On a normal FreeBSD system, a full backup includes dumps of /, /var, and /usr. /tmp is a separate filesystem, too, but you’re not supposed to be storing valuable data there.

/ and /var are usually small and trivial to dump, with /usr being much larger. If you’re dumping to files on a FAT filesystem, the 2G/4G file size limit can be an unwelcome surprise.

dump can take either mountpoints like /var, or the device node of an unmounted filesystem, like /dev/ada0s1d. If a filesystem is mounted, the -L snapshot option should be used.

Useful dump Options
One of the nice usability features of FreeBSD is that ctrl-T sends a SIGINFO to the controlling process. With dump, this means you can press ctrl-T and get a "percent completed" report. That report will also be printed automatically every five minutes, if you’re the patient sort.

Giving dump a larger cache with the -C option can help speed things up. Be a little cautious: since dump forks multiple processes, the cache size will be used multiple times. If you overcommit memory and the computer starts to swap, the dump will change from "annoyingly slow" to "glacially agonizing". The dump(8) man page recommends 8 to 32 megabytes. It also recommends always using -C when the -L snapshot option is used.

Directories to be skipped in a dump can be marked with the nodump flag:

# chflags nodump /usr/ports
Because the Ports Collection is easy to recreate and often contains large distfiles, it’s a prime candidate to skip in backups. (On computers with poor net access where those distfiles are hard to download, you might want to back it up; as always, it depends on your situation.)

Other large directories with easy-to-recreate or unnecessary data could include /usr/src and various types of cache, like web browser cache or the files created by devel/ccache.

restore(8)
restore has many useful options, including the ability to interactively choose individual files for restoration. Here, we’ll only show restoring a whole filesystem, but please see the restore(8) man page.

restore puts restored files in the current directory. Don’t forget to cd to the right directory before running restore.
Simple dump
Back up a single system to an external hard drive. The external hard drive is UFS-formatted, so file size limits are not a problem.

# mount /dev/da0s1 /mnt
# dump -C16 -0uanL -h0 -f /mnt/root.dump /
# dump -C16 -0uanL -h0 -f /mnt/var.dump /var
# dump -C16 -0uanL -h0 -f /mnt/usr.dump /usr
# umount /mnt
The external drive is mounted at /mnt, then the /, /var, and /usr filesystems are dumped to files on it.

Restoring this backup is also pretty simple. This example restores the /var filesystem into a temporary directory. This is the type of restore used when you’re looking for some files that were accidentally deleted. If you were restoring a whole system, you’d restore the data over the filesystem where it originated.

# mount /dev/da0s1 /mnt
# mkdir /tmp/oldvar
# cd /tmp/oldvar
# restore -ruf /mnt/var.dump
# umount /mnt
See the restore(8) man page for the -i option, which provides a shell-like interactive session for selecting files and directories to restore.

dump With Compression
# mount /dev/da0s1 /mnt
# dump -C16 -0uanL -h0 -f - / | gzip -2 > /mnt/root.dump.gz
# dump -C16 -0uanL -h0 -f - /var | gzip -2 > /mnt/var.dump.gz
# dump -C16 -0uanL -h0 -f - /usr | gzip -2 > /mnt/usr.dump.gz
# umount /mnt
Like the previous example, but dump output is piped through gzip, creating compressed dump files.

gzcat is used to decompress these files for restoration. This example restores to a temporary directory so the backed-up directories and files are available but don’t overwrite the current files.

# mount /dev/da0s1 /mnt
# mkdir /tmp/usr
# gzcat /mnt/usr.dump.gz | (cd /tmp/usr && restore -ruf -)
# umount /mnt
dump Via SSH
ssh allows dump to send files to another system on the network, which is very handy, at least if you have more than one system.

# dump -C16 -0uanL -h0 -f - / | gzip -2 | ssh -c blowfish user@otherhost dd of=root.dump.gz
# dump -C16 -0uanL -h0 -f - /var | gzip -2 | ssh -c blowfish user@otherhost dd of=var.dump.gz
# dump -C16 -0uanL -h0 -f - /usr | gzip -2 | ssh -c blowfish user@otherhost dd of=usr.dump.gz
otherhost is the system receiving the backup files, and user is the username on that other system. As before, gzip compresses the files, but then ssh logs in to the other system and copies the input into a file in the user’s home directory with dd.

Backup speeds may be slower than to a directly-connected disk. dump and ssh prompts are mixed together, which can be confusing when ssh is waiting for a password. But the convenience of this method sometimes makes a FreeBSD notebook or netbook a better "backup device" than a simple external hard disk.

Restoring these files is a reversal of the dump command line:

# mkdir /tmp/root /tmp/var /tmp/usr
# ssh -c blowfish usr@otherhost gzcat root.dump.gz | (cd /tmp/root && restore -ruf -)
# ssh -c blowfish usr@otherhost gzcat var.dump.gz | (cd /tmp/var && restore -ruf -)
# ssh -c blowfish usr@otherhost gzcat usr.dump.gz | (cd /tmp/usr && restore -ruf -)
Depending on the speed of the computers and the network, it may be faster to run the dump or restore on one computer and the compression on the other. For example, a restore that sends the compressed dump file over the network and decompresses it on the destination computer:

# ssh -c blowfish usr@otherhost dd bs=64k if=usr.dump.gz | (cd /tmp/usr && gzcat | restore -ruf -)
There are many possible variations. Networks and computers vary so widely that only testing will show what is effective on a particular system.

Copying Filesystems
Filesystems can be copied directly from one disk to another by piping the output of dump right into restore. Source and target filesystems don’t have to be the same size, the target just needs to be large enough to hold all the data in the source.

This example copies a running FreeBSD system onto a new drive. Slices and partitions have already been set up on the new disk, da0. The target is the first slice, with a traditional FreeBSD partition layout: a is /, d is /var, e is /tmp, and f is /usr. See Disk Setup On FreeBSD for background.

/var and / are copied last. They are smaller and may have changed during the copy of the much-larger /usr filesystem.

# mount /dev/da0s1f /mnt
# dump -C16 -0uanL -h0 -f - /usr | (cd /mnt && restore -ruf -)
# umount /mnt
# mount /dev/da0s1e /mnt
# dump -C16 -0uanL -h0 -f - /tmp | (cd /mnt && restore -ruf -)
# umount /mnt
# mount /dev/da0s1d /mnt
# dump -C16 -0uanL -h0 -f - /var | (cd /mnt && restore -ruf -)
# umount /mnt
# mount /dev/da0s1a /mnt
# dump -C16 -0uanL -h0 -f - / | (cd /mnt && restore -ruf -)
# umount /mnt
restore(8) Over A Live System
It is possible to restore over a live system. For example, if a hard drive dies, do a minimal FreeBSD install on a new hard drive, then boot that system and restore the /usr, /var, and then / filesystems. I suggest that order because continuing to run after overwriting important data in / is probably something to avoid. After the restore and a reboot, doing a full buildworld/kernel/installworld cycle is a good idea, to make sure the whole system is consistent.

The New Huge Disk section of the FreeBSD FAQ shows a different technique for a similar operation.

Clonezilla

Clonezilla is an open source live CD/USB image that has some of the functionality of Ghost. It’s not as easy to use; you need to know a few Linuxisms like that external hard drive you just connected is /dev/sda or sdb, or maybe hda. But once you get past that, it has lots of useful features. Several open-source backup programs are used to make sure that only occupied space is copied. Data is compresses and split into 2G files so it can use FAT filesystems for backup. It can use external drives, or SSH, Samba, or NFS network drives. It’s menu-driven, so it’s not terribly difficult to use. And the latest versions even support FreeBSD’s UFS filesystems.

Individual files aren’t accessible in the backups, and there’s no way to skip directories that don’t need to be backed up. But for whole-disk, mostly-automated backup of FreeBSD systems, you could do worse.

dd

dd is not a good way to back up a system. It has many disadvantages, including copying every block on the drive or slice, whether used or not.

The advantages are that dd is very easy to use. Just copy one drive to another—what could go wrong?

Well… the target drive must be at least as large as the source drive. It can be larger, although any extra space will be wasted.

Be very careful about source and destination drives. Get them mixed up on the command line, and the source drive will be overwritten. Allow me to repeat that: getting source and target mixed up will destroy your data.

Some notable utilities are based on using dd like this. g4u (Ghost For Unix) is one of the better-known versions.

Simple dd Usage
Copy drive ad0 to ad4. ad0 is your source drive, right? And you’re positive about that, right? The drive numbers didn’t change after adding the second drive? Okay…

# dd if=/dev/ad0 of=/dev/ad4 bs=64k
Setting the bs (block size) parameter to 64k will buffer the reads and writes and speed up the copy. Why not an even larger buffer? Because a larger buffer won’t speed up a typical drive. If you have a RAID setup or something faster than typical SATA or IDE, a larger buffer might be useful.

dd With Compression
A lot of space on the average disk is empty, but the blocks still have old data in them. That random old data doesn’t compress well if you dd that drive into an image file. You can improve the situation by filling all unused space with zeros. The brute-force method—and dd is nothing if not brute force—is to just build a file full of zeros until you completely fill the disk. Then delete the file.

# dd if=/dev/zero of=zerofile bs=1M
...(wait for an out-of-space error)
# rm zerofile
Unused space on the drive is now mostly compressible zeros.

# mount /dev/da0s1 /mnt
# dd if=/dev/ad0 bs=1M | gzip -2 > /mnt/diskimg.gz
# umount /mnt
While the image file is smaller, restoring it will still require a drive at least as large as the original.

Other Programs

There are advocates of all sorts of programs for backup. cpio(1), pax(1), and tar(1) are mentioned frequently in this context. For some things, they work fine. But only dump backs up at the filesystem block level and can be trusted for any of the weird situations a UFS filesystem may contain.

There are other situations, of course. For making an archive of a directory tree, tar(1) with a compression option like -z or -j is a widespread standard.

For copying files or directories, net/rsync is very useful. It only copies the differences between source and target, and supports compression, so updates are very quick. Built-in support for ssh(1) means those directories can be on separate computers on the network. If you wanted to make a backup of selected directories of data files, rsync works extremely well for that, and it’s versatile enough to be used in lots of ways.

Conclusion

There are many ways to go about keeping data that was expensive and difficult to create, some that go far beyond the ones listed above. There’s Bacula, and Amanda, and all sorts of commercial and homebuilt schemes.

Whatever you pick, remember that backups are the kind of thing you only need when you don’t have them.

Reference:
http://www.wonkity.com/~wblock/docs/html/backup.html

Thursday, December 9, 2010

Do-It-Yourself Backup System Using Rsync

Do-It-Yourself Backup System Using Rsync

Written by Kevin Korb

as a presentation for GOLUG

Presented 2010-03-02

This document is available at http://www.sanitarium.net/golug/rsync_backups_2010.html


  • What is rsync?
  • Rsync is a program for synchronizing two directory trees across different file systems even if they are on different computers. It can run its host to host communications over ssh to keep things secure and to provide key based authentication. If a file is already present in the target and is the same as on the source the file will not be transmitted. If the file on the target is different than the one on the source then only the parts of it that are different are transferred. These features greatly increase the performance of rsync over a network.

  • What are hard links?
  • Hard links are similar to symlinks. They are normally created using the ln command but without the -s switch. A hard link is when two file entries point to the same inode and disk blocks. Unlike symlinks there isn't a file and a pointer to the file but rather two links to the same file. If you delete either entry the other will remain and will still contain the data. Here is an example of both:
      ------------- Symbolic Link Demo -------
      % echo foo > x
      % ln -s x y
      % ls -li ?
      38062 -rw-r--r--  1 kmk users 4 Jul 25 14:28 x
      38066 lrwxrwxrwx  1 kmk users 1 Jul 25 14:28 y -> x
      -- As you can see, y is only a pointer to x.
    
      % grep . ?
      x:foo
      y:foo
      -- They contain the same data.
      % rm x
      % ls -li ?
      38066 lrwxrwxrwx  1 kmk users 1 Jul 25 14:28 y -> x
      % grep . ?
      grep: y: No such file or directory
      -- Now that x is gone y is simply broken.
      ------------ Hard Link Demo ------------
      % echo foo > x
      % ln x y
      % ls -li ?
      38062 -rw-r--r--  2 kmk users 4 Jul 25 14:28 x
      38062 -rw-r--r--  2 kmk users 4 Jul 25 14:28 y
      -- They are the same file occupying the same disk space.
      % grep . ?
      x:foo
      y:foo
      -- They contain the same data.
    
      % rm x
      % ls -li ?
      38062 -rw-r--r--  1 kmk users 4 Jul 25 14:28 y
      % grep . ?
      y:foo
      -- Now y is simply an ordinary file.
      ---------- Breaking a Hard Link ----------
      % echo foo > x
      % ln x y
      % ls -li ?
      38062 -rw-r--r--  2 kmk users 4 Jul 25 14:34 x
      38062 -rw-r--r--  2 kmk users 4 Jul 25 14:34 y
      % grep . ?
      x:foo
      y:foo
      % rm y ; echo bar > y
      % ls -li ?
      38062 -rw-r--r--  1 kmk users 4 Jul 25 14:34 x
      38066 -rw-r--r--  1 kmk users 4 Jul 25 14:34 y
      % grep . ?
      x:foo
      y:bar

  • Why backup with rsync instead of something else?
    • Disk based: Rsync is a disk based backup system. It doesn't use tapes which are too slow to backup (and more importantly restore) modern systems with large hard drives. Also, disk based backup solutions are much cheaper than equivalently sized tape backup systems.
    • Fast: Rsync only backs up what has changed since the last backup. It NEVER has to repeat the full backup unlike most other systems that have monthly/weekly/daily differential configurations.
    • Less work for the backup client: Most of the work in rsync backups including the rotation process is done on the backup server which is usually dedicated to doing backups. This means that the client system being backed up is not hit with as much load as with some other backup programs. The load can also be tailored to your particular needs through several rsync options and backup system design decisions.
    • Fastest restores possible: If you just need to restore a single file or set of files it is as simple as a cp or scp command. Restoring an entire file system is just a reverse of the backup procedure. Restoring an entire system is a bit long but is less work than backup systems that require you to reinstall your OS first and about the same as other manual backup systems like dump or tar.
    • Only one restore needed: Even though each backup is an incremental they are all accessible as full backups. This means you only restore the backup you want instead of restoring a full and an incremental or a monthly followed by a weekly followed by a daily.
    • Cross Platform: Rsync can backup and recover anything that can run rsync. I have used it to backup Linux, Windows, DOS, OpenBSD, Solaris, and even ancient SunOS 4 systems. The only limitation is that the file system that the backups are stored on must support all of the file metadata that the file systems containing files to be backed up supports. In other words if you were to use a vfat file system for your backups you would not be able to preserve file ownership when backing up an ext3 file system. If this is a problem for you try looking into rdiff-backup.
    • Cheap: It doesn't seem like it would be cheap to have enough disk space for 2 copies of everything and then some but it is. With tape drives you have to choose between a cheap drive with expensive tapes or an expensive drive with cheap tapes. In a hard drive based system you just buy cheap hard drives and use RAID to tie them together. My current backup server uses two 500GB IDE drives in a software RAID-0 configuration for a total of 1TB for about $100 which is about 1/6th what I paid for the DDS3 tape drive that I used to use and that doesn't even include the tapes that cost about $10/12GB.
    • Internet: Since rsync can run over ssh and only transfers what has changed it is perfect for backing up things across the internet. This is perfect for backing up and updating a web site at a web hosting company or even a co-located server. Internet based backup systems are also becoming more and more popular. Rsync is the perfect tool to backup to such services over the internet.
    • Do-it-yourself: There are FOSS backup packages out now that use rsync as their back end but the nice thing here is that you are using standard command line tools (rsync, ssh, rm) so you can engineer your own backup system that will do EXACTLY what you want and you don't need a special tool to restore.

  • Why/When wouldn't you want to use rsync for backups?
    • Databases: Rsync is a file level backup so it is not suitable for databases. If your primary data is databases then you should look somewhere else. If you have databases but they are not your primary data then there are procedures below to integrate database backups into the rsync backups.
    • Windows: If you plan to backup windows boxes then rsync probably isn't for you. It is possible to backup Windows boxes with rsync but the system recovery process is UGLY and if you want a complete backup of the OS you will have to boot the computer into Linux or use Shadow Copy to be able to read some of the files. I have yet to find a simple comprehensive procedure for restoring a complete Windows system based on a copy of all files from C:. If someone has such a procedure I would love to see it.
    • Compression: Since rsync doesn't put the files into any kind of archive there is no compression at all. In most cases it is still more cost effective to store uncompressed data on a hard drive than it is to store compressed data on a tape or some other media but this might not be true for everyone. Also, most modern file formats are already compressed so in many cases the compression wouldn't help anyways.
    • Commercial support: Like most of the stuff I talk about there is no real commercial support for this. If you want a backup software vendor that you can call and beg for help from then go buy some big commercial backup system but expect to pay a ton of money for something that isn't anywhere near as flexible as rsync.
    • Security: Since rsync runs over ssh you would normally set it up so that root on your backup server can ssh into all of your other machines as root without a password. This means that the security of your backup server becomes very important as anyone who roots it can root any other server with one command. There are ways that you could design around this or you could simply require the person running the backup to type in the root passwords as it goes but those solutions all over-complicate things. Giving your backup server all of the keys isn't really as bad as it sounds though when you consider that in any other backup system the backup server would still have some kind of root access to the other servers as well as a complete copy of them that a hacker could use to find vulnerabilities. Note that it is possible to restrict the ssh key used by the backups to only work from the backup server using the from= parameter in the authorized_keys file.
    • Do-it-yourself: Again, this is a do-it-yourself system. You have to decide how you want your backups to work and how you want them organized. If you don't want to write/modify shell scripts then look for something else or look at the available backup systems that use rsync as their back end. Examples of less do-it-yourself oriented backup systems include rsnapshot, dirvish, and hopefully the one I created based on rsync.

  • Why not just use RAID / Is this like using RAID-1? / Is this like DRBD?
  • I don't think I can ever say this enough times.... RAID is NOT a backup system! RAID (other than level 0) does a wonderful job of protecting your data from disk failures. However, it provides absolutely NO protection against file corruption, files destroyed by a virus or a hacker, or the "oops, I deleted the wrong file" problem which most of us have encountered. There is a time and a place for RAID and RAID is not always needed however data should ALWAYS be backed up regardless of what media it is stored on or how redundant that media may be. Networked mirroing solutions like DRBD have the same drawbacks as RAID as they are a simple real-time mirror. My general rule of thumb is that if you can't restore your data to the way it was last Monday or last night using a storage device other than the one the data was on last Monday then you don't have a backup system.

  • Do I need to backup the OS or just the data?
  • In my opinion yes, you need to backup the OS as well as the data. Many people feel that the OS is easily recreated by doing a re-install plus loading a list of applications that was saved during the backup run. While this is true in theory it isn't so easy in practice. If you ever have the catastrophic loss of a server you will find out very quickly that every minute counts. If you have a backup of the OS and an established and practiced procedure for restoring it the recovery will go very quickly and it will probably work the first time. If your recovery procedure includes "install the OS" and "install all the applications" expect to add a full day of listening to users complain while you do those steps. Also, in terms of gigabytes, the OS is usually tiny compared to the data it supports. The extra disk space required to backup the OS will probably not even make a difference in the choice of how big to make the backup system. With the typical ratio of OS vs data it is just silly to not backup the OS. Finally, the worst case scenario is that your data-only backup system misses some configuration or data file that was assumed to be part of the OS but had been modified. If you aren't backing it up you will simply lose it.

  • Why all this talk of a backup server? Why not just use an external hard drive?
  • While it is completely possible to do the backups this way using these procedures (and I have done it this way myself) there are a couple of drawbacks...
    • Security: One of the reasons we have backups is because of the possibility of malicious activity (hackers, worms, trojans, etc). If your backup device is plugged into the computer being backed up then any malicious users or software that can destroy your data can also destroy your backups. Keeping your backups on a separate isolated server protects them from this possibility. Note that this is also why I prefer to pull backups from a script running on the backup server rather than pushing backups from a script running on the backup clients.
    • Performance: Rsync's ability to transfer only the parts of a file that have changed does not work on local transfers. This is because the feature would actually be counter-productive on a local transfer. Rsync would have to read and hash both versions of the file then write out the new version of the file instead of simply reading from the source and writing to the target. Also, most external hard drives are USB which is a pretty slow interface. Note that this is also true if you use a network mount (such as NFS, Samba, or CIFS) to access the remote data instead of a network transport like ssh or rsyncd. Read the rsync man page section on --whole-file for more information.

  • What about a Network Attached Storage (NAS) device instead of a server?
  • This depends on the quality of the NAS device. Rsync is designed to reduce network IO at the expense of disk IO and CPU cycles. It can do this because normally you have two instances of rsync running on two systems with a network protocol in between. Both instances of rsync have local disk access to one side of the transfer so they can do calculations to reduce the amount of data that needs to be transmitted across the network. If rsync is only running at one end of the network connection then disk IO is really network IO so those features are automatically disabled. Some of the higher end NAS devices support rsync directly and can be treated exactly like a standard rsync backup server. Unfortunately not all NAS devices are this smart. Some will only provide access via network mounts and some even only support CIFS instead of NFS. If yours doesn't at least support NFS I would not suggest using it for rsync backups. If your best choice is NFS then note that --whole-file will be forced by rsync to reduce the performance impact of NFS.

  • How do you do off-site/off-line backups with rsync?
  • The best way to do an off-site or off-line backup is to do the rsync backup like normal and then backup the backup to tape or whatever media you want to use for your off-line/off-site backups. This gives you all the speed advantages of rsync during the actual backups and restores while allowing you to do the slower tape backups during the day when the backup server would otherwise be idle. Note that I do not recommend using removable hard drives for off-site rsync backups. Hard drives have very fragile moving parts and if you are constantly transporting them around they will not last long and will probably fail when you need them most as that is when they will be transported.

  • How do you handle databases?
  • Databases can't just be backed up like files. This is because database engines are constantly making changes to the database files at the block level. If you backed them up with a file based tool like rsync the backup would be inconsistent and possibly even unusable. The best way to backup most databases is to take an LVM snapshot of the database then rsync backup the snapshot of the database. This allows you to have all the advantages of an rsync backup with as little impact to the running database as possible. If you can't use LVM snapshots then your next best bet is to use the database specific tools (mysqldump, pg_dump, etc) to dump the database contents to files that can be backed up. If all else fails you can lock or shutdown the database engine so the files are not changing during the backup but this will be a huge impact outage.

  • How much space does it take to do rsync backups while keeping old copies?
  • This completely depends on how much change there is between each backup and how many backups you retain. I have seen it as low as 5% and as high as 40% but it is completely dependant on your data and your retention policy.

  • Organizing backups
  • Since this is a do-it-yourself system this is totally up to you to design. I have my backup storage mounted under /backup and put all of my rsync backups under /backup/rsync. Within that directory I make a directory for each host that gets backed up. Then for each backup of each file system I change '/' to '_' in the mount point name and time stamp the file system so my backup of /home/asylum done at 17:47 on 2005-07-25 would be stored in /backup/rsync/asylum/_home_asylum.2005-07-25.17-47-42. When the backup is done I would create a symlink from that directory to /backup/rsync/asylum/_home_asylum.current to make it easier to find especially from scripts. As the backup is running I replace the date and time in the filename with "incomplete" so that if a backup is aborted or fails it does not appear to be a complete one and does not count against the number of old backups to keep.

  • Rotating backups
  • Rsync does the incremental backups using "hard links" and the --link-dest parameter. However, it has no mechanism for purging old backups when they reach a predefined age. The purging can be done with a simple rm -rf of the oldest backup(s) as needed. However, the deletion of a large directory tree can take a significant amount of time and probably isn't something you want to waste time on during your backup window. Therefore, instead of doing an rm -rf I suggest just doing an mv to a deletion pool directory within the same file system. This will allow you to easily do all of the deletions later after the backups have finished.

    Here is how the organization with the hard links looks:
    You can determine the current backup with:
      # readlink _home_asylum.current
      _home_asylum.2005-07-25.15-32-42
    Here is an example of 10 backups of my home directory:
      # du -shc _home_asylum.2*
      9.7G    _home_asylum.2005-06-21.15-29-25
      161M    _home_asylum.2005-06-22.20-12-01
      207M    _home_asylum.2005-06-30.18-36-21
      125M    _home_asylum.2005-07-01.12-15-05
      173M    _home_asylum.2005-07-05.11-05-34
      181M    _home_asylum.2005-07-07.13-43-22
      176M    _home_asylum.2005-07-07.17-22-09
      234M    _home_asylum.2005-07-13.11-14-32
      160M    _home_asylum.2005-07-18.16-32-54
      168M    _home_asylum.2005-07-25.15-32-42
      12G     total
      # foreach f (_home_asylum.2*)
      foreach? du -sh $f
      foreach? end
      9.7G    _home_asylum.2005-06-21.15-29-25
      9.7G    _home_asylum.2005-06-22.20-12-01
      9.7G    _home_asylum.2005-06-30.18-36-21
      9.7G    _home_asylum.2005-07-01.12-15-05
      9.7G    _home_asylum.2005-07-05.11-05-34
      9.8G    _home_asylum.2005-07-07.13-43-22
      9.8G    _home_asylum.2005-07-07.17-22-09
      9.8G    _home_asylum.2005-07-13.11-14-32
      9.7G    _home_asylum.2005-07-18.16-32-54
      9.8G    _home_asylum.2005-07-25.15-32-42
    Note that each backup individually is complete but when taken together there is only a small increase in disk usage. This concept is the key of rsync incremental backups.

    To purge old backups simply count how many there are and if there are too many just move the oldest one to your deletion pool and repeat the procedure until there is no longer too many old backups.

  • Actually backing up
  • Now we get to actually look at rsync. When you run rsync you will tell it to backup the live file system into a new empty directory and to look to the previous backup for files that have already been backed up. Whenever rsync finds a new file it will copy over that file. Whenever it finds a modified file it will copy over the differences making a new file in the new backup directory but leaving the old version of the file as it was in the old backup directory. When rsync finds a file that has not changed since the last backup it will simply be hard linked into the new backup directory requiring almost no additional disk space. There is a wide variety of options that can be used with rsync to tailor it to your specific needs but here is what my system uses by default:
      # rsync --archive --one-file-system --hard-links \
      --human-readable --inplace --numeric-ids --delete \
      --delete-excluded --exclude-from=excludes.txt \
      --link-dest=/backup/rsync/asylum/_home_asylum.2005-07-25.15-32-42 \
      asylum:/home/asylum/ /backup/rsync/asylum/_home_asylum.incompllete/
    I also add --verbose --progress --itemize-changes when I am watching the backup run instead of using it from a cron job. Now I will explain the components of that rather long command...
    • rsync: Duh, the rsync command ;)
    • --archive: This causes rsync to backup (they call it "preserve") things like file permissions, ownerships, and timestamps.
    • --one-file-system: This causes rsync to NOT recurse into other file systems. If you use this like I do then you must backup each file system (mount point) one at a time. The alternative is to simply backup / and exclude things you don't want to backup (like /proc, /sys, /tmp, and any network or removable media mounts)
    • --hard-links: This causes rsync to maintain hard links that are on the server being backed up. This has nothing to do with the hard links used during the rotation.
    • --human-readable: This tells rsync to output numbers of bytes with K, M, G, or T suffixes instead of just long strings of digits.
    • --inplace: This tells rsync to update files on the target at the block level instead of building a temporary replacement file. It is a significant performance improvement however it should not be used for things other than backups or if your version of rsync is old enough that --inplace is incompatible with --link-dest.
    • --numeric-ids: This tells rsync to not attempt to translate UID <> userid or GID <> groupid. This is very important when doing backups and restores. If you are doing a restore from a live cd such as SystemRescueCD or Knoppix your file ownerships will be completely screwed up if you leave this out.
    • --delete: This tells rsync to delete files that are no longer on the server from the backup. This is less important when using --link-dest because you should be backing up to an empty directory so there would be nothing to delete however I include it because of the possibility that the *.incomplete directory I am backing up to is actually left over from a previous failed run and may have things to delete.
    • --delete-excluded: This tells rsync that it can delete stuff from a previous backup that is now within the excluded list.
    • --exclude-from=excludes.txt: This is a plain text file with a list of paths that I do not want backed up. The format of the file is simply one path per line. I tend to add things that will always be changing but are unimportant such as unimportant log and temp files. If you have a ~/.gvfs entry you should add it too as it will cause a non-fatal error.
    • --link-dest=/backup/rsync/asylum/_home_asylum.2005-07-25.15-32-42: This is the most recent complete backup that was current when we started. We are telling rsync to link to this backup for any files that have not changed.
    • asylum:: This is the host name that rsync will ssh to.
    • /home/asylum/: This is the path on the server that is to be backed up. Note that the trailing slash IS significant.
    • /backup/rsync/asylum/_home_asylum.incomplete/: This is the empty directory we are going to backup to. It should be created with mkdir -p first. If the directory exists from a previous failed or aborted backup it will simply be completed. This trailing slash is not significant but I prefer to have it.
    • --verbose: This causes rsync to list each file that it touches.
    • --progress: This adds to the verbosity and tells rsync to print out a %completion and transfer speed while transferring each file.
    • --itemize-changes: This adds to the file list a string of characters that explains why rsync believes each file needs to be touched. See the man page for the explanation of the characters.

  • Recovering files from backups
  • Because rsync doesn't put the backed up files into any kind of archive this is as simple as copying a file. Just find the file you need on the backup server and copy it to where you need it to be. If you are restoring it to another server just use rsync or scp to get it there. Here are 2 examples of files that can be restored from my home directory:
      # ls -li _home_asylum.2*/kmk/bin/encode
      3605946 5 kmk users 2223 Jul  2 11:34 _home_asylum.2005-07-05.11-05-34/kmk/bin/encode
      3605946 5 kmk users 2223 Jul  2 11:34 _home_asylum.2005-07-07.13-43-22/kmk/bin/encode
      3605946 5 kmk users 2223 Jul  2 11:34 _home_asylum.2005-07-07.17-22-09/kmk/bin/encode
      3605946 5 kmk users 2223 Jul  2 11:34 _home_asylum.2005-07-13.11-14-32/kmk/bin/encode
      3605946 5 kmk users 2223 Jul  2 11:34 _home_asylum.2005-07-18.16-32-54/kmk/bin/encode
      4853134 1 kmk users 4012 Jul 21 19:31 _home_asylum.2005-07-25.15-32-42/kmk/bin/encode
      # ls -li _home_asylum.2*/kmk/bin/mp3db
      4074469 1 kmk users 29598 Jun 19 16:01 _home_asylum.2005-06-21.15-29-25/kmk/bin/mp3db
      4082467 1 kmk users 29943 Jun 22 19:10 _home_asylum.2005-06-22.20-12-01/kmk/bin/mp3db
      4124342 1 kmk users 30570 Jun 30 17:22 _home_asylum.2005-06-30.18-36-21/kmk/bin/mp3db
      2617551 1 kmk users 30701 Jul  1 12:17 _home_asylum.2005-07-01.12-15-05/kmk/bin/mp3db
      3605948 1 kmk users 35604 Jul  1 16:50 _home_asylum.2005-07-05.11-05-34/kmk/bin/mp3db
      4411207 2 kmk users 35668 Jul  6 11:06 _home_asylum.2005-07-07.13-43-22/kmk/bin/mp3db
      4411207 2 kmk users 35668 Jul  6 11:06 _home_asylum.2005-07-07.17-22-09/kmk/bin/mp3db
      4523360 1 kmk users 37041 Jul  9 17:28 _home_asylum.2005-07-13.11-14-32/kmk/bin/mp3db
      4675812 1 kmk users 37201 Jul 18 09:50 _home_asylum.2005-07-18.16-32-54/kmk/bin/mp3db
      4853138 1 kmk users 37200 Jul 19 16:46 _home_asylum.2005-07-25.15-32-42/kmk/bin/mp3db
    As you can see my encode script has been fairly constant while my mp3db script has changed almost every time I have run a backup. I can choose to restore whichever version I want as they are all just plain files.

  • Recovering entire file systems from backups
  • This is a simple reverse of the backup procedure. Just format the new file system and rsync the files back to it and make sure you use the same rsync options especially --archive and --numeric-ids.

  • Recovering entire systems from backups
  • This is where things get a little ugly. Of course this is for times that are already ugly because you probably just lost your boot drive and have a brand new one installed that is completely blank. This procedure varies a bit depending on what OS you are restoring but here is the general idea:
    1. Boot from some media that gives you an OS, networking, rsync, and ssh. SystemRescueCD, Knoppix, or most other Live distribution can do the job for Linux systems. In the case of OpenBSD I boot their install disc and then use ftp to transfer a tarball the rsync backup instead of using rsync. The same thing will work in Solaris although it is usually easier to NFS mount the backup repository.
    2. Partition the new drive with fdisk or whatever you usually use. If you follow my advice in the advanced section you will have an .sfdisk file and you can duplicate the original partition table with 'sfdisk /dev/whatever < file.sfdisk'.

    3. Format the new partitions. Linux choices are mke2fs, mkfs.ext4, mkfs.xfs, and mkswap. For most other operating systems it is simply newfs.

    4. Mount up the new partitions in a convenient location with something like:
          # mkdir /s
          # mount -vt [fstype] /dev/[root partition] /s
          # mkdir /s/usr /s/var /s/proc /s/dev /s/tmp
          # chmod 1777 /s/tmp
          # mount -vt [fstype] /dev/[var partition] /s/var
          # mount -vt [fstype] /dev/[usr partition] /s/usr
    5. Now run your file system level restores just like you would if you weren't recovering the entire system. You will need to restore each file system that was on the old boot disk.

    6. If you have made any changes such as device names, mount points, or partition layouts you should now update /s/etc/fstab and /s/boot/grub/menu.lst.

    7. Fix up /dev if needed
            # cp -av /dev/console /dev/null /s/dev/
    8. Now you have to make the disk bootable again. This totally varies by operating system and boot loader...


      For Linux systems using grub:
            # grub-install --root-directory=/s /dev/sda
      Or, if that doesn't work:
            # grub
            root (hd0,0) # or whatever partition matches your boot disk
            setup (hd0)
            exit
      For Linux systems using lilo (why are you still using lilo?):
            # mount -vo bind /dev /s/dev
            # mount -vo bind /proc /s/proc
            # chroot /s /bin/bash
            # lilo -v
            # exit

      For OpenBSD systems:
            # cd /s/usr/mdec
            # ./installboot /s/boot ./biosboot /dev/rwd0c (or /dev/rsd0c if using SCSI)
      For Solaris systems:
            # installboot /s/usr/platform/`uname -i`/lib/fs/ufs/bootblk /dev/rdsk/c0t0d0s0

  • Advanced topics
    • System Cloning: Once you have an rsync backup system and restore procedure in place it is easy to use your restore procedure as a method of cloning existing OS installs onto new systems. This has the added benefit of forcing you to practice your restore procedure so it will be well known and tested when the day comes that you need to restore something.
    • Format of backup repository: Assuming you are using a Linux box as your backup server you have multiple choices for the file system type that you want to format the backup drive with. I generally use ext4 because it is the fastest well established file system available currently and it does a good job as long as there aren't too many files for fsck to handle in a reasonable amount of time. However, XFS is also a good choice because it is better at dealing with large files and it is much better at doing the delete portion of the backup rotation. XFS also eliminates the need for the periodic off-line fsck which may make it your only choice if you have many millions of files to deal with. You may want to play with these 2 choices a bit before you make your final decision. I do not recommend using JFS as it has horrible performance or reiserfs as it has horrible reliability.
    • ZFS: If you have many millions of files to deal with you may discover that this system simply takes too long to delete old backups and if you ever need an fsck you may even be down for days waiting for it to finish. ZFS on OpenSolaris is the answer to your prayers. ZFS can handle multiple LVM-like snapshots. The benefit here is that you can run rsync backups without the --link-dest parameter and simply overwrite the previous backup each run. Then you use the ZFS snapshots to retain the old backups. Each old backup becomes a snapshot mount. The snapshots are created and deleted in less than a second removing the need for the long rm operations to purge old backups and allowing rsync to just sync files without bothering to create hard links. Hopefully soon btrfs will give us this capability in Linux but until then ZFS on OpenSolaris is the thing that completes large scale rsync backups.
    • RAIDed backup repository: This is a somewhat interesting topic. There are many opinions out there about whether or not a backup repository should be made redundant using RAID. For many people (including me for personal use) the backups are an additional redundant copy of something that is already stored on a redundant RAID array and therefore the backups do not need to be redundant. That is why my personal backups are on a RAID-0 volume for pure speed and large capacity. For others (including me for professional use) the most important part of the backups is the old backups which contain data that no longer exists on the other systems. This means that the backups should be on redundant storage here. At work I use either RAID-1 or RAID-10 depending on the size of the backup system. It is of course also possible to use RAID-5 but depending on your hardware you may not like the performance. If you do use RAID other than RAID-1 you should set the stripe size fairly small as most of the work for rsync backups is done at the file system metadata level not the large file level.
    • Cross-platform handling of /dev and other device files: Since different operating systems handle major and minor numbers differently I suggest excluding /dev from the rsync backups. I keep a /dev.tar tarball on all of my boxes with a backup of /dev in it just in case I ever need to restore that. The tarball will be very small since there are no actual data in it. Note that this is completely unimportant on Linux systems that use udev for /dev.
    • What is different between 2 backups: I wrote a perl script that scans 2 backups of the same directory and lists what has changed between them. I have published that script at http://www.sanitarium.net/unix_stuff/Kevin%27s%20Rsync%20Backups/diff_backup.pl.txt
    • Storing data that isn't kept in a file: I wrote a perl script that does backups of data that isn't stored in files such as partition tables. My main backup script runs this "getinfo" script at the start of a backup if it detects an infotab file telling it what to backup. The script is published at http://www.sanitarium.net/unix_stuff/Kevin%27s%20Rsync%20Backups/getinfo.pl.txt. I also have an example of its tab file format published at http://www.sanitarium.net/unix_stuff/Kevin%27s%20Rsync%20Backups/asylum/infotab
    • rsync --dry-run: This is rsync's testing mode. You can use this on any other rsync command to have rsync tell you what it would have done without actually doing anything.
    • rsync --whole-file: This tells rsync to transfer entire files instead of using its block level comparison system. If you have a nice fast link (like a LAN) this can make things faster since rsync doesn't have to checksum files at all but if you are transferring across the internet you don't want this.
    • rsync --checksum: Normally rsync compares the timestamp and the size of a file to determine if it has changed since the last backup. If you use --checksum rsync will ignore the time stamps and checksum any files that are the same size to determine if they are different. Obviously this adds a significant slowdown to the backup process. You wouldn't normally use this option however it is good to have if you believe your backup data has become corrupted in a way that doesn't affect the information you see in an ls -l output.
    • rsync --size-only:
    • rsync --sparse: This tells rsync to turn files with large chunks of null characters into sparse files as it transfers them. This is common for things like virtual machine images that have free space inside of them. Without this option such files will be larger on the backup than on the source.
    • rsync --delete-*: There are several options that control when rsync does the deletion process. Normally you would just use --delete and let rsync use the fastest one available in your version of rsync however there are times when you want to force it to behave differently. As of version 3.0.6 --delete-during is the default for --delete. See the man page for more information.
    • rsync --temp-dir: If you have a tmpfs mount you can get a very small speed boost by using this parameter. It causes the partial files used during the delta transfers to be stored in an alternate (faster) location until the file is complete. This will only help if you are doing delta transfers and if the directory you specify is on a tmpfs mount. Note that your tmpfs mount must be big enough to hold any single file or it will cause rsync to fail with an insufficient disk space error. Also, if your tmpfs mount goes into swap you will completely kill your performance. IOW, don't use this unless you are sure it is going to help. Also, note that the --inplace parameter is even better than this.
    • rsync --bwlimit: Allows you to limit how much bandwidth rsync uses in its network communications. It is measured in KB per second.
    • rsync --ignore-errors: This overrides one of rsync's built in safety features. Normally if there is a problem during the backup rsync will NOT run its delete pass. If you use --ignore-errors the delete pass will run regardless of any other errors. Note that this isn't as dangerous as it sounds because rsync with --link-dest should be operating on an empty directory with nothing to delete anyway and even if it does delete something it would not delete from the previous backup directories.
    • rsync --max-delete: This allows you to re-implement the safety feature above with a threshold. You can tell rsync how many files it can delete before it decides that something must be wrong and stops.
    • rsync --compress: This tells rsync to use zlib compression on its communications. This would be good if you are backing up over the internet but it is usually counter productive on a LAN. You can also do compression at the ssh level however rsync's is a little more efficient. Of course you should not do both.
    • rsync --acls: This tells rsync to transfer ACLs in addition to permissions. Note that this is a compile time option.
    • rsync --xattr: This tells rsync to transfer extended attributes (the ones you set with chattr) in addition to permissions. Note that this is a compile time option.
    • push instead of pull: Rsync can push data just as well as it can pull it. It is possible to have all servers push their backups to the backup server instead of the backup server pulling the data from them. I personally don't like this approach because it means that all your servers have the key to your backup server instead of the other way around and because you have to engineer a much more complicated way of doing the rotations as well as making sure you don't have 20 servers trying to back themselves up at the same time which would flood the backup server.
    • Buddy backups: If you don't want to dedicate a box to running backups you could pair off your boxes and have them backup each other. You could also do this in a ring layout.
    • LVM Snapshots: It is often wise to use an LVM to take an instant shot of a file system and then backup that snapshot. This would remove any chance of a file system changing during the backup. As mentioned before this is the preferred method of backing up a database but it is also good for things like email servers.
    • Squashfs for archives: If you want to make a permanent archive of a particular backup (perhaps to burn it) squashfs is a great way to do it. Squashfs creates a compressed mountable archive of a directory tree. You create a squashfs archive with mksquashfs which works much like mkisofs and then you can mount the resulting file as a loopback device.
    • FAT: I do not recommend backing up to a FAT file system using rsync. However, rsync is perfectly capable of backing up a FAT file system. However, there are issues with how FAT stores time stamps. FAT can only store time stamps with a 2 second resolution. The easy fix for that is to use rsync with --modify-window=2. FAT also handles daylight savings time changes differently. When the time changes FAT file systems will appear to be 1 hour off. The easiest solution for that is to use rsync with --modify-window=3602.
    • Sudo: It is possible to use rsync under sudo even on both ends. I personally believe that this is ugly, insecure, and an abuse of the sudo system but it can be done. If you run rsync under sudo and add --rsync-path='/usr/bin/sudo /usr/bin/rsync' then configure sudo to not prompt for a password when that user runs rsync --server it will work.

  • Helpful links
Reference: http://www.sanitarium.net/golug/rsync_backups_2010.html

Wednesday, March 24, 2010

想嘗試找尋看有沒有 類似 TrueImage 好用的 備份 方式,讓系統 死了 可以 短時間內恢愎

想嘗試找尋看有沒有 類似 TrueImage 好用的 備份 方式,讓系統 死了 可以 短時間內恢愎

有幾種方式 不同效果

- 只備份 /etc /usr/local/etc 設定檔

- rsync 到另一台主機

- snapshot ?

- dump and restore ?

- dd

- RAID

Tuesday, February 23, 2010

InnoDB takes over an hour to import 600MB file, MyISAM in a few minutes

I'm currently working on creating an environment to test performance of an app; I'm testing with MySQL and InnoDB to find out which can serve us best. Within this environment, we'll automatically prepare the database (load existing dumps) and instrument our test tools.

I'm preparing to test the same data dump with MySQL and InnoDB, but I'm already failing to bring the initial import to an usable speed for the InnoDB part. The initial dump took longer, but that didn't concerned me yet:

$ for i in testdb_myisam testdb_innodb; do time mysqldump --extended-insert $i > $i.sql; done

real 0m38.152s
user 0m8.381s
sys 0m2.612s

real 1m16.665s
user 0m6.600s
sys 0m2.552s
However, the import times were quite different:

$ for i in testdb_myisam testdb_innodb; do time mysql $i < $i.sql; done

real 2m52.821s
user 0m10.505s
sys 0m1.252s

real 87m36.586s
user 0m10.637s
sys 0m1.208s
After research I came over http://stackoverflow.com/questions/457060/changing-tables-from-myisam-to-innodb-make-the-system-slow and then used set global innodb_flush_log_at_trx_commit=2:

$ time mysql testdb_innodb < testdb_innodb.sql

real 64m8.348s
user 0m10.533s
sys 0m1.152s

IMHO still shockingly slow. I've also disabled log_bin for these tests and here's a list of all mysql variables.

Do I've to accept this long InnoDB times or can they be improved? I've full control over this MySQL server as it's purely for this test environment.

I can apply special configurations only for initial import and change them back for applications tests so they better match production environments.

Update:

Given the feedback, I've disabled autocommit and the various checks:

$ time ( echo "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" \
; cat testdb_innodb.sql ; echo "COMMIT;" ) | mysql testdb_innodb;date

real 47m59.019s
user 0m10.665s
sys 0m2.896s

The speed improved, but not that much. Is my test flawed?

Update 2:

I was able to gain access to a different machine were imports only took about 8 minutes. I compared the configurations and applied the following settings to my MySQL installation:

innodb_additional_mem_pool_size = 20971520
innodb_buffer_pool_size = 536870912
innodb_file_per_table
innodb_log_buffer_size = 8388608
join_buffer_size = 67104768
max_allowed_packet = 5241856
max_binlog_size = 1073741824
max_heap_table_size = 41943040
query_cache_limit = 10485760
query_cache_size = 157286400
read_buffer_size = 20967424
sort_buffer_size = 67108856
table_cache = 256
thread_cache_size = 128
thread_stack = 327680
tmp_table_size = 41943040

With these settings I'm now down to about 25 minutes. Still far away from the few minutes MyISAM takes, but it's getting more usable for me.

================================

Did you try the Bulk Data Loading Tips from the InnoDB Performance Tuning Tips (especially the first one):

When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

SET autocommit=0;
... SQL import statements ...
COMMIT;

If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET autocommit and COMMIT statements.

If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.

If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;

For big tables, this can save a lot of disk I/O.

IMO, the whole chapter is worth the read.

Backing up And Restoring MySQL Innodb Database

Backing up And Restoring MySQL Innodb Database

MySQL backups are performed using the common mysqldump tool. This is a command line utility that ships with MySQL and you use at as follows:
% mysqldump --user=user --password=pass --opt DBNAME > dumpfile.sql

You may also need to specify the --host= parameter to force the hostname you are connecting to. This depends largely on how you've setup your user security. This will produce a text file with a series of INSERT/DROP/CREATE SQL statements that will recreate the database.
The --opt flag is very important. This is shorthand to pass in many flags at once; --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This ensures that your database is in a good state while the backup is performed, including restricting all write access while the backup is in operation. Any locks placed will be automatically removed when this utility finishes.


Restoring a backup
Restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:

SET AUTOCOMMIT = 0;

SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;

COMMIT;

SET AUTOCOMMIT = 1;

This turns off all the checks and auto-commits. This is a safe operation to do if you are fully restoring a database since the previous dump has already been validated for legal keys. If however you are importing, or adding to an existing database, then this step is not advisable.
You can then easily import the SQL file into MySQL using:

% mysql --user=user --password=pass DBNAME < dumpfile.sql

or using

mysql> USE db_name

mysql> SET AUTOCOMMIT = 0;

mysql> SET UNIQUE_CHECKS = 0;

mysql> SET FOREIGN_KEY_CHECKS = 0;

mysql> SOURCE dump_file_name

mysql> SET UNIQUE_CHECKS = 1;

mysql> SET FOREIGN_KEY_CHECKS = 1;

mysql> COMMIT;

mysql> SET AUTOCOMMIT = 1;

Note: after done some testings, specifying the autocommit = 0 command will not work, if your sql dump file contains LOCK TABLES WRITE statement. Because the LOCK TABLES WRITE statement seems automatically does "COMMIT;".

According to mysql manual says: Locks may be used to emulate transactions or to get more speed when updating tables.

http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html

Note: how I do testing is I tried to import and export sql dump file, opened up the dump file, and observe the binary log file.

Tuesday, November 24, 2009

Database Backups

5.9.1. Database Backups

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables, followed by FLUSH TABLES for the tables. See Section 13.4.5, “LOCK TABLES and UNLOCK TABLES Syntax”, and Section 13.5.5.2, “FLUSH Syntax”. You need only a read lock; this allows other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES

statement is needed to ensure that the all active index pages are written to disk before you start the backup.

To make an SQL-level backup of a table, you can use SELECT INTO ... OUTFILE. For this statement, the output file cannot previously exist because allowing extant files to be overwritten would constitute a security risk. See Section 13.2.7, “SELECT Syntax”.

Another technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script. See Section 8.10, “mysqldump — A Database Backup Program”, and Section 8.11, “mysqlhotcopy — A Database Backup Program”.

  1. Create a full backup of your database:

    shell> <strong class="userinput"><code>mysqldump --tab=<em class="replaceable"><code>/path/to/some/dir</code></em> --opt <em class="replaceable"><code>db_name</code></em></code></strong>

    Or:

    shell> <strong class="userinput"><code>mysqlhotcopy <em class="replaceable"><code>db_name</code></em> <em class="replaceable"><code>/path/to/some/dir</code></em></code></strong><br />

    You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM tables.)

  2. Stop mysqld if it is running, then start it with the --log-bin[=file_name] option. See Section 5.11.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you executed mysqldump.

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables; see Section 8.10, “mysqldump — A Database Backup Program”.

MySQL supports incremental backups: You need to start the server with the --log-bin option to enable binary logging; see Section 5.11.4, “The Binary Log”. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained further below. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See Section 8.10, “mysqldump — A Database Backup Program”, and Section 8.11, “mysqlhotcopy — A Database Backup Program”.

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave's data. These files are always needed to resume replication after you restore the slave's data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the --slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.

If you have to restore MyISAM tables, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure. Note that it works only if you have enabled binary logging by starting MySQL with the --log-bin option.

  1. Restore the original mysqldump backup, or binary backup.

  2. Execute the following command to re-run the updates in the binary logs:

    shell> <strong class="userinput"><code>mysqlbinlog binlog.[0-9]* | mysql</code></strong><br />

    In some cases, you may want to re-run only certain binary logs, from certain positions (usually you want to re-run all binary logs from the date of the restored backup, excepting possibly some incorrect statements). See Section 8.8, “mysqlbinlog — Utility for Processing Binary Log Files”, for more information on the mysqlbinlog utility and how to use it.

You can also make selective backups of individual files:

  • To dump the table, use SELECT * INTO OUTFILE 'file_name' FROM tbl_name.

  • To reload the table, use LOAD DATA INFILE 'file_name' REPLACE .... To avoid duplicate rows, the table must have a PRIMARY KEY or a UNIQUE index. The REPLACE keyword causes old rows to be replaced with new ones when a new row duplicates an old row on a unique key value.

If you have performance problems with your server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See Section 6.1, “Introduction to Replication”.

If you are using a Veritas filesystem, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.

  2. From another shell, execute mount vxfs snapshot.

  3. From the first client, execute UNLOCK TABLES.

  4. Copy files from the snapshot.

  5. Unmount the snapshot.



Monday, November 23, 2009

Backup MySQL on FreeBSD using Snapshots

Introduction

This page contains details on backing up MySQL on the FreeBSD Operating System.
1. Backup MySQL on FreeBSD using Snapshots

Snapshots on FreeBSD allow an administrator to take a frozen image of a filesystem at a given instant in time.

The following commands should be run with root privileges.
1.1 Creating a snapshot of your MySQL data directory

Creating a snapshot under FreeBSD is simple but first you need to know where your MySQL data directory is.
If you used the ports collection to install MySQL this directory is most likely /var/db/mysql
If you are still unsure and have MySQL running you can find out by issuing the following command mysql -e "show variables like 'datadir';"
Step 1 - Flush MySQL queries and lock tables

* Within the mysql client issue the following command:

FLUSH TABLES WITH READ LOCK;

This command may take a while to complete if you have long running queries. The command FLUSH TABLES WITH READ LOCK actually waits for all statements to complete, even selects. So be careful if you have any long running queries. If you're using only Innodb tables and do not need to synchronize binary log position with backup you can skip this step. To keep the lock open you will need to keep your MySQL client connected and open. The easiest way to do this would be to open another shell to perform the actual snapshot or script the whole process as outlined later.

Step 2 - Create Snapshot

*

Snapshot files must be created in the filesystem that is being snapshotted, so if your MySQL data directory is located on the /var filesystem, an obvious folder would be /var/.snap If this folder does not exist create it as follows:

# mkdir /var/.snap

You can create a snapshot using the mount command or the more simplified mksnap_ffs command as follows:

mount command

# mount -u -o snapshot /var/.snap/mysqlsnap1 /var

mksnapp_ffs command

# mksnap_ffs /var /var/.snap/mysqlsnap1

The above commands will take a snapshot of your /var filesystem and place it in the file /var/.snap/mysqlsnap1.
Note: There is a limit of 20 snapshots per filesystem.
Active snapshots are recorded in the superblock, so they persist across unmount and remount operations and across system reboots.

You can use the snapinfo command to list the current snapshot files by issuing the command:

# snapinfo -a

Step 3 - Unlock MySQL tables

* Now that we have taken the snapshot it is safe to unlock the tables and exit the MySQL client.

UNLOCK TABLES;
exit;

1.2 Mount a snapshot to access the frozen datafiles

To mount the snapshot (Read-Only) you need to do the following.
Step 1 - Attach snapshot file to a memory disk

*

# mdconfig -a -t vnode -f /var/.snap/mysqlsnap1 -u 4

Step 2 - Mount memory disk for access (Read Only)

*

# mkdir /mnt/mysql-backup
# mount -r /dev/md4 /mnt/mysql-backup

1.3 Remove a snapshot to reclaim space

When you are done with a snapshot, it can be removed with the rm command.
If you have already mounted the snapshot you will have to unmount and detach the memory disk before issuing the rm command as follows:
Step 1 - Unmount the snapshot

*

# umount /mnt/mysql-backup

Step 2 - Detach the memory disk

*

# mdconfig -d -u 4

Step 3 - Delete the snapshot

*

# rm -f /var/.snap/mysqlsnap1

Note: Snapshot removal of an 8Gb filesystem takes about two minutes. Filesystem activity is never suspended during snapshot removal.

1.4 Scheduling periodic snapshots

The easiest way to schedule snapshots would be to write a script and use the native Cron tool to run your script as needed. There are tools in the ports collection that can help with scheduling snapshots but as they do not lock the tables you may end up with inconsistent data in your snapshot. To ensure that you get a clean snapshot of your MySQL database you need to run the command FLUSH TABLES WITH READ LOCK from within a seperate session to your snapshot shell and keep that session open for the duration of the snapshot. For this reason it is often easier to script the process, you can download a sample Perl script here for inspiration:

*

mysqlsnap.pl

connect('DBI:mysql:mysql', "$username", "$password") || die "Could not connect to database: $DBI::errstr";
$dbh->do('FLUSH TABLES WITH READ LOCK');
print "Creating snapshot file $snapshot\n";
system("mksnap_ffs $filesystem $snapshot");
$dbh->do('UNLOCK TABLES');
$dbh->disconnect();
print "Snapshot created.\n";
exit(0);

1.5 References

For more general information on snapshots, please see http://www.mckusick.com/softdep/

*

FreeBSD Handbook
*

A brief history of the BSD Fast File System

Friday, November 20, 2009

mysql_ufs_snapshot

mysql_ufs_snapshot

Bourne shell script for FreeBSD servers that use MySQL and the UFS2 filesystem.

Description:
1. Unmounts and deletes old snapshot if found.
2. The mysql(1) monitor is used to flush & lock the database
3. Using MySQL's SYSTEM(), to generate new snapshot using mksnap_ffs(8)
4. Mount resulting snapshot read-only to make it available for external backup.

It's meant to be used with cron(8) as a way to do make daily MySQL snapshots.
Also worth noting, other than the mysql monitor, it only depends on the
FreeBSD base system.

It has only been tested on FreeBSD. If you make changes to get it
working on other 4.4BSD systems that support UFS2, feel free to send any
enhancements.

Sample outputs:

# mysql_ufs_snapshot.sh -h
usage: mysql_ufs_snapshot [-h]
Description: Creates UFS2 snapshot of MySQL data directory
Then mounts the snapshot for further backup operations
Dependencies:
Deps: mksnap_ffs(8), mount(8), mdconfig(8), mysql(1), rm(1), awk(1)

# mysql_ufs_snapshot.sh
-> mysql_ufs_snapshot v2 starting

-> Old snapshot found
-> Detaching memory disk
-> Deleting old snapshot
-> Old snapshot deleted, continuing..
->
-> Launching mysql(1) monitor to lock tables and generate snapshot...
-> done
-> Attaching snapshot to memory disk
-> Snapshot mounted at /mnt/mysql_snapshot
#

#!/bin/sh
# Copyright (c) 2007 TrueStep
# Name: mysql_ufs_snapshot.sh
# Author: Rory Arms - http://www.TrueStep.com/
# CDate: 2007-11-17
# Description: 
# 1. Unmounts and deletes old snapshot if found.
# 2. The mysql(1) monitor is used to flush & lock the database
# 3. Using MySQL's SYSTEM(), to generate new snapshot using mksnap_ffs(8)
# 4. Mount resulting snapshot read-only to make it available for backup
#
# Installation:
# 1. Make sure SNAP_MOUNT (see user knobs) directory exists.
# 2. Put this file in a local path, /usr/local/sbin probably.
# 3. Add a crontab(5) entry to execute this script as as often as desired.
# Example crontab entry for daily execution at 2:30:
# 30      2       *       *       *       root    /usr/local/sbin/mysql_ufs_snapshot.sh 1>/dev/null
#
# Tested with: FreeBSD 6.1, 6.2
# Deps: mksnap_ffs(8), mount(8), mdconfig(8), mysql(1), rm(1), awk(1)
# $Id: mysql_ufs_snapshot.sh,v 1.1.1.1 2007/11/26 21:35:12 rorya Exp $

# user knobs
SNAP_MOUNT="/mnt/mysql_snapshot" # where to mount read-only snapshot
MYSQL_ROOT="/var/db/mysql" # mysql root directory, where the data lives
MYSQL_PASSWORD="" # mysql password for the root user


AUTHOR="TrueStep"
NAME="mysql_ufs_snapshot"
VERSION="2"
SNAP_NAME="mysql_snap" # only change if you want a different snapshot filename
PATH=/bin:/usr/bin:/sbin:/usr/sbin:/usr/local/bin

print() {
 echo "-> $*"
}

print_error() {
 echo "Error: $*" > /dev/stderr
}

print_stderr() {
 echo "$*" > /dev/stderr
}

banner() {
 print "$NAME v$VERSION starting"
 echo
}

prechecks() {
 # Check FreeBSD version
 if [ ! $(uname -r | awk -F. '{ print $1 }') -ge 5 ]; then
  print_error "FreeBSD 5 or higher required"
  exit 255
 fi

 # Make sure MySQL is installed
 if [ ! -x $(which mysql) ]; then
  print_error "mysql(1) monitor not found"
  exit 255
 fi

 # Make sure this is the root user
 if [ ! $(id -u) -eq 0 ]; then
  print_error "You are not a superuser"
  exit 255
 fi

 # Make sure mount point exists
 if [ ! -e $SNAP_MOUNT ]; then
  print_error "mount point $SNAP_MOUNT does not exist, exiting"
  exit 255
 fi

 # Resolve partition mount and snapshot location from MYSQL_ROOT
 SNAP_FS="/$(echo $MYSQL_ROOT | awk -F/ '{ print $2 }')"
 SNAP_DIR="/$(echo $MYSQL_ROOT | awk -F/ '{ print $2 }')/.snap"
 SNAP_PATH=${SNAP_DIR}/${SNAP_NAME}

 # Make sure the .snap directory exists in the target filesystem
 if [ ! -d $SNAP_DIR ]; then
  print_error "$SNAP_DIR doesn't exist, are you sure this is a UFS2 partition?"
  exit 255
 fi
}

delete_oldsnap() {
 # Check to see if there is an existing snapshot from the last time 
 # this was used
 if [ -e $SNAP_PATH ]; then
  print "Old snapshot found"

  # unmount the snapshot
  umount $SNAP_MOUNT

  # Detach memory disk
  print "Detaching memory disk"
  mdconfig -d -u 4

  # Delete snapshot
  print "Deleting old snapshot"
  rm -f $SNAP_PATH
  # Check to make sure the memory disk detached successfully
  if [ ! $? = 0 ]; then
   print_error "memory disk did not detach, exiting"
   exit 255
  fi
  print "Old snapshot deleted, continuing.. "
  print 
 fi
}

create_snap() {
 # Check to see if there is a MySQL password
 if [ -z $MYSQL_PASSWORD ]; then
  MYSQL_OPTIONS=""
 else
  MYSQL_OPTIONS="-p$MYSQL_PASSWORD"
 fi
 # Flush mysql and use SYSTEM() to call mksnap_ffs(8) to create a 
 # UFS2 snapshot
 # This is done because the MySQL read lock is only held while the 
 # mysql(1) session is open.
 print "Launching mysql(1) monitor to lock tables and generate snapshot... "
 mysql $MYSQL_OPTIONS << SQL_MONITOR
FLUSH TABLES WITH READ LOCK;
SYSTEM mksnap_ffs /var '$SNAP_PATH';
UNLOCK TABLES;
EXIT
SQL_MONITOR
print "done"

 # Check mysql(1) status code
 if [ ! $? = 0 ]; then
  print_error "mysql(1) failed to create snapshot, exiting"
  exit 255
 fi
}

mount_snap() {
 # Attach snapshot file to a memory disk
 print "Attaching snapshot to memory disk"
 mdconfig -a -t vnode -o readonly -f $SNAP_PATH -u 4 
 # Check to make sure the snapshot attached
 if [ ! $? = 0 ]; then
  print_error "mdconfig(8) failed to attach snapshot $SNAP_PATH, exiting"
  exit 255
 fi

 # Mount memory disk
 mount -r /dev/md4 $SNAP_MOUNT
 # Check to make sure the mount succeeded
 if [ ! $? = 0 ]; then
  print_error "mount(8) failed to mount snapshot, exiting"
  exit 255
 fi
 print "Snapshot mounted at $SNAP_MOUNT"
}

print_usage() {
 print_stderr "usage: $NAME [-h]"
 print_stderr "Description: Creates UFS2 snapshot of MySQL data directory"
 print_stderr "Then mounts the snapshot for further backup operations"
 print_stderr "Dependencies:"
 print_stderr "Deps: mksnap_ffs(8), mount(8), mdconfig(8), mysql(1), rm(1), awk(1)"
}

print_version() {
 print_stderr "$NAME version $VERSION"
}

# main

# print usage if -h is specified
if [ ! -z $1 ]; then
 case $1 in
  -h|--help)
   print_usage
  ;;
  -v|-V|--version)
   print_version
  ;;
 esac
 exit 1
fi

banner
prechecks
delete_oldsnap
create_snap
mount_snap

MySQL Snapshots on FreeBSD

I read a lot about MySQL backups using LVM Snapshots on Linux, WAFL Snapshots on NetApp and more recently ZFS Snapshots. But did you know you can do the same under FreeBSD?

FreeBSD has had snapshot capability since around 2001 allowing administrators to take a frozen



image of a filesystem at a given instant in time with minimal impact on the server / filesystem. So how does this help with MySQL Backups?

If like me you have loads of space on your database volume and often find yourself making frequent risky modifications to your databases, a snapshot can save you loads of time and headaches by creating a point in time marker that you can fallback on if needed.

Snapshots provide the following benefits:

Efficient – Only as blocks in the active filesystem are modified and written to new locations on disk does the snapshot begin to consume extra space.

Fast – It takes about 30 seconds to create a snapshot of an 8Gb filesystem. Of that time 25 seconds is spent in preparation; filesystem activity is only suspended for the final 5 seconds of that period.

Reliable – FreeBSD ensures that no disk write activity can take place during the setup of the snapshot (typically less than a second). The result is a consistent replica.

If you are running MySQL on FreeBSD, snapshots can certainly help improve your backup strategy. So how do you get started?

Well if you head over to the Wiki you’ll find a detailed guide to creating your first snapshot, but before you click that link there is another new FreeBSD feature that you may not be aware of – Security Event Auditing.

Protecting your data requires more than just regular backups and the security auditing feature in FreeBSD 6.2+ can help assist with the logging of activity on your MySQL server.







Based on Sun’s published Basic Security Module (BSM), the de facto industry standard for auditing, FreeBSD Security Event Auditing provides reliable, fine-grained, and configurable logging of a variety of security-relevant system events, including logins, configuration changes, and file and network access. These log records can be invaluable for live system monitoring, intrusion detection, and postmortem analysis.

At the moment the stable release of FreeBSD will require a Kernel compile to enable audit capability, but future releases should have audit enabled in the generic kernel. You never know by the time FreeBSD 7.0 is available I may have figured out a way to audit MySQL logins using auditd.





If your feeling brave you can download the latest monthly snapshot of the upcoming FreeBSD 7.0 release and put auditing to the test.

Right – I’m off to get a working NetUnit jar ready for the end of the week – I hope





Posted in: BinaryStor | FreeBSD | MySQL | OpenSource | Security

6 Responses to “MySQL Snapshots on FreeBSD” - join in - Click Here

Paul M - October 10th, 2007 at 1:33 am

1

Thanks for the link.

If I find the time and there is FreeBSD available for EC2 I will give this a whirl.

Alan Snelson - October 10th, 2007 at 7:57 am

2

Looks like you may need to cast your vote: http://developer.amazonwebservices.com/connect/thread.jspa?messageID=48392봈

Pythian Group Blog » Blog Archive » Log Buffer #66: a Carnival of the Vanities for DBAs - October 12th, 2007 at 5:36 pm

3

[...] blog has an item on MySQL Snapshots on FreeBSD: backing up your MySQL stuff using the FreeBSD OS’s system snapshot. The item also mentions [...]

Chen Shapira - October 12th, 2007 at 10:21 pm

4

Thanks for the information, I was looking for this solution for quite some time. Good thing Pythian linked here.

Small question:

The procedure includes flushing tables to get a consistent snapshot:

FLUSH TABLES WITH READ LOCK;

However, IIRC, tables kept on innodb storage will ignore the FLUSH command, so the snapshot may still be inconsistent.

Did I get it wrong, or will snapshots really give inconsistent results for INNODB?

Alan Snelson - October 13th, 2007 at 8:26 am

5

Hi Chen,

As far as I am aware an InnoDB snapshot can be taken without locking the tables. On restore InnoDB will recover as if there was a system crash, rolling the data forward from the binary logs. Although any uncommitted transactions that were present at the time of the snapshot would be rolled back.

Be aware however this can potentially take some time…

Future releases of MySQL may take innodb filesystem snapshots into consideration to prevent the recovery step.

Keep watching http://forge.mysql.com/wiki/OnlineBackup as the API develops.

Rory Arms - April 4th, 2009 at 9:20 pm

6

A few years ago, I wrote a tool to be used from the system crontab(5), written in bourne shell, which only depends on mysql-client & mysql-server packges. It’s purpose is to easily generate periodic snapshots of /var (default location for mysql data) which are mounted. This mount point would then be available to an asynchronous backup process (such as rsync, tar, etc) which would perform the backup on that mounted snapshot. Perhaps someone reading, might find it useful: http://www.truestep.com/free_code/BSD/mysql_ufs_snapshot/