- MySQL Copy Data from Live Table: A Helper Script
- Published: 2009-08-14 10:20:50
- Updated: 2009-08-14 10:22:11
- Language: Bash
- Author: jonesy
- Description:
This is a script that can help you in a situation where a production database cannot be made unavailable for the amount of time it would take to copy data to a new table with a different indexing scheme, and indexing the production table directly would lock the table for way too long.
This script copies rows in small "nibbles" from the production table to an empty table you create with the same column defs, but different indexes (or, that was the reason I created it, you might use it for other purposes). Between copies, it checks the load average to insure you're not killing the server.
The variables at the top of the script are pretty self-explanatory, except to note that there are separate NEWDB and OLDDB variables in case your new table also lives in a new database. The INCREMENT is the number of rows you want to copy over at a time. If you set INCREMENT to 1000, it’ll copy 1000 rows, check the load average, and if it’s under MAXLOAD, it’ll copy over another 1000 rows. It also keeps track of the number of rows in each database as it goes, since writes are still happening on the bad table while this is going on.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | #!/bin/bash
###
### Written by Brian K. Jones (bkjones@gmail.com)
###
### Takes an increment, old db, and new db, and copies rows from olddb to newdb.
### Along the way, it'll check system load and sleep if it's too high.
### There's too much hard-coding right now, but it's a useful template, and
### has been tested. The script takes no CLI arguments.
###
INCR=10000
NEWDB=shiny
OLDDB=busted
OLDTABLE=bad
NEWTABLE=good
MAXLOAD=3
DBUSER=mydbuser
DBPASS=mydbpass
rows_old=`mysql -N -D ${OLDDB} -u ${DBUSER} -p${DBPASS} -e "select count(*) from ${OLDTABLE}"`
echo "rows_old is now ${rows_old}"
rows_new=`mysql -N -D ${NEWDB} -u ${DBUSER} -p${DBPASS} -e "select count(*) from ${NEWTABLE}"` ## num. rows in new table
echo "rows_new is now ${rows_new}"
for (( rows_new=$rows_new; rows_new < $rows_old; rows_new+=$INCR )); do
if [ $((rows_old - (rows_new + INCR))) -gt 0 ]; then ## Check to see if there are at least $INCR rows left to copy over.
mysql -N -D ${NEWDB} -u ${DBUSER} -p${DBPASS} -e "INSERT INTO ${NEWTABLE} SELECT * FROM ${OLDDB}.${OLDTABLE} LIMIT ${rows_new},${INCR}"
while [ "`awk -v max=${MAXLOAD} '$1 > max {print "TRUE"}' /proc/loadavg`" = "TRUE" ]; do
echo "sleeping due to load > ${MAXLOAD}"
sleep 30
done
# we update rows_old because it'll be growing while this script runs.
rows_old=`mysql -N -D ${OLDDB} -u ${DBUSER} -p${DBPASS} -e "select count(*) from ${OLDTABLE}" `
rows_new=`mysql -N -D ${NEWDB} -u ${DBUSER} -p${DBPASS} -e"select count(*) from ${NEWTABLE}"`
time=`date +%R`
echo "${time} -- rows_new = ${rows_new}, rows_old = ${rows_old}"
else ## There are < $INCR rows left. Select remaining rows.
remaining=$((rows_old - rows_new))
mysql -N -D ${NEWDB} -u ${DBUSER} -p${DBPASS} -e "INSERT INTO ${NEWTABLE} SELECT * FROM ${OLDDB}.${OLDTABLE} LIMIT ${rows_new},${remaining}"
echo "All done!"
exit
fi
done
|