8

I have created a mysql master-slave configuration and things look fine . the " show master status;" on slave doesnt show any error . this is the output

 Slave_IO_State: Waiting for master to send event
                  Master_Host: 109.123.100.58
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             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: 106
              Relay_Log_Space: 106
              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: 

However on replication on slave doesnt seem to be happening when master undergoes change

On master . SHOW MASTER STATUS

+------------------+----------+--------------+-------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB        |
+------------------+----------+--------------+-------------------------+
| mysql-bin.000001 |   639495 |              | mysql,informationschema |
+------------------+----------+--------------+-------------------------+

on slave SHOW PROCESSLIST yields

+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| Id  | User        | Host      | db   | Command | Time  | State                                                                 | Info             |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
| 174 | system user |           | NULL | Connect | 25777 | Waiting for master to send event                                      | NULL             |
| 175 | system user |           | NULL | Connect | 25777 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 199 | root        | localhost | NULL | Query   |     0 | NULL                                                                  | show processlist |
+-----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Am i missing something

4
  • Two questions. Do you have the same version of MySQL running on master and slave? Do you have a different server id set in the config for the master/slave? edit: can we see the full SHOW MASTER STATUS too, the top bit is missing I think.
    – James C
    May 11, 2013 at 21:48
  • sorry my mistake the top bit was the output for SHOW SLAVE STATUS\G . show master status on slave yields +------------------+----------+--------------+-------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+-------------------------+ | mysql-bin.000004 | 106 | | mysql,informationschema | +------------------+----------+--------------+-------------------------+
    – Soyeed
    May 12, 2013 at 12:19
  • Do not look at SHOW MASTER STATUS on the slave. This information is not relevant unless the slave is also serving as a master for other slaves further downstream. May 12, 2013 at 18:16
  • If you SHOW PROCESSLIST on the master, do you see the incoming connection from the slave? You should. Also, I would try STOP SLAVE IO_THREAD; then START SLAVE IO_THREAD; on the slave and see how the output of SHOW SLAVE STATUS (on the slave) changes, if at all. May 12, 2013 at 18:19

4 Answers 4

5

The output you presented is very confusing. On one hand, the master shows:

mysql-bin.000001   639495 

The slave's PROCESSLIST indicates that replication works (the IO thread is connected to the master, the SQL thread is waiting).

The slave's SHOW SLAVE STATUS again claims all is well, but the master's position is 106.

Are all these outputs issued at roughly the same time? If not, then they are not valuable.

I would check the following:

  • The obvious: is 109.123.100.58 really your master?
  • Does SHOW PROCESSLIST on master list the connection made by the slave under the replica user?
  • Do you have different server-id settings for master and slave?
  • Do you have any replicate-do-* or replicate-ignore-* settings in your mysql.cnf file?
3
  • yeah the output are at the same time. 1. yeah 109.123.100.58 is the master . 2. no 3. yes 4. no . Show process list on master shows '+-------+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+------+-----------+------+---------+------+-------+------------------+ | 23243 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | +-------+------+-----------+------+---------+------+-------+------------------+
    – Soyeed
    May 16, 2013 at 21:21
  • 3
    2-no ?? Weirder by the minute. I'm very unsure that your slave is actually connected to the right master. May 17, 2013 at 19:55
  • 1
    Thanks for the tip! In my case I had M->S1->S2 replication, and my S2 server-id matched my M server-id.
    – Aaron R.
    Feb 6, 2015 at 19:48
1

Better than using binlog-ignore-db is to use slave-side filtering. I was stuck in a similar situation, yes, it took me a whole day to figure out:

I missed something big!!!

The difference between

--replicate-do-db=
--replicate-do-table=

On the slave side. And

--replicate-do-table='mydb.%'

didn't work for me on -do-table. I had to use

--replicate-do-db='mydb'

I guess this would have worked for a wildcard

--replicate-wild-do-table='mydb.%'

...Now on to figure out how to replicate the same table from multiple masters. Okay, I figured it out, MySQL doesn't support it natively; MariaDB 10 does.

I also had another problem initially: My password was longer than 32 characters. Boo!

1

I also came across this problem. The root cause was having the master's server_id=0. MariaDB has special behavior for server_id=0 which will not allow replication to work.

The server_id must be unique for each server in the replicating group. If left at 0, the default until MariaDB 10.2.1, a slave will not connect to a master, and a master will refuse all slave connections.

When I started indexing based on 1 everything worked fine.

-2

You didn't tell the slave which DB to replicate: the variable Replicate_Do_DB is empty.

Try to add the line

replicate-do-db=name_of_db (where "name_of_db" is the name of the database to replicate)

in your my.cnf (on slave) and restart the slave-server.

EDIT: I don't think my answer is the right solution, meanwhile ;-)

Perhaps have a look a this page instead: http://dev.mysql.com/doc/refman/5.1/en/replication-problems.html

6
  • I don't think that's correct. You can see that 639495 bytes have been logged to the master's binlog
    – James C
    May 11, 2013 at 21:47
  • The question is why replication is not working - not if something was written to the binlog. Usually the comand SHOW MASTER STATUS does not show more info.
    – Chris
    May 11, 2013 at 23:09
  • sorry my bad .. show master status yields +------------------+----------+--------------+-------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+-------------------------+ | mysql-bin.000004 | 106 | | mysql,informationschema | +------------------+----------+--------------+-------------------------+
    – Soyeed
    May 12, 2013 at 12:20
  • also i was told that if i dont meantion replicate-do-db it replicates all databases. instead i had the entry binlog_ignore_db = mysql binlog_ignore_db = informationschema
    – Soyeed
    May 12, 2013 at 12:23
  • 1
    Do not follow advice in this answer, as you don't need to tell database which schema to replicate (defaults to all schemas), and this would very possibly break your replication, and most probably not what you are looking for. You want (99.999%) to replicate all schemas. May 13, 2013 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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