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 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

share|improve this question
    
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 '13 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 | +------------------+----------+--------------+-------------------------+ –  Aboo Soyeed May 12 '13 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. –  Michael - sqlbot May 12 '13 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. –  Michael - sqlbot May 12 '13 at 18:19
add comment

migrated from stackoverflow.com May 12 '13 at 1:07

This question came from our site for professional and enthusiast programmers.

3 Answers

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?
share|improve this answer
    
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 | +-------+------+-----------+------+---------+------+-------+------------------+ –  Aboo Soyeed May 16 '13 at 21:21
2  
2-no ?? Weirder by the minute. I'm very unsure that your slave is actually connected to the right master. –  Shlomi Noach May 17 '13 at 19:55
add comment

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!

share|improve this answer
add comment

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

share|improve this answer
    
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 '13 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 '13 at 23:09
    
sorry my bad .. show master status yields +------------------+----------+--------------+-------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+-------------------------+ | mysql-bin.000004 | 106 | | mysql,informationschema | +------------------+----------+--------------+-------------------------+ –  Aboo Soyeed May 12 '13 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 –  Aboo Soyeed May 12 '13 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. –  Shlomi Noach May 13 '13 at 5:00
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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