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

MySQL InnoDB freeze/lock-up under insert load

It's been up for a few days and runs fast/well, but twice now it has suddenly locked up with NO activity at all - all of the Innodb thread slots have tasks which seem stuck, and hundreds of other threads waiting - this goes on forever, like 1-2 hours at CPU 100% idle, no queries executing, etc. until we discovered FLUSH TABLES will fix it.
http://forums.mysql.com/read.php?22,273419,274069#msg-274069

Reason: You may need to increase the value of the table_cache variable. You can check whether you need to increase the table cache by checking the Opened_tables status variable. If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, you should increase the table cache size

Solution: increase table_cache

InnoDB freeze/lock-up

It's been up for a few days and runs fast/well, but twice now it has suddenly >locked up with NO activity at all - all of the Innodb thread slots have tasks which seem stuck, and hundreds of other threads waiting - this goes on forever, like 1-2 hours at CPU 100% idle, no queries executing, etc. until we discovered FLUSH TABLES will fix it।

रासों :

Manually DB Creation with 11G

1) Set your ORACLE_SID

shell>export ORACLE_SID=test

2) Create pfile

shell> vi $ORACLE_HOME/dbs/inittest.ora


control_files = (/u01/app/oracle/product/11.1.0/db_1/dbs/control_test_1.ctl,/u01/app/oracle/product/11.1.0/db_1/dbs/control_test_2.ctl,/u01/app/oracle/product/11.1.0/db_1/dbs/control_test_3.ctl)
undo_management = AUTO
undo_tablespace = ts_undo
db_name = test
db_block_size = 8192
sga_max_size = 524288000 # 500M
sga_target = 524288000 #500M


3) Create a password file

#$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/pwdtest.ora password=oracle entries=5


4) Start the instance



sqlplus / as sysdba

startup nomount

5) Create the database

create database test
user sys identified by gd123
user system identified by gd123
controlfile reuse
maxdatafiles 22
maxinstances 2
character set we8iso8859p1
national character set utf8
--set default bigfile tablespace
-- database logging clauses
logfile
group 1 ('/u01/app/oracle/oradata/test/redo_test_1a.log', '/u01/app/oracle/oradata/test/redo_test_2a.log') size 4M,
group 2 ('/u01/app/oracle/oradata/test/redo_test_1b.log', '/u01/app/oracle/oradata/test/redo_test_2b.log') size 4M
--maxlogfiles 3
--maxlogmembers 7
maxloghistory 1000
archivelog
force logging
-- Tablespace Clauses
extent management local
datafile '/u01/app/oracle/oradata/test/system.dbf' size 300M autoextend ON
sysaux datafile '/u01/app/oracle/oradata/test/sysaux.dbf' size 100M autoextend on
default tablespace ts_test_data datafile '/u01/app/oracle/oradata/test/data.dbf' size 1M autoextend on next 128K maxsize 2M extent management local
default temporary tablespace ts_test_temp tempfile '/u01/app/oracle/oradata/test/temp.dbf' size 2M extent management local
undo tablespace ts_undo datafile '/u01/app/oracle/oradata/test/undo.dbf' size 20M
--
set time_zone = '-01:00';


6) Run catalog and catproc

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql