Percona Cluster as replication slave - problems

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I’ve got a 3-node Percona cluster, which I’ve set up to be a replication slave of an old MySQL server, by replicating into node A (the others being B and C).

I can see that the cluster is functioning - if I make a change on A it is replicated to B; if I make a change on B it is replicated to A.

I can also see that the replication is working - new changes are arriving on A.

But these are not being replicated from A to B. On A I have log-slave-updates=true, and I can see mysqld-relay-bin* logfiles being produced.

Any suggestions for how I can diagnose why the consumed changes on A aren’t then being passed on to the other nodes in the cluster?

2

Can you please share my.cnf files from node A & B to check it further.

3

Here you are. Apologies for putting them inline, but when I try to upload as attachments it says they’re invalid files.

I’ve edited out the passwords, and some comments to get below the 10000 character limit.

As you can see they’re messy.

----------------Config for A

#############################################################

The following options will be passed to all MySQL clients

#############################################################

[client]

port = 3306
socket = /var/run/mysqld/mysqld.sock

#############################

mysqld - the MySQL server

#############################

[mysqld]

EH set these explicitly to allow xtrabackup to pick them up

datadir = /mnt/ssd/mysql

ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

EH end

port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M

EH change for replication issues

read_buffer_size = 2M
read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M
thread_cache_size = 8

EH No query cache with clustering?

query_cache_size = 0
query_cache_type = 0

Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 8

Don’t listen on a TCP/IP port at all. This can be a security enhancement,

if all processes that need to connect to mysqld run on the same host.

All interaction with mysqld must be made via Unix sockets or named pipes.

Note that using this option without enabling named pipes on Windows

(via the “enable-named-pipe” option) will render mysqld useless!

#skip-networking

Logging and Replication

Both location gets rotated by the cronjob.

Be aware that this log type is a performance killer.

As of 5.1 you can enable the log at runtime!

#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

Error log - should be very few entries.

log_error = /var/log/mysql/error.log

Replication Master Server (default)

binary logging is required for replication

log-bin=mysql-bin
binlog-format=ROW

Logging of slave updates is required so that we replicate out changes consumed from spcok

to other cluster members. This can be removed once we have finished migration

log-slave-updates=true
slave-skip-errors = 1062,1032

required unique id between 1 and 2^32 - 1

defaults to 1 if master-host is not set

but will not function as a master if omitted

server-id = 101

Uncomment the following if you are using InnoDB tables

innodb_file_per_table
innodb_data_home_dir = /mnt/ssd/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /mnt/ssd/mysql

You can set …_buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

Set …_log_file_size to 25 % of buffer pool size

innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

########################################

mysqld - Galera Custer Configuration

########################################

Configuration for the cluster as a whole

bootstrapping

wsrep_cluster_address = gcomm://

Normal operation

#wsrep_cluster_address = gcomm://80.68.84.41,80.68.84.40,80.68.84.39

wsrep_provider = /usr/lib/libgalera_smm.so # Path to Galera library
wsrep_provider_options=’socket.checksum=1′
wsrep_cluster_name = freegle # Identify the cluster
wsrep_sst_method = xtrabackup-v2 # How to synchronize nodes
wsrep_sst_auth = root: # Should find a way to use SSH keys

binlog_format = ROW # Required for Galera - binlog format must be ROW
innodb_autoinc_lock_mode = 2 # Required for Galera - this changes how InnoDB autoincrement locks are managed
innodb_locks_unsafe_for_binlog = 1 # Performance
default_storage_engine = InnoDB # MyISAM storage engine has only experimental support

Configuration for specific nodes

fd1

wsrep_node_name = fd1
wsrep_node_address = 80.68.84.41

mail1

#wsrep_node_name = mail1
#wsrep_node_address = 80.68.84.40

web1

#wsrep_node_name = web1
#wsrep_node_address = 80.68.84.39

###########################

application - mysqldump

###########################

[mysqldump]
quick
max_allowed_packet = 16M

#######################

application - mysql

#######################

[mysql]
no-auto-rehash

Remove the next comment character if you are not familiar with SQL

#safe-updates

###########################

application - myisamchk

###########################

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

##############################

application - mysqlhotcopy

##############################

[mysqlhotcopy]
interactive-timeout

------------------ Config for B

#############################################################

The following options will be passed to all MySQL clients

#############################################################

[client]

port = 3306
socket = /var/run/mysqld/mysqld.sock

#############################

mysqld - the MySQL server

#############################

[mysqld]

EH set these explicitly to allow xtrabackup to pick them up

datadir = /var/lib/mysql

EH end

port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8

EH No query cache with clustering?

query_cache_size = 0
query_cache_type = 0

Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 8

Don’t listen on a TCP/IP port at all. This can be a security enhancement,

if all processes that need to connect to mysqld run on the same host.

All interaction with mysqld must be made via Unix sockets or named pipes.

Note that using this option without enabling named pipes on Windows

(via the “enable-named-pipe” option) will render mysqld useless!

#skip-networking

Logging and Replication

Both location gets rotated by the cronjob.

Be aware that this log type is a performance killer.

As of 5.1 you can enable the log at runtime!

#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

Error log - should be very few entries.

log_error = /var/log/mysql/error.log

Replication Master Server (default)

binary logging is required for replication

log-bin=mysql-bin

required unique id between 1 and 2^32 - 1

defaults to 1 if master-host is not set

but will not function as a master if omitted

server-id = 101

Uncomment the following if you are using InnoDB tables

innodb_file_per_table
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql

You can set …_buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M

Set …_log_file_size to 25 % of buffer pool size

innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

########################################

mysqld - Galera Custer Configuration

########################################

Configuration for the cluster as a whole

bootstrapping

#wsrep_cluster_address = gcomm://

Normal operation

wsrep_cluster_address = gcomm://80.68.84.41,80.68.84.40,80.68.84.39

wsrep_provider = /usr/lib/libgalera_smm.so # Path to Galera library
wsrep_provider_options=’socket.checksum=1′
wsrep_cluster_name = freegle # Identify the cluster
wsrep_sst_method = xtrabackup-v2 # How to synchronize nodes
wsrep_sst_auth = root: # Should find a way to use SSH keys

binlog_format = ROW # Required for Galera - binlog format must be ROW
innodb_autoinc_lock_mode = 2 # Required for Galera - this changes how InnoDB autoincrement locks are managed
innodb_locks_unsafe_for_binlog = 1 # Performance
default_storage_engine = InnoDB # MyISAM storage engine has only experimental support

Configuration for specific nodes

fd1

#wsrep_node_name = fd1
#wsrep_node_address = 80.68.84.41

mail1

#wsrep_node_name = mail1
#wsrep_node_address = 80.68.84.40

web1

wsrep_node_name = web1
wsrep_node_address = 80.68.84.39

###########################

application - mysqldump

###########################

[mysqldump]
quick
max_allowed_packet = 16M

#######################

application - mysql

#######################

[mysql]
no-auto-rehash

Remove the next comment character if you are not familiar with SQL

#safe-updates

###########################

application - myisamchk

###########################

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

##############################

application - mysqlhotcopy

##############################

[mysqlhotcopy]
interactive-timeout

4

Possible doh moment - does the clustering stuff work with MyISAM tables yet?

(Yes - [url]Percona XtraDB Cluster)

5

Just noticed that A has the empty gcomm list from bootstrapping. I’ll try removing that.

6

No, that didn’t help. It would stop A from joining the cluster, but in this case I had bootstrapped on A.

7

Even odder. I tried creating a test table on an InnoDB database which is replicated into A. That change is replicated into A and on to B. However if I add data into that table, it is replicated into A but not on to B. If I then DROP the table, that change is replicated to both.

So structural changes are working but data is not. Does that help?

8

And in this example I can see using mysqlbinlog that the row change has been written out to the logfile on A.

at 272132

#140505 2:44:35 server id 1 end_log_pos 402169 Query thread_id=1605 exec_time=0 error_code=0
SET TIMESTAMP=1399254275/!/;
/!\C utf8 //!/;
SET @@session.character_set_client=33,@@session.collat ion_connection=33,@@session.collation_server=8/!/;
BEGIN
/!/;

at 272205

at 272248

#140505 2:44:35 server id 1 end_log_pos 402212 Table_map: wiki.test mapped to number 293
#140505 2:44:35 server id 1 end_log_pos 402246 Write_rows: table id 293 flags: STMT_END_F

INSERT INTO wiki.test

SET

@1=2

at 272282

#140505 2:44:35 server id 1 end_log_pos 402320 Query thread_id=1605 exec_time=0 error_code=0
SET TIMESTAMP=1399254275/!/;
COMMIT
/!/;

at 272356

#140505 2:44:40 server id 1 end_log_pos 402400 Query thread_id=546 exec_time=0 error_code=0

9

This is looking like a MyISAM issue. Even with wsrep_replicate_myisam set, the DML changes don’t seem to replicate. The docs say that MyISAM replication is still experimental, so I think I’ll have to see if I can convert my tables to InnoDB.