MySQL Forums
Forum List  »  Replication

Re: MySQL Circular Replication
Posted by: Subhanshu Gupta
Date: July 25, 2007 01:40PM

Hi Ryan,

The first thing you need to check the MySQL version on ur both machines and my reply assumes that you are using same version on both machines.

I come to your my.cnf file which i feel is not rightly configured.

Now you have two servers. Now the my.cnf for both the servers should like the following:

Server I

For Server-I as Master
server-id = 1
log-bin = mysql-bin

For Server-I as Slave (uncomment the slave related parameters)

server-id = 1
master-host = <ip addr of the Server II>
master-user = <username> (username with replication slave privilege created at the Server II)
master-password = <password>
master-port 3306


Server II

For Server-II as Master
server-id = 2
log-bin = mysql-bin

For Server-II as Slave (uncomment the slave related parameters)

server-id = 2
master-host = <ip addr of the Server I>
master-user = <username> (username with replication slave privilege created at the Server I)
master-password = <password>
master-port 3306

You can set your auto-increment parameters according to your own logic and requirements.

After saving these settings in the my.cnf file restart both the servers.

Now please always remember one thing master has nothing to do with the replication. Its the slave which has to keep track of the all the events happening at the master's end. And this is done by keeping track of the binaries generated and stored in the binary log files at the master. The binary log files are generally have the name like "mysql-bin.000001" which are by default stored in the mysql data directory till you don't mention the binary log file's position in the my.cnf file.

At the master's end (Server I) use the following command to get information

mysql>SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000003 | 98 | | |
+------------------+-----------+--------------+------------------+

Now at the slave's end (Server II) use the following command to get information

mysql>SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <ip addr of Server I>
Master_User: <username>
Master_Port: 3306
Connect_Retry: <time you set for retry in secs>
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 98
Relay_Log_File: <server II machine name>-relay-bin.000001 ...depends on which relay log file the binary logs are being written
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000023
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: 98
Relay_Log_Space:
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: 0
1 row in set (0.00 sec)

ERROR:
No query specified

If you get such an information by this command means your both master(Server I) and slave (Server II) are in sync. The important parameters here to look for are the
1.Master_Host -- Should be same as you have set in the my.cnf
2.Master_User -- Should be same as you have set in the my.cnf
3.Master_Port -- Should be same as you have set in the my.cnf
4.Master_Log_File -- Should be same as the you found at Master end
5.Exec_Master_Log_Pos -- Should be same as you find at Master end

If any of these information is mismatching then don't panic. Just find the master.info file which you'll generally find in the mysql data directory. Or else execute
$ locate master.info

to find the master.info file and edit this file coz the wrong information due to
mysql>SHOW SLAVE STATUS \G;
command is parsed from the master.info file only

The file looks like following

12
mysql-bin.000010
100
<ip addr of Server I>
<username>
<password>
3306
<time you set for retry in secs>
0

This is an example false file.

Now you need to update the file as follows and save it:

12
mysql-bin.000003
98
<ip addr of Server I>
<username>
<password>
3306
<time you set for retry in secs>
0

Now this has the correct information to start the replication.

Restart the mysql at slave and congrats replication is restored !!

Follow the same steps for setting up the other way side replication i.e. Server II as Master and Server I as Slave. And I think you should be successful in setting up the two way replication.

If still you are unsuccessful please revert back with the details you tried at your end.

Options: ReplyQuote


Subject
Views
Written By
Posted
11515
July 13, 2007 01:05PM
3450
July 19, 2007 12:59PM
Re: MySQL Circular Replication
5369
July 25, 2007 01:40PM
3246
August 28, 2007 10:48AM
2868
August 28, 2007 05:49PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.