MySQL Replication On The Same Machine

Source: http://www.howtoforge.com/one_machine_mysql_replication

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 

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 .

[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

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 &;

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

[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

Now starts the mysql server by:

cd /usr/local/mysql-slave/bin

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

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 status\G;

*************************** 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

connect mysql at slave 

mysql> show slave status\G;

*************************** 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

binlog file at masterand its position must be same in mysql slave by show slave status\G;

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

Some Issues:

Replication may be fail because of number of issues. I share some of my experience with replication.

issue: replication fails, master got down.

Master could be down due to many of the reasons. Do CHECK the FILE LIMITS IN DATABASE, SQL QUERIES AND DISK USAGE. If any of the reason shows, fix that, restart mysqld and check master status:

mysql> show master status\G;

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

adam-bin.000003

Position: 227

Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)


now check slave: by show slave status:

mysql> show slave status\G;

*************************** 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: No

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)

as above values shows that master has incremented to 3rd binlog while slave is still pointed to binlog1. so change this value by connecting at mysql slave 

mysql>; stop slave;

mysql> change master to master_log_file='adam-bin.000003', master_log_pos=227;

mysql> start slave;

Now check with show slave status\G; its working fine.

Issue 2: At Slaves if duplicate error key appear

i.e
At slave

Mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.152
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: adam-bin.000048
Read_Master_Log_Pos: 317714810
Relay_Log_File: db4-relay-bin.000001
Relay_Log_Pos: 290512385
Relay_Master_Log_File: adam-bin.000048
Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062

Last_Error: Error 'Duplicate entry 'dekq5g820avnfdmar5qi9dkhv3' for key 1' on query. Default database:'session_sql'. Query: 'INSERT INTO sessi ons5 VALUES ('dekq5g820avnfdmar5qi9dkhv3', UNIX_TIMESTAMP(NOW()) + 18000, 'redir ect|i:1;')'

Skip_Counter:0
Exec_Master_Log_Pos: 290512419
Relay_Log_Space: 317714776
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)

That means that by unavailablilty of master, slave was read, or unavailability of slave, master was updated, so a query got two primary keys found for one entry which is a mess up.

So fixing it is by:
At slave:

Mysql> set global sql_slave_skip_counter=1;

Mysql> start slave;

Mysql> show slave status\G;

This will show the synchronized value with master. Fixed.