mysql replication successful but slave not replicating

Question

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

Answer 1

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?

Answer 2

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!

Answer 3

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.

Answer 4

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