Percona XtraDB and crash-safe replication

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi,

Is it possible to enable crash-safe replication on Percona XtraDB cluster?

When I tried to add the following variables the MySQL server refuses to start and it constantly print the same message in the error log:

/etc/my.cnf
…
relay_log_info_repository = TABLE
relay_log_recovery = 1
…

Error log:

It works when i tested this with standard Percona server
OS: CentOS 6.5
MySQL : Percona-XtraDB-Cluster-server-56-5.6.15-25.5.759

2

What is the purpose you are trying to enable it? Is any of the cluster members also an asyc slave of another mysql instance?

3

I’m not using Galera feature for now, just standard MySQL replication hence I would like to enable this option.

4

I cannot reproduce this kind of crash, can you post your full my.cnf? Does it crash right after mysqld start or only once some some trx is applied via replication?

5

It crashes, right after mysqld start.

[mysqld_safe]
log-error = /var/log/mysql/mysql-error.log

[mysqld]
user = mysql
port = 3306
bind_address = 0.0.0.0
socket = /var/lib/mysql/mysql.sock

datadir = /var/lib/mysql
tmpdir = /tmp
pid-file = /var/lib/mysql/mysql.pid

character-set-server = utf8
explicit_defaults_for_timestamp = 1

slow-query-log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 5

log-bin = /var/lib/mysql/log/mysql-bin
binlog-format = MIXED
expire_logs_days = 14
relay-log = /var/lib/mysql/log/mysql-relay
relay_log_info_file = /var/lib/mysql/log/relay-log.info
slave_load_tmpdir = /var/lib/mysql/log/tmp
server-id = 737731821

# Crash-safe replication
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1

#skip-slave-start

read_only

back_log = 50
max_connections = 500
max_connect_errors = 100
skip-name-resolve

key_buffer_size = 256M
sort_buffer_size = 2M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 128M
max_allowed_packet = 32M
max_heap_table_size = 64M
tmp_table_size = 64M

table_open_cache = 2048
thread_cache_size = 16

query_cache_type = 0
query_cache_size = 0

# InnoDB
innodb_data_home_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 512M
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 400
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysql]
no-auto-rehash
socket=/var/lib/mysql/mysql.sock

[client]
socket=/var/lib/mysql/mysql.sock
6

OK, binlog-format = MIXED was the key here. Please change it to ROW (as MIXED is not supported by Galera anyway), and try enabling crash safe again.

Bug reported here: [url]https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1313756[/url]