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

Updated: Monolith Toolkit – MySQL DBA tools 0.4.2

2008/10/14 by admin in Monolith Toolkit | Leave a comment

Updated the release, sure it’s only been a matter of hours but I added the standard cnf files for 2,4,8 and 16GB server installations. Also added the rhcluster-wrapper script that can be used to run just about anything on the active database node when using mysql in an active/passive setup.

Download here:  http://code.google.com/p/monolith-toolkit/

Read More

Monolith Toolkit – MySQL DBA tools released!

2008/10/14 by admin in Monolith Toolkit, Scripts | Leave a comment

Today I decided to package all of my various scripts together into a useful toolkit. Some are perl, some are shell script. These scripts are, in general, one off scripts that I wrote in order to get things done that weren’t available at the time. Now they’re nicely organized and will receive updates at the google code repo.

So far the toolkit includes the following scripts:

  • mt-backup-parallel -> the parallel backup script I wrote about in my last post
  • mt-check-replication -> script to report on replication status for slave servers
  • mt-check-rhcluster-filesystems -> reports on redhat cluster filesystems (for mysql active/passive clustering)
  • 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

To download go here: http://code.google.com/p/monolith-toolkit/

Note: these are not associated (yet) with the main Monolith project that is for monitoring. Although it will be soon :)

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

Making Monolith scripts RH Cluster aware

2008/03/14 by admin in Monolith | 1 Comment

If you have Monolith installed in a RedHat cluster environment and are wondering how to make the cron job scripts only execute on the active cluster node, this script is a working example. It needs to be installed and running on each cluster node. The only variable to change is “MYHOST” depending on which node the script is running on. This analyzes the output of /usr/sbin/clustat for active node status and service type.

Feel free to use this as a starting point for other RH cluster aware wrappers.

Here are the associated crontab entries for the monolith cluster aware wrappers.

# Monolith Monitor Wrapper Scripts
02 * * * * root /usr/local/bin/monolith_cluster_report-generator.sh > /dev/null 2>&1
*/15 * * * * root /usr/local/bin/monolith_cluster_agent.sh > /dev/null 2>&1
02 * * * * root /usr/local/bin/monolith_cluster_cronexec.sh > /dev/null 2>&1
10 * * * * root /etc/init.d/crond restart > /dev/null 2>&1

And the actual wrapper:

#!/bin/sh
#mysql_service monolith-node01p started
MYHOST="monolith-node01p" #replace this hostname with whatever the hostname is of the node this script goes on
HOST_STATE=`clustat | tail -n 1 | awk {'print $2'}`
SERV_STATE=`clustat | tail -n 1 | awk {'print $3'}`
DATE=`date +'%c'`
LOG="/var/log/monolith_cluster_cronexec.log"
echo "DATE: $DATE"
echo "HOST_STATE: $HOST_STATE"
echo "SERV_STATE: $SERV_STATE"
if [ "$HOST_STATE" = "$MYHOST" ]; then
if [ "$SERV_STATE" = "started" ]; then
echo "Running cronexec..."
/var/www/html/monolith/cron/readcron.sh /var/www/html/monolith/cron root
echo "$DATE - mysql_service $SERV_STATE on $HOST_STATE :: Running Monolith Cronexec" >> $LOG
else
/bin/rm -f /etc/cron.d/monolith
echo "$DATE - mysql_service $SERV_STATE on $HOST_STATE :: Service not started, removing /etc/cron.d/monolith" >> $LOG
/etc/init.d/crond restart
fi
else
/bin/rm -f /etc/cron.d/monolith
echo "$DATE - mysql_service $SERV_STATE on $HOST_STATE :: Not active node, removing /etc/cron.d/monolith" >> $LOG
/etc/init.d/crond restart
fi
echo "DONE"

Read More