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` "

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

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

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.

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 &

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