Renew or replace the MySQL/Galera certificates managed by salt-minion

Renew or replace the MySQL/Galera certificates managed by salt-minion

This section describes how to renew or replace the MySQL/Galera certificates managed by salt-minion.

Prerequisites:

  1. Log in to the Salt Master node.

  2. Verify that the MySQL/Galera cluster is up and synced:

    salt -C 'I@galera:master' mysql.status | grep -EA1 'wsrep_(local_state_c|incoming_a|cluster_size)'
    

    Example of system response:

    wsrep_cluster_size:
        3
    
    wsrep_incoming_addresses:
        192.168.2.52:3306,192.168.2.53:3306,192.168.2.51:3306
    
    wsrep_local_state_comment:
        Synced
    
  3. Verify that the log files have no errors:

    salt -C 'I@galera:master or I@galera:slave' cmd.run 'cat /var/log/mysql/error.log |grep ERROR|wc -l'
    

    Example of system response:

    dbs01.multinode-ha.int
        0
    
    dbs02.multinode-ha.int
        0
    
    dbs03.multinode-ha.int
        0
    

    Any value except 0 in the output indicates that the log files include errors. Review them before proceeding to operations with MySQL/Galera.

  4. Verify that the ca-salt_master_ca certificate is available on all nodes with MySQL/Galera:

    salt -C 'I@galera:master or I@galera:slave' cmd.run 'ls /usr/local/share/ca-certificates/ca-salt_master_ca.crt'
    

    Example of system response:

    dbs01.multinode-ha.int
        /usr/local/share/ca-certificates/ca-salt_master_ca.crt
    
    dbs02.multinode-ha.int
        /usr/local/share/ca-certificates/ca-salt_master_ca.crt
    
    dbs03.multinode-ha.int
        /usr/local/share/ca-certificates/ca-salt_master_ca.crt
    

To renew or replace the MySQL/Galera certificates managed by salt-minion:

  1. Log in to the Salt Master node.

  2. Obtain the list of the Galera cluster minions:

    salt -C 'I@galera:master or I@galera:slave' pillar.get _nonexistent | cut -d':' -f1
    

    Example of system response:

    dbs02.multinode-ha.int
    dbs03.multinode-ha.int
    dbs01.multinode-ha.int
    
  3. Verify the certificates validity dates:

    salt -C 'I@galera:master' cmd.run 'openssl x509 -in /etc/mysql/ssl/cert.pem -text -noout' | grep -Ei 'after|before'
    salt -C 'I@galera:slave' cmd.run 'openssl x509 -in /etc/mysql/ssl/cert.pem -text -noout' | grep -Ei 'after|before'
    

    Example of system response:

    Not Before: May 30 17:21:10 2018 GMT
    Not After : May 30 17:21:10 2019 GMT
    Not Before: May 30 17:25:24 2018 GMT
    Not After : May 30 17:25:24 2019 GMT
    Not Before: May 30 17:26:52 2018 GMT
    Not After : May 30 17:26:52 2019 GMT
    
  4. Prepare the Galera nodes to work with old one and new Salt Master CA certificates:

    salt -C 'I@galera:master or I@galera:slave' cmd.run 'cat /usr/local/share/ca-certificates/ca-salt_master_ca.crt /usr/local/share/ca-certificates/ca-salt_master_ca_old.crt > /etc/mysql/ssl/ca.pem'
    
  5. Verify that the necessary files are present in the ssl directory:

    salt -C 'I@galera:master or I@galera:slave' cmd.run 'ls /etc/mysql/ssl'
    

    Example of system response:

    dbs01.multinode-ha.int
        ca.pem
        cert.pem
        key.pem
    
    dbs02.multinode-ha.int
        ca.pem
        cert.pem
        key.pem
    
    dbs03.multinode-ha.int
        ca.pem
        cert.pem
        key.pem
    
  6. Identify the Galera nodes minions IDs:

    • For the Galera master node:

      salt -C 'I@galera:master' test.ping --output yaml | cut -d':' -f1
      

      Example of system response:

      dbs01.multinode-ha.int
      
    • For the Galera slave nodes:

      salt -C 'I@galera:slave' test.ping --output yaml | cut -d':' -f1
      

      Example of system response:

      dbs02.multinode-ha.int
      dbs03.multinode-ha.int
      
  7. Restart the MySQL service for every Galera minion ID one by one. After each Galera minion restart, verify the Galera cluster size and status. Proceed to the next Galera minion restart only if the Galera cluster is synced.

    • To restart the MySQL service for a Galera minion:

      salt <minion_ID> service.stop mysql
      salt <minion_ID> service.start mysql
      
    • To verify the Galera cluster size and status:

      salt -C 'I@galera:master' mysql.status | grep -EA1 'wsrep_(local_state_c|incoming_a|cluster_size)'
      

      Example of system response:

      wsrep_cluster_size:
          3
      
      wsrep_incoming_addresses:
          192.168.2.52:3306,192.168.2.53:3306,192.168.2.51:3306
      
      wsrep_local_state_comment:
          Synced
      
  8. If you replace the certificates, remove the private key:

    salt -C 'I@galera:master' cmd.run 'mv /etc/mysql/ssl/key.pem /root'
    
  9. Force the certificates regeneration for the Galera master node:

    salt -C 'I@galera:master' cmd.run 'mv /etc/mysql/ssl/cert.pem /root; mv /etc/mysql/ssl/ca.pem /root'
    
    salt -C 'I@galera:master' state.sls salt.minion.cert -l debug
    
    salt -C 'I@galera:master' cmd.run 'cat /usr/local/share/ca-certificates/ca-salt_master_ca.crt /usr/local/share/ca-certificates/ca-salt_master_ca_old.crt > /etc/mysql/ssl/ca.pem'
    
  10. Verify that the certificates validity dates have changed:

    salt -C 'I@galera:master' cmd.run 'openssl x509 -in /etc/mysql/ssl/cert.pem -text -noout' | grep -Ei 'after|before'
    

    Example of system response:

    Not Before: Jun  4 16:14:24 2018 GMT
    Not After : Jun  4 16:14:24 2019 GMT
    
  11. Verify that the necessary files are present in the ssl directory on the Galera master node:

    salt -C 'I@galera:master' cmd.run 'ls /etc/mysql/ssl'
    

    Example of system response:

    dbs01.multinode-ha.int
        ca.pem
        cert.pem
        key.pem
    
  12. Restart the MySQL service on the Galera master node:

    salt -C 'I@galera:master' service.stop mysql
    salt -C 'I@galera:master' service.start mysql
    
  13. Verify that the Galera cluster status is up. For details, see the step 7.

  14. If you replace the certificates, remove the private key:

    salt -C 'I@galera:slave' cmd.run 'mv /etc/mysql/ssl/key.pem /root'
    
  15. Force the certificates regeneration for the Galera slave nodes:

    salt -C 'I@galera:slave' cmd.run 'mv /etc/mysql/ssl/cert.pem /root; mv /etc/mysql/ssl/ca.pem /root'
    
    salt -C 'I@galera:slave' state.sls salt.minion.cert -l debug
    
    salt -C 'I@galera:slave' cmd.run 'cat /usr/local/share/ca-certificates/ca-salt_master_ca.crt /usr/local/share/ca-certificates/ca-salt_master_ca_old.crt > /etc/mysql/ssl/ca.pem'
    
  16. Verify that the necessary files are present in the ssl directory on the Galera slave nodes:

    salt -C 'I@galera:slave' cmd.run 'ls /etc/mysql/ssl'
    

    Example of system response:

    dbs02.multinode-ha.int
        ca.pem
        cert.pem
        key.pem
    
    dbs03.multinode-ha.int
        ca.pem
        cert.pem
        key.pem
    
  17. Verify that the certificates validity dates have changed:

    salt -C 'I@galera:slave' cmd.run 'openssl x509 -in /etc/mysql/ssl/cert.pem -text -noout' | grep -Ei 'after|before'
    

    Example of system response:

    Not Before: Jun  4 16:14:24 2018 GMT
    Not After : Jun  4 16:14:24 2019 GMT
    Not Before: Jun  4 16:14:31 2018 GMT
    Not After : Jun  4 16:14:31 2019 GMT
    
  18. Restart the MySQL service for every Galera slave minion ID one by one. After each Galera slave minion restart, verify the Galera cluster size and status. Proceed to the next Galera slave minion restart only if the Galera cluster is synced. For details, see the step 7.