Percona Cluster as replication slave - problems

[edwh]

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?

[mirfan]

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

[edwh]

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:<password> # 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:<password> # 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

[edwh]

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

(Yes - http://www.percona.com/doc/percona-x...er-5.5.23.html)

[edwh]

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

[edwh]

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

[edwh]

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?

[edwh]

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

...but not replicated to other cluster members.

[edwh]

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.