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.

INSTALLATION OF POSTGIS/POSTGRESQL ON CENTOS 5

Install PROJ
If you are missing proj based on above or running a version below 4.5, then install by following these steps.
wget http://download.osgeo.org/proj/proj-4.6.0.tar.gz
tar xvzf proj-4.6.0.tar.gz
cd proj-4.6.0
./configure && make clean && make
make install
ldconfig
cd ..
install GEOS
If you are missing geos based on above or running a version below 3.0, then install by following these steps.
wget http://download.osgeo.org/geos/geos-3.0.3.tar.bz2
tar xvjf geos-3.0.3.tar.bz2
cd geos-3.0.3
./configure && make clean && make
make install
ldconfig
cd ..

Install Postgresql 8.3.8
Exclude Postgresql from the CentOS base repository as Postgresql 8.3 is not included in the base repository.
echo "exclude=postgresql* >> " /etc/yum.repos.d/CentOS-Base.repo
Download and install the RPMs from http://yum.pgsqlrpms.org:
wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-centos-8.3-6.noarch.rpm
rpm -ivh pgdg-centos-8.3-6.noarch.rpm
yum install postgresql postgresql-server postgresql-contrib postgresql-devel

Add:
/usr/local/pgsql/lib
/usr/local/lib
to "/etc/ld.so.conf" and run "ldconfig".

Retrieve the PostGIS source archive from http://postgis.refractions.net/download/postgis-1.4.0.tar.gz. Uncompress and untar the archive.
# gzip -d -c postgis-1.4.0.tar.gz | tar xvf -
Then run:
# ./configure
If you want support for coordinate reprojection, you must have the Proj4 library installed. If ./configure didn't find it, try using --with-proj=PATH switch specify a specific Proj4 installation directory.
If you want to use GEOS functionality, you must have the GEOS library installed. Geos 3.0.3+ is preferred and is required for some functions such as ST_SimplifyPreserveTopology to be available. If ./configure didn't find it, try using --with-geos=PATH to specify the full path to the geos-config program full path.
• Run the compile and install commands.
# make
# make install
All files are installed using information provided by pg_config
Libraries are installed [pkglibdir]/lib/contrib.
Important support files such as lwpostgis.sql are installed in [prefix]/share/contrib.
Loader and dumper binaries are installed in [bindir]/.
PostGIS requires the PL/pgSQL procedural language extension. Before loading the lwpostgis.sql file, you must first enable PL/pgSQL. You should use the createlang command. The PostgreSQL Programmer's Guide has the details if you want to this manually for some reason.
# createlang plpgsql template_postgis
Now load the PostGIS object and function definitions into your database by loading the lwpostgis.sql definitions file.
# psql -Upostgres -d template_postgis -f /usr/share/pgsql/contrib/postgis.sql
The PostGIS server extensions are now loaded and ready to use.
For a complete set of EPSG coordinate system definition identifiers, you can also load the spatial_ref_sys.sql definitions file and populate the SPATIAL_REF_SYS table.
# psql -Upostgres -d template_postgis -f /usr/share/pgsql/contrib/spatial_ref_sys.sql

Master Slave Setup

Assumptions:

1. Master Host IP is: 192.168.16.41
2. Slave Host IP is: 192.168.16.52

--Preparing the Master:

1. Create Replication User on Master
a. Logon to Master database server and issue following command from MySQL prompt.
$ mysql –uroot –p
mysql> GRANT REPLICATION SLAVE ON *.*
-> TO 'repl'@’192.168.16.52' IDENTIFIED BY 'slavepass';
mysql> flush privileges;
mysql> quit;
2. Update my.cnf file using your favourite editor (vi or nano).
3. Add following lines under [mysqld] section:

log-bin=mysql-bin
server-id=1

4. Restart Master database server
$ /etc/init.d/mysql restart

5. Get the master status information
a. Logon to MySQL and run following commands
$ mysql –uroot –p
mysql> FLUSH TABLES WITH READ LOCK;
b. Leave this session running until you have taken the data snapshot

6. Create a data snapshot [using mysqldump tool]
a. In another ssh session use mysqldump to create a snapshot of all the databases. For example:
$ mysqldump –uroot –p --all-databases --master-data=1 > fulldump.db

7. In the session/window where you acquired the read lock, release the lock:
mysql> UNLOCK TABLES;
8. Copy the backup file i.e. fulldump.db onto the replication database server] using scp/rsync:
$ scp fulldump.db root@192.168.16.52:

--Preparing the Slave:

1. Update my.cnf file using your favourite editor (vi or nano).
a) Add following lines under [mysqld] section

server-id=2
master-host=192.168.16.41
master-user=repl
master-password = slavepass
relay-log=localhost-relay-bin
relay-log-index=localhost-relay-bin.index

b) Restart database server
$ /etc/init.d/mysql restart
c) Restore backup from file
$ mysql –uroot –p < fulldump.db
2. Start Replication Slave
mysql> START SLAVE;
3. Monitor Replication Status
$ mysql> show slave status\G
4. The slave is catching up Master database if the Slave_IO_Running and Slave_SQL_Running values are both Yes

Installing PostgreSQL Standby Server

Assumptions:

1. password less authentication is enabled between Primary and Standby database servers.
2. rsync is installed on both servers
3. vhosts file (i.e. /etc/vhosts) contain entry for ‘standby’ server
4. pg_standby script is installed onto standby server
5. This path exists on 'standby' server i.e. /backup/psql_wal. And postgres user has permissions to read/write
6. PostgreSQL 8.3.8 is installed on both primary and standby servers.

Primary Database Setup:

--Edit postgresql.config file. Set the following parameters

archive_command = '/usr/bin/rsync -a %p standby:/backup/psql_wal/%f </dev/null'
archive_timeout = 120

--Restart postgres to apply chnages:

su postgres
pg_ctl -D /data/pgdata restart

--Start hot backup:
echo "SELECT pg_start_backup('mybackup');" | psql -U postgres

--Backup data directory using tar

tar -cvf /tmp/backup.tar /data/pgdata

--Copy backup onto standby server:

rsync /tmp/backup.tar standby:/backup

--Stop hotbackup, clear the flag
echo "SELECT pg_stop_backup();" | psql -U postgres

Standby Database Setup:

--Stop postgres:
su postgres
pg_ctl -D /data/pgdata stop

--Restore the backup:
rm -fR /data/pgdata/*
tar -xvf /tmp/backup.tar /data/pgdata
rm -fR /data/pgdata/pg_xlog/*
mkdir /data/pgdata/pg_xlog/archive_dir

--Create recovery file: /data/pgdata/recovery.conf
--add following line:
restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5432 /backup/psql_wal/ %f %p %r 2>>standby.log'

--Correct permissions:
chown -R postgres:postgres /data/pgdata

--Start postgres on standby server:
su postgres
pg_ctl -D /data/pgdata -l /data/pgdata/pg.log start

Monitor Standby Server:
tail –f /data/pgdata/standby.log

Using MySQL with memcached

Installing memcached on Centos 5 (x86_64)

$ wget http://apt.sw.be/redhat/el5/en/x86_64/rpmforge/RPMS/rpmforge-release-0.3.6-1.el5.rf.x86_64.rpm

--Install a new RPM server

$ rpm -Uvh rpmforge-release-0.3.6-1.el5.rf.x86_64.rpm

--Now we can simply use yum to install Memcached:
$ yum install memcached

--Starting memcached . Note memcached runs on port 11211

$ memcached -d -u nobody -l 127.0.0.1

--install libmemcached

$ wget http://download.tangent.org/libmemcached-0.35.tar.gz
$ tar -zxf libmemcached-0.35.tar.gz
$ cd libmemcached-0.35
$ ./configure --prefix=/usr/local/memcached
$ make clean && gmake
$ gmake install

--Add following lines into /etc/ld.so.conf

/usr/local/lib
/usr/lib
/usr/lib64/mysql
/usr/local/memcached/lib
/usr/lib64/mysql/lib

-- Creates the necessary links and cache to the most recent shared libraries

$ ldconfig

-- install memcached UDFs
$ wget http://download.tangent.org/memcached_functions_mysql-0.9.tar.gz
$ tar -zxf memcached_functions_mysql-0.9.tar.gz
$ cd memcached_functions_mysql-0.9
$ ./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib64/mysql --with-libmemached=/usr/local/memcached/lib
$ make clean && gmake
$ gmake install

-- Once this is done, initialize memcached functions with MySQL.

shell# cd sql

shell# echo install_functions.sql | mysql

-- Connect to MySQL

mysql> SELECT memc_servers_set('127.0.0.1:11211');
mysql> SELECT memc_set('mykey', 'Getting this with SELECT means all works well');
+--------------------------------------------------------------------+
| memc_set('mykey', 'Getting this with SELECT means all works well') |
+--------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------+
1 row in set (0.03 sec)

mysql> SELECT memc_get('mykey');
+-----------------------------------------------+
| memc_get('mykey') |
+-----------------------------------------------+
| Getting this with SELECT means all works well |
+-----------------------------------------------+
1 row in set (0.00 sec)

replication monitoring script

This script monitors mysqld and replication status. If mysql is not running or replication slave is not running then it will log messages into log file. you can customize this script to send an alert via email etc.

##########

HOST=`hostname -s`

USR=root

PASS=xxxxx

MYSQL="/usr/bin/mysql -h$HOST -u$USR -p$PASS"

TMP=/tmp/mysql.log

LOG=/var/logs/monitoring.txt

MYSQLADMIN="/usr/bin/mysqladmin -h$HOST -u$USR -p$PASS"

MAXBEHIND=7200 # [Critical] Two hours behind

MINBEHIND=3600 # [Warning] One hour behind

# Check if mysqld is alive and accepting connections

IS_ALIVE=`$MYSQLADMIN ping | grep -c 'alive'`

if [ "$IS_ALIVE" != "1" ]; then

ERRMSG="[Critical] MySQL Down. `date`"

echo $ERRMSG >> $LOG

exit

fi

# Replication Monitoring

$MYSQL -e"show slave status\G" > $TMP

SB=`grep Seconds_Behind_Master $TMP | cut -f2 -d: | tr -d " "`

SBNUM=$SB

if [ "$SB" = "NULL" ]; then

ERRMSG="[Critical] Replication Down. `date`"

echo $ERRMSG >> $LOG

$MYSQL -e"show slave status\G" >> $LOG

elif [ $SBNUM -ge $MAXBEHIND ]; then

ERRMSG="[Critical] Replication behind ${SBNUM}s. `date`"

echo $ERRMSG >> $LOG

elif [ $SBNUM -ge $MINBEHIND ]; then

ERRMSG="[Warning] Replication behind ${SBNUM}s. `date`"

echo $ERRMSG >> $LOG

fi

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

Check log file for warnings

shell# tail -f /var/logs/monitoring.txt