I’m getting errors on the “mysql” database syncing error and it affects all three nodes which causes them to lock up.
Here’s node1 error log:
Here’s node2 error log:
And Here’s node3 error log:
Here’s is the mysql configuration
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock default-character-set=utf8 [mysqld] character_set_server=utf8 port = 3306 datadir = /var/lib/mysql tmpdir = /tmp socket = /var/run/mysqld/mysqld.sock max_connections = 90 transaction-isolation = 'READ-COMMITTED' user=mysql # caching and optimisations join_buffer_size = 4M table_cache = 4M table_open_cache = 4M thread_cache_size = 128 # see http://cl.ly/142s250i1S0o thread_stack = 1M tmp_table_size = 384M max_allowed_packet = 500M read_buffer_size = 256K 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 = 3 log_slow_queries = /var/log/mysql/log-slow-queries.log skip-external-locking skip-name-resolve #========# # InnoDB # #========# innodb_flush_method = ALL_O_DIRECT # see http://cl.ly/040b1B1H1I2C innodb_buffer_pool_size = 1024M # for 8GB RAM - it should be ~70-80% of the available RAM. innodb_log_buffer_size = 24M innodb_log_file_size = 100M # 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_import_table_from_xtrabackup = 1 # allows restoring a single table - see http://cl.ly/2F230t0D340A 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 #================================# # Galera synchronous replication # #================================# binlog_format = row binlog_cache_size = 128K innodb_locks_unsafe_for_binlog = 1 server-id = 1 # 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 innodb_autoinc_lock_mode = 2 query_cache_type = 0 query_cache_size = 0 default_storage_engine = InnoDB wsrep_cluster_name = "xxxxxxxx" wsrep_node_name = "node1" # change this on each node! wsrep_provider = /usr/lib/libgalera_smm.so wsrep_cluster_address = "gcomm://node2,node3" #"gcomm://db1:4567" set to "gcom://"" to reinitialise (reset) a node; otherwise ensure it is pointing to a valid node wsrep_slave_threads = 8 # 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) # 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 wsrep_sst_method = xtrabackup #Authentication for SST method wsrep_sst_auth="xxxxxx:xxxxxxxx" [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates default-character-set=utf8 [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [xtrabackup] target_dir = /var/lib/mysql
It has occurred twice lately and I’m not so sure how to fix this. I’ve only been restarting the nodes but I have no idea how to debug this.
Thanks for any help.