christo's Nifty table repair script -

chris (2003-02-20 14:57:24)
2343 views
0 replies
#!/bin/bash


#$Id: history-table-repair-utility,v 1.1 2003/02/20 14:20:17 chris Exp $
# repair history tables offline, capturing new data into an empty history table replica,
# then mysql it back in once the new tables are in place

# shutdown the db server
/usr/local/mysql/bin/mysqladmin -pxxxxx shutdown
if [ $? -ne 0 ]
then
echo "couldn't shut down the database" && exit 1
else
echo "db server has been shut down"
fi


# move the history data files
mv /export/mount1/c3/history* /root/.
if [ $? -ne 0 ]
then
echo "couldn't move the history files out " && exit 1
else
echo "those history files moved okay"
fi

# restart the database
/etc/rc.d/rc.local
if [ $? -ne 0 ]
then
echo "couldn't restart the database" && exit 1
else
echo "db server restarted okay"
fi
sleep 3

# create an empty history table to collect history for the moment
/usr/local/mysql/bin/mysql c3 -pxxxxx < /root/historystructure.sql
if [ $? -ne 0 ]
then
echo "couldn't replicate history table structure into c3" && exit 1
else
echo "history table recreated on c3"
fi

# run repairs on the history data
/usr/local/mysql/bin/myisamchk -r /root/*.MYI
if [ $? -ne 0 ]
then
echo "couldn't repair MyISAM table" && exit 1
else
echo "MyISAM table was repaired okay"
fi

# take a dump of the history created in the last 15 mins
/usr/local/mysql/bin/mysqldump --no-create-info -pxxxxx c3 history > /root/newhistory.sql
if [ $? -ne 0 ]
then
echo "couldn't dump intermediate history data" && exit 1
else
echo "history data was dumped alright"
fi

# shutdown the database for a sec
/usr/local/mysql/bin/mysqladmin -pxxxxx shutdown
if [ $? -ne 0 ]
then
echo "couldn't shutdown the database" && exit 1
else
echo "db server has been shut down again"
fi

# move the repaired history data back into place
mv /root/history.M* /export/mount1/c3/.
if [ $? -ne 0 ]
then
echo "couldn't move the repaired table back into place" && exit 1
else
echo "repaired table moved back where it belongs"
fi

# restart the database
/etc/rc.d/rc.local
if [ $? -ne 0 ]
then
echo "couldn't restart the db server" && exit 1
else
echo "db server has been restarted"
fi

sleep 5

# pipe in the little bit of history
/usr/local/mysql/bin/mysql -pxxxxx c3 < /root/newhistory.sql
if [ $? -ne 0 ]
then
echo "couldn't insert the intermediate history data" && exit 1
else
echo "intermediate history data was inserted just fine"
fi

# go make a cuppa
echo "all done"
comment