MySQL Replication On The Same Machine

Want to support HowtoForge? Become a subscriber!
 
Submitted by sheikhsa (Contact Author) (Forums) on Sat, 2006-07-01 14:41. :: MySQL

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.


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Seth Delconte (not registered) on Tue, 2010-01-12 17:52.
The title leads me to believe that your article explains how to replicate a MySQL database to another database on the same server machine...this doesn't seem to be the case.
Submitted by Anonymous (not registered) on Thu, 2006-07-20 05:34.

I have the similar MySQL Replicate Setup Guide at http://planetmy.com/blog/?p=104