HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   My SQL Replication issue ? (http://www.howtoforge.com/forums/showthread.php?t=48632)

jithinkcs 9th September 2010 08:02

My SQL Replication issue ?
 
3 Attachment(s)
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.

Mark_NL 9th September 2010 12:07

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.

jithinkcs 9th September 2010 15:30

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','POSTG RESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTI ONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL3 23','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_B ACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_AL L_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALI D_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','POSTG RESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTI ONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL3 23','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_B ACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_AL L_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','POSTG RESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTI ONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL3 23','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_B ACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_AL L_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALI D_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','POSTG RESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTI ONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL3 23','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_B ACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_AL L_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)

Mark_NL 9th September 2010 16:00

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

jithinkcs 10th September 2010 12:59

Yes i imported the database using ,

LOAD DATA FROM MASTER;

falko 10th September 2010 16:47

Please try the SQL dump method.


All times are GMT +2. The time now is 01:09.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.