Chapter 3. 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/

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

   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/

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

   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/

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

   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

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

      require_secure_transport = on

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

      tls_version = TLSv1.3

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

2. Restart the mysqld service:

   # systemctl restart mysqld.service

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 |
   +---------------+------------------------+

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 |
   +---------------+---------+

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 '^ssl_ca|^ssl_cert|^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 |
   +-----------------+-------------------------------------------------+

Procedure

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

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

   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 using a TLS-encrypted connection:

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

Verification

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

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

   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)

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

Procedure

1. If the database you want to restore already exists and your SQL file does not contain DROP TABLE IF EXISTS statements, you must manually remove the tables or the entire database:

   • To remove a table, enter:

     # mysql -u root -p -e "DROP TABLE <database>.<table>;"

     Repeat this command for every table that the SQL file will re-create.

   • To remove a database, enter:

     # mysql -u root -p -e "DROP DATABASE <database>;"

     Repeat this command for every database that the SQL file will re-create.

2. Import the SQL file:

   # mysql -u root -p < backup.sql"

Procedure

1. Stop the mysqld service:

   # systemctl stop mysqld.service

2. Create a backup directory, for example:

   # mkdir -p /root/mysqld-backup/{data,config}/

3. Back up the data directory:

   # cp -rp /var/lib/mysql/ /root/mysqld-backup/data/

4. Back up the configuration files:

   # cp -rp /etc/my.cnf /etc/my.cnf.d/ /root/mysqld-backup/config/

5. Start the mysqld service:

   # systemctl start mysqld.service

Procedure

1. Stop the mysqld service:

   # systemctl stop mysqld.service

2. Remove the current /var/lib/mysql/ directory:

   # rm -rf /var/lib/mysql/

3. Restore the data directory from your backup:

   # cp -rp /root/mysqld-backup/data/mysql/ /var/lib/

4. Ensure the correct ownership of the /var/lib/mysql/ directory:

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

5. Restore the SELinux context of the /var/lib/mysql/ directory:

   # restorecon -Rv /var/lib/mysql/

6. Remove the current configuration files:

   # rm -rf /etc/my.cnf /etc/my.cnf.d/

7. Restore the configuration files from your backup:

   # cp -rp /root/mysqld-backup/config/my.cnf /root/mysqld-backup/config/my.cnf.d/ /etc/

8. Ensure the correct ownership of the configuration files:

   # chown -R root:root /etc/my.cnf /etc/my.cnf.d/

9. Restore the SELinux context of the configuration files:

   # restorecon -Rv /etc/my.cnf /etc/my.cnf.d/

10. Start the mysqld service:

    # systemctl start mysqld.service

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 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

   • 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

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 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

   • 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

Procedure

1. Create a replication user:

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

2. Grant the user replication permissions:

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

3. Reload the grant tables in the MySQL database:

   mysql> FLUSH PRIVILEGES;

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

   mysql> SET @@GLOBAL.read_only = ON;

Procedure

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

   mysql> SET @@GLOBAL.read_only = ON;

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';

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

   mysql> START REPLICA;

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

   mysql> SET @@GLOBAL.read_only = OFF;

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

   mysql> SHOW REPLICA STATUS\G;

   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;

   and try to start the replica server again.

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

   mysql> STOP REPLICA;

Procedure

1. Create an example database on the source server:

   mysql> CREATE DATABASE test_db_name;

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;

   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.