backups on SWiK

Parallel mysqldump backup script available. Testers wanted.

Tue, 07 Oct 2008 11:57:28 -0700

Large databases, long mysqldump times, long waits for globally locked tables. These problems basically never go away when you rely on mysqldump with –all-databases or a list of databases, as it dumps schemas serially. I’m not going to explain serial vs parallel processing here since that’s a larger topic. Suffice to say that in these days of multi-core / multi-cpu servers we only make use of one processor’s core when we serially export databases using mysqldump. So, I have a new script that attempts to alleviate those issues and now I need testers to provide feedback/improvements.

In order to keep some sanity when dealing with hundreds of database servers, the script takes care of the following:

low global locking time requirements: solved by parallel tasks / forked processes
backup file checking: with mysqldump files; it checks for “–Dump completed” at the end of the sql file
ability to backup all-databases or an array of databases
if backing up all databases, the ability to skip schema names like information_schema, lost&found, test, etc that can be in the data directory but aren’t really schemas
compression of sql files
size reporting of plain text and compressed sql files
overall time and single export elapsed time reporting for each schema backup
debug level logging of sub routines for troubleshooting if needed
file pruning to keep 28 days of backups, so you don’t have to manually remove files to keep the backups partition from filling up
talkback reporting for Monolith monitoring system / email reporting as well
logs to /var/log/messages with final report so we get information in network based syslog collection
process state exit code reporting in the case of various issues like “mysqldump failed because binlog not enabled when using –master-data=x”

So, that all said, if you want to help test this script email me at ” the matt reid at g m a i l . c o m”

It’s a bit over 800 lines of perl so I’m not going to paste it here, therefore email me

Amanda Open Source Backup

Sun, 05 Oct 2008 02:45:40 -0700

iPhone and iPod touch: About backups

Tue, 30 Sep 2008 04:20:07 -0700

eigenclass - A better backup system based on Git

Mon, 29 Sep 2008 06:18:13 -0700

git-home-history(1)

Mon, 29 Sep 2008 06:18:13 -0700

Configuring Postfix as backup MX host

Fri, 26 Sep 2008 10:08:22 -0700

Joomla! Extensions Directory - JoomlaPack - AJAX powered backup and restore

Thu, 25 Sep 2008 03:14:16 -0700

ZetaBack ZFS backup and recovery tool - Trac

Wed, 24 Sep 2008 18:14:21 -0700

Zetaback. Respect. - Esoteric Curio

Wed, 24 Sep 2008 18:14:21 -0700

rdiff-backup-web - A web GUI for rdiff-backup

Thu, 18 Sep 2008 08:03:54 -0700

Automated Backups With rdiff-backup | HowtoForge - Linux Howtos and Tutorials

Thu, 18 Sep 2008 08:03:54 -0700

New innobackup feature: --slave-info

Wed, 17 Sep 2008 10:05:17 -0700

To have online backups of MySQL, We recently bought a license for InnoBase/Oracle's InnoDB Hot Backup Tool, ibbackup. This tool, used in conjunction with innobackup, has worked great in creating a nightly backup, with no downtime during the backup. Not even nagios messages!

I run innobackup/ibbackup on one of our slaves (well, it's also a dual master, but not used by apps). innobackup produces a backup in a directory that I specified, and when run results in a time-stamped directory, as show below:


ls -l 2008-09-17_03-00-03/
total 276272
-rw-r--r-- 1 root root       349 2008-09-17 03:00 backup-my.cnf
drwxr-x--- 2 root root      4096 2008-09-17 03:55 grazr
-rw-r--r-- 1 root root        27 2008-09-17 03:55 ibbackup_binlog_info
-rw-r----- 1 root root 186109952 2008-09-17 03:55 ibbackup_logfile
-rw-r----- 1 root root  10485760 2008-09-17 03:00 ibdata1
-rw-r----- 1 root root  85983232 2008-09-17 03:00 ibdata2
drwxr-xr-x 2 root root      4096 2008-09-17 03:55 mysql
-rw-r--r-- 1 root root         0 2008-09-17 03:55 mysql-stderr
-rw-r--r-- 1 root root      1326 2008-09-17 03:56 mysql-stdout
drwxr-xr-x 2 root root      4096 2008-09-17 03:55 test

These are all files needed in the datadir, and once you run innobackup with --apply-log, it applies the transaction log, and you can run off of this directory in the event of data loss. (ehem, some Denver newspaper...) Also, you can simply copy this directory to any new db you're building and have an instant slave.

innobackup also provides you with the binary log settings of the server the backup was created on (by running SHOW MASTER STATUS), which is great because you can apply them and have your server for an incremental backup to current time. The file in the listing about is:

ibbackup_binlog_info

It looks like this:

bin.002934 22485369 grazr

What is *doesn't* give you is the binary log file name and position of the slave's master, if you are running the backup on a slave. I like having this information because then I can take the backup, put it on a new MySQL slave, then start replication reading from the same master the server the backup was created on, along with binlog name and position of that master, and let replication update that server.

Since innobackup doesn't provide this, what I've done is to manually look at the master's binlog starting from the time of day that I perform the dump, get a binlog position, and use that as my starting point. This is a bit rough, and may not give you the exact starting point you want from that master.

So what I decided to do was to add functionality to get this information at the same time the server's own binlogs are read, to also perform a SHOW SLAVE STATUS to also get the server's master's binlog name and position.

This option is called --slave-info. It just tells innobackup to obtain this information when it obtains its own binlog position/name. It writes this info into a new file:

ibbackup_slave_info

And I decided to make it even more convenient for use:

CHANGE MASTER TO MASTER_LOG_FILE='bin.000305', MASTER_LOG_POS=133418571

In effect, you now get a backup that you can simply run this master position change and and let replication take care of getting the server to catch up!

So, I make this patch available at:

http://www.patg.net/downloads/innobackup.patch

I would make the whole script available, but not sure if that's ok with InnoBase folk (it is GPL, but I want to check with them)

flyback - Google Code

Mon, 15 Sep 2008 13:05:28 -0700

[iPhone] FIX the long backups, not disable them. Solution is here!

Sat, 06 Sep 2008 03:07:04 -0700

BigAdmin Submitted Tech Tip: Integrating Symantec Veritas NetBackup With ZFS Snapshots in the Solaris 10 OS

Thu, 04 Sep 2008 13:58:10 -0700

Geek to Live: Back up Gmail with fetchmail

Tue, 02 Sep 2008 06:52:47 -0700

Hardware compression - The Open Source Backup Wiki (Amanda, MySQL Backup)

Thu, 28 Aug 2008 17:54:55 -0700

Practicing Safe Emacs | blog.arithm

Mon, 18 Aug 2008 22:16:29 -0700

The Blog of Ben Rockwood

Wed, 13 Aug 2008 16:05:47 -0700

Optimal remote backups with rsync over Samba

Sat, 09 Aug 2008 16:08:12 -0700

Geek to Live: Back up Gmail with fetchmail

Thu, 07 Aug 2008 13:04:22 -0700

Chapter 1 Rough Draft Complete

Sun, 27 Jul 2008 14:56:11 -0700

I have completed a rough draft of the first chapter of "Drupal Performance and Scalability". The first chapter of this online book is divided into four sections, the first of which focuses on the importance of fully defining your performance and scalability goals, helping you to identify what you need to accomplish and how to set concrete and attainable goals. The second section discusses monitoring and measuring your ongoing progress, helping you decide what you need to monitor, and how to monitor it. The third section stresses the importance of making regular backups, discussing what needs to be backed up, and offering example scripts for backing up your entire website, including the database. Finally, the fourth section takes an in depth look at using revision control tools to manage your website, providing useful recipes showing how Git can track changes to your website, helping you update to new releases and push those updates into production.

It is important to realize that this is a rough draft, and as such it may contain spelling or grammatical errors, it may be missing key points, and the writing style may not be very polished. However, the book has to start somewhere, and this is the first step toward the end goal of publishing a useful and freely available online resource. I welcome all criticisms, suggestions and feedback. If you find errors in the text or have specific comments, you can help with this writing project by posting your feedback on the appropriate page. The current status of this project is tracked here.

Comparison - Monolith vs. MySQL Enterprise Dashboard

Thu, 24 Jul 2008 13:53:04 -0700

I’m attempting this is be as unbiased as possible, since I write the Monolith application. This will hopefully help one decide between a free MySQL server monitoring system and paying for a per-server based licensed product. Both have strengths and weaknesses that should be pointed out before making a decision. You can infer the weaknesses based on the strengths below. That said, let’s just get into it.

Strengths of each product over the other

Monolith - MySQL DBA Console

presents overall database size, index size, data size, number of schema per server, and aggregate statistics for all monitored servers
runs mysql server backups remotely and reports on backup state execution
collects cnf files from each server during the talkback script execution for historical viewing
built on the LAMP stack, no need for tomcat/jboss knowlege
provides overall server report, and change control documentation with recommended actions for tuning the server(s)
application can run under Redhat Cluster Services for high availability
comes with XML export API for off-application data processing and trending
compatible with Visual Mining
is FREE software, GPL licensed, costs absolutely nothing to the user

MySQL Enterprise Dashboard

customizable alerting instead of fixed alerts
fine tuned date range graphing (from date to date instead of last X days)
support for bug tracking that utilizes hooks into bugs.mysql.com
uses local server agent for information gathering, allows CPU and OS memory to be reported on
presentation design is arguably more easy on the eyes
comes with the MySQL Enterprise Server software, and thus a support contract from MySQL/Sun.
customizable organization of server list instead of organization by client name

Feel free to post your viewpoints on each if you have used both products. I’m always interested in what users have to say.

You can get Monolith here: http://sourceforge.net/projects/monolith-mysql

You can get MySQL Enterprise here: http://www.mysql.com/products/enterprise/

Clonezilla

Wed, 16 Jul 2008 12:51:33 -0700

Automated Backups With rdiff-backup | HowtoForge - Linux Howtos and Tutorials

Wed, 09 Jul 2008 14:49:23 -0700

Bacula, the Network Backup Tool for Linux, Unix, and Windows

Wed, 09 Jul 2008 14:42:55 -0700

Clonezilla

Wed, 09 Jul 2008 12:51:10 -0700

Info Node: (emacs)Backup

Mon, 07 Jul 2008 00:47:48 -0700

[from martti] Backing up Large Files

Fri, 04 Jul 2008 05:49:13 -0700

Abnrowesable online backups storage

Wed, 02 Jul 2008 03:48:11 -0700