Easy MySQL: how to backup databases to a remote machine

2010/08/13 by admin in Backups, MySQL Server, Scripts | 6 Comments

Here’s a simple answer to a simple question. “How do I run a backup of MySQL to another machine without writing to the local server’s filesystem?” – this is especially useful if you are running out of space on the local server and cannot write a temporary file to the filesystem during backups.

Method one – this writes a remote file.
mysqldump [options] [db_name|--all-databases]| gzip -c | ssh user@host.com "cat > /path/to/new/file.sql.gz"

Method two – this writes directly into a remote mysql server
mysqldump [options] [db_name|--all-databases]| mysql --host=[remote host] –user=root –password=[pass] [db_name]

Read More

Monolith DBA Toolkit 0.4.3 Released

2008/12/17 by admin in Backups, Monolith Toolkit, Scripts | Leave a comment

The Monolith Toolkit of scripts for DBA routines. 0.4.3 has been released. You can download it here: http://code.google.com/p/monolith-toolkit/

Some information on the toolkit and what it contains:

  • mt-backup-parallel -> runs mysql backups in parallel super fast, has lots of reporting features
  • mt-check-replication -> script to report on replication status for slave servers
  • mt-rhcluster-check-filesystems -> reports on redhat cluster filesystems (for mysql active/passive clustering)
  • mt-rhcluster-script-wrapper -> wrapper script for running any of these scripts on a redhat cluster, chooses the active node to run the script on
  • mt-connections-log -> logs connections to mysql to disk, reports on threshold overages
  • mt-flush-tables-sequence -> runs through schema.tables to flush in sequence before global flush
  • mt-rotate-logs -> rotates general and slow query logs on periodic basis
  • mt-my.cnf_xG -> MySQL configuration file for a particular RAM size installation

Changes for this release:

  • Fixed the cnf files error that specified G instead of M for tmp table size and max_heap_table_size.
  • Fixed the parallel backup script pruning command. Uses system rm instead of UNLINK.
  • Fixed the default number of forks to create for parallel backup so we don’t overload the server.
Read More

Parallel mysqldump backup script available. Testers wanted.

2008/10/07 by admin in Backups, Monolith, Scripts | 1 Comment

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:

  1. low global locking time requirements: solved by parallel tasks / forked processes
  2. backup file checking: with mysqldump files; it checks for “–Dump completed” at the end of the sql file
  3. ability to backup all-databases or an array of databases
  4. 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
  5. compression of sql files
  6. size reporting of plain text and compressed sql files
  7. overall time and single export elapsed time reporting for each schema backup
  8. debug level logging of sub routines for troubleshooting if needed
  9. 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
  10. talkback reporting for Monolith monitoring system / email reporting as well
  11. logs to /var/log/messages with final report so we get information in network based syslog collection
  12. 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 :)

Read More

“Dump Completed” on mysqldump

2008/03/06 by admin in Backups | Leave a comment

If you’re verifying successful backups by using the last line of a mysqldump sql file that says “dump completed” – be aware that prior to version 5.0.27 mysqldump does not output that line. Confusion could ensue!

Read More

Rotating General Query & Slow Logs

2008/02/05 by admin in Backups, Scripts | 4 Comments

Sometimes you need to have the general query log on and even though it causes more disk I/O than you may want, it’s good for troubleshooting. This log can and probably will fill up your disks rather quickly. Then there’s the slow query log – setting log_slow_queries and log_queries_not_using_indexes will write out the queries that take longer than long_query_time to execute, as well as any query not using an index.

So, since MySQL does not apply the expire_logs_days value to these logs – only to the binary log (log_bin), we need another solution. There are probably a bunch of custom scripts out there that do this, but big surprise – we have one as well. This was originally written by Jim Wood until I got my hands on it and made some changes. The changes are listed in the head of the script. This little guy will rotate the logs out to another directory and gzip them. Ideally you’ll run this from cron as such:

#mysql rotate logs
01 * * * * root /usr/local/bin/mysql_rotatelogs > /dev/null 2>&1

The script can be downloaded here.

Read More