Thursday 25 February 2010

Backup using mysqlhotcopy script

I needed to setup a script to backup myisam tables, so I thought I shall publish it on web too

########################
# Create Backup Folder #
########################

NOW=$(date +"%m_%d_%Y")
BACKUP_LOCATION="/u02/backup/dev/$NOW/"
mkdir -p `echo $BACKUP_LOCATION`
cd /u02/backup/dev/$NOW

########################################
# Array of databases we want to backup #
########################################

ARRAY="bugs my_company knowledge_base mysql wikidb"

MYSQL_HOTCOPY="/usr/bin/mysqlhotcopy -p xxxxx -q "

echo "----BACKUP STARTED @`date`"

#################################################
# Iterate through Array and Backup each of them #
################################################

for DATABASE in `echo $ARRAY`
do
echo "Taking backup of $DATABASE.."
$MYSQL_HOTCOPY $DATABASE $BACKUP_LOCATION
echo "Done.."
echo "Compressing backup directory using tar"
/bin/tar cfP $DATABASE.tar $BACKUP_LOCATION/$DATABASE
echo "Done.."
echo "Delete backup directory after tar command"
rm -fR $DATABASE
echo "Done.."
echo "Compressing $DATABASE.tar file using gzip"
/bin/gzip $DATABASE.tar
echo "done"
done

echo " ----- END -----@`date` "

##################################

Friday 12 February 2010

Backup MySQL Replication Database Server

Here is the script, which I use to backup MySQL Replication Slave database server.

HOST=localhost
USR=root
PASS=xxxxx
SOCKET=/tmp/mysql.sock

NOW=$(date +"%m_%d_%Y")
BACKUP_LOCATION=/path/to/backup/$NOW

mkdir -p `echo $BACKUP_LOCATION`

MYSQL="/usr/bin/mysql -h$HOST -u$USR -p$PASS -S $SOCKET -v"
MYSQLDUMP="/usr/bin/mysqldump -u$USR -p$PASS -S $SOCKET --all-databases"

# STOP_SLAVE
echo 'STOP SLAVE SQL_THREAD; FLUSH TABLES;'| $MYSQL

# GET_SLAVE_STATUS
echo "SHOW SLAVE STATUS\G" | $MYSQL > $BACKUP_LOCATION/bin_log_pos.log

# BACKUP_TABLE_STRUCTURES
$MYSQLDUMP --no-data --routines --result-file=$BACKUP_LOCATION/table_structure.sql

# BACKUP_RECORDS
$MYSQLDUMP --result-file=$BACKUP_LOCATION/all_records.sql

# START_SLAVE
echo "START SLAVE" | $MYSQL

####################

if you prefer, you can automate this script, add following line into crontab

0 4 * * * /path/to/script/backup.sh

Monday 8 February 2010

MyISAM Table Maintenance

There are numerous ways to check/repair MyISAM tables

1. mysqlchk

2. mysqlcheck

3. myisam_recover

One should not use mysqlchk with online database, unlike mysqlcheck it directly accesses tables and may damage them. There are two alternative solutions

  1. MYISAM_RECOVER option: quick option to automate recovery but it should be used very carefully. As all live tables are accessed quite frequently, enabling this option may trigger many check/repair at once.
  2. CHECK TABLE: it is safer compared to myisam_recover option. Unlike myisam_chk it does not directly access tables.

It is not recommend that one should run extended table checks regularly. However it is highly recommended using medium/quick check options to ensure that all tables are okay. Furthermore one should be optimizing tables regularly, as this reduces risks associated with table errors. Unlike check table operations, optimize table operation takes much longer but the good news is that we can easily identify the table(s) that need optimization.

Friday 5 February 2010

Running Multiple Instances of MySQL on Centos

Today I needed to setup multiple instances of mysql on centos5. To be honest there are numerous ways of doing the same. I have used very basic method of doing the same.

Before you follow these steps:

1. Setup one MySQL configuration file per instance e.g. /etc/my_1.cnf , /etc/my_2.cnf
2. Setup separate data directory for each instance
3. Different port number for each instance
4. Different PID file for each instance.

MySQL Configuration for 1st instance: /etc/my_1.cnf

#########################################

[mysqld]
datadir=/var/lib/mysql_1
port=3306

[mysqld_safe]
log-error=/var/log/mysqld_1.log
pid-file=/var/run/mysqld/mysqld_1.pid


MySQL Configuration for 2nd instance: /etc/my_2.cnf

#########################################

[mysqld]
datadir=/var/lib/mysql_2
port=3307

[mysqld_safe]
log-error=/var/log/mysqld_2.log
pid-file=/var/run/mysqld/mysqld_2.pid

Initializing and starting MySQL:

-- initializing database for 1st instance

shell# mysql_install_db --user=mysql --datadir=/var/lib/mysql_1

-- initializing database for 2nd instance

shell# mysql_install_db --user=mysql --datadir=/var/lib/mysql_2

-- Starting 1st instance

shell# mysqld_safe --user=mysql --defaults-file=/etc/my_1.cnf &

-- Starting 2nd instance

shell# mysqld_safe --user=mysql --defaults-file=/etc/my_2.cnf &



Thursday 4 February 2010

Optimize tables that need optimization


This script looks for tables that needs optimization. The logic is to check 'Data_free' field of SHOW TABLE STATUS command , if it is not zero, this means table has unused bytes i.e. the table has got holes somewhere in the table. You can customize this script according to your need, that is, optimize found tables etc.

#!/bin/sh

# This script check tables in the database.
# Additionaly you can supply a filter to check only certain tables.

user=root
pass=xxxxx
db=test
#filter=
TMP=~/scripts/query.log
LOG=~/scripts/need_optimization.log
MAXBYTES=100

mysql="/usr/bin/mysql -u$user -p$pass -s $db"


# Ok now do the check on each table
echo "Checking database $db ..."
echo "show tables like \"${filter}\"" | $mysql | while read table
do
echo "SHOW TABLE STATUS LIKE \"${table}\"\G" | $mysql > $TMP
bytes=`grep Data_free $TMP | cut -f2 -d: | tr -d " "`

if [ $bytes -gt $MAXBYTES ]; then

echo "--------------------- Table Status ($table) `date`" >> $LOG
cat $TMP >> $LOG
echo "---------------------------------------------------------------------------" >> $LOG

fi

done

echo "Checking database $db finished..."

##############################

let's run this script

shell# ./db_optimize.sh

check what table(s) need optimization...

shell# more need_optimization.log

mysqlcheck automatic script

This script check tables in the database. Also It captures error messages into log file. you can customize this script to send an alert via email etc.



#!/bin/sh

# This script check tables in the database.
# Additionaly you can supply a filter to check only certain tables.

user=root
pass=xxxxxx
db=mydb
#filter=

mysql="/usr/bin/mysql -u$user -p$pass -s $db"
stats_method="SET myisam_stats_method=nulls_equal"

# Ok now do the check on each table
echo "Checking database $db ..."
echo "show tables like \"${filter}\"" | $mysql | while read table
do
echo "$stats_method;check table $table\G" | $mysql > ~/scripts/query_output.log
error=`cat ~/scripts/query_output.log | grep Error`
count=`expr length "$error"`

if [ "$count" != "0" ]; then

echo "--------------------- Check Table Report ($table) `date`" >> ~/scripts/bad_tables.log
tail --lines=4 ~/scripts/query_output.log >> ~/scripts/bad_tables.log
echo "------------------------------------------------------------------------------------" >> ~/scripts/bad_tables.log

fi
done

echo "Checking database $db finished..."

Now run the script

shell# ./check_table.sh

let's see if there is any bad table..

shell# more bad_tables.log