cannot force sync

[umarzuki]

I deleted /var/lib/mysql, created new /var/lib/mysql folder owned by mysql.mysql

But it could not get sync via rsync

2015-05-22 11:52:08 21304 [Note] WSREP: Quorum results:
version = 3,
component = PRIMARY,
conf_id = 3,
members = 2/3 (joined/total),
act_id = 2458635,
last_appl. = -1,
protocols = 0/7/3 (gcs/repl/appl),
group UUID = 19bf9b09-ed47-11e4-8005-f75eee435bfc
2015-05-22 11:52:08 21304 [Note] WSREP: Flow-control interval: [28, 28]
2015-05-22 11:52:08 21304 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 2458635)
2015-05-22 11:52:08 21304 [Note] WSREP: State transfer required:
Group state: 19bf9b09-ed47-11e4-8005-f75eee435bfc:2458635
Local state: 00000000-0000-0000-0000-000000000000:-1
2015-05-22 11:52:08 21304 [Note] WSREP: New cluster view: global state: 19bf9b09-ed47-11e4-8005-f75eee435bfc:2458635, view# 4: Primary, number of nodes: 3, my index: 1, protocol version 3
2015-05-22 11:52:08 21304 [Warning] WSREP: Gap in state sequence. Need state transfer.
2015-05-22 11:52:08 21304 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '10.1.1.100' --auth '' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --parent '21304' '' '
2015-05-22 11:52:08 21304 [Note] WSREP: Prepared SST request: rsync|10.1.1.100:4444/rsync_sst
2015-05-22 11:52:08 21304 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2015-05-22 11:52:08 21304 [Note] WSREP: REPL Protocols: 7 (3, 2)
2015-05-22 11:52:08 21304 [Note] WSREP: Service thread queue flushed.
2015-05-22 11:52:08 21304 [Note] WSREP: Assign initial position for certification: 2458635, protocol version: 3
2015-05-22 11:52:08 21304 [Note] WSREP: Service thread queue flushed.
2015-05-22 11:52:08 21304 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (19bf9b09-ed47-11e4-8005-f75eee435bfc): 1 (Operation not permitted)
at galera/src/replicator_str.cpp:prepare_for_IST():456. IST will be unavailable.
2015-05-22 11:52:08 21304 [Note] WSREP: Member 1.0 (db1) requested state transfer from '*any*'. Selected 2.0 (db2)(SYNCED) as donor.
2015-05-22 11:52:08 21304 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 2458635)
2015-05-22 11:52:08 21304 [Note] WSREP: Requesting state transfer: success, donor: 2
2015-05-22 11:52:10 21304 [Warning] WSREP: 2.0 (db2): State transfer to 1.0 (db1) failed: -255 (Unknown error 255)
2015-05-22 11:52:10 21304 [ERROR] WSREP: gcs/src/gcs_group.cpp:int gcs_group_handle_join_msg(gcs_group_t*, const gcs_recv_msg_t*)():731: Will never receive state. Need to abort.
2015-05-22 11:52:10 21304 [Note] WSREP: gcomm: terminating thread
2015-05-22 11:52:10 21304 [Note] WSREP: gcomm: joining thread
2015-05-22 11:52:10 21304 [Note] WSREP: gcomm: closing backend
2015-05-22 11:52:10 21304 [Note] WSREP: view(view_id(NON_PRIM,65fff90b,126) memb {
eb09815b,0
} joined {
} left {
} partitioned {
65fff90b,0
ebec3ac9,0
})
2015-05-22 11:52:10 21304 [Note] WSREP: view((empty))
2015-05-22 11:52:10 21304 [Note] WSREP: gcomm: closed
2015-05-22 11:52:10 21304 [Note] WSREP: /usr/sbin/mysqld: Terminated.
Aborted (core dumped)
150522 11:52:10 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
WSREP_SST: [ERROR] Parent mysqld process (PID:21304) terminated unexpectedly. (20150522 11:52:10.816)
WSREP_SST: [INFO] Joiner cleanup. (20150522 11:52:10.819)
WSREP_SST: [INFO] Joiner cleanup done. (20150522 11:52:11.331)

I found many permission issues log in syslog on that node

May 22 11:52:09 db1 rsyncd[21375]: *** Skipping any contents from this failed directory ***
May 22 11:52:09 db1 rsyncd[21375]: rsync: recv_generator: mkdir "my_db" (in rsync_sst) failed: Permission denied (13)

/var/lib/mysql/ on donor and this node both owned by mysql.mysql

How do I rectify this?

[umarzuki]

weirdly, if I change my settings to like below, I’m able to simply restart percona’s service and synched

my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
federated
skip-external-locking
bind-address = 10.1.1.100
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql.log
binlog_format=ROW
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_cluster_name = dbcluster
wsrep_sst_method=rsync
wsrep_node_name = db1
wsrep_cluster_address=gcomm://10.1.1.100:4567,10.1.1.101:4567,10.1.2.171:4567
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/

diff between good config and bad config

2,3c2,3
< port = 3306
< socket = /var/run/mysqld/mysqld.sock
---
> port = 3306
> socket = /var/run/mysqld/mysqld.sock
5,6c5,6
< socket = /var/run/mysqld/mysqld.sock
< nice = 0
---
> socket = /var/run/mysqld/mysqld.sock
> nice = 0
8,15c8,15
< user = mysql
< pid-file = /var/run/mysqld/mysqld.pid
< socket = /var/run/mysqld/mysqld.sock
< port = 3306
< basedir = /usr
< datadir = /var/lib/mysql
< tmpdir = /tmp
< lc-messages-dir = /usr/share/mysql
---
> user = mysql
> pid-file = /var/run/mysqld/mysqld.pid
> socket = /var/run/mysqld/mysqld.sock
> port = 3306
> basedir = /usr
> datadir = /var/lib/mysql
> tmpdir = /tmp
> lc-messages-dir = /usr/share/mysql
18,25c18,35
< bind-address = 10.1.1.100
< key_buffer = 16M
< max_allowed_packet = 16M
< thread_stack = 192K
< thread_cache_size = 8
< myisam-recover = BACKUP
< query_cache_limit = 1M
< query_cache_size = 16M
---
> bind-address = 10.1.1.100
> key_buffer = 24M
> tmp_table_size = 64M
> max_heap_table_size = 64M
> max_allowed_packet = 512M
> skip_name_resolve
> memlock
> sysdate_is_now=1
> #thread_stack = 192K
> thread_cache_size = 512
> query_cache_size = 0
> table_open_cache=1024
> lower_case_table_names=0
> # 5.6 backwards compatibility
> explicit_defaults_for_timestamp=1
> #myisam-recover = BACKUP
> #query_cache_limit = 1M
> #query_cache_size = 16M
27a38,54
> ### INNODB OPTIONS
> innodb_buffer_pool_size=94065M
> innodb_flush_log_at_trx_commit=2
> innodb_file_per_table=1
> innodb_data_file_path = ibdata1:100M:autoextend
> ## You may want to tune the below depending on number of cores and disk sub
> innodb_read_io_threads=4
> innodb_write_io_threads=4
> innodb_io_capacity=200
> innodb_doublewrite=1
> innodb_log_file_size=1024M
> innodb_log_buffer_size=96M
> innodb_buffer_pool_instances=8
> innodb_log_files_in_group=2
> innodb_thread_concurrency=64
> #innodb_file_format=barracuda
> innodb_flush_method = O_DIRECT
28a56,58
> ## avoid statistics update when doing e.g show tables
> innodb_stats_on_metadata=0
> default_storage_engine=innodb
31c61,62
< wsrep_sst_method=rsync
---
> wsrep_sst_method = rsync
> #wsrep_sst_auth = root:password
34,35c65,67
< wsrep_cluster_address=gcomm://10.1.1.100:4567,10.1.1.101:4567,10.1.2.171:4567
< expire_logs_days = 10
---
> wsrep_cluster_address=gcomm://10.1.1.101:4567
> #wsrep_cluster_address=gcomm://10.1.1.100:4567,10.1.1.101:4567,10.1.2.171:4567
> expire_logs_days = 10
36a69,70
> # replicate myisam
> wsrep_replicate_myisam=1
40c74
< max_allowed_packet = 16M
---
> max_allowed_packet = 16M
43c77
< key_buffer = 16M
---
> key_buffer = 16M