– Unit mysql.service has begun starting up.Aug 12 12:57:09 ip-10-60-1-11.ap-north-1.compute.internal systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILUREAug 12 12:57:09 ip-10-60-1-11.ap-north-1.compute.internal mysql-systemd[23680]: WARNING: mysql pid file /var/run/mysqld/mysqld.pid empty or not readableAug 12 12:57:09 ip-10-60-1-11.ap-north-1.compute.internal mysql-systemd[23680]: WARNING: mysql may be already deadAug 12 12:57:09 ip-10-60-1-11.ap-north-1.compute.internal systemd[1]: mysql.service: Failed with result ‘exit-code’.Aug 12 12:57:09 ip-10-60-1-11.ap-north-1.compute.internal systemd[1]: Failed to start Percona XtraDB Cluster.-- Subject: Unit mysql.service has failed-- Defined-By: systemd
Hi Lokesh123,
There’s no helpful information here. You need to provide MySQL’s error log, typically located at /var/log/mysqld.log
----2020-08-12T18:24:09.109559Z 0 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 02020-08-12T18:24:09.109604Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this nodeview ((empty))2020-08-12T18:24:09.109757Z 0 [ERROR] [MY-000000] [Galera] failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out) at gcomm/src/pc.cpp:connect():1592020-08-12T18:24:09.109788Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_core.cpp:gcs_core_open():220: Failed to open backend connection: -110 (Connection timed out)2020-08-12T18:24:09.109891Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs.cpp:gcs_open():1700: Failed to open channel ‘pxc-cluster’ at ‘gcomm://10.60.1.157,10.60.1.129’: -110 (Connection timed out)2020-08-12T18:24:09.109913Z 0 [ERROR] [MY-000000] [Galera] gcs connect failed: Connection timed out2020-08-12T18:24:09.109934Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://10.60.1.157,10.60.1.129) failed to establish connection with cluster (reason: 7)2020-08-12T18:24:09.109955Z 0 [ERROR] [MY-010119] [Server] Aborting2020-08-12T18:24:09.110233Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-10) Percona XtraDB Cluster (GPL), Release rel10, Revision 727f180, WSREP version 26.4.3.2020-08-12T18:24:09.110519Z 0 [Note] [MY-000000] [Galera] dtor state: CLOSED2020-08-12T18:24:09.110561Z 0 [Note] [MY-000000] [Galera] MemPool(TrxHandleSlave): hit ratio: 0, misses: 0, in use: 0, in pool: 02020-08-12T18:24:09.112795Z 0 [Note] [MY-000000] [Galera] apply mon: entered 02020-08-12T18:24:09.115115Z 0 [Note] [MY-000000] [Galera] apply mon: entered 02020-08-12T18:24:09.117348Z 0 [Note] [MY-000000] [Galera] apply mon: entered 02020-08-12T18:24:09.117499Z 0 [Note] [MY-000000] [Galera] cert index usage at exit 02020-08-12T18:24:09.117515Z 0 [Note] [MY-000000] [Galera] cert trx map usage at exit 02020-08-12T18:24:09.117525Z 0 [Note] [MY-000000] [Galera] deps set usage at exit 02020-08-12T18:24:09.117541Z 0 [Note] [MY-000000] [Galera] avg deps dist 02020-08-12T18:24:09.117550Z 0 [Note] [MY-000000] [Galera] avg cert interval 02020-08-12T18:24:09.117557Z 0 [Note] [MY-000000] [Galera] cert index size 02020-08-12T18:24:09.117592Z 0 [Note] [MY-000000] [Galera] Service thread queue flushed.2020-08-12T18:24:09.117629Z 0 [Note] [MY-000000] [Galera] wsdb trx map usage 0 conn query map usage 02020-08-12T18:24:09.117644Z 0 [Note] [MY-000000] [Galera] MemPool(LocalTrxHandle): hit ratio: 0, misses: 0, in use: 0, in pool: 02020-08-12T18:24:09.118354Z 0 [Note] [MY-000000] [Galera] Flushing memory map to disk…
I see “Connection timed out” in many locations in your log file. This tells me that this node is unable to make contact with any other nodes in your cluster. Check firewalls or other networking access. Be sure to allow port 3306, 4444, and 4567
firewalls are not installed in my system.
Are you in AWS? or is this all local on the same machine. Are you trying to start node #1? If so, you must always bootstrap the first node of the cluster.
Matthewb On AWS:
I got there was some issue with ssl configuration.
when I set
pxc-encrypt-cluster-traffic=OFF cluster is working fine.
But I run with “pxc-encrypt-cluster-traffic=ON” default with below configuration and bootstap node1 is working but when I start with same configuration my second node not able to start with some errors.
# Template my.cnf for PXC
# Edit to your requirements.[client]socket=/var/lib/mysql/mysql.sockssl-ca=/home/adminuser/srt/ca.pemssl-key=/home/adminuser/srt/client-key.pemssl-cert=/home/adminuser/srt/client-cert.pem
[mysqld]server-id=1datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid# Binary log expiration period is 604800 seconds, which equals 7 days
binlog_expire_logs_seconds=604800######## wsrep ###############
# Path to Galera librarywsrep_provider=/usr/lib64/galera4/libgalera_smm.so# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,#in order to do that you need to bootstrap this nodewsrep_cluster_address=gcomm://192.168.1.129,192.168.1.157,192.168.1.164# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW# Slave thread to use
wsrep_slave_threads=8wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2# Node IP address
wsrep_node_address=192.168.1.157# Cluster namewsrep_cluster_name=pxc-cluster#If wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc-cluster-node-2#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCINGwsrep_sst_method=xtrabackup-v2
wsrep_provider_options=”socket.ssl_key=/home/adminuser/srt/server-key.pem;socket.ssl_cert=/home/adminuser/srt/server-cert.pem;socket.ssl_ca=/home/adminuser/srt/ca.pem”
[sst]
encrypt=4ssl-key=/home/adminuser/srt/server-key.pemssl-ca=/home/adminuser/srt/ca.pemssl-cert=/home/adminuser/srt/server-cert.pemlogs:
iod = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 128M; gcache.recover = yes; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 10; socket.checksum = 2; socket.recv_buf_size = 212992;2020-08-14T07:10:45.279603Z 0 [Note] [MY-000000] [Galera] Service thread queue flushed.2020-08-14T07:10:45.279693Z 0 [Note] [MY-000000] [Galera] ####### Assign initial position for certification: 488e520d-dd4e-11ea-8402-5725b4971a3d:66, protocol version: -12020-08-14T07:10:45.279759Z 0 [Note] [MY-000000] [WSREP] Starting replication2020-08-14T07:10:45.279820Z 0 [Note] [MY-000000] [Galera] Connecting with bootstrap option: 02020-08-14T07:10:45.279876Z 0 [Note] [MY-000000] [Galera] Setting GCS initial position to 488e520d-dd4e-11ea-8402-5725b4971a3d:662020-08-14T07:10:45.279969Z 0 [Note] [MY-000000] [Galera] protonet asio version 02020-08-14T07:10:45.280276Z 0 [Note] [MY-000000] [Galera] Using CRC-32C for message checksums.2020-08-14T07:10:45.280358Z 0 [Note] [MY-000000] [Galera] backend: asio2020-08-14T07:10:45.280466Z 0 [Note] [MY-000000] [Galera] gcomm thread scheduling priority set to other:02020-08-14T07:10:45.280586Z 0 [Warning] [MY-000000] [Galera] Fail to access the file (/var/lib/mysql//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown2020-08-14T07:10:45.280651Z 0 [Note] [MY-000000] [Galera] Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown2020-08-14T07:10:45.280865Z 0 [Note] [MY-000000] [Galera] GMCast version 02020-08-14T07:10:45.281052Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) listening at tcp://0.0.0.0:45672020-08-14T07:10:45.281122Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) multicast: , ttl: 12020-08-14T07:10:45.281421Z 0 [Note] [MY-000000] [Galera] EVS version 12020-08-14T07:10:45.281546Z 0 [Note] [MY-000000] [Galera] gcomm: connecting to group ‘pxc-cluster’, peer '192.168.1.129:,192.168.1.157:,192.168.1.164:'2020-08-14T07:10:45.282224Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) Found matching local endpoint for a connection, blacklisting address tcp://192.168.1.129:45672020-08-14T07:10:48.282764Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) connection to peer 00000000 with addr tcp://192.168.1.157:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)2020-08-14T07:10:48.283343Z 0 [Note] [MY-000000] [Galera] announce period timed out (pc.announce_timeout)2020-08-14T07:10:48.283494Z 0 [Note] [MY-000000] [Galera] EVS version upgrade 0 -> 12020-08-14T07:10:48.283584Z 0 [Note] [MY-000000] [Galera] PC protocol upgrade 0 -> 12020-08-14T07:10:48.283679Z 0 [Warning] [MY-000000] [Galera] no nodes coming from prim view, prim not possible2020-08-14T07:10:48.283772Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this nodeview (view_id(NON_PRIM,45a01e3c,1)memb { 45a01e3c,0 }joined { }left { }partitioned { })
left { }partitioned { })2020-08-14T07:10:48.783973Z 0 [Warning] [MY-000000] [Galera] last inactive check more than PT1.5S (3*evs.inactive_check_period) ago (PT3.50253S), skipping check2020-08-14T07:10:52.783995Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) connection to peer 00000000 with addr tcp://192.168.1.157:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)2020-08-14T07:10:57.284705Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) connection to peer 00000000 with addr tcp://192.168.1.157:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)2020-08-14T07:11:01.785258Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) connection to peer 00000000 with addr tcp://192.168.1.157:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)2020-08-14T07:11:06.285896Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) connection to peer 00000000 with addr tcp://192.168.1.157:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)2020-08-14T07:11:10.286798Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) connection to peer 00000000 with addr tcp://192.168.1.157:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)2020-08-14T07:11:14.287545Z 0 [Note] [MY-000000] [Galera] (45a01e3c, ‘tcp://0.0.0.0:4567’) connection to peer 00000000 with addr tcp://192.168.1.157:4567 timed out, no messages seen in PT3S (gmcast.peer_timeout)2020-08-14T07:11:18.292483Z 0 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 02020-08-14T07:11:18.292681Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this nodeview ((empty))2020-08-14T07:11:18.293109Z 0 [ERROR] [MY-000000] [Galera] failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out) at gcomm/src/pc.cpp:connect():1592020-08-14T07:11:18.293157Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_core.cpp:gcs_core_open():220: Failed to open backend connection: -110 (Connection timed out)2020-08-14T07:11:18.293216Z 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs.cpp:gcs_open():1700: Failed to open channel ‘pxc-cluster’ at ‘gcomm://192.168.1.129,192.168.1.157,192.168.1.164’: -110 (Connection timed out)2020-08-14T07:11:18.293228Z 0 [ERROR] [MY-000000] [Galera] gcs connect failed: Connection timed out2020-08-14T07:11:18.293239Z 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://192.168.1.129,192.168.1.157,192.168.1.164) failed to establish connection with cluster (reason: 7)2020-08-14T07:11:18.293251Z 0 [ERROR] [MY-010119] [Server] Aborting2020-08-14T07:11:18.293396Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19-10) Percona XtraDB Cluster (GPL), Release rel10, Revision 727f180, WSREP version 26.4.3.2020-08-14T07:11:18.293611Z 0 [Note] [MY-000000] [Galera] dtor state: CLOSED2020-08-14T07:11:18.293633Z 0 [Note] [MY-000000] [Galera] MemPool(TrxHandleSlave): hit ratio: 0, misses: 0, in use: 0, in pool: 02020-08-14T07:11:18.295832Z 0 [Note] [MY-000000] [Galera] apply mon: entered 02020-08-14T07:11:18.297998Z 0 [Note] [MY-000000] [Galera] apply mon: entered 02020-08-14T07:11:18.300149Z 0 [Note] [MY-000000] [Galera] apply mon: entered 02020-08-14T07:11:18.300168Z 0 [Note] [MY-000000] [Galera] cert index usage at exit 02020-08-14T07:11:18.300173Z 0 [Note] [MY-000000] [Galera] cert trx map usage at exit 02020-08-14T07:11:18.300177Z 0 [Note] [MY-000000] [Galera] deps set usage at exit 02020-08-14T07:11:18.300184Z 0 [Note] [MY-000000] [Galera] avg deps dist 02020-08-14T07:11:18.300188Z 0 [Note] [MY-000000] [Galera] avg cert interval 02020-08-14T07:11:18.300193Z 0 [Note] [MY-000000] [Galera] cert index size 02020-08-14T07:11:18.300210Z 0 [Note] [MY-000000] [Galera] Service thread queue flushed.2020-08-14T07:11:18.300223Z 0 [Note] [MY-000000] [Galera] wsdb trx map usage 0 conn query map usage 02020-08-14T07:11:18.300229Z 0 [Note] [MY-000000] [Galera] MemPool(LocalTrxHandle): hit ratio: 0, misses: 0, in use: 0, in pool: 02020-08-14T07:11:18.300773Z 0 [Note] [MY-000000] [Galera] Flushing memory map to disk…
In order to use SSL and pxc-encrypt-cluster-traffic=ON, you must MANUALLY copy the SSL certificates that are created on node #1 after you bootstrap it to all other nodes. SSL certificates are NOT transfered automatically to any joining nodes.
So, shut down your cluster cleanly. Then, set pxc-encrypt-cluster-traffic=ON on node #1 and bootstrap it. This should come up with no issues. Next, copy the SSL certs from node #1 to node #2. Set node#2 pxc-encrypt-cluster-traffic=ON and start it normally. It should connect to node #1. Repeat for #3.
@mathhwes IS this right configuration for SSL ?
And
Is there a particular location to put all SSL certification or I can put any where as like in below configuration?
[mysqld]
pxc-encrypt-cluster-traffic=ON
wsrep_provider_options=”socket.ssl_key=/home/adminuser/srt/server-key.pem;socket.ssl_cert=/home/adminuser/srt/server-cert.pem;socket.ssl_ca=/home/adminuser/srt/ca.pem”
[sst]
encrypt=4ssl-key=/home/adminuser/srt/server-key.pemssl-ca=/home/adminuser/srt/ca.pemssl-cert=/home/adminuser/srt/server-cert.pem
Yes, that should be good. Now, bootstrap that node #1, and copy *.pem to same location on all nodes and configure the same.
wsrep_provider_options=”socket.ssl_key=/home/adminuser/server-key.pem;socket.ssl_cert=/home/adminuser/server-cert.pem;socket.ssl_ca=/home/adminuser/ca.pem”
pxc-encrypt-cluster-traffic=ON[sst]encrypt=4ssl-key=/home/adminuser/server-key.pemssl-ca=/home/adminuser/ca.pemssl-cert=/home/adminuser/server-cert.pem
getting same logs not resolved
Also when I am trying with first node1 putting in config
ssl-key=/home/adminuser/srt/server-key.pemssl-ca=/home/adminuser/srt/ca.pemssl-cert=/home/adminuser/srt/server-cert.pem
It has to show the location for SSL files is
/home/adminuser/srt/ca.pem
/home/adminuser/srt/server-cert.pem
/home/adminuser/srt/server-key.pem
But It shows last one
show variables like ‘%ssl%’ -> ;±-------------------±----------------+| Variable_name | Value |±-------------------±----------------+| have_openssl | YES || have_ssl | YES || mysqlx_ssl_ca | || mysqlx_ssl_capath | || mysqlx_ssl_cert | || mysqlx_ssl_cipher | || mysqlx_ssl_crl | || mysqlx_ssl_crlpath | || mysqlx_ssl_key | || ssl_ca | ca.pem || ssl_capath | || ssl_cert | server-cert.pem || ssl_cipher | || ssl_crl | || ssl_crlpath | || ssl_fips_mode | OFF || ssl_key | server-key.pem |±-------------------±----------------+17 rows in set (0.01 sec)
Stop node #1. Put these lines:
pxc-encrypt-cluster-traffic=ON
ssl-key=/home/adminuser/server-key.pem
ssl-ca=/home/adminuser/ca.pem
ssl-cert=/home/adminuser/server-cert.pem
inside [mysqld]
Remove the ENTIRE [sst] section.
Remove “wsrep_provider_options”.
Bootstrap node 1. Put the SAME CONFIG as above into the other nodes. Start them normally.
Thank-you so much Matthewb for your valuable time and help. =D
It’s working.