Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 9th September 2010, 08:02
jithinkcs jithinkcs is offline
Junior Member
 
Join Date: Sep 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default My SQL Replication issue ?

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
File Type: txt my.ini Master.txt (5.1 KB, 199 views)
File Type: txt my.ini Slave.txt (5.1 KB, 191 views)
File Type: txt Slave mysql log.txt (7.9 KB, 460 views)
Reply With Quote
Sponsored Links
  #2  
Old 9th September 2010, 12:07
Mark_NL Mark_NL is offline
Senior Member
 
Join Date: Sep 2008
Location: The Netherlands
Posts: 912
Thanks: 12
Thanked 100 Times in 96 Posts
Default

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.
Reply With Quote
  #3  
Old 9th September 2010, 15:30
jithinkcs jithinkcs is offline
Junior Member
 
Join Date: Sep 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)
Reply With Quote
  #4  
Old 9th September 2010, 16:00
Mark_NL Mark_NL is offline
Senior Member
 
Join Date: Sep 2008
Location: The Netherlands
Posts: 912
Thanks: 12
Thanked 100 Times in 96 Posts
Default

Did you dump and imported the complete database? (including the mysql database?)
Reply With Quote
  #5  
Old 10th September 2010, 12:59
jithinkcs jithinkcs is offline
Junior Member
 
Join Date: Sep 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes i imported the database using ,

LOAD DATA FROM MASTER;
Reply With Quote
  #6  
Old 10th September 2010, 16:47
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,735 Times in 2,571 Posts
 
Default

Please try the SQL dump method.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
Reply

Bookmarks

Tags
error in replication, replication

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql ssl data replication problem certificate issue? isn Server Operation 5 30th July 2010 15:38
SQL Replication Error Gimly General 6 1st September 2009 23:43
[hardy heron] freeradius-mysql-daloradius kdclaver Installation/Configuration 1 12th August 2009 17:31
Chilispot on dd-wrt + freeradius / phpmyprepaid on Centos 5 wrong user password treeman HOWTO-Related Questions 0 31st May 2009 06:49
Mail Server Setup With Exim, MySQL, Cyrus-Imapd, Horde Webmail On Centos 5.1 - issue c0mrade HOWTO-Related Questions 25 19th April 2009 18:11


All times are GMT +2. The time now is 19:30.


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