Solved

Two-way MySQL replication not working?

Posted on 2014-03-20
2
315 Views
Last Modified: 2015-02-09
I have used this tutorial to setup two way replication:
http://www.neocodesoftware.com/replication/

The problem is, if I shut down server 1, then make a change on server 2, and then restart server 1, the changes on 2 do not replicate to 1.

however, If I shut down server 2, and make a change to server 1, then restart server 2, the changes made on server 1 are propogated to server 2.

So it is working but only server 1->server 2, and not the other way around.

Here are the configs (below). How do I troubleshoot this?
my.cnf at Master1

server-id        = 1
auto-increment-increment = 10
auto-increment-offset = 1
replicate-same-server-id = 0
log-bin          = /var/lib/mysql/mysql-bin
relay-log        = /var/lib/mysql/mysql-relay
relay-log-index  = /var/lib/mysql/mysql-relay.index
expire_logs_days = 10
max_binlog_size  = 100M
binlog_format    = 'MIXED'

my.cnf at Master2

server-id               = 2
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2
#expire_logs_days       = 10
#max_binlog_size         = 100M
log-bin          = /var/log/mysql/mysql-bin
#relay-log        = /var/log/mysql/mysql-relay-bin
#relay-log-index  = /var/log/mysql/mysql-relay.index
expire_logs_days = 10
max_binlog_size  = 100M
binlog_format    = 'MIXED'



mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.30.164.115', MASTER_USER='repl', MASTER_PASSWORD='60NmzdnsLBUBLnu44MSq';
mysql> start slave;




mysql>  SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.30.164.115
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000030
          Read_Master_Log_Pos: 1027
               Relay_Log_File: mysqld-relay-bin.000020
                Relay_Log_Pos: 1173
        Relay_Master_Log_File: mysql-bin.000030
             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: 1027
              Relay_Log_Space: 1476
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)



mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000030 |      207 |              |                  |
+------------------+----------+--------------+------------------+

Open in new window

0
Comment
Question by:DrDamnit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 78

Expert Comment

by:arnold
ID: 39944875
Look at show slave status on both systems for any replication errors.

Show slave status you posted, is that from server 1 or from server 2?
Make sure that the master_server_id reflects the other sserver id:slave on 1 should reflect 2 as the master. Slave on 2 should reflect 1.

Why are you changing master?


You are missing the replicate-db directives and binlogemail-do-DB. This is that you only replicate specific database excluding the mysql database.
0
 
LVL 78

Accepted Solution

by:
arnold earned 500 total points
ID: 39944894
You should name your binlog such that includes the server name,  this way when you show slave status reflects the log file with the incorrect name(same name as the slave) you'll more easily determine that you misreferenced the master on one of them.

You should also include the reference to the master host in the my.cnf such that as soon as the system comes up, it will reestablish the connection automatically

Look at the master IP.
0

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question