MySQL Replication Setup: Fixing ‘Empty Set’ Error in SHOW MASTER STATUS

When setting up MySQL replication, encountering an Empty set response from SHOW MASTER STATUS typically indicates that binary logging isn't properly configured or activated. This prevents the master server from tracking database changes that need to be replicated.

Your my.cnf shows good replication parameters, but let's verify the critical components:

[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = fruit
binlog-format = MIXED
expire-logs-days = 10
max_binlog_size = 100M

1. Binary Log Path Permissions: MySQL needs write access to the log directory:

sudo chown -R mysql:mysql /var/log/mysql/
sudo chmod -R 755 /var/log/mysql/

2. Missing binlog_format: Add this to your my.cnf:

binlog-format = ROW

Check if binary logging is enabled:

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

After configuration changes, always restart MySQL:

sudo service mysql restart

If issues persist, check MySQL's error log (location may vary):

sudo tail -f /var/log/mysql/error.log

For deeper diagnostics, run:

mysql> SHOW GLOBAL VARIABLES LIKE '%bin%';
mysql> SHOW MASTER LOGS;

If you're using MySQL 8.0+, consider using the new replication syntax:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='10.0.0.3',
    SOURCE_USER='repl_user',
    SOURCE_PASSWORD='password',
    SOURCE_AUTO_POSITION=1;

When setting up MySQL replication, encountering an "Empty set" response to SHOW MASTER STATUS typically indicates that binary logging isn't properly configured or activated. This command should display critical replication information including:

Current binary log file name
Position within the log file
Which databases are being logged

Let's examine the critical parts of your my.cnf configuration:

# Essential replication parameters
server-id = 1
log-bin = /var/log/mysql-replication.log
binlog-do-db = fruit

The key elements here appear correct, but there might be permission issues or path problems with the log-bin directive.

First, check if binary logging is actually enabled:

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

If this returns OFF, your configuration isn't being applied properly. Common reasons include:

MySQL not reading the correct my.cnf file
Syntax errors in the configuration
Insufficient permissions for the log directory

Here's a step-by-step approach to resolve this:

1. Verify Configuration File Loading

$ mysql --help | grep "my.cnf"

This shows which configuration files MySQL is reading. You might need to place your settings in a different file location.

2. Check Directory Permissions

$ sudo mkdir -p /var/log/mysql/
$ sudo chown -R mysql:mysql /var/log/mysql
$ sudo chmod -R 755 /var/log/mysql

3. Restart MySQL Properly

$ sudo service mysql restart
$ sudo tail -f /var/log/mysql/error.log

For modern MySQL versions (5.7+), consider this cleaner configuration:

[mysqld]
server-id = 1
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
binlog_do_db = fruit
sync_binlog = 1
expire_logs_days = 10

After making changes, run these diagnostic commands:

mysql> FLUSH LOGS;
mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER STATUS;

Using relative paths in log-bin directive
Not restarting MySQL after configuration changes
Conflicting parameters in multiple my.cnf files
SELinux or AppArmor blocking file access

When successful, SHOW MASTER STATUS should return something like:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | fruit        |                  |
+------------------+----------+--------------+------------------+