My SQL Replication issue ?

Discussion in 'Server Operation' started by jithinkcs, Sep 9, 2010.

  1. jithinkcs

    jithinkcs New Member

    Hi Guys,


    I have two My SQL servers running on two different machines.

    PC -1 ( Master)
    ****
    Version :My SQL 5.1.30
    IP : 192.168.0.97

    PC -2 ( Slave)
    ****
    Version : My SQL 5.1.30
    IP : 192.168.0.37


    I have two databases running on PC-1. After the successfull configuration of MySQL replication , i started the slave. And i found that the the database 1 is replicating fine . At the same time database 2 is not at all replicating . So in order to check out the database 2 replication issue , i just made some changes to database 2 in PC-1 (matster) and check for the changes in PC-2 ( Slave). Unfortunatly its not replicating , and i found that database-1 also failed the replication. Again i changed the configuration in my.ini in master and slave. But the Database1 is replicating and databse 2 failed in replication.

    Please help me to solve the issue .. I attached my master my.ini,slave my.ini file and mysql log of slave.

    I configured musql replication based on this link http://www.howtoforge.com/mysql_database_replication.
     

    Attached Files:

  2. Mark_NL

    Mark_NL New Member

    You should try and understand the slave_mysql_log.txt file ..

    Code:
    100909 11:11:48 [ERROR] Slave SQL: Error 'Duplicate entry '693' for key 'PRIMARY'' on query. Default database: 'web2project'. Query: 'INSERT INTO `user_access_log`(`user_id`,`date_time_in`,`user_ip`) VALUES ('1',NOW(),'192.168.0.37')', Error_code: 1062
    Looks pretty straight forward to me ..

    you're replication is inconsistent and needs to be setup all over again ..

    stop slave on pc2
    lock tables on pc1
    read master log pos on pc1
    mysqldump from pc1
    unlock tables on pc1
    import mysqldump in pc2
    set masterlog pos on pc2
    start slave on pc2

    done.
     
  3. jithinkcs

    jithinkcs New Member

    Hi Mark,

    Thanks for responding to my post. I just followed your suggestion and my
    DB 1 replicates fine , but DB2 replication failed.

    Please help me to solve the issue ?

    My Slaves MY SQL log


    100909 18:40:43 InnoDB: Started; log sequence number 0 46469
    100909 18:40:43 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
    100909 18:40:43 [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=wins_pc_c33_1-relay-bin' to avoid this problem.
    100909 18:40:43 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 18598, relay log '.\wins_pc_c33_1-relay-bin.000003' position: 2115
    100909 18:40:43 [Note] Slave I/O thread: connected to master 'jith@192.168.0.97:3306',replication started in log 'mysql-bin.000002' at position 18598
    100909 18:40:43 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_Z
    100909 18:40:43 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
    100909 18:40:43 [Note] wampmysqld: ready for connections.
    Version: '5.1.30-community-log' socket: '' port: 3306 MySQL Community Server (GPL)
    100909 18:49:50 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
    100909 18:49:50 [Note] Slave I/O thread killed while reading event
    100909 18:49:50 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000002', position 22095
    100909 18:49:50 [Note] Error reading relay log event: slave SQL thread was killed
    100909 18:49:55 [Note] wampmysqld: Normal shutdown

    100909 18:49:57 [Warning] wampmysqld: Forcing close of thread 3 user: 'root'

    100909 18:49:57 InnoDB: Starting shutdown...
    100909 18:49:58 InnoDB: Shutdown completed; log sequence number 0 46469
    100909 18:49:58 [Note] wampmysqld: Shutdown complete

    100909 18:50:01 InnoDB: Started; log sequence number 0 46469
    100909 18:50:01 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
    100909 18:50:01 [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=wins_pc_c33_1-relay-bin' to avoid this problem.
    100909 18:50:01 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 22095, relay log '.\wins_pc_c33_1-relay-bin.000005' position: 3748
    100909 18:50:01 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_Z
    100909 18:50:01 [Note] Slave I/O thread: connected to master 'jith@192.168.0.97:3306',replication started in log 'mysql-bin.000002' at position 22095
    100909 18:50:01 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
    100909 18:50:01 [Note] wampmysqld: ready for connections.
    Version: '5.1.30-community-log' socket: '' port: 3306 MySQL Community Server (GPL)
     
  4. Mark_NL

    Mark_NL New Member

    Did you dump and imported the complete database? (including the mysql database?)
     
  5. jithinkcs

    jithinkcs New Member

    Yes i imported the database using ,

    LOAD DATA FROM MASTER;
     
  6. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Please try the SQL dump method.
     

Share This Page