Mysql replication problem

Discussion in 'Server Operation' started by lano, Jul 1, 2009.

  1. lano

    lano New Member

    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
     
  2. lano

    lano New Member

    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
     
  3. YbeddyJ

    YbeddyJ New Member

    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)
     
  4. YbeddyJ

    YbeddyJ New Member

    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;
     
  5. YbeddyJ

    YbeddyJ New Member

    my Question is, Is it posible to exclude a particular table from the replication effort?
     
  6. lano

    lano New Member

    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
     
  7. lano

    lano New Member

    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%
     

Share This Page