MySQL replication: 'show master status' shows 'Empty set (0.00 sec)'

Original illustration: mysql logo — the image file was not found in the original dataset.

After preparing two servers for MySQL replication with identical data, the next step is setting up replication. Run “show master status” on the master to obtain its bin-log and position for the CHANGE MASTER TO command on the slave. But instead of those values, “show master status” returned “Empty set (0.00 sec)”. The MySQL error log contained no information. I then ran “show binary logs” and received “ERROR 1381 (HY000): You are not using binary logging”, suggesting a problem creating binary log files.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 117905
Server version: 5.5.14-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show master status;
Empty set (0.00 sec)
 
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

How to fix it: First check whether /etc/my.cnf already contains log-bin. If not, add it as shown below: log-bin = /var/lib/mysql/log-bin If it is already present, the problem is the permissions on the directory holding the bin-log files. Set both its owner and group to mysql, as in this example:

[root@ezylinux ~]# service mysql stop
[root@ezylinux ~]# chown -R mysql:mysql /path/of/bin-log
[root@ezylinux ~]# service mysql start