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 made a master(A)-slave(B)-slave(C) mode MySQL instances for backup. But today when I see whether the mode works fine, I find A and B are OK. That is B can get binlog from A, but B does not write updates to its binlog. So C cannot sync with B.

After some investigation, I see that B's new binlog file is 120 bytes and there are not any errors in MySQL's log. The sql_log_bin is ON. So I don't know what can be the reason that causes the problem. What I can supply as an addition is that the new binlog's create time is when I made a mysqldump on B. So I guess there may be some relationship between the mysqldump event and the binlog write event? Hopefully someone can help me out, thanks a lot.

B's master status:

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysqlbin.000016 |      120 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

B's binlog file size status:

ll /data/mysql/
-rw-rw----. 1 mysql mysql        166 7月   7 02:00 mysqlbin.000015
-rw-rw----. 1 mysql mysql        120 7月   7 02:00 mysqlbin.000016
-rw-rw----. 1 mysql mysql        288 7月   7 02:00 mysqlbin.index

B's mysqldump SQL:

mysqldump --master-data=2 --all-databases --flush-logs --user=xxx  --socket=/tmp/mysqld.sock -A > /tmp/backup.sql
share|improve this question

1 Answer 1

up vote 1 down vote accepted

You have chain replication as

Master--> SLAVE A--> SLAVE B (Slave A is Slave of Master and Slave B is slave of Slave A)

As you told On Slave Server(A) binary log is enabled.

You additionally need to enable log_slave_updates because a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log.

So you need to enable log_slave_updates on Slave A.

Please have a look at Improving Replication Performance.

share|improve this answer

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.