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
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 [B]replicate-wild-do-table=dbispconfig.dns_%[/B] 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/mysql-replication-replicate-by-choice/ David
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)
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;
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
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%
