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

score 1 · Answer 1 · answered Jul 03 '14 at 22:18

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?

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

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.

mysqld is not producing binary log

2 Answers2

Give it a Try !!!