List:Replication« Previous MessageNext Message »
From:Rick James Date:June 30 2011 11:09pm
Subject:Re: Mysql 5.5 multi master replication
View as plain text  
A slave can have only one master.

You have binlog_do_db, so adding replicate_do_db would be redundant.  
(binlog... filters on the Master; replicate... filters on the Slave.)

On 6/24/11 5:14 PM, Alexis Guajardo wrote:
> well then doesn't replicate-do-db need to be set?
>
> I thought this was a slave with two masters, but it appears more basic.
>
> On Fri, Jun 24, 2011 at 5:07 PM, Rick James<rjames@stripped>  wrote:
>> A "master" starts a new binlog whenever it is restarted, or a FLUSH LOGS is
>> performed.  (And possibly other times.)  Each of the two machines is both a
>> Master and a Slave.  I would _guess_ that one of those things happened.
>>
>> Note, that also means that the offset into the binlogs is likely to get out
>> of sync.  This is normal.
>>
>> The relay logs are similarly restarted at potentially inconsistent times,
>> leading to divergent names and/or positions.
>>
>> On 6/24/11 4:56 PM, Alexis Guajardo wrote:
>>> This might be basic but did you set the bin log position on both
>>> servers?  from the slave status it looks like one is at 000008 and one
>>> is at 000007
>>>
>>>
>>>
>>> On Fri, Jun 24, 2011 at 1:56 PM, Rick James<rjames@stripped>   
> wrote:
>>>> I assume that 'replpassword' is not the real password?  (It does not
>>>> match
>>>> the GRANT.)
>>>>
>>>> There must be an error file somewhere.
>>>>
>>>> SHOW SLAVE STATUS has enough information to say that the connection was
>>>> made.  Plz provide an example of a statement that fails to replicate.
>>>>   (Be
>>>> specific about what database that statement hits.)
>>>>
>>>> Use mysqlbinlog to dump the binlogs (don't provide the info here).  See
>>>> if
>>>> there is something other than a header in each.
>>>>
>>>> On 6/23/11 5:52 PM, list@stripped wrote:
>>>>> On Thu, 23 Jun 2011 16:40:26 -0700, Rick
> James<rjames@stripped>
>>>>> wrote:
>>>>>> "no luck" is rather vague.
>>>>>>
>>>>>> For both servers...
>>>>>> What does mysqld.err say?
>>>>>> SHOW SLAVE STATUS \G
>>>>>> SHOW MASTER STATUS;
>>>>>> SHOW GRANTS FOR repl@'%';
>>>>>>
>>>>>> Rick James - MySQL Geek
>>>>> Rick,
>>>>>
>>>>> There is no mysqld.err or mysql-bin.err file anywhere on the server,
> but
>>>>> here are the other configs.
>>>>>
>>>>> Server 1:
>>>>> ----------------------------
>>>>> Slave status
>>>>>
>>>>> *************************** 1. row ***************************
>>>>>                 Slave_IO_State: Waiting for master to send event
>>>>>                    Master_Host: 10.123.70.11
>>>>>                    Master_User: repl
>>>>>                    Master_Port: 3306
>>>>>                  Connect_Retry: 60
>>>>>                Master_Log_File: mysql-bin.000007
>>>>>            Read_Master_Log_Pos: 107
>>>>>                 Relay_Log_File: mysqld-relay-bin.000021
>>>>>                  Relay_Log_Pos: 253
>>>>>          Relay_Master_Log_File: mysql-bin.000007
>>>>>               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: 107
>>>>>                Relay_Log_Space: 556
>>>>>                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: 2
>>>>>
>>>>> Master status:
>>>>>
>>>>> +------------------+----------+--------------+------------------+
>>>>> | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
>>>>> +------------------+----------+--------------+------------------+
>>>>> | mysql-bin.000008 |      107 | cmgapp,puma  |                  |
>>>>> +------------------+----------+--------------+------------------+
>>>>>
>>>>> Grants:
>>>>>
>>>>> SHOW GRANTS FOR repl@'10.123.70.11'\G;
>>>>> *************************** 1. row ***************************
>>>>> Grants for repl@stripped: GRANT REPLICATION SLAVE ON *.* TO
>>>>> 'repl'@'10.123.70.11' IDENTIFIED BY PASSWORD
>>>>> '*8EFB117805381DD9B0A1D5D9BAB4D1EB7AD76198'
>>>>>
>>>>>
>>>>> Server 2
>>>>> ----------------------------
>>>>>
>>>>> *************************** 1. row ***************************
>>>>>                 Slave_IO_State: Waiting for master to send event
>>>>>                    Master_Host: 10.123.70.10
>>>>>                    Master_User: repl
>>>>>                    Master_Port: 3306
>>>>>                  Connect_Retry: 60
>>>>>                Master_Log_File: mysql-bin.000008
>>>>>            Read_Master_Log_Pos: 107
>>>>>                 Relay_Log_File: mysqld-relay-bin.000013
>>>>>                  Relay_Log_Pos: 253
>>>>>          Relay_Master_Log_File: mysql-bin.000008
>>>>>               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: 107
>>>>>                Relay_Log_Space: 556
>>>>>                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
>>>>>
>>>>> Master status:
>>>>>
>>>>> +------------------+----------+--------------+------------------+
>>>>> | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
>>>>> +------------------+----------+--------------+------------------+
>>>>> | mysql-bin.000007 |      107 | cmgapp,puma  |                  |
>>>>> +------------------+----------+--------------+------------------+
>>>>>
>>>>> Grants:
>>>>>
>>>>> SHOW GRANTS FOR repl@'10.123.70.10'\G;
>>>>> *************************** 1. row ***************************
>>>>> Grants for repl@stripped: GRANT REPLICATION SLAVE ON *.* TO
>>>>> 'repl'@'10.123.70.10' IDENTIFIED BY PASSWORD
>>>>> '*8EFB117805381DD9B0A1D5D9BAB4D1EB7AD76198'
>>>>>
>>>> --
>>>> Rick James - MySQL Geek
>>>>
>>>>
>>>> --
>>>> MySQL Replication Mailing List
>>>> For list archives: http://lists.mysql.com/replication
>>>> To unsubscribe:
>>>>   http://lists.mysql.com/replication?unsub=1
>>>>
>>>>
>>>
>> --
>> Rick James - MySQL Geek
>>
>>
>
>

-- 
Rick James - MySQL Geek

Thread
Mysql 5.5 multi master replicationlist23 Jun
  • Re: Mysql 5.5 multi master replicationRick James24 Jun
Re: Mysql 5.5 multi master replicationRick James24 Jun
  • Re: Mysql 5.5 multi master replicationAlexis Guajardo25 Jun
    • Re: Mysql 5.5 multi master replicationRick James25 Jun
      • Re: Mysql 5.5 multi master replicationAlexis Guajardo25 Jun
        • Re: Mysql 5.5 multi master replicationRick James2 Jul
          • Re: Mysql 5.5 multi master replicationAlexis Guajardo2 Jul