Chapter 2. Using MySQL

Procedure

1. Store the CA and server certificates in the /etc/pki/tls/certs/ directory:

   # mv <path>/server.example.com.crt.pem /etc/pki/tls/certs/
   # mv <path>/ca.crt.pem /etc/pki/tls/certs/

   Copy to Clipboard

2. Set permissions on the CA and server certificate that enable the MySQL server to read the files:

   # chmod 644 /etc/pki/tls/certs/server.example.com.crt.pem /etc/pki/tls/certs/ca.crt.pem

   Copy to Clipboard

   Because certificates are part of the communication before a secure connection is established, any client can retrieve them without authentication. Therefore, you do not need to set strict permissions on the CA and server certificate files.

3. Store the server’s private key in the /etc/pki/tls/private/ directory:

   # mv <path>/server.example.com.key.pem /etc/pki/tls/private/

   Copy to Clipboard

4. Set secure permissions on the server’s private key:

   # chmod 640 /etc/pki/tls/private/server.example.com.key.pem
   # chgrp mysql /etc/pki/tls/private/server.example.com.key.pem

   Copy to Clipboard

   If unauthorized users have access to the private key, connections to the MySQL server are no longer secure.

5. Restore the SELinux context:

   # restorecon -Rv /etc/pki/tls/

   Copy to Clipboard

Procedure

1. Create the /etc/my.cnf.d/mysql-server-tls.cnf file:

   1. Add the following content to configure the paths to the private key, server and CA certificate:

      [mysqld]
      ssl_key = /etc/pki/tls/private/server.example.com.key.pem
      ssl_cert = /etc/pki/tls/certs/server.example.com.crt.pem
      ssl_ca = /etc/pki/tls/certs/ca.crt.pem

      Copy to Clipboard

   2. If you have a Certificate Revocation List (CRL), configure the MySQL server to use it:

      ssl_crl = /etc/pki/tls/certs/example.crl.pem

      Copy to Clipboard

   3. Optional: Reject connection attempts without encryption. To enable this feature, append:

      require_secure_transport = on

      Copy to Clipboard

   4. Optional: Set the TLS versions the server should support. For example, to support only TLS 1.3, append:

      tls_version = TLSv1.3

      Copy to Clipboard

      By default, the server supports TLS 1.2 and TLS 1.3.

2. Restart the mysqld service:

   # systemctl restart mysqld

   Copy to Clipboard

1. Verify that MySQL now has TLS encryption enabled:

   # mysql -u root -p -h <MySQL_server_hostname> -e "SHOW session status LIKE 'Ssl_cipher';"
   +---------------+------------------------+
   | Variable_name | Value                  |
   +---------------+------------------------+
   | Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
   +---------------+------------------------+

   Copy to Clipboard

2. If you configured the MySQL server to only support specific TLS versions, display the tls_version variable:

   # mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'tls_version';"
   +---------------+---------+
   | Variable_name | Value   |
   +---------------+---------+
   | tls_version   | TLSv1.3 |
   +---------------+---------+

   Copy to Clipboard

3. Verify that the server uses the correct CA certificate, server certificate, and private key files:

   # mysql -u root -e "SHOW GLOBAL VARIABLES WHERE Variable_name REGEXP '{caret}ssl_ca|{caret}ssl_cert|{caret}ssl_key';"
   +-----------------+-------------------------------------------------+
   | Variable_name   | Value                                           |
   +-----------------+-------------------------------------------------+
   | ssl_ca          | /etc/pki/tls/certs/ca.crt.pem                   |
   | ssl_capath      |                                                 |
   | ssl_cert        | /etc/pki/tls/certs/server.example.com.crt.pem   |
   | ssl_key         | /etc/pki/tls/private/server.example.com.key.pem |
   +-----------------+-------------------------------------------------+

   Copy to Clipboard

Procedure

1. Connect as an administrative user to the MySQL server:

   # mysql -u root -p -h server.example.com

   Copy to Clipboard

   If your administrative user has no permissions to access the server remotely, perform the command on the MySQL server and connect to localhost.

2. Use the REQUIRE SSL clause to enforce that a user must connect by using a TLS-encrypted connection:

   MySQL [(none)]> ALTER USER 'example'@'%' REQUIRE SSL;

   Copy to Clipboard

Verification

1. Connect to the server as the example user by using TLS encryption:

   # mysql -u example -p -h server.example.com
   ...
   MySQL [(none)]>

   Copy to Clipboard

   If no error is shown and you have access to the interactive MySQL console, the connection with TLS succeeds.

   By default, the client automatically uses TLS encryption if the server provides it. Therefore, the --ssl-ca=ca.crt.pem and --ssl-mode=VERIFY_IDENTITY options are not required, but improve the security because, with these options, the client verifies the identity of the server.

2. Attempt to connect as the example user with TLS disabled:

   # mysql -u example -p -h server.example.com --ssl-mode=DISABLED
   ERROR 1045 (28000): Access denied for user 'example'@'server.example.com' (using password: YES)

   Copy to Clipboard

   The server rejected the login attempt because TLS is required for this user but disabled (--ssl-mode=DISABLED).

Procedure

1. Stop the mysqld service:

   # systemctl stop mysqld.service

   Copy to Clipboard

2. Copy the data files to the required location:

   # cp -r /var/lib/mysql/ /backup-location/data/

   Copy to Clipboard

3. Optional: Copy the configuration files to the required location:

   # cp -r /etc/my.cnf /etc/my.cnf.d/ /backup-location/configuration/

   Copy to Clipboard

4. Optional: Copy the log files to the required location:

   # cp /var/log/mysql/* /backup-location/logs/

   Copy to Clipboard

5. Start the mysqld service:

   # systemctl start mysqld.service

   Copy to Clipboard

6. When loading the backed up data from the backup location to the /var/lib/mysql/ directory, ensure that mysql:mysql is an owner of all data in /var/lib/mysql/:

   # chown -R mysql:mysql /var/lib/mysql/

   Copy to Clipboard

Procedure

1. Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section:

   • bind-address=source_ip_adress

     This option is required for connections made from replicas to the source.

   • server-id=id

     The id must be unique.

   • log_bin=path_to_source_server_log

     This option defines a path to the binary log file of the MySQL source server. For example: log_bin=/var/log/mysql/mysql-bin.log.

   • gtid_mode=ON

     This option enables global transaction identifiers (GTIDs) on the server.

   • enforce-gtid-consistency=ON

     The server enforces GTID consistency by allowing execution of only statements that can be safely logged by using a GTID.

   • Optional: binlog_do_db=db_name

     Use this option if you want to replicate only selected databases. To replicate more than one selected database, specify each of the databases separately:

     binlog_do_db=db_name1
     binlog_do_db=db_name2
     binlog_do_db=db_name3

     Copy to Clipboard

   • Optional: binlog_ignore_db=db_name

     Use this option to exclude a specific database from replication.

2. Restart the mysqld service:

   # systemctl restart mysqld.service

   Copy to Clipboard

Procedure

1. Include the following options in the /etc/my.cnf.d/mysql-server.cnf file under the [mysqld] section:

   • server-id=id

     The id must be unique.

   • relay-log=path_to_replica_server_log

     The relay log is a set of log files created by the MySQL replica server during replication.

   • log_bin=path_to_replica_sever_log

     This option defines a path to the binary log file of the MySQL replica server. For example: log_bin=/var/log/mysql/mysql-bin.log.

     This option is not required in a replica but strongly recommended.

   • gtid_mode=ON

     This option enables global transaction identifiers (GTIDs) on the server.

   • enforce-gtid-consistency=ON

     The server enforces GTID consistency by allowing execution of only statements that can be safely logged by using a GTID.

   • log-replica-updates=ON

     This option ensures that updates received from the source server are logged in the replica’s binary log.

   • skip-replica-start=ON

     This option ensures that the replica server does not start the replication threads when the replica server starts.

   • Optional: binlog_do_db=db_name

     Use this option if you want to replicate only certain databases. To replicate more than one database, specify each of the databases separately:

     binlog_do_db=db_name1
     binlog_do_db=db_name2
     binlog_do_db=db_name3

     Copy to Clipboard

   • Optional: binlog_ignore_db=db_name

     Use this option to exclude a specific database from replication.

2. Restart the mysqld service:

   # systemctl restart mysqld.service

   Copy to Clipboard

Procedure

1. Create a replication user:

   mysql> CREATE USER 'replication_user'@'replica_server_hostname' IDENTIFIED WITH mysql_native_password BY 'password';

   Copy to Clipboard

2. Grant the user replication permissions:

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replica_server_hostname';*

   Copy to Clipboard

3. Reload the grant tables in the MySQL database:

   mysql> FLUSH PRIVILEGES;

   Copy to Clipboard

4. Set the source server to read-only state:

   mysql> SET @@GLOBAL.read_only = ON;

   Copy to Clipboard

Procedure

1. Set the replica server to read-only state:

   mysql> SET @@GLOBAL.read_only = ON;

   Copy to Clipboard

2. Configure the replication source:

   mysql> CHANGE REPLICATION SOURCE TO
          SOURCE_HOST='source_hostname',
          SOURCE_USER='replication_user',
          SOURCE_PASSWORD='password',
          SOURCE_AUTO_POSITION=1,
          SOURCE_SSL=1,
          SOURCE_SSL_CA='path_to_ca_on_source',
          SOURCE_SSL_CAPATH='path_to_directory_with_certificates',
          SOURCE_SSL_CERT='path_to_source_certificate',
          SOURCE_SSL_KEY='path_to_source_key';

   Copy to Clipboard

3. Start the replica thread in the MySQL replica server:

   mysql> START REPLICA;

   Copy to Clipboard

4. Unset the read-only state on both the source and replica servers:

   mysql> SET @@GLOBAL.read_only = OFF;

   Copy to Clipboard

5. Optional: Inspect the status of the replica server for debugging purposes:

   mysql> SHOW REPLICA STATUS\G;

   Copy to Clipboard
   Note

   If the replica server fails to start or connect, you can skip a certain number of events following the binary log file position displayed in the output of the SHOW MASTER STATUS command. For example, skip the first event from the defined position:

   mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

   Copy to Clipboard

   Afterwards, try to start the replica server again.

6. Optional: Stop the replica thread in the replica server:

   mysql> STOP REPLICA;

   Copy to Clipboard

Procedure

1. Create an example database on the source server:

   mysql> CREATE DATABASE test_db_name;

   Copy to Clipboard
2. Verify that the test_db_name database replicates on the replica server.

3. Display status information about the binary log files of the MySQL server by executing the following command on either of the source or replica servers:

   mysql> SHOW MASTER STATUS;

   Copy to Clipboard

   The Executed_Gtid_Set column, which shows a set of GTIDs for transactions executed on the source, must not be empty.

   Note

   The same set of GTIDs is displayed in the Executed_Gtid_Set row when you use the SHOW REPLICA STATUS on the replica server.