MySQL replication issue

Discussion in 'Server Operation' started by catdude, Jul 15, 2011.

  1. catdude

    catdude ISPConfig Developer ISPConfig Developer

    I'm trying to set up MySQL replication between two servers. This will eventually expand to circular multi-master replication, but I need to get one-way replication between two machines working first.

    Both sides running version: 5.1.52 Source distribution on Amazon Linux. I'm working with the directions at http://www.howtoforge.com/mysql_master_master_replication

    My machines are cpu1 and cpu2. On cpu2 (the replication master) /etc/my.cnf contains:

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    default-storage-engine=MyISAM
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    skip-innodb

    server-id=2
    auto_increment_increment=10
    auto_increment_offset=2

    log-bin

    binlog-do-db=cphulkd
    binlog-do-db=cst_accounts
    binlog-do-db=custom
    binlog-do-db=iowa
    binlog-do-db=mcc
    binlog-do-db=mcc_users
    binlog-do-db=modsec
    binlog-do-db=mpi
    binlog-do-db=mysql
    binlog-do-db=noc
    binlog-do-db=online_encoding
    binlog-do-db=play_details
    binlog-do-db=ppv
    binlog-do-db=reporting
    binlog-do-db=support

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid


    On cpu1 (the intended replicaqtion slave) the /etc/my.cnf file contains:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    default-storage-engine=MyISAM
    skip-innodb

    server-id=3
    auto-increment-increment=10
    auto-increment-offset=3

    #master-host=10.2.13.160
    master-host=50.16.113.13
    master-user=<my replication user name>
    master-password=<my replication password)
    master-port=3306


    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid


    I stop/start cpu2 and get normal indications in log file.

    I stop/start cpu1 and the log file contains the following:
    110715 16:53:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    110715 16:53:48 [Warning] 'for replication startup options' is deprecated and will be removed in a future release. Please use ''CHANGE MASTER'' instead.
    110715 16:53:48 [Note] Plugin 'InnoDB' is disabled.
    110715 16:53:48 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
    110715 16:53:48 [Note] Event Scheduler: Loaded 0 events
    110715 16:53:48 [Note] /usr/libexec/mysqld: ready for connections.
    Version: '5.1.52' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
    110715 16:53:48 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 106, relay log './mysqld-relay-bin.000011' position: 4
    110715 16:53:48 [Note] Slave I/O thread: connected to master 'test@:3306',replication started in log 'FIRST' at position 4
    110715 16:53:48 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593
    110715 16:53:48 [Note] Slave I/O thread exiting, read up to log 'FIRST', position 4


    The next to last log line is what has me confused. You can clearly see from the my.cnf files above that the master's my.cnf contains "server-id=2" while the slave's my.cnf contains "server-id=3".

    Any idea what's going on with that error message?
     
  2. falko

    falko Super Moderator ISPConfig Developer

  3. catdude

    catdude ISPConfig Developer ISPConfig Developer

    Yes. I have made sure to restart both master and slave MySQL processes every time I've changed anything.
     

Share This Page