So, as I mentioned a little while back, I've been playing around with
snapshot-based backups of MySQL. The key to success is knowing how to
properly quiesce InnoDB tables. I still have a bunch of consistency
testing to do, but it's looking very promising indeed. It should work
on any underlying filesystem architecture that has the capability to
take snapshots and mount them at a different location, even read-only.
This inxludes ZFS, 3PAR, should include NetApp, should include btrfs
when it's ready for production.
This is the basic pseudocode:
Acquire up-front data:
filesystem holding DBs
filesystem holding logs, if different
MySQL access access credentials
SET AUTOCOMMIT=0;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
check that status is good
execute snapshot
UNLOCK TABLES;
check that snapshot worked
IF shapshot succeeded
clone snapshot
mount snapshot
report success back to bacula
ELSE
report failure
After completion of backup, delete clone and snapshot
I have 6.1GB of MySQL DBs on my main server. Here's the output from the
initial full backup:
07-Oct 19:06 babylon4-dir JobId 3326: shell command: run BeforeJob
"/opt/bacula/etc/make_mysql_snapshot"
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Initiating MYSQL
database snapshot:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Creating snapshot...
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: All specified ZFS
filesystems are present.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: SnapBack is connecting
to MySQL:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Connected to
10.24.32.14.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Prepared MySQL
to ensure InnoDB consistency.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: SnapBack is commencing
snapshot run:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Locking tables
(if this is taking awhile please check the MySQL process list).
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Tables locked.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Master status
retrieved.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Snapping ZFS DB
filesystem.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Unlocking tables.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Tables unlocked.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: SnapBack snapshot run
completed.
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Snapshots created:
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob:
rpool/my...@20101007190658
07-Oct 19:06 babylon4-dir JobId 3326: BeforeJob: Cloning snapshot...
07-Oct 19:07 babylon4-dir JobId 3326: BeforeJob: Clone ready for backup.
07-Oct 19:07 babylon4-dir JobId 3326: shell command: run BeforeJob
"/opt/bacula/sbin/checkhost babylon4"
07-Oct 19:07 babylon4-dir JobId 3326: Start Backup JobId 3326,
Job=MySQL_Backup.2010-10-07_19.06.56_10
07-Oct 19:07 babylon4-dir JobId 3326: Using Device "FileStorage"
07-Oct 19:07 babylon4-sd JobId 3326: Volume "FULL-20101007-08:34"
previously written, moving to end of data.
07-Oct 19:07 babylon4-sd JobId 3326: Ready to append to end of Volume
"FULL-20101007-08:34" size=5932859338
07-Oct 19:16 babylon4-sd JobId 3326: Job write elapsed time = 00:09:05,
Transfer rate = 12.05 M Bytes/second
07-Oct 19:16 babylon4-dir JobId 3326: Bacula babylon4-dir 5.0.3
(04Aug10): 07-Oct-2010 19:16:10
Build OS: i386-pc-solaris2.10 solaris 5.10
JobId: 3326
Job: MySQL_Backup.2010-10-07_19.06.56_10
Backup Level: Full
Client: "babylon4" 5.0.3 (04Aug10)
i386-pc-solaris2.10,solaris,5.10
FileSet: "Catalog Snapshot" 2010-10-07 18:45:55
Pool: "Full-Disk" (From Job FullPool override)
Catalog: "Catalog" (From Client resource)
Storage: "babylon4-sd" (From Pool resource)
Scheduled time: 07-Oct-2010 19:06:49
Start time: 07-Oct-2010 19:07:00
End time: 07-Oct-2010 19:16:10
Elapsed time: 9 mins 10 secs
Priority: 10
FD Files Written: 672
SD Files Written: 672
FD Bytes Written: 6,571,658,030 (6.571 GB)
SD Bytes Written: 6,571,742,016 (6.571 GB)
Rate: 11948.5 KB/s
Software Compression: None
VSS: no
Encryption: no
Accurate: no
Volume name(s): FULL-20101007-08:34
Volume Session Id: 1
Volume Session Time: 1286488706
Last Volume Bytes: 12,509,496,538 (12.50 GB)
Non-fatal FD errors: 0
SD Errors: 0
FD termination status: OK
SD termination status: OK
Termination: Backup OK
07-Oct 19:16 babylon4-dir JobId 3326: shell command: run AfterJob
"/opt/bacula/etc/delete_mysql_snapshot"
07-Oct 19:16 babylon4-dir JobId 3326: AfterJob: Cleaning up MYSQL
database snapshot:
07-Oct 19:16 babylon4-dir JobId 3326: AfterJob: Destroying clone...
07-Oct 19:16 babylon4-dir JobId 3326: AfterJob: Finding source snapshot...
07-Oct 19:16 babylon4-dir JobId 3326: AfterJob: Destroying snapshot...
07-Oct 19:16 babylon4-dir JobId 3326: AfterJob: Done.
As you can see, total elapsed time to back up all the DBs was 9 minutes
10 seconds. Here's last night's incremental:
08-Oct 04:57 babylon4-dir JobId 3335: shell command: run BeforeJob
"/opt/bacula/etc/make_mysql_snapshot"
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Initiating MYSQL
database snapshot:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Creating snapshot...
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: All specified ZFS
filesystems are present.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: SnapBack is connecting
to MySQL:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Connected to
10.24.32.14.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Prepared MySQL
to ensure InnoDB consistency.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: SnapBack is commencing
snapshot run:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Locking tables
(if this is taking awhile please check the MySQL process list).
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Tables locked.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Master status
retrieved.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Snapping ZFS DB
filesystem.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Unlocking tables.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Tables unlocked.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: SnapBack snapshot run
completed.
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Snapshots created:
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob:
rpool/my...@20101008045741
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Cloning snapshot...
08-Oct 04:57 babylon4-dir JobId 3335: BeforeJob: Clone ready for backup.
08-Oct 04:57 babylon4-dir JobId 3335: shell command: run BeforeJob
"/opt/bacula/sbin/checkhost babylon4"
08-Oct 04:57 babylon4-dir JobId 3335: Start Backup JobId 3335,
Job=MySQL_Backup.2010-10-08_04.55.00_19
08-Oct 04:57 babylon4-dir JobId 3335: Using Device "FileStorage"
08-Oct 04:57 babylon4-sd JobId 3335: Volume "INCR-20101008-04:30"
previously written, moving to end of data.
08-Oct 04:57 babylon4-sd JobId 3335: Ready to append to end of Volume
"INCR-20101008-04:30" size=4393754579
08-Oct 05:03 babylon4-sd JobId 3335: Job write elapsed time = 00:05:29,
Transfer rate = 10.21 M Bytes/second
08-Oct 05:03 babylon4-dir JobId 3335: Bacula babylon4-dir 5.0.3
(04Aug10): 08-Oct-2010 05:03:14
Build OS: i386-pc-solaris2.10 solaris 5.10
JobId: 3335
Job: MySQL_Backup.2010-10-08_04.55.00_19
Backup Level: Incremental, since=2010-10-07 19:07:00
Client: "babylon4" 5.0.3 (04Aug10)
i386-pc-solaris2.10,solaris,5.10
FileSet: "Catalog Snapshot" 2010-10-07 18:45:55
Pool: "Incr-Disk" (From Job IncPool override)
Catalog: "Catalog" (From Client resource)
Storage: "babylon4-sd" (From Pool resource)
Scheduled time: 08-Oct-2010 04:55:00
Start time: 08-Oct-2010 04:57:44
End time: 08-Oct-2010 05:03:14
Elapsed time: 5 mins 30 secs
Priority: 15
FD Files Written: 11
SD Files Written: 11
FD Bytes Written: 3,359,907,916 (3.359 GB)
SD Bytes Written: 3,359,909,204 (3.359 GB)
Rate: 10181.5 KB/s
Software Compression: None
VSS: no
Encryption: no
Accurate: no
Volume name(s): INCR-20101008-04:30
Volume Session Id: 10
Volume Session Time: 1286488706
Last Volume Bytes: 7,756,155,955 (7.756 GB)
Non-fatal FD errors: 0
SD Errors: 0
FD termination status: OK
SD termination status: OK
Termination: Backup OK
08-Oct 05:03 babylon4-dir JobId 3335: shell command: run AfterJob
"/opt/bacula/etc/delete_mysql_snapshot"
08-Oct 05:03 babylon4-dir JobId 3335: AfterJob: Cleaning up MYSQL
database snapshot:
08-Oct 05:03 babylon4-dir JobId 3335: AfterJob: Destroying clone...
08-Oct 05:03 babylon4-dir JobId 3335: AfterJob: Finding source snapshot...
08-Oct 05:03 babylon4-dir JobId 3335: AfterJob: Destroying snapshot...
08-Oct 05:03 babylon4-dir JobId 3335: AfterJob: Done.
Notice that we got a TRUE incremental, which we do NOT get via the
mysqldump method. mysqldump dumps EVERYTHING; this backs up only tables
that changed.
The output here doesn't show the fine details of the snapshot process,
but detailed output shows that the total tables-locked time for both of
these is about 1.5 seconds. I still have consistency testing to do, but
this looks very promising.
--
Phil Stracchino, CDK#2 DoD#299792458 ICBM: 43.5607, -71.355
[email protected] [email protected] [email protected]
Renaissance Man, Unix ronin, Perl hacker, Free Stater
It's not the years, it's the mileage.
------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today.
http://p.sf.net/sfu/beautyoftheweb
_______________________________________________
Bacula-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
