#1  
Old 1st July 2009, 13:04
lano lano is offline
Member
 
Join Date: May 2007
Posts: 94
Thanks: 10
Thanked 8 Times in 7 Posts
Default Mysql replication problem

I have two servers with ispconfig3 installed. First server is master, and second server is about to be used just as slave name server.
So I thought replicated database dbispconfig from master to slave.
Problem is that i'm getting errors, probably because both servers writes to second database. Here is output of SHOW SLAVE STATUS \G

Code:
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 194.19.21.20
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 757713
             Relay_Log_File: slave-relay.000002
              Relay_Log_Pos: 973
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: dbispconfig
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '1-disk_usage-1246442722' for key 1' on query. Default database: 'dbispconfig'. Query: 'INSERT INTO monitor_data (server_id, type, created, data, state) VALUES (1, 'disk_usage', 1246442722, 'a:4:{i:1;a:7:{s:2:\"fs\";s:8:\"/dev/md0\";s:4:\"type\";s:4:\"ext3\";s:4:\"size\";s:4:\"290G\";s:4:\"used\";s:4:\"6.0G\";s:9:\"available\";s:4:\"270G\";s:7:\"percent\";s:2:\"3%\";s:7:\"mounted\";s:1:\"/\";}i:2;a:7:{s:2:\"fs\";s:5:\"tmpfs\";s:4:\"type\";s:5:\"tmpfs\";s:4:\"size\";s:5:\"1014M\";s:4:\"used\";s:1:\"0\";s:9:\"available\";s:5:\"1014M\";s:7:\"percent\";s:2:\"0%\";s:7:\"mounted\";s:12:\"/lib/init/rw\";}i:3;a:7:{s:2:\"fs\";s:4:\"udev\";s:4:\"type\";s:5:\"tmpfs\";s:4:\"size\";s:3:\"10M\";s:4:\"used\";s:3:\"80K\";s:9:\"available\";s:3:\"10M\";s:7:\"percent\";s:2:\"1%\";s:7:\"mounted\";s:4:\"/dev\";}i:4;a:7:{s:2:\"fs\";s:5:\"tmpfs\";s:4:\"type\";s:5:\"tmpfs\";s:4:\"size\";s:5:\"1014M\";s:4:\"used\";s:1:\"0\";s:9:\"available\";s:5:\"1014M\";s:7:\"percent\";s:2:\"0%\";s:7:\"mounted\";s:8:\"/dev/s
               Skip_Counter: 0
        Exec_Master_Log_Pos: 202756
            Relay_Log_Space: 555930
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
Is there any way to skip or ignore just that table?
Maybe there is any other way to get automatic sync of dns records with ispconfig3/mydns?

Thanks, David
Reply With Quote
Sponsored Links
  #2  
Old 1st July 2009, 15:52
lano lano is offline
Member
 
Join Date: May 2007
Posts: 94
Thanks: 10
Thanked 8 Times in 7 Posts
Default

Problem solved.

On slave server in my.cnf in [mysqld] section I added:
Code:
master-host=master_ip_address
master-connect-retry=60
master-user=slave_user
master-password=slave_password
replicate-do-db=dbispconfig
replicate-wild-do-table=dbispconfig.dns_%

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
Let me remind you, that in my case I'm using second server just for the slave DNS server, so I need only DNS records from master.

... and recovered replication. To do that, you do:

On the slave:
Code:
cd /var/lib/mysql
rm *relay*
rm master.info
On the master:

Code:
cd /var/log/mysql
rm -f *
This should give you a fresh start on things. You can now start again doing database replication setup.

Source:
http://crazytoon.com/2008/04/21/mysq...ate-by-choice/

David
Reply With Quote
The Following User Says Thank You to lano For This Useful Post:
falko (2nd July 2009)
  #3  
Old 17th January 2010, 03:21
YbeddyJ YbeddyJ is offline
Junior Member
 
Join Date: Sep 2008
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
Default

I have similar problem as listed above

i did a maser slave replication except that i'm replicating the entire mysql database as i want to ensure that i backup all newly created tables and so on.
i'm only running mydns on the slave (ns2) see output below
Code:
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: master-bin.000158
        Read_Master_Log_Pos: 11212149
             Relay_Log_File: mysqld-relay-bin.000206
              Relay_Log_Pos: 62423635
      Relay_Master_Log_File: master-bin.000157
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '216' for key 1' on query. Default database: 'dbispconfig'. Query: 'INSERT INTO sys_datalog (dbtable,dbidx,
server_id,action,tstamp,user,data) VALUES ('dns_soa','id:4','1','u','1263592533','admin','a:2:{s:3:\"old\";a:18:{s:2:\"id\";s:1:\"4\";s:10:\"sys_userid\";s:1:\
"1\";s:11:\"sys_groupid\";s:1:\"0\";s:13:\"sys_perm_user\";s:4:\"riud\";s:14:\"sys_perm_group\";s:4:\"riud\";s:14:\"sys_perm_other\";s:0:\"\";s:9:\"server_id\"
;s:1:\"1\";s:6:\"origin\";s:11:\"XXX.XXX.XX.\";s:2:\"ns\";s:16:\"ns1.XXX.XXX.XXX.\";s:4:\"mbox\";s:23:\"postmaster.XXXX.XX.XX.\";s:6:\"serial\";s:10:\"2009090
401\";s:7:\"refresh\";s:5:\"28800\";s:5:\"retry\";s:4:\"7200\";s:6:\"expire\";s:6:\"604800\";s:7:\"minimum\";s:5:\"86400\";s:3:\"ttl\";s:5:\"86400\";s:6:\"acti
ve\";s:1:\"Y\";s:4:\"xfer\";s:12:\"NN.NN.NN.NN\";}s:3:\"new\";a:18:{s:2:\"id\";s:1:\"4\";s:10:\"sys_userid\";s:1:\"1\";s:11:\"sys_groupid\";s:1:\"0\";s:13:\"s
ys_perm_user\";s:4:\"riud\";s:14:\"sys_perm_group\";s:4:\"riud\";s:14:\"sys_perm_other\";s:0:\"\";
               Skip_Counter: 0
        Exec_Master_Log_Pos: 62423497
            Relay_Log_Space: 78392887
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.00 sec)
Reply With Quote
  #4  
Old 17th January 2010, 03:28
YbeddyJ YbeddyJ is offline
Junior Member
 
Join Date: Sep 2008
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
Default

The last time i had that same type of error i almost had to redo the whole master-slave replication thing.
after doing that the error returned and i had to do the following to get over it

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 10;
Reply With Quote
  #5  
Old 17th January 2010, 03:57
YbeddyJ YbeddyJ is offline
Junior Member
 
Join Date: Sep 2008
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
Default

my Question is, Is it posible to exclude a particular table from the replication effort?
Reply With Quote
  #6  
Old 17th January 2010, 08:34
lano lano is offline
Member
 
Join Date: May 2007
Posts: 94
Thanks: 10
Thanked 8 Times in 7 Posts
Default

Best practice would be, that you do fresh install of mydns, at setup name mydns mysql database dbispconfig instead of default database name, and then simply replicate whole database (or just dbispconfig table).
Just make sure, that you don't make any entry on ns2 server.

David
Reply With Quote
  #7  
Old 1st February 2010, 18:56
lano lano is offline
Member
 
Join Date: May 2007
Posts: 94
Thanks: 10
Thanked 8 Times in 7 Posts
 
Default

Quote:
Originally Posted by YbeddyJ View Post
my Question is, Is it posible to exclude a particular table from the replication effort?
Yes, you can:
in my.cnf on slave server you put
Code:
replicate-ignore-table = dbname.tablename
This is very useful and often ignored. If you have logging table which you only do writes to but never read from, there is no real point to replicate that table to slave(s). This way you ignore specific tables.

Second option is to use wildcards:
Code:
replicate-wild-ignore-table=dbname.tablename%

And here are some more tips:
Code:
replicate-do-db = dbname (or) replicate-do-db = dbname1, dbname2, Ö, dbnameN
This option is used on slave server to tell the server to only replicate dbname db on this particular host. You would want to use this if you have a master which is replicating to multiple slaves and each slave may contain different database for read performance reasons.

Code:
replicate-do-table = dbname.tablename
This specifies a table from a database to be replicated.

Code:
replicate-wild-do-table=dbname.tablename%
Letís say you have database with multiple type of open source installations (phpbb, wordpress, drupal, etc), and you want to designate slave(s) to only be used for phpbb, you would do:
Code:
replicate-wild-do-table=dbname.phpbb%
Reply With Quote
The Following User Says Thank You to lano For This Useful Post:
falko (2nd February 2010)
Reply

Bookmarks

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
sending e-mail using mail() function linuxuser1 HOWTO-Related Questions 38 21st April 2009 12:20
Problem on editing data on MySQL table satimis Server Operation 6 12th December 2008 00:52
mysql apache charset problem foxphiles Server Operation 2 7th November 2006 16:41
postfix - MySQL connection problem epo Installation/Configuration 7 4th August 2006 20:49
Problem with Virtual Users And Domains With Postfix, Courier And MySQL Howto fenix Server Operation 4 11th March 2006 02:53


All times are GMT +2. The time now is 16:26.


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