script to cycle through servers to retrieve 'mysql' schema/data
Here's a little script for the google cache and email archive engines to
store in case anyone ever needs this.
We have several servers (dev/test/prod) and replication setup too.
Sometimes we need to restore one server from another, however we have
different mysql user accounts setup on each for various web/scripts that
need varying levels of security.
This will cycle through each server and do a schema and data dump and
create a tarball for you. Useful for a weekly or monthly or even a
one-off.
Use as you like:
#!/bin/bash
# written by Daevid Vincent on 01/22/09
# This will harvest all the 'mysql' tables schema and data from each
$MYSQLRDBMS
# make sure we're running as root
if (( `/usr/bin/id -u` != 0 )); then { echo -e \e[00;31mSorry, must be
root. Exiting...\e[00m; exit; } fi
USERNAME=mysql_user_on_all_servers
PASSWORD=mysql_user_password_on_all_servers
# .01 Dev Master
# .02 Dev Slave
# .03 Test Master
# .04 Test Slave
# .05 Prod Master
# .06 Prod Slave
MYSQLRDBMS=01 02 03 04 05 06
MYSQLDUMPOPTS=--skip-opt --add-drop-table --add-drop-database
--add-locks --create-options --complete-insert --quote-names
--disable-keys --extended-insert --quick --set-charset --comments
BACKUPDIR=/tmp
echo
set $(date)
for ip in $MYSQLRDBMS; do
HOST=10.10.10.$ip
CONNECTION=-u $USERNAME -p$PASSWORD -h$HOST
echo -e \e[00;31m[DEBUG] `date`\e[00m
echo -e \e[01;37m[DEBUG] mysql $CONNECTION\e[00m
echo -e \e[01;32mHarvesting 'mysql' database on $HOST\e[00m
mysqldump $CONNECTION $MYSQLDUMPOPTS --databases mysql
/tmp/$6-$2-$3_mysql_$HOST.sql
echo -e \e[00;31m[DEBUG] `date`\e[00m
echo
done #hosts
echo -e \e[01;32mTarballing mysqldump of ALL databases to
$BACKUPDIR/$6-$2-$3_mysql_db.tgz\e[00m
/bin/tar czvpf
$BACKUPDIR/$6-$2-$3_mysql_db.tgz /tmp/$6-$2-$3_mysql_*.sql
rm -f /tmp/$6-$2-$3_mysql_*.sql
echo -e \e[00;31m[DEBUG] `date`\e[00m
echo -e \e[01;33mCompleted.\e[00m
How do you backup HUGE tables?
We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 This can take a good amount of time to copy even just the file, and a mysqldump can take hours to export and import took 3 days (with the usual intelligent optimizations, locks, inserts, keys, etc.). Yowza! So, it's not really feasible or ideal to do this daily. We'd like to do some kind of daily diff and weekly or monthly full backup. Also, as any good backup strategy involves off site backups, it's not very convenient or even feasible to transfer 80+ GB over our pipe ever day (which according to iperf is getting around 11 MBytes/sec from our data center to our office). Looking for ideas as to what others are doing to backup their very large sets? We do have a master/slave setup. We're thinking of adding two more slaves that are read-only and not accessed via the web at all. Just sits there being a backup effectively. One being offsite in another building and the logic that we'll trickle in maybe 100k per minute as the data is inserted into the real M/S so that should be negligible on our intra/internet. --- I've done some research here, but nothing stands out as the winner... but I'm open to any of these ideas if you can make a strong case for them. http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html InnoDB Hot Backup is an online backup tool you can use to backup your InnoDB database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free (commercial) add-on tool with an annual license fee per computer on which the MySQL server is run. http://www.innodb.com/hot-backup/ [not loving that it's a commercial tool] http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html http://dev.mysql.com/doc/refman/5.1/en/backup.html read the comments You can also take very fast online or hot backups if you have linux volume management or LVM ... I knew there was a way to use LVM for backups somehow. Maybe a solution for us? http://www.mysqlperformanceblog.com/?s=backup http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Some other backup solutions maybe: http://www.debianhelp.co.uk/mysqlscript.htm http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works so well, no need to improve it?) http://www.ozerov.de/bigdump.php
Re: Need to pivot rows into columns
Attila, I would like to select only the most recent targettime within 1 minute and only display only the rows that are the latest and print out all of the stats as columns on a per toolname basis: One way: a three-step: 1. There is a formula (see Group data by time periods at http://www.artfulsoftware.com/queries.php) for calculating periods that are evenly divisible into 60 mins: |((60/periodMinutes) * HOUR( timevalue ) + FLOOR( MINUTE( timevalue ) / periodMinutes )). |For your query, periodMinutes=1, so it simplifies to 60*HOUR(target_time) + FLOOR(MINUTE(target_time)). 2. Use an exclusion join (see Within-group aggregates at http://www.artfulsoftware.com/queries.php) to find the latest rows to the nearest minute (from the above formula) per scenario and toolname group: SELECT a.* FROM data a LEFT JOIN data b ON a.scenarioname=b.scenarioname AND a.toolname=b.toolname AND 60*HOUR(a.target_time)+FLOOR(MINUTE(a.target_time)) 60*HOUR(b.target_time)+FLOOR(MINUTE(b.target_time)) WHERE b.id IS NULL; 3. Use the above as the inner query for an outer query which pivots (see steps 2 and 3 in Group column statistics in rows at http://www.artfulsoftware.com/queries.php) the last two columns of the above result: SELECT target_time, scenarioname, toolname, SUM( CASE statname WHEN 'byte_count' THEN statvalue ELSE 0 END ) AS bytes, SUM( CASE statname WHEN 'udp_count' THEN statvalue ELSE 0 END ) AS udps, SUM( CASE statname WHEN 'tcp_count' THEN statvalue ELSE 0 END ) AS tcps FROM ( SELECT a.target_time,a.scenarioname,a.toolname,a.statname,a.statvalue FROM data a LEFT JOIN data b ON a.scenarioname=b.scenarioname AND a.toolname=b.toolname AND 60*HOUR(a.target_time)+FLOOR(MINUTE(a.target_time)) 60*HOUR(b.target_time)+FLOOR(MINUTE(b.target_time)) WHERE b.id IS NULL ) AS stats GROUP BY target_time,scenarioname,toolname; +-+--+--+---+--+--+ | target_time | scenarioname | toolname | bytes | udps | tcps | +-+--+--+---+--+--+ | 2009-01-21 19:34:00 | scenario1| tool1| 100 | 200 | 300 | | 2009-01-21 19:34:00 | scenario1| tool2| 400 | 500 | 600 | +-+--+--+---+--+--+ PB - Attila wrote: Hi, I have the following table: CREATE TABLE DATA ( TARGET_TIME datetime NOT NULL, SCENARIONAMEvarchar(20) NOT NULL, TOOLNAME varchar(20) NOT NULL, STATNAME varchar(100) NOT NULL, STATVALUE int(10) NOT NULL, PRIMARY KEY (TARGET_TIME, SCENARIONAME, SIMTOOLNAME, STATNAME) ); I am trying to collect statistics from running tools and feeding them into the DB. If I have the following entries: (2009-01-21 19:34:00, scenario1, tool1, byte_count, 100), (2009-01-21 19:34:00, scenario1, tool1, udp_count, 200), (2009-01-21 19:34:00, scenario1, tool1, tcp_count, 300), (2009-01-21 19:34:00, scenario1, tool2, byte_count, 400), (2009-01-21 19:34:00, scenario1, tool2, udp_count, 500), (2009-01-21 19:34:00, scenario1, tool2, tcp_count, 600), (2009-01-21 19:33:00, scenario1, tool1, byte_count, 10), (2009-01-21 19:33:00, scenario1, tool1, udp_count, 20), (2009-01-21 19:33:00, scenario1, tool1, tcp_count, 30), (2009-01-21 19:33:00, scenario1, tool2, byte_count, 40), (2009-01-21 19:33:00, scenario1, tool2, udp_count, 50), (2009-01-21 19:33:00, scenario1, tool2, tcp_count, 60) (Notice that the old targettime will be ignored in this particular query as we are only interested in the latest) I would like to select only the most recent targettime within 1 minute and only display only the rows that are the latest and print out all of the stats as columns on a per toolname basis: Targettime ScenarioToolname byte_count udp_count tcp_count 2009-01-21 19:34:00 scenario1 tool1 100 200 300 2009-01-21 19:34:00 scenario1 tool2 400 500 600 The purpose of the query is to display the latest statistics for each scenario/toolname group. It is important that the original data comes in rows (statname may not be known ahead of time, so it must be stored as a string in a field value). I am using MySQL and have found some possibilities with SQL Server (Pivot, crosstab, etc) ... but the requirements call for MySQL. Is the above query possible? Thanks a lot! No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 7:08 AM
Re: How do you backup HUGE tables?
On Fri, Jan 23, 2009 at 4:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 Daevid, we have started working on an incremental/differential InnoDB backup tool. It is in need of a sponsor though. I'm betting that you don't change all 70GB of that table every day, and you'd appreciate being able to keep differentials and only do full backups every so often. For big datasets like this, dump is impossible or too expensive at some point. There are a lot of ways you could do this, but I'd recommend filesystem snapshots and binary copies. Unless you like long dumps and long restores... There might also be some higher-level strategies like archiving and purging or aggregation that would benefit you. These are the kinds of things I see pretty often and help people select good strategies, but it requires a lot of knowledge of your application to give good advice. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: crosstab with percent change between years
Hi Baron, Thanks for your message. After endless nights trying I could not get it to work, so I finally created a temporary table and run a second query to get the percentages. It works fine for now, but I wonder if it will take too long once there are thousands of records. Is there an alternative way I should look into? Thanks for your help! Eudald On Jan 11, 2009, at 7:15 PM, Baron Schwartz wrote: eud...@digitalecologies.com
Re: How do you backup HUGE tables?
I would also suggest to use the innodb storage option 'innodb-file-per-table=ON' so that at least the datafile is split to have as many (smaller) datafiles as innodb tables. This could make it easier to deal with the whole database. Cheers Claudio Baron Schwartz wrote: On Fri, Jan 23, 2009 at 4:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 Daevid, we have started working on an incremental/differential InnoDB backup tool. It is in need of a sponsor though. I'm betting that you don't change all 70GB of that table every day, and you'd appreciate being able to keep differentials and only do full backups every so often. For big datasets like this, dump is impossible or too expensive at some point. There are a lot of ways you could do this, but I'd recommend filesystem snapshots and binary copies. Unless you like long dumps and long restores... There might also be some higher-level strategies like archiving and purging or aggregation that would benefit you. These are the kinds of things I see pretty often and help people select good strategies, but it requires a lot of knowledge of your application to give good advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do you backup HUGE tables?
Something totally ghetto that might work... If you could convert the files to appear to be text with some kind of reversible fast translation, rsync might be able to handle the diff part. You'd sure want to test this out thoroughly... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How do you backup HUGE tables?
We have a very large, multi-terabyte database with individual tables that are over 100Gig. We have it on a Red Hat Linux system and we set up logical volumes, take LVM snapshots, then use rsync to move the data over. This works well and is a lot faster than dumping and certainly restore is faster. On Fri, Jan 23, 2009 at 3:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 This can take a good amount of time to copy even just the file, and a mysqldump can take hours to export and import took 3 days (with the usual intelligent optimizations, locks, inserts, keys, etc.). Yowza! So, it's not really feasible or ideal to do this daily. We'd like to do some kind of daily diff and weekly or monthly full backup. Also, as any good backup strategy involves off site backups, it's not very convenient or even feasible to transfer 80+ GB over our pipe ever day (which according to iperf is getting around 11 MBytes/sec from our data center to our office). Looking for ideas as to what others are doing to backup their very large sets? We do have a master/slave setup. We're thinking of adding two more slaves that are read-only and not accessed via the web at all. Just sits there being a backup effectively. One being offsite in another building and the logic that we'll trickle in maybe 100k per minute as the data is inserted into the real M/S so that should be negligible on our intra/internet. --- I've done some research here, but nothing stands out as the winner... but I'm open to any of these ideas if you can make a strong case for them. http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html InnoDB Hot Backup is an online backup tool you can use to backup your InnoDB database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free (commercial) add-on tool with an annual license fee per computer on which the MySQL server is run. http://www.innodb.com/hot-backup/ [not loving that it's a commercial tool] http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html http://dev.mysql.com/doc/refman/5.1/en/backup.html read the comments You can also take very fast online or hot backups if you have linux volume management or LVM ... I knew there was a way to use LVM for backups somehow. Maybe a solution for us? http://www.mysqlperformanceblog.com/?s=backup http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Some other backup solutions maybe: http://www.debianhelp.co.uk/mysqlscript.htm http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works so well, no need to improve it?) http://www.ozerov.de/bigdump.php -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: How do you backup HUGE tables?
I know how you feel! I think your two best options are these: 1.) Use LVM snapshots per the MPB links you mentioned as a guide. Your incremental backup would be the binary logs that MySQL writes. You could copy any of this data off site by mounting the snapshots and using your remote copy mechanizm of choice. 2.) Create a slave and create your backups from it, again using the binary logs as the incremental backup. You could also use the snapshot technique to create the initial data set for this host if you're not able to take this host down for an extended period of time. Claudio has an excellent point with innodb-file-per-table as well. Just make sure a single table will never grow to more than the maximum file size of your filesystem. Good luck, -Aaron On Fri, Jan 23, 2009 at 1:18 PM, Daevid Vincent dae...@daevid.com wrote: We have some INNODB tables that are over 500,000,000 rows. These obviously make for some mighty big file sizes: -rw-rw 1 mysql mysql 73,872,179,200 2009-01-22 02:31 ibdata1 This can take a good amount of time to copy even just the file, and a mysqldump can take hours to export and import took 3 days (with the usual intelligent optimizations, locks, inserts, keys, etc.). Yowza! So, it's not really feasible or ideal to do this daily. We'd like to do some kind of daily diff and weekly or monthly full backup. Also, as any good backup strategy involves off site backups, it's not very convenient or even feasible to transfer 80+ GB over our pipe ever day (which according to iperf is getting around 11 MBytes/sec from our data center to our office). Looking for ideas as to what others are doing to backup their very large sets? We do have a master/slave setup. We're thinking of adding two more slaves that are read-only and not accessed via the web at all. Just sits there being a backup effectively. One being offsite in another building and the logic that we'll trickle in maybe 100k per minute as the data is inserted into the real M/S so that should be negligible on our intra/internet. --- I've done some research here, but nothing stands out as the winner... but I'm open to any of these ideas if you can make a strong case for them. http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html InnoDB Hot Backup is an online backup tool you can use to backup your InnoDB database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free (commercial) add-on tool with an annual license fee per computer on which the MySQL server is run. http://www.innodb.com/hot-backup/ [not loving that it's a commercial tool] http://dev.mysql.com/doc/refman/5.0/en/replication-implementation-details.html http://dev.mysql.com/doc/refman/5.1/en/backup.html read the comments You can also take very fast online or hot backups if you have linux volume management or LVM ... I knew there was a way to use LVM for backups somehow. Maybe a solution for us? http://www.mysqlperformanceblog.com/?s=backup http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/ Some other backup solutions maybe: http://www.debianhelp.co.uk/mysqlscript.htm http://mysqlbackup.phpmagazine.net/ (seems dead, but maybe it just works so well, no need to improve it?) http://www.ozerov.de/bigdump.php
