Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've been setting up MySQL master replication (on Debian 6.0.1) following these instructions faithfully: http://www.neocodesoftware.com/replication/

I've got as far as:

mysql > show master status;

but this is unfortunately producing the following, rather than any useful output:

Empty set (0.00 sec)

The error log at /var/log/mysql.err is just an empty file, so that's not giving me any clues.

Any ideas?

This is what I have put in /etc/mysql/my.cnf on one server (amended appropriately for the other server):

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 10.0.0.3
master-user = <myusername>
master-password = <mypass>
master-connect-retry = 60
replicate-do-db = fruit
log-bin = /var/log/mysql-replication.log
binlog-do-db = fruit

And I have set up users and can connect from MySQL on Server A to the database on Server B using the username/password/ipaddress above.

share|improve this question

migrated from superuser.com May 6 '11 at 5:27

This question came from our site for computer enthusiasts and power users.

    
I've also tried following the simpler instructions at: howtoforge.com/mysql_database_replication (on one server alone) and again, when I get to show master status I see Empty set. Baffled! – simon May 4 '11 at 21:01
    
Restart the service & check. If any error logs generated after restart, paste that also. – flower May 5 '11 at 10:06

Interestingly, I have mysql running on my PC with binary logs not enabled. I did the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.12 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

mysql>

As shown, since MySQL shows "Empty Set" for SHOW MASTER STATUS; because binary logging was not enabled. That's obvious given the configuration I have.

First thing you should do is make sure the error log has a specific folder

[mysqld]
log-error=/var/log/mysql/mysql.err
log-bin = /var/log/mysql/mysql-replication.log

Then run the following:

service mysql stop
mkdir /var/log/mysql
chown -R mysql:mysql /var/log/mysql
service mysql start

Then in the mysql client run these SQL Commands

SHOW MASTER STATUS;
SHOW BINARY LOGS;

If you get the same output I had before, then MySQL cannot write binary logs to the designated folder. Your dilemma becomes why MySQL cannot write to /var/log.

This is not a full answer but I hope this helps.

share|improve this answer
    
Setting /var/log to be recursively owned and with a group of mysql is going to break a linux system fairly badly. I strongly advise people not to do this. Instead, run that specifically on the log file which mysql is trying to write to, and if it's not there touch it first. – John Hunt Jul 9 '15 at 10:54
    
You know something, @JohnHunt ? You are right. I will change the folder. – RolandoMySQLDBA Jul 9 '15 at 15:09

If thhe Mysql version is >5.0, your replication settings master-host, master-user, master-password and a few others in your my.cnf will be ignored. Use CHANGE MASTER TO for initial replication setup.

Compare http://dev.mysql.com/doc/refman/5.1/en/replication-howto-slaveinit.html

share|improve this answer

Your setting for log-bin is incorrect, so MySQL can't write binary logs. It isn't a filename, it is a partial filename pattern, to which MySQL will prepend a directory and append a sequence number and extension. The usual setting is something like

log-bin=log-bin

Check the manual.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.