Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to setup a master - master setup and I see both masters connect to each other in workbench:

server 1:

-- Thread Id: 112
-- User: slave
-- Host: 207.244.73.38:55164
-- DB: None
-- Command: Binlog Dump
-- Time: 62
-- State: Master has sent all binlog to slave; waiting for binlog to be updated

Server 2:

-- Thread Id: 5
-- User: slave
-- Host: 2:52510
-- DB: None
-- Command: Binlog Dump
-- Time: 283
-- State: Master has sent all binlog to slave; waiting for binlog to be updated

Server 1 Master and Slave status. Server version: 5.6.15-log MySQL Community Server (GPL)

mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+
---------------+------------------+---------------------+-----------------------
-----------+---------------+-----------------------+------------------+---------
----------+-----------------+---------------------+--------------------+--------
----------------+-------------------------+-----------------------------+-------
-----+--------------------------------------------------------------------------
------------------------------------------------------------+--------------+----
-----------------+-----------------+-----------------+----------------+---------
------+--------------------+--------------------+--------------------+----------
-------+-------------------+----------------+-----------------------+-----------
--------------------+---------------+---------------+----------------+----------
--------------------------------------------------------------------------------
--------------------------------------------+-----------------------------+-----
-------------+--------------------------------------+---------------------------
-----------------------------+-----------+---------------------+----------------
---------+--------------------+-------------+-------------------------+---------
-----------------+----------------+--------------------+--------------------+---
----------------+---------------+
| Slave_IO_State                   | Master_Host   | Master_User | Master_Port |
 Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File
           | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQ
L_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replica
te_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_E
rrno | Last_Error
                                                            | Skip_Counter | Exe
c_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Lo
g_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SS
L_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL
_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_
Error
                                            | Replicate_Ignore_Server_Ids | Mast
er_Server_Id | Master_UUID                          | Master_Info_File
                             | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Runni
ng_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL
_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Ex
ecuted_Gtid_Set | Auto_Position |
+----------------------------------+---------------+-------------+-------------+
---------------+------------------+---------------------+-----------------------
-----------+---------------+-----------------------+------------------+---------
----------+-----------------+---------------------+--------------------+--------
----------------+-------------------------+-----------------------------+-------
-----+--------------------------------------------------------------------------
------------------------------------------------------------+--------------+----
-----------------+-----------------+-----------------+----------------+---------
------+--------------------+--------------------+--------------------+----------
-------+-------------------+----------------+-----------------------+-----------
--------------------+---------------+---------------+----------------+----------
--------------------------------------------------------------------------------
--------------------------------------------+-----------------------------+-----
-------------+--------------------------------------+---------------------------
-----------------------------+-----------+---------------------+----------------
---------+--------------------+-------------+-------------------------+---------
-----------------+----------------+--------------------+--------------------+---
----------------+---------------+
| Waiting for master to send event | 207.244.73.38 | slave       |        3306 |
            60 | mysql-bin.000005 |                 714 | WIN-UE8ASUJKKO0-relay-
bin.000006 |          1078 | mysql-bin.000003      | Yes              | No
          |                 |                     |                    |
                |                         |                             |
1051 | Error 'Unknown table 'sapp.new_table'' on query. Default database: 'sapp'
. Query: 'DROP TABLE `new_table` /* generated by server */' |            0 |
             915 |            6167 | None            |                |
    0 | No                 |                    |                    |
       |                   |                |                  NULL | No
                    |             0 |               |           1051 | Error 'Un
known table 'sapp.new_table'' on query. Default database: 'sapp'. Query: 'DROP T
ABLE `new_table` /* generated by server */' |                             |
           2 | 7c6c5fdb-6bd1-11e3-81ed-e83935f0c04c | C:\ProgramData\MySQL\MySQL
 Server 5.6\data\master.info |         0 |                NULL |
         |              86400 |             |                         | 131223 1
7:43:23          |                |                    |                    |
                |             0 |
+----------------------------------+---------------+-------------+-------------+
---------------+------------------+---------------------+-----------------------
-----------+---------------+-----------------------+------------------+---------
----------+-----------------+---------------------+--------------------+--------
----------------+-------------------------+-----------------------------+-------
-----+--------------------------------------------------------------------------
------------------------------------------------------------+--------------+----
-----------------+-----------------+-----------------+----------------+---------
------+--------------------+--------------------+--------------------+----------
-------+-------------------+----------------+-----------------------+-----------
--------------------+---------------+---------------+----------------+----------
--------------------------------------------------------------------------------
--------------------------------------------+-----------------------------+-----
-------------+--------------------------------------+---------------------------
-----------------------------+-----------+---------------------+----------------
---------+--------------------+-------------+-------------------------+---------
-----------------+----------------+--------------------+--------------------+---
----------------+---------------+
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+-------------------------------+-----------------
-+-------------------+
| File             | Position | Binlog_Do_DB                  | Binlog_Ignore_DB
 | Executed_Gtid_Set |
+------------------+----------+-------------------------------+-----------------
-+-------------------+
| mysql-bin.000006 |  7553142 | joomlatest,sapp,tcadmin,whmcs |
 |                   |
+------------------+----------+-------------------------------+-----------------
-+-------------------+
1 row in set (0.00 sec)

Server 2 master and slave status: Server version: 5.6.15-log MySQL Community Server (GPL)

mysql> SHOW SLAVE STATUS;
+----------------------------------+--------------+-------------+-------------+-
--------------+------------------+---------------------+------------------------
----------+---------------+-----------------------+------------------+----------
---------+-----------------+---------------------+--------------------+---------
---------------+-------------------------+-----------------------------+--------
----+---------------------------------------------------------------------------
-------------------------------------------------+--------------+---------------
------+-----------------+-----------------+----------------+---------------+----
----------------+--------------------+--------------------+-----------------+---
----------------+----------------+-----------------------+----------------------
---------+---------------+---------------+----------------+---------------------
--------------------------------------------------------------------------------
-----------------------+-----------------------------+------------------+-------
-------------------------------+------------------------------------------------
--------+-----------+---------------------+-------------------------+-----------
---------+-------------+-------------------------+--------------------------+---
-------------+--------------------+--------------------+-------------------+----
-----------+
| Slave_IO_State                   | Master_Host  | Master_User | Master_Port |
Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File
          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL
_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicat
e_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Er
rno | Last_Error
                                                 | Skip_Counter | Exec_Master_Lo
g_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Mas
ter_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Ma
ster_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Ser
ver_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error

                       | Replicate_Ignore_Server_Ids | Master_Server_Id | Master
_UUID                          | Master_Info_File
        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Ret
ry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Ma
ster_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Aut
o_Position |
+----------------------------------+--------------+-------------+-------------+-
--------------+------------------+---------------------+------------------------
----------+---------------+-----------------------+------------------+----------
---------+-----------------+---------------------+--------------------+---------
---------------+-------------------------+-----------------------------+--------
----+---------------------------------------------------------------------------
-------------------------------------------------+--------------+---------------
------+-----------------+-----------------+----------------+---------------+----
----------------+--------------------+--------------------+-----------------+---
----------------+----------------+-----------------------+----------------------
---------+---------------+---------------+----------------+---------------------
--------------------------------------------------------------------------------
-----------------------+-----------------------------+------------------+-------
-------------------------------+------------------------------------------------
--------+-----------+---------------------+-------------------------+-----------
---------+-------------+-------------------------+--------------------------+---
-------------+--------------------+--------------------+-------------------+----
-----------+
| Waiting for master to send event | 46.249.47.12 | slave       |        3306 |
           60 | mysql-bin.000006 |             8293811 | WIN-VVIN4NIIEBT-relay-b
in.000002 |        673805 | mysql-bin.000003      | Yes              | No
         |                 |                     |                    |
               |                         |                             |       1
051 | Error 'Unknown table 'sapp.test'' on query. Default database: 'sapp'. Quer
y: 'DROP TABLE `test` /* generated by server */' |            0 |            146
85952 |        20584142 | None            |                |             0 | No
                |                    |                    |                 |
                |                |                  NULL | No
         |             0 |               |           1051 | Error 'Unknown table
 'sapp.test'' on query. Default database: 'sapp'. Query: 'DROP TABLE `test` /* g
enerated by server */' |                             |                1 | ef0316
ab-5ce8-11e3-a0b5-a0369f030dfe | C:\ProgramData\MySQL\MySQL Server 5.6\data\mast
er.info |         0 |                NULL |                         |
   86400 |             |                         | 131223 11:44:46          |
             |                    |                    |                   |
         0 |
+----------------------------------+--------------+-------------+-------------+-
--------------+------------------+---------------------+------------------------
----------+---------------+-----------------------+------------------+----------
---------+-----------------+---------------------+--------------------+---------
---------------+-------------------------+-----------------------------+--------
----+---------------------------------------------------------------------------
-------------------------------------------------+--------------+---------------
------+-----------------+-----------------+----------------+---------------+----
----------------+--------------------+--------------------+-----------------+---
----------------+----------------+-----------------------+----------------------
---------+---------------+---------------+----------------+---------------------
--------------------------------------------------------------------------------
-----------------------+-----------------------------+------------------+-------
-------------------------------+------------------------------------------------
--------+-----------+---------------------+-------------------------+-----------
---------+-------------+-------------------------+--------------------------+---
-------------+--------------------+--------------------+-------------------+----
-----------+
1 row in set (0.00 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+-------------------------------+-----------------
-+-------------------+
| File             | Position | Binlog_Do_DB                  | Binlog_Ignore_DB
 | Executed_Gtid_Set |
+------------------+----------+-------------------------------+-----------------
-+-------------------+
| mysql-bin.000005 |      714 | joomlatest,sapp,tcadmin,whmcs |
 |                   |
+------------------+----------+-------------------------------+-----------------
-+-------------------+
1 row in set (0.00 sec)

But when I create a new table on each server it's not being synced. Both servers have a server status of Waiting for master to send event.

I've tried restarting the servers a couple of time but this didn't help.

What I also noticed is that when I first started master2 it would synch changes to master1 but not the other way around. I haven't been able to reproduce that.

How can I fix this?

share

migration rejected from stackoverflow.com Dec 24 '13 at 14:53

This question came from our site for professional and enthusiast programmers. Votes, comments, and answers are locked due to the question being closed here, but it may be eligible for editing and reopening on the site where it originated.

closed as off-topic by Jack Douglas Dec 24 '13 at 14:53

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

    
Full output of SHOW SLAVE STATUS and SHOW MASTER STATUS from each server is likely useful here. –  AndySavage Dec 23 '13 at 17:03
    
The SHOW {MASTER/SLAVE} STATUS is a lot more readable if executed with \G instead of ; –  fredrik Dec 23 '13 at 17:13
1  
It seems you have a few DROP TABLE statements that weren't able to execute, causing your salve SQL to stop running. The information is right there in your status. –  Mike Brant Dec 23 '13 at 17:20

1 Answer 1

I've resynched the database on both servers. Now it's working fine.

share
    
This should be a comment, not an answer !!! –  RolandoMySQLDBA Dec 24 '13 at 14:46
    
Please use the edit link on your question to add additional information. The Post Answer button should be used only for complete answers to the question. –  RolandoMySQLDBA Dec 24 '13 at 14:46
    
@Rolando if it's the answer it's much more use here than as a comment. Of course it means the question is too localised! –  Jack Douglas Dec 24 '13 at 14:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.