2

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
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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