Not the answer you need?
Register and ask your own question!

Getting error after configuration Percona Xtradb 8.0?

lokesh123lokesh123 Current User Role Patron
-- 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/FAILURE
Aug 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 readable
Aug 12 12:57:09 ip-10-60-1-11.ap-north-1.compute.internal mysql-systemd[23680]:  WARNING: mysql may be already dead
Aug 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

Best Answer

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Accepted Answer
    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.

Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Hi Lokesh123,
    There's no helpful information here. You need to provide MySQL's error log, typically located at /var/log/mysqld.log
  • lokesh123lokesh123 Current User Role Patron
    ----
    2020-08-12T18:24:09.109559Z 0 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 0
    2020-08-12T18:24:09.109604Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
    view ((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():159
    2020-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 out
    2020-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] Aborting
    2020-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: CLOSED
    2020-08-12T18:24:09.110561Z 0 [Note] [MY-000000] [Galera] MemPool(TrxHandleSlave): hit ratio: 0, misses: 0, in use: 0, in pool: 0
    2020-08-12T18:24:09.112795Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
    2020-08-12T18:24:09.115115Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
    2020-08-12T18:24:09.117348Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
    2020-08-12T18:24:09.117499Z 0 [Note] [MY-000000] [Galera] cert index usage at exit 0
    2020-08-12T18:24:09.117515Z 0 [Note] [MY-000000] [Galera] cert trx map usage at exit 0
    2020-08-12T18:24:09.117525Z 0 [Note] [MY-000000] [Galera] deps set usage at exit 0
    2020-08-12T18:24:09.117541Z 0 [Note] [MY-000000] [Galera] avg deps dist 0
    2020-08-12T18:24:09.117550Z 0 [Note] [MY-000000] [Galera] avg cert interval 0
    2020-08-12T18:24:09.117557Z 0 [Note] [MY-000000] [Galera] cert index size 0
    2020-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 0
    2020-08-12T18:24:09.117644Z 0 [Note] [MY-000000] [Galera] MemPool(LocalTrxHandle): hit ratio: 0, misses: 0, in use: 0, in pool: 0
    2020-08-12T18:24:09.118354Z 0 [Note] [MY-000000] [Galera] Flushing memory map to disk...

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    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
  • lokesh123lokesh123 Current User Role Patron
    firewalls are  not installed in my system.

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    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.
  • lokesh123lokesh123 Current User Role Patron
    edited August 14
    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.sock
    ssl-ca=/home/adminuser/srt/ca.pem
    ssl-key=/home/adminuser/srt/client-key.pem
    ssl-cert=/home/adminuser/srt/client-cert.pem

    [mysqld]
    server-id=1
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    log-error=/var/log/mysqld.log
    pid-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 library
    wsrep_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 node
    wsrep_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=8
    wsrep_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 name
    wsrep_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=ENFORCING
    wsrep_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=4
    ssl-key=/home/adminuser/srt/server-key.pem
    ssl-ca=/home/adminuser/srt/ca.pem
    ssl-cert=/home/adminuser/srt/server-cert.pem


    logs:

    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: -1
    2020-08-14T07:10:45.279759Z 0 [Note] [MY-000000] [WSREP] Starting replication
    2020-08-14T07:10:45.279820Z 0 [Note] [MY-000000] [Galera] Connecting with bootstrap option: 0
    2020-08-14T07:10:45.279876Z 0 [Note] [MY-000000] [Galera] Setting GCS initial position to 488e520d-dd4e-11ea-8402-5725b4971a3d:66
    2020-08-14T07:10:45.279969Z 0 [Note] [MY-000000] [Galera] protonet asio version 0
    2020-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: asio
    2020-08-14T07:10:45.280466Z 0 [Note] [MY-000000] [Galera] gcomm thread scheduling priority set to other:0
    2020-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 shutdown
    2020-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 shutdown
    2020-08-14T07:10:45.280865Z 0 [Note] [MY-000000] [Galera] GMCast version 0
    2020-08-14T07:10:45.281052Z 0 [Note] [MY-000000] [Galera] (45a01e3c, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
    2020-08-14T07:10:45.281122Z 0 [Note] [MY-000000] [Galera] (45a01e3c, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
    2020-08-14T07:10:45.281421Z 0 [Note] [MY-000000] [Galera] EVS version 1
    2020-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:4567
    2020-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 -> 1
    2020-08-14T07:10:48.283584Z 0 [Note] [MY-000000] [Galera] PC protocol upgrade 0 -> 1
    2020-08-14T07:10:48.283679Z 0 [Warning] [MY-000000] [Galera] no nodes coming from prim view, prim not possible
    2020-08-14T07:10:48.283772Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
    view (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 check
    2020-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 -> 0
    2020-08-14T07:11:18.292681Z 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
    view ((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():159
    2020-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 out
    2020-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] Aborting
    2020-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: CLOSED
    2020-08-14T07:11:18.293633Z 0 [Note] [MY-000000] [Galera] MemPool(TrxHandleSlave): hit ratio: 0, misses: 0, in use: 0, in pool: 0
    2020-08-14T07:11:18.295832Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
    2020-08-14T07:11:18.297998Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
    2020-08-14T07:11:18.300149Z 0 [Note] [MY-000000] [Galera] apply mon: entered 0
    2020-08-14T07:11:18.300168Z 0 [Note] [MY-000000] [Galera] cert index usage at exit 0
    2020-08-14T07:11:18.300173Z 0 [Note] [MY-000000] [Galera] cert trx map usage at exit 0
    2020-08-14T07:11:18.300177Z 0 [Note] [MY-000000] [Galera] deps set usage at exit 0
    2020-08-14T07:11:18.300184Z 0 [Note] [MY-000000] [Galera] avg deps dist 0
    2020-08-14T07:11:18.300188Z 0 [Note] [MY-000000] [Galera] avg cert interval 0
    2020-08-14T07:11:18.300193Z 0 [Note] [MY-000000] [Galera] cert index size 0
    2020-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 0
    2020-08-14T07:11:18.300229Z 0 [Note] [MY-000000] [Galera] MemPool(LocalTrxHandle): hit ratio: 0, misses: 0, in use: 0, in pool: 0
    2020-08-14T07:11:18.300773Z 0 [Note] [MY-000000] [Galera] Flushing memory map to disk...
                                                                                           






  • matthewbmatthewb Senior [email protected] Percona Staff Role
    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.
  • lokesh123lokesh123 Current User Role Patron
    @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=4
    ssl-key=/home/adminuser/srt/server-key.pem
    ssl-ca=/home/adminuser/srt/ca.pem
    ssl-cert=/home/adminuser/srt/server-cert.pem

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    Yes, that should be good. Now, bootstrap that node #1, and copy *.pem to same location on all nodes and configure the same.
  • lokesh123lokesh123 Current User Role Patron

    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=4
    ssl-key=/home/adminuser/server-key.pem
    ssl-ca=/home/adminuser/ca.pem
    ssl-cert=/home/adminuser/server-cert.pem

    getting same logs not resolved
  • lokesh123lokesh123 Current User Role Patron
    Also when I am trying with first node1  putting  in config

    ssl-key=/home/adminuser/srt/server-key.pem
    ssl-ca=/home/adminuser/srt/ca.pem
    ssl-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)

  • lokesh123lokesh123 Current User Role Patron
    edited August 15
    Thank-you so much Matthewb  for your valuable  time and help. =D
    It's working.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.