mysqld is not producing binary log

Question

I'm trying to understand why mysqld is not writing binary log anymore...

[root@ip-10-179-155-214 mysql]# grep ^log-bin /etc/my.cnf
log-bin=mysql-bin
[root@ip-10-179-155-214 mysql]# ll mysql-bin.*
-rw-rw----. 1 mysql mysql    28463 Jun 27 16:04 mysql-bin.000001
-rw-rw----. 1 mysql mysql  1038814 Jun 27 16:04 mysql-bin.000002
-rw-rw----. 1 mysql mysql      264 Jun 27 16:06 mysql-bin.000003
-rw-rw----. 1 mysql mysql      264 Jun 29 10:22 mysql-bin.000004
-rw-rw----. 1 mysql mysql 23751127 Jun 29 10:30 mysql-bin.000005
-rw-rw----. 1 mysql mysql      264 Jun 29 10:35 mysql-bin.000006
-rw-rw----. 1 mysql mysql 23755331 Jun 29 10:45 mysql-bin.000007
-rw-rw----. 1 mysql mysql      245 Jun 29 10:45 mysql-bin.000008
-rw-rw----. 1 mysql mysql      152 Jun 29 10:45 mysql-bin.index
[root@ip-10-179-155-214 mysql]# date
Thu Jul  3 17:55:13 EDT 2014
[root@ip-10-179-155-214 mysql]# ps auxww | grep mysqld
mysql     4131  0.0  0.0 115348  1688 ?        Ss   Jun29   0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql     4787  0.1  8.9 3822688 331928 ?      Sl   Jun29  10:13 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/ip-10-179-155-214.ec2.internal.err --open-files-limit=8192 --pid-file=ip-10-179-155-214.ec2.internal.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root     18996  0.0  0.0 112640   976 pts/2    S+   17:56   0:00 grep --color=auto mysqld
[root@ip-10-179-155-214 mysql]# 

any ideas what am I missing?

* UPDATE *

MariaDB [(none)]> select @@global.sql_log_bin;
+----------------------+
| @@global.sql_log_bin |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select NOW() - interval variable_value second from information_schema.global_status where variable_name='uptime';
+----------------------------------------+
| NOW() - interval variable_value second |
+----------------------------------------+
| 2014-06-29 10:45:14.000000             |
+----------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 

Answer 1

Things I'd check:

• Are any changes happening? Binary log doesn't include SELECT queries, only changes. Compare the timestamp on files like ib_logfile*.
• Are replication filters excluding changes being made?
• Are sessions making changes while SQL_LOG_BIN=0 is suppressing binary logging, either globally as @RolandoMySQLDBA suggests, or else per session?
• Disk is full?
• You are looking at binlogs in the wrong directory?

Answer 2

I just saw your comment

Seconds_Behind_Master is 0, so I assume replication itself is working fine, disk is not full and i'm looking at the right place

The problem is simple

• You have setup MySQL Replication
• The Slave has been running since 2014-06-29 10:45:14
  • mysql-bin.000007 has the same datetime
  • mysql-bin.000008 has the same datetime
  • mysql-bin.index has the same datetime
• Seconds_Behind_Master is 0, so replication is running fine.

Why are changes made on the the Slave not making it to its binary logs ?

You forgot to add log-slave-updates to my.cnf on the Slave.

I have written about this before : Multi-master + slaves replication in MySQL

Simply add log-slave-updates to my.cnf, restart mysql, and everything will be fine.

Give it a Try !!!