From: Rick James Date: June 30 2011 11:09pm Subject: Re: Mysql 5.5 multi master replication List-Archive: http://lists.mysql.com/replication/2112 Message-Id: <4E0D022C.5040800@yahoo-inc.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 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 >>>>> 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=alexis@stripped >>>> >>>> >>> >> -- >> Rick James - MySQL Geek >> >> > > -- Rick James - MySQL Geek