This How To explain the replication in MySQL on the same machine.
You have large hits, with database driven site and performance, redundancy, security now comes in mind. DBA is now input by many fingers to seek some good solution to make happy faces. So here comes words like Cluster, Replication, Backup, Fail Over and others. So what these are? We currently discuss Replication in terms of a web based database driven site for large hits.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.
