Hello,
i had a power failure and all nodes wasn’t synced. Its centos 7 and mysql 5.6.
Node number 3 is working as bootstrap. Node number 1 restarted successfully.
Node 2 was stucked.
i removed /var/lib/mysql/* from the node
no mysql running, and /var/lib/mysql directory owner and group is mysql:mysql
then" service mysql start" and here is the logs
from /var/log/messages
Jun 22 19:19:03 ppdb02 systemd: Starting Percona XtraDB Cluster...
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 0 [Warning] option 'wsrep_max_ws_size': unsigned value 20401094656 adjusted to 2147483647
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 0 [Note] /usr/sbin/mysqld (mysqld 5.6.41-84.1-56-log) starting as process 2747 ...
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] WSREP: Setting wsrep_ready to false
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] WSREP: Read nil XID from storage engines, skipping position init
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] WSREP: wsrep_load(): loading provider library 'none'
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] WSREP: Setting wsrep_ready to true
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Using atomics to ref count buffer pool pages
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: The InnoDB memory heap is disabled
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Memory barrier is not used
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Compressed tables use zlib 1.2.7
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Using Linux native AIO
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Using CPU crc32 instructions
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Initializing buffer pool, size = 6.3G
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Completed initialization of buffer pool
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Database physically writes the file full: wait...
Jun 22 19:19:03 ppdb02 mysql-systemd: 2021-06-22 19:19:03 2747 [Note] InnoDB: Setting log file ./ib_logfile101 size to 512 MB
Jun 22 19:19:04 ppdb02 mysql-systemd: InnoDB: Progress in MB: 100 200 300 400 500
Jun 22 19:19:06 ppdb02 mysql-systemd: 2021-06-22 19:19:06 2747 [Note] InnoDB: Setting log file ./ib_logfile1 size to 512 MB
Jun 22 19:19:06 ppdb02 mysql-systemd: InnoDB: Progress in MB: 100 200 300 400 500
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Warning] InnoDB: New log files created, LSN=45781
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Doublewrite buffer not found: creating new
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Doublewrite buffer created
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: 128 rollback segment(s) are active.
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Warning] InnoDB: Creating foreign key constraint system tables.
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Foreign key constraint system tables created
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Creating tablespace and datafile system tables.
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Tablespace and datafile system tables created.
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Creating zip_dict and zip_dict_cols system tables.
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: zip_dict and zip_dict_cols system tables created.
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Waiting for purge to start
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.41-84.1 started; log sequence number 0
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
Jun 22 19:19:08 ppdb02 mysql-systemd: 2021-06-22 19:19:08 2747 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
Jun 22 19:19:09 ppdb02 mysql-systemd: 2021-06-22 19:19:09 2747 [Note] WSREP: Service disconnected.
Jun 22 19:19:09 ppdb02 mysql-systemd: 2021-06-22 19:19:09 2747 [Note] WSREP: Waiting to close threads......
Jun 22 19:19:14 ppdb02 mysql-systemd: 2021-06-22 19:19:14 2747 [Note] WSREP: Some threads may fail to exit.
Jun 22 19:19:14 ppdb02 mysql-systemd: 2021-06-22 19:19:14 2747 [Note] Binlog end
Jun 22 19:19:14 ppdb02 mysql-systemd: 2021-06-22 19:19:14 2747 [Note] InnoDB: FTS optimize thread exiting.
Jun 22 19:19:14 ppdb02 mysql-systemd: 2021-06-22 19:19:14 2747 [Note] InnoDB: Starting shutdown...
Jun 22 19:19:15 ppdb02 mysql-systemd: 2021-06-22 19:19:15 2747 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2747 [Note] InnoDB: Shutdown completed; log sequence number 1631996
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 0 [Warning] option 'wsrep_max_ws_size': unsigned value 20401094656 adjusted to 2147483647
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 0 [Note] /usr/sbin/mysqld (mysqld 5.6.41-84.1-56-log) starting as process 2868 ...
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] WSREP: Setting wsrep_ready to false
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] WSREP: Read nil XID from storage engines, skipping position init
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] WSREP: wsrep_load(): loading provider library 'none'
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] WSREP: Setting wsrep_ready to true
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: Using atomics to ref count buffer pool pages
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: The InnoDB memory heap is disabled
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: Memory barrier is not used
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: Compressed tables use zlib 1.2.7
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: Using Linux native AIO
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: Using CPU crc32 instructions
Jun 22 19:19:16 ppdb02 mysql-systemd: 2021-06-22 19:19:16 2868 [Note] InnoDB: Initializing buffer pool, size = 6.3G
Jun 22 19:19:17 ppdb02 mysql-systemd: 2021-06-22 19:19:17 2868 [Note] InnoDB: Completed initialization of buffer pool
Jun 22 19:19:17 ppdb02 mysql-systemd: 2021-06-22 19:19:17 2868 [Note] InnoDB: Highest supported file format is Barracuda.
Jun 22 19:19:17 ppdb02 mysql-systemd: 2021-06-22 19:19:17 2868 [Note] InnoDB: 128 rollback segment(s) are active.
Jun 22 19:19:17 ppdb02 mysql-systemd: 2021-06-22 19:19:17 2868 [Note] InnoDB: Waiting for purge to start
Jun 22 19:19:17 ppdb02 mysql-systemd: 2021-06-22 19:19:17 2868 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.41-84.1 started; log sequence number 1631996
Jun 22 19:19:17 ppdb02 mysql-systemd: 2021-06-22 19:19:17 2868 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
Jun 22 19:19:17 ppdb02 mysql-systemd: 2021-06-22 19:19:17 2868 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
Jun 22 19:19:18 ppdb02 mysql-systemd: 2021-06-22 19:19:18 2868 [Note] WSREP: Service disconnected.
Jun 22 19:19:18 ppdb02 mysql-systemd: 2021-06-22 19:19:18 2868 [Note] WSREP: Waiting to close threads......
Jun 22 19:19:23 ppdb02 mysql-systemd: 2021-06-22 19:19:23 2868 [Note] WSREP: Some threads may fail to exit.
Jun 22 19:19:23 ppdb02 mysql-systemd: 2021-06-22 19:19:23 2868 [Note] Binlog end
Jun 22 19:19:23 ppdb02 mysql-systemd: 2021-06-22 19:19:23 2868 [Note] InnoDB: FTS optimize thread exiting.
Jun 22 19:19:23 ppdb02 mysql-systemd: 2021-06-22 19:19:23 2868 [Note] InnoDB: Starting shutdown...
Jun 22 19:19:23 ppdb02 mysql-systemd: 2021-06-22 19:19:23 2868 [Note] InnoDB: Waiting for page_cleaner to finish flushing of buffer pool
Jun 22 19:19:25 ppdb02 mysql-systemd: 2021-06-22 19:19:25 2868 [Note] InnoDB: Shutdown completed; log sequence number 1632006
Jun 22 19:19:25 ppdb02 mysql-systemd: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
Jun 22 19:19:25 ppdb02 mysql-systemd: To do so, start the server, then issue the following commands:
Jun 22 19:19:25 ppdb02 mysql-systemd: /usr/bin/mysqladmin -u root password 'new-password'
Jun 22 19:19:25 ppdb02 mysql-systemd: /usr/bin/mysqladmin -u root -h ppdb02 password 'new-password'
Jun 22 19:19:25 ppdb02 mysql-systemd: Alternatively you can run:
Jun 22 19:19:25 ppdb02 mysql-systemd: /usr/bin/mysql_secure_installation
Jun 22 19:19:25 ppdb02 mysql-systemd: which will also give you the option of removing the test
Jun 22 19:19:25 ppdb02 mysql-systemd: databases and anonymous user created by default. This is
Jun 22 19:19:25 ppdb02 mysql-systemd: strongly recommended for production servers.
Jun 22 19:19:25 ppdb02 mysql-systemd: See the manual for more instructions.
Jun 22 19:19:25 ppdb02 mysql-systemd: Please report any problems with Percona XtraDB Cluster 5.6 at https://jira.percona.com/projects/PXC/issues
Jun 22 19:19:25 ppdb02 mysql-systemd: The latest information about Percona XtraDB Cluster 5.6 is available on the web at
Jun 22 19:19:25 ppdb02 mysql-systemd: http://www.percona.com/doc/percona-xtradb-cluster/5.6/
Jun 22 19:19:25 ppdb02 mysql-systemd: Support Percona XtraDB Cluster 5.6 by buying support at http://www.percona.com/products/mysql-support
Jun 22 19:19:25 ppdb02 mysql-systemd: WARNING: Default config file /etc/my.cnf exists on the system
Jun 22 19:19:25 ppdb02 mysql-systemd: This file will be read by default by the MySQL server
Jun 22 19:19:25 ppdb02 mysql-systemd: If you do not want to use this, either remove it, or use the
Jun 22 19:19:25 ppdb02 mysql-systemd: --defaults-file argument to mysqld_safe when starting the server
Jun 22 19:19:27 ppdb02 mysqld_safe: Logging to '/var/lib/mysql/ppdb02.err'.
Jun 22 19:19:27 ppdb02 mysqld_safe: 210622 19:19:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Jun 22 19:19:27 ppdb02 mysqld_safe: 210622 19:19:27 mysqld_safe Skipping wsrep-recover for empty datadir: /var/lib/mysql
Jun 22 19:19:27 ppdb02 mysqld_safe: 210622 19:19:27 mysqld_safe Assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position
Jun 22 19:19:27 ppdb02 mysqld_safe: 210622 19:19:27 mysqld_safe mysqld from pid file /var/lib/mysql/ppdb02.pid ended
Jun 22 19:19:27 ppdb02 mysql-systemd: /usr/bin/mysql-systemd: line 134: kill: (2950) - No such process
Jun 22 19:19:27 ppdb02 mysql-systemd: ERROR! mysqld_safe with PID 2950 has already exited: FAILURE
Jun 22 19:19:27 ppdb02 systemd: mysql.service: control process exited, code=exited status=1
Jun 22 19:19:27 ppdb02 mysql-systemd: WARNING: mysql pid file /var/lib/mysql/ppdb02.pid empty or not readable
Jun 22 19:19:27 ppdb02 mysql-systemd: ERROR! mysql already dead
Jun 22 19:19:27 ppdb02 systemd: mysql.service: control process exited, code=exited status=2
Jun 22 19:19:27 ppdb02 mysql-systemd: WARNING: mysql pid file /var/lib/mysql/ppdb02.pid empty or not readable
Jun 22 19:19:27 ppdb02 mysql-systemd: WARNING: mysql may be already dead
Jun 22 19:19:27 ppdb02 systemd: Failed to start Percona XtraDB Cluster.
Jun 22 19:19:28 ppdb02 systemd: Unit mysql.service entered failed state.
Jun 22 19:19:28 ppdb02 systemd: mysql.service failed.
=====================
here is my configurations:
[MYSQLD]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_warnings=2
bind-address=0.0.0.0
### INNODB OPTIONS
innodb_buffer_pool_size=6500M
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=512M
innodb_log_buffer_size=64M
innodb_buffer_pool_instances=5
innodb_log_files_in_group=2
innodb_thread_concurrency=64
innodb_file_format=barracuda
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
## avoid statistics update when doing e.g show tables
innodb_stats_on_metadata=0
default_storage_engine=innodb
connect_timeout=5000
wait_timeout=180
interactive_timeout=3600000
slow_query_log_file=/var/lib/mysql/slow-queries.log
slow_query_log = On
slow_query_log_timestamp_precision = microsecond
slow_query_log_timestamp_always = ON
long_query_time = 2
# OTHER THINGS, BUFFERS ETC
key_buffer_size = 24M
tmp_table_size=128M
max_heap_table_size=128M
max_allowed_packet = 1024M
join_buffer_size = 16M
low_priority_updates=1
skip_name_resolve
memlock=0
sysdate_is_now=1
ignore-db-dir=lost+found
max_connections=400
thread_cache_size=512
query_cache_type = 1
query_cache_size = 8M
table_open_cache=1024
open_files_limit=20480
lower_case_table_names=0
# 5.6 backwards compatibility
explicit_defaults_for_timestamp=1
##
## WSREP options
##
# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_node_address=10.151.158.53
# Provider specific configuration options
wsrep_provider_options="gcache.size=2048M; evs.keepalive_period=PT3S; evs.inactive_check_period=PT10S; evs.suspect_timeout=PT30S; evs.inactive_timeout=PT1M; evs.install_timeout=PT1M; evs.send_window=1024; evs.user_send_window=512; gcs.fc_limit=64;"
# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="patientportal_cluster"
# Group communication system handle
#wsrep_cluster_address="gcomm://10.245.1.51,10.245.1.52,10.245.1.53"
wsrep_cluster_address=gcomm://10.151.158.26,10.151.158.54
# Human_readable node name (non-unique). Hostname by default.
wsrep_node_name=ppdb02
# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address=
# How many threads will process writesets from other nodes
wsrep_slave_threads=16
# DBUG options for wsrep provider
wsrep_log_conflicts=OFF
# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1
# Location of the directory with data files. Needed for non-mysqldump
# state snapshot transfers. Defaults to mysql_real_data_home.
#wsrep_data_home_dir=
# Maximum number of rows in write set
#wsrep_max_ws_rows=131072
wsrep_max_ws_rows=0
# Maximum size of write set
#wsrep_max_ws_size=1073741824
wsrep_max_ws_size=20401094656
# to enable debug level logging, set this to 1
wsrep_debug=0
# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0
# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=2
# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1
# replicate myisam
wsrep_replicate_myisam=1
## retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0
# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0
# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status - new status of this node
# --uuid - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index - index of this node in the list
#wsrep_notify_cmd=
##
## WSREP State Transfer options
##
# State Snapshot Transfer method
# ClusterControl currently DOES NOT support wsrep_sst_method=mysqldump
wsrep_sst_method=xtrabackup-v2
#wsrep_sst_method=rsync
# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
#wsrep_sst_receive_address=10.151.158.53
# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
#wsrep_sst_auth=root:IWcqzv9RjL8H6
wsrep_sst_auth = root:IWcqzv9RjL8H6
# Protocol version to use
[MYSQL]
socket=/var/lib/mysql/mysql.sock
[client]
socket=/var/lib/mysql/mysql.sock
[mysqldump]
max_allowed_packet = 1024M
socket=/var/lib/mysql/mysql.sock
[MYSQLD_SAFE]
datadir=/var/lib/mysql
[sst]
compressor='pigz'
decompressor='pigz -dc'