MySQL Database Replication Problem

Discussion in 'HOWTO-Related Questions' started by black_magik, Jul 1, 2010.

  1. black_magik

    black_magik New Member

    I've read the article of Falko Timme at:

    http://www.howtoforge.com/mysql_database_replication

    and i've follow all the instruction to make a MySQL replication between
    two servers, one with Centos 5.5, MySQL 5.0.77 and one with Gentoo Base
    System release 1.12.13 and MySQL 5.0.84
    i want to replicate 5 different databases and i've added them in my.cnf
    in this way on the master:

    log-bin = /var/log/mysql/mysql-bin.log
    binlog-do-db=database1
    binlog-do-db=database2
    binlog-do-db=database3
    binlog-do-db=database4
    binlog-do-db=database5
    server-id=1

    and on the slave:

    server-id=2
    master-host=XXX.XXX.XXX.XXX (the master address)
    master-user=slave_user
    master-password=pwd
    master-connect-retry=60
    replicate-do-db=database1
    replicate-do-db=database2
    replicate-do-db=database3
    replicate-do-db=database4
    replicate-do-db=database4

    for the rest i've follow step-by-step all the instruction on the guide
    and i didn't receive any error.
    But doesn't work anything!!!!

    The db doesn't replicate

    Can you help me please

    Thanks
     
  2. Mark_NL

    Mark_NL New Member

    on the slave:

    Code:
    slave start
    ?

    what's the output of
    Code:
    show slave status
     
  3. black_magik

    black_magik New Member

    i've already do the slave start, but nothing it do; if i do:

    show slave status this is the answer:

    +----------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
    | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
    +----------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
    | | 192.168.3.16 | slave_user | 3306 | 60 | mysql-bin.000010 | 98 | mysqld-relay-bin.000001 | 98 | mysql-bin.000010 | No | Yes | website,eucilia,euclyd,aaveye,cardiogenet | | | | | | 0 | | 0 | 98 | 98 | None | | 0 | No | | | | | | NULL |
    +----------------+--------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-------------------------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
    1 row in set (0.00 sec)
     
  4. Mark_NL

    Mark_NL New Member

    Well the replication is not running, check both servers mysql error log files for errors.

    mostly they're called hostname.err
     
  5. black_magik

    black_magik New Member

    And WHERE is located this file ???
    in /var/log/mysql
    because if it is located there is not present
     
  6. falko

    falko Super Moderator ISPConfig Developer

    Please check /var/log/syslog and/or /var/log/messages for MySQL errors.
     
  7. black_magik

    black_magik New Member

    In the /var/log/messages (both maste and slave) i didn't find anything about mysql but if i do the command:

    tail -f /var/log/mysql/mysqd.err on the slave i receive:

    100623 14:01:34 [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).
    100623 14:01:34 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000008', position 98
    100623 14:02:10 [Note] Error reading relay log event: slave SQL thread was killed
    100623 14:04:09 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000010' at position 98, relay log './mysqld-relay-bin.000001' position: 4
    100623 14:04:09 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysql-bin.000010' at position 98
    100623 14:04:09 [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).
    100623 14:04:09 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000010', position 98
    100701 12:22:44 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysql-bin.000010' at position 98
    100701 12:22:44 [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).
    100701 12:22:44 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000010', position 98

    i didn't understand wich id is equal, because i've set:
    server-id=1 on the master and:
    server-id=2 on the slave (as you see in the previous post)
     
  8. falko

    falko Super Moderator ISPConfig Developer

    Did you restart MySQL on both nodes?
     
  9. black_magik

    black_magik New Member

    Yes, of course
     
  10. falko

    falko Super Moderator ISPConfig Developer

    Do you have more than one slave server?

    Did you try a reboot?
     
  11. black_magik

    black_magik New Member

    I've only one master and one slave; maybe i must to try to reboot system; what do you suggest me to reboot: master, slave or both
    Thanks
     
  12. falko

    falko Super Moderator ISPConfig Developer

    I'd reboot both.
     

Share This Page