I have 2 locations, a corporate office which has a server with mysql 5.6.17 installed. This houses the UI which users enter data so that the website can be updated. It also holds all the data about 1TB so far, so that reports can be generated.
The other location is stored on AWS. It’s a percona cluster with mysql: 5.6.15-56. This holds data with respect to visitors and what they do on the site. We currently get 20-50 million clicks a data and instead of holding all the data on AWS and racking up major $$$ in HD space. Every day the data should be transferred from the AWS DB to the corporate office DB. At midnight, the DB of the AWS server will be cleared. So thats why only a small DB needs to be on the AWS instances of the cluster.
I have setup circular replication between the corporate office and the master of the xtradb cluster. Replication seems to work fine. Also within the cluster replication seems to work fine here.
However, it gets sticky when I do an insert from another node in the cluster which then gets replicated to the Master in the cluster and then replicated over to the corporate server. What happens is for some reason the row tries to get replicated back to the Master in the cluster and I get this. Last_SQL_Error: Could not execute Write_rows event on table tracking.foo_B; Duplicate entry ‘2’ for key ‘PRIMARY’, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event’s master log mysql-bin.000001, end_log_pos 488
But when I do an insert at the Master of the cluster, it gets replicated with no problems.
So let me break this down:
- 1 mysql instance, Site A.
- 1 xtradb cluster, Site B.
- Circular replication between the mysql instance and master of the xtradb cluster.
- With an insert on each master (site A or site B), no issues. Data gets replicated to all nodes.
- With an insert on another server of the xtra cluster (node 2, node 3) then the data gets replicated to all nodes. The xtradb master replicates to its slave (site A). Yet, the master (site A) then replicates back to the slave (site B) and I get the duplicate entry error.
Another thing to be aware of:
Tables from Site A, get replicated one way —> Site B.
Tables from Site B, get replicated one way —> Site A.
There is no cross transactions with the same table. Site A <----> Site B.
I have tried putting in replicate-ignore-table which I thought would solve the issue. However when its placed on the master of the cluster, when a transaction occurs on another node, it does not get replicated on the primary node.
Site A config:
## General ignore-db-dir = lost+found datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock tmpdir = /var/lib/mysqltmp skip-name-resolve ## Cache #table-cache = 150 table-definition-cache = 4096 table-open-cache = 4096 #table-open-cache-instances = 1 thread-cache-size = 150 query-cache-type = off query-cache-size = 0 query-cache-limit = 0 ## Per-thread Buffers #join-buffer-size = 512K #read-buffer-size = 512K #read-rnd-buffer-size = 512K #sort-buffer-size = 512K ## Temp Tables #max-heap-table-size = 64M #tmp-table-size = 32M ## Networking #interactive-timeout = 3600 max-connections = 400 max-connect-errors = 1000000 max-allowed-packet = 16M skip-name-resolve wait-timeout = 600 ## InnoDB #innodb-buffer-pool-size = 256M innodb-file-format = Barracuda #innodb-file-per-table = 1 #innodb-flush-method = O_DIRECT #innodb-log-file-size = 128M ## Replication and PITR binlog-format = ROW expire-logs-days = 7 log-bin = mysql-bin log-slave-updates = 1 #max-binlog-size = 128M #read-only = 1 relay-log = relay-bin relay-log-space-limit = 16G server-id = 1
Site B config:
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld] user = mysql port = 3306 datadir = /opt/mysql/data socket = /var/run/mysqld/mysqld.sock max_connections = 500 transaction-isolation = 'READ-COMMITTED' auto_increment_increment = 1 auto_increment_offset = 1 # caching and optimisations join_buffer_size = 128 #table_cache = 96 table_open_cache = 64 thread_cache_size = 128 # see http://cl.ly/142s250i1S0o thread_stack = 256K tmp_table_size = 256M max_allowed_packet = 500M read_buffer_size = 128K slow-query-log-file = /var/log/mysql/slow-queries.log # file must first be created and owned by the MySQL user - see http://cl.ly/142T211c0Z35 long_query_time = 5 skip-external-locking = 1 skip-name-resolve = 1 #========# # InnoDB # #========# innodb_flush_method = ALL_O_DIRECT # see http://cl.ly/040b1B1H1I2C innodb_buffer_pool_size = 5G # for 8GB RAM - it should be ~70-80% of the available RAM. innodb_log_buffer_size = 8M innodb_log_file_size = 50M # DO see http://cl.ly/3A3l2y170J29 and http://cl.ly/133W402C050v for more details on how to change this setting safely innodb_support_xa = 0 # OK with Galera synchronous replication innodb_flush_log_at_trx_commit = 0 # speeds things up, can lose 1 second worth of transactions if MySQL crashes. This is fine for us at the moment. innodb_doublewrite = 1 # ensures an incremental state transfer will be tried if possible - see http://cl.ly/1E0w1v0z1m0n innodb_file_per_table = 1 innodb_locks_unsafe_for_binlog = 1 # minimises risk of phantom updates/selects innodb_autoinc_lock_mode = 2 # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera #===================# # MySQL Replication # #===================# log-bin = mysql-bin relay-log = relay-bin log-slave-updates = 1 #================================# # Galera synchronous replication # #================================# binlog_format = row # In order for Galera to work correctly binlog format should be ROW server-id = 2 # change on each node! - not strictly required for Galera, but it's safer to have a unique id on each node in case normal replication is also used default_storage_engine = InnoDB # MyISAM storage engine has only experimental support wsrep_cluster_name = "percona-east-01" wsrep_node_name = "node1" # change this on each node! wsrep_node_address = 10.0.1.153 # Node #1 address wsrep_provider = /usr/lib64/libgalera_smm.so # Path to Galera library wsrep_cluster_address = gcomm://10.0.1.153,10.0.3.125,10.0.5.233 # set to "gcom://"" to reinitialise (reset) a node; otherwise ensure it is pointing to a valid node. Cluster connection URL contains the IPs of node#1, node#2 and node#3 wsrep_slave_threads = 2 # recommended: 4 slave thread per core - see http://cl.ly/0z0l0K3v3Y0j wsrep_retry_autocommit = 2 # how many times to retry deadlocked autocommits wsrep_convert_LOCK_to_trx = 0 # convert locking sessions into transactions; see http://cl.ly/2c0N3B0f1v41 wsrep_certify_nonPK = 1 # Generate fake primary keys for non-PK tables (required for multi-master and parallel applying operation) wsrep_sst_method=rsync # rsync or xtrabackup; remember to create the user 'mysql'@'localhost' on each node, with all privileges for the xtrabackup-based SST to work. see http://cl.ly/153r0A2k0G1N [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash prompt="(\u@\h) [\d]>\_"
It’s a bit of a shame that there is no wsrep options for ignoring tables rather than relying on replicate-do-db. That why I would be able to use that functionality between the Master servers in circular replication.
If anyone has any ideas, please let me know how to resolve this!