This How To explain the replication in MySQL on the same machine.

What Replication is and why?

Replication allows DBA to clone the database of master to another database server with same database. That include the master and slave identities. Slave make itself the exact copy of the master database server and or its databases. There may be one-one, one-many, many-one relation among master(s) and slave(s). Slave continously reads the binary log at master(binlog store the queries written at master database server) and get input to its slave database server.

What for Replication is not:

consider the solution for backup, performance, security and redundancy. There are other techniques for it.

We currently see the Replication of MySQL server at same machine, i.e master and slave running on same machine. We will discuss some issue with replication too.

Pre-requisite:

MySQL server 4.1.12 or above in source format. Download it them from http://mysql.com in source format.

You can for ease, download mysql client from http://mysql.com or put the mysql in the environmental variable. Other wise mysql would have to be called from the respective master/slave directories.

some Linux distribution. (I used Fedora Core 2).

remove dependencies for the mysql server
<mospagebreak>
=====================================================

MySQL Master Installation and Configuration:

=====================================================

Download mysql-4.1.12 Source at /misc folder

tar xzvf mysql-4.1.12.tar.gz

cd /misc/mysql-4.1.12

./configure --prefix=/usr/local/mysql-master

make

make install

cd /usr/local/mysql-master/bin

./mysql_install_db

(it will create a var folder )

cd ../var

cp /misc/mysql-4.1.12/support-files/my-medium.cnf my.cnf

cd ..

groupadd mysql

useradd -g mysql mysql

chown -R root .

chown -R mysql var

chgrp -R mysql .

*****************config file starts***************

[mysqld]

port = 3306

socket = /usr/local/mysql-master/mysql.sock

#skip-networking // we have skip this in our case as we are doing both master and slave on same machine.

# Replication Master Server (default)

# binary logging is required for replication

log-bin

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id = 1

*****************config file ends***************

configure other settings according to your requirement or remain same, it will work!! :)

Now starts the mysql server by:

cd /usr/local/mysql-master/bin

./mysqld_safe --defaults-file=/usr/local/mysql-master/var/my.cnf &;

<mospagebreak>

===============================================================

MySQL Slave:

===============================================================

Now extract mysql-4.1.12.tar.gz at different dir,

cd /opt/mysql-4.1.12

./configure --prefix=/usr/local/mysql-slave

make

make install

cd /usr/local/mysql-slave

cd bin

./mysql_install_db

(it will create a var folder )

cd ../var

cp /opt/mysql-4.1.12/support-files/my-medium.cnf my.cnf

cd ..

groupadd mysql

useradd -g mysql mysql

chown -R root .

chown -R mysql var

chgrp -R mysql .

Edit my.cnf in the var folder

*****************config file starts **********************

[mysqld]

port = 3307

socket = /usr/local/mysql-slave/var/mysql.sock

#skip-networking

server-id = 2

# The replication master for this slave - required

master-host = localhost

master-user = slavedb

master-password = q1w2e3r4t5

master-port = 3306

*****************config file ends **********************

Now starts the mysql server by:

cd /usr/local/mysql-slave/bin

./mysqld_safe --defaults-file=/usr/local/mysql-slave/var/my.cnf &

<mospagebreak>

=======================================================

Configure Replication:

=======================================================

connect to mysql master by:

mysql --sock=/usr/local/mysql-master/mysql.sock

Create account at master for slave:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slavedb@"192.168.1.27/255.255.255.0" identified by 'q1w2e3r4t5'; Query OK, 0 rows affected (0.28 sec)

connect to mysql slave by:

mysql --sock=/usr/local/mysql-slave/mysql.sock

mysql> slave start;

Query OK, 0 rows affected, 1 warning (0.04 sec)

====================================================

Testing:

====================================================

mysql connect at master:

mysql> show master statusG;

*************************** 1. row ***************************

File: adam-bin.000001

Position: 227

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.01 sec)

ERROR:

No query specified

//*************************** 1. row ***************************

connect mysql at slave

mysql> show slave statusG;

*************************** 1. row ***************************

Slave_IO_State: Connecting to master

Master_Host: localhost

Master_User: slavedb

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: adam-bin.000001

Read_Master_Log_Pos: 4

Relay_Log_File: adam-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: adam-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 4

Relay_Log_Space: 4

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

1 row in set (0.00 sec)

ERROR:

No query specified

*************************** 1. row ***************************

binlog file at masterand its position must be same in mysql slave by show slave statusG;

If every things fine, you are running a working copy of one-one master and slave relation on same system.

---