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.
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.
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 '[email protected]: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 '[email protected]: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)
