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

multi-threaded mysqldump

What a great feature! isn't it? let's see how we can use it.

Installation:
------------

1. We need to install latest version of perl ( version v5.8.8 or higher)
2. Download mk-parallel-dump script
i.e. wget http://www.maatkit.org/get/mk-parallel-dump
3. Test if it works!
shell# chmod +x mk-parallel-dump
shell# ./mk-parallel-dump --help

We have now successfully installed multi-threaded mysqldump tool! now its the time to learn it.

let's backup mysql database

[root@localhost maatkit]# ./mk-parallel-dump --user root --databases mysql
CHUNK TIME EXIT SKIPPED DATABASE.TABLE
db 0.49 0 0 mysql
all 0.49 0 0 -

[root@localhost maatkit]# ls -lh mysql/
total 476K
-rw-r--r-- 1 root root 654 Jan 20 14:39 00_columns_priv.sql
-rw-r--r-- 1 root root 1.6K Jan 20 14:39 00_db.sql
-rw-r--r-- 1 root root 351 Jan 20 14:39 00_func.sql
-rw-r--r-- 1 root root 325 Jan 20 14:39 00_help_category.sql
-rw-r--r-- 1 root root 230 Jan 20 14:39 00_help_keyword.sql
-rw-r--r-- 1 root root 243 Jan 20 14:39 00_help_relation.sql
-rw-r--r-- 1 root root 360 Jan 20 14:39 00_help_topic.sql
-rw-r--r-- 1 root root 1.5K Jan 20 14:39 00_host.sql
-rw-r--r-- 1 root root 750 Jan 20 14:39 00_procs_priv.sql
-rw-r--r-- 1 root root 1.6K Jan 20 14:39 00_proc.sql
-rw-r--r-- 1 root root 838 Jan 20 14:39 00_tables_priv.sql
-rw-r--r-- 1 root root 234 Jan 20 14:39 00_time_zone_leap_second.sql
-rw-r--r-- 1 root root 190 Jan 20 14:39 00_time_zone_name.sql
-rw-r--r-- 1 root root 232 Jan 20 14:39 00_time_zone.sql
-rw-r--r-- 1 root root 291 Jan 20 14:39 00_time_zone_transition.sql
-rw-r--r-- 1 root root 403 Jan 20 14:39 00_time_zone_transition_type.sql
-rw-r--r-- 1 root root 2.6K Jan 20 14:39 00_user.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 columns_priv.000000.sql
-rw-r--r-- 1 root root 197 Jan 20 14:39 db.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 func.000000.sql
-rw-r--r-- 1 root root 1.2K Jan 20 14:39 help_category.000000.sql
-rw-r--r-- 1 root root 6.4K Jan 20 14:39 help_keyword.000000.sql
-rw-r--r-- 1 root root 7.9K Jan 20 14:39 help_relation.000000.sql
-rw-r--r-- 1 root root 376K Jan 20 14:39 help_topic.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 host.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 proc.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 procs_priv.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 tables_priv.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_leap_second.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_name.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_transition.000000.sql
-rw-r--r-- 1 root root 0 Jan 20 14:39 time_zone_transition_type.000000.sql
-rw-r--r-- 1 root root 783 Jan 20 14:39 user.000000.sql

innodb_file_per_table- move specific tables to separate physical disks

Use of innodb_file_per_table can be useful if you want to move specific database or table(s) to a separate disk.
To enable multiple table spaces, add a line to the [mysqld] section of my.cnf (or my.ini) file and restart mysql server.
[mysqld]
innodb_file_per_table

The new innodb tables will have their own .frm and .ibd files, this is similar to what Myisam storage engine does but it divides the tables into .myi and .myd.

In this post, I'll show you how to move specific database onto separate disk.

Assumptions:

- datadir= /var/lib/mysql
- you have basic linux skills

Steps:

log on to mysql

shell# mysql -uroot -p
# create database
mysql> create database my_db;
mysql> quit;

# stop mysql
shell# /etc/init.d/mysql stop

# create new directory onto separate disk
shell# mkdir /disk2/innodb

# move database onto separate disk
shell# cd /var/lib/mysql
shell# mv my_db /disk2/innodb

# create symlink
shell# ln -s /disk2/innodb/my_db my_db

# correct permissions
shell# chown -R mysql:mysql /disk2/innodb

# start mysql server
shell# /etc/init.d/mysql start

you have now successfully moved my_db database onto separate disk.