Chapter 3. Using MySQL

The MySQL server is an open source fast and robust database server. MySQL is a relational database that converts data into structured information and provides an SQL interface for accessing data. It includes multiple storage engines and plugins, as well as geographic information system (GIS) and JavaScript Object Notation (JSON) features.

Learn how to install and configure MySQL on a RHEL system, how to back up MySQL data, how to migrate from an earlier MySQL version, and how to replicate a MySQL.

3.1. Installing MySQL

RHEL 9.0 provides MySQL 8.0 as the initial version of this Application Stream, which you can install easily as an RPM package.

Note

The MySQL and MariaDB database servers cannot be installed in parallel in RHEL 9 due to conflicting RPM packages. You can use the MySQL and MariaDB database servers in parallel in containers, see Running multiple MySQL and MariaDB versions in containers.

To install MySQL, use the following procedure.

Procedure

  1. Install MySQL server packages:

    # dnf install mysql-server
  2. Start the mysqld service:

    # systemctl start mysqld.service
  3. Enable the mysqld service to start at boot:

    # systemctl enable mysqld.service
  4. Recommended: To improve security when installing MySQL, run the following command:

    $ mysql_secure_installation

    The command launches a fully interactive script, which prompts for each step in the process. The script enables you to improve security in the following ways:

    • Setting a password for root accounts
    • Removing anonymous users
    • Disallowing remote root logins (outside the local host)

3.1.1. Running multiple MySQL and MariaDB versions in containers

To run both MySQL and MariaDB on the same host, run them in containers because you cannot install these database servers in parallel due to conflicting RPM packages.

This procedure includes MySQL 8.0 and MariaDB 10.5 as examples but you can use any MySQL or MariaDB container version available in the Red Hat Ecosystem Catalog.

Prerequisites

  • The container-tools meta-package is installed.

Procedure

  1. Use your Red Hat Customer Portal account to authenticate to the registry.redhat.io registry:

    # podman login registry.redhat.io

    Skip this step if you are already logged in to the container registry.

  2. Run MySQL 8.0 in a container:

    $ podman run -d --name <container_name> -e MYSQL_ROOT_PASSWORD=<mysql_root_password> -p <host_port_1>:3306 rhel9/mysql-80

    For more information about the usage of this container image, see the Red Hat Ecosystem Catalog.

  3. Run MariaDB 10.5 in a container:

    $ podman run -d --name <container_name> -e MYSQL_ROOT_PASSWORD=<mariadb_root_password> -p <host_port_2>:3306 rhel9/mariadb-105

    For more information about the usage of this container image, see the Red Hat Ecosystem Catalog.

  4. Run MariaDB 10.11 in a container:

    $ podman run -d --name <container_name> -e MYSQL_ROOT_PASSWORD=<mariadb_root_password> -p <host_port_3>:3306 rhel9/mariadb-1011

    For more information about the usage of this container image, see the Red Hat Ecosystem Catalog.

    Note

    The container names and host ports of the two database servers must differ.

  5. To ensure that clients can access the database server on the network, open the host ports in the firewall:

    # firewall-cmd --permanent --add-port={<host_port_1>/tcp,<host_port_2>/tcp,<host_port_3>/tcp,...}
    # firewall-cmd --reload

Verification steps

  1. Display information about running containers:

    $ podman ps
  2. Connect to the database server and log in as root:

    # mysql -u root -p -h localhost -P <host_port> --protocol tcp

3.2. Configuring MySQL

To configure the MySQL server for networking, use the following procedure.

Procedure

  1. Edit the [mysqld] section of the /etc/my.cnf.d/mysql-server.cnf file. You can set the following configuration directives:

    • bind-address - is the address on which the server listens. Possible options are:

      • a host name
      • an IPv4 address
      • an IPv6 address
    • skip-networking - controls whether the server listens for TCP/IP connections. Possible values are:

      • 0 - to listen for all clients
      • 1 - to listen for local clients only
    • port - the port on which MySQL listens for TCP/IP connections.
  2. Restart the mysqld service:

    # systemctl restart mysqld.service

3.3. Setting up TLS encryption on a MySQL server

By default, MySQL uses unencrypted connections. For secure connections, enable TLS support on the MySQL server and configure your clients to establish encrypted connections.

3.3.1. Placing the CA certificate, server certificate, and private key on the MySQL server

Before you can enable TLS encryption on the MySQL server, store the certificate authority (CA) certificate, the server certificate, and the private key on the MySQL server.

Prerequisites

  • The following files in Privacy Enhanced Mail (PEM) format have been copied to the server:

    • The private key of the server: server.example.com.key.pem
    • The server certificate: server.example.com.crt.pem
    • The Certificate Authority (CA) certificate: ca.crt.pem

    For details about creating a private key and certificate signing request (CSR), as well as about requesting a certificate from a CA, see your CA’s documentation.

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/

3.3.2. Configuring TLS on a MySQL server

To improve security, enable TLS support on the MySQL server. As a result, clients can transmit data with the server using TLS encryption.

Prerequisites

  • You installed the MySQL server.
  • The mysqld service is running.
  • The following files in Privacy Enhanced Mail (PEM) format exist on the server and are readable by the mysql user:

    • The private key of the server: /etc/pki/tls/private/server.example.com.key.pem
    • The server certificate: /etc/pki/tls/certs/server.example.com.crt.pem
    • The Certificate Authority (CA) certificate /etc/pki/tls/certs/ca.crt.pem
  • The subject distinguished name (DN) or the subject alternative name (SAN) field in the server certificate matches the server’s host name.

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 TLS 1.2 and TLS 1.3, append:

      tls_version = TLSv1.2,TLSv1.3

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

  2. Restart the mysqld service:

    # systemctl restart mysqld

Verification

To simplify troubleshooting, perform the following steps on the MySQL server before you configure the local client to use TLS encryption:

  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.2,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 |
    +-----------------+-------------------------------------------------+

3.3.3. Requiring TLS encrypted connections for specific user accounts

Users that have access to sensitive data should always use a TLS-encrypted connection to avoid sending data unencrypted over the network.

If you cannot configure on the server that a secure transport is required for all connections (require_secure_transport = on), configure individual user accounts to require TLS encryption.

Prerequisites

  • The MySQL server has TLS support enabled.
  • The user you configure to require secure transport exists.
  • The CA certificate is stored on the client.

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

Additional resources

3.4. Globally enabling TLS encryption with CA certificate validation in MySQL clients

If your MySQL server supports TLS encryption, configure your clients to establish only secure connections and to verify the server certificate. This procedure describes how to enable TLS support for all users on the server.

3.4.1. Configuring the MySQL client to use TLS encryption by default

On RHEL, you can globally configure that the MySQL client uses TLS encryption and verifies that the Common Name (CN) in the server certificate matches the hostname the user connects to. This prevents man-in-the-middle attacks.

Prerequisites

  • The MySQL server has TLS support enabled.
  • The CA certificate is stored in the /etc/pki/tls/certs/ca.crt.pem file on the client.

Procedure

  • Create the /etc/my.cnf.d/mysql-client-tls.cnf file with the following content:

    [client]
    ssl-mode=VERIFY_IDENTITY
    ssl-ca=/etc/pki/tls/certs/ca.crt.pem

    These settings define that the MySQL client uses TLS encryption and that the client compares the hostname with the CN in the server certificate (ssl-mode=VERIFY_IDENTITY). Additionally, it specifies the path to the CA certificate (ssl-ca).

Verification

  • Connect to the server using the hostname, and display the server status:

    # mysql -u root -p -h server.example.com -e status
    ...
    SSL:        Cipher in use is TLS_AES_256_GCM_SHA384

    If the SSL entry contains Cipher in use is…​, the connection is encrypted.

    Note that the user you use in this command has permissions to authenticate remotely.

    If the hostname you connect to does not match the hostname in the TLS certificate of the server, the ssl-mode=VERIFY_IDENTITY parameter causes the connection to fail. For example, if you connect to localhost:

    # mysql -u root -p -h localhost -e status
    ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed

Additional resources

  • The --ssl* parameter descriptions in the mysql(1) man page.

3.5. Backing up MySQL data

There are two main ways to back up data from a MySQL database in Red Hat Enterprise Linux 9:

  • Logical backup
  • Physical backup

Logical backup consists of the SQL statements necessary to restore the data. This type of backup exports information and records in plain text files.

The main advantage of logical backup over physical backup is portability and flexibility. The data can be restored on other hardware configurations, MySQL versions or Database Management System (DBMS), which is not possible with physical backups.

Note that logical backup can be performed if the mysqld.service is running. Logical backup does not include log and configuration files.

Physical backup consists of copies of files and directories that store the content.

Physical backup has the following advantages compared to logical backup:

  • Output is more compact.
  • Backup is smaller in size.
  • Backup and restore are faster.
  • Backup includes log and configuration files.

Note that physical backup must be performed when the mysqld.service is not running or all tables in the database are locked to prevent changes during the backup.

You can use one of the following MySQL backup approaches to back up data from a MySQL database:

  • Logical backup with mysqldump
  • File system backup
  • Replication as a backup solution

3.5.1. Performing logical backup with mysqldump

The mysqldump client is a backup utility, which can be used to dump a database or a collection of databases for the purpose of a backup or transfer to another database server. The output of mysqldump typically consists of SQL statements to re-create the server table structure, populate it with data, or both. mysqldump can also generate files in other formats, including XML and delimited text formats, such as CSV.

To perform the mysqldump backup, you can use one of the following options:

  • Back up one or more selected databases
  • Back up all databases
  • Back up a subset of tables from one database

Procedure

  • To dump a single database, run:

    # mysqldump [options] --databases db_name > backup-file.sql
  • To dump multiple databases at once, run:

    # mysqldump [options] --databases db_name1 [db_name2 ...] > backup-file.sql
  • To dump all databases, run:

    # mysqldump [options] --all-databases > backup-file.sql
  • To load one or more dumped full databases back into a server, run:

    # mysql < backup-file.sql
  • To load a database to a remote MySQL server, run:

    # mysql --host=remote_host < backup-file.sql
  • To dump a literal,subset of tables from one database, add a list of the chosen tables at the end of the mysqldump command:

    # mysqldump [options] db_name [tbl_name ...​] > backup-file.sql
  • To load a literal,subset of tables dumped from one database, run:

    # mysql db_name < backup-file.sql
    Note

    The db_name database must exist at this point.

  • To see a list of the options that mysqldump supports, run:

    $ mysqldump --help

Additional resources

3.5.2. Performing file system backup

To create a file system backup of MySQL data files, copy the content of the MySQL data directory to your backup location.

To back up also your current configuration or the log files, use the optional steps of the following procedure.

Procedure

  1. Stop the mysqld service:

    # systemctl stop mysqld.service
  2. Copy the data files to the required location:

    # cp -r /var/lib/mysql /backup-location
  3. Optionally, copy the configuration files to the required location:

    # cp -r /etc/my.cnf /etc/my.cnf.d /backup-location/configuration
  4. Optionally, copy the log files to the required location:

    # cp /var/log/mysql/* /backup-location/logs
  5. Start the mysqld service:

    # systemctl start mysqld.service
  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

3.5.3. Replication as a backup solution

Replication is an alternative backup solution for source servers. If a source server replicates to a replica server, backups can be run on the replica without any impact on the source. The source can still run while you shut down the replica and back the data up from the replica.

For instructions on how to replicate a MySQL database, see Replicating MySQL.

Warning

Replication itself is not a sufficient backup solution. Replication protects source servers against hardware failures, but it does not ensure protection against data loss. It is recommended that you use any other backup solution on the replica together with this method.

Additional resources

3.6. Migrating to a RHEL 9 version of MySQL 8.0

RHEL 8 contains the MySQL 8.0, MariaDB 10.3, and MariaDB 10.5 implementations of a server from the MySQL databases family. RHEL 9 provides MySQL 8.0 and MariaDB 10.5.

This procedure describes migration from a RHEL 8 version of MySQL 8.0 to a RHEL 9 version of MySQL 8.0 using the mysql_upgrade utility. The mysql_upgrade utility is provided by the mysql-server package.

Prerequisites

  • Before performing the upgrade, back up all your data stored in the MySQL databases. See Backing up MySQL data.

Procedure

  1. Ensure that the mysql-server package is installed on the RHEL 9 system:

    # dnf install mysql-server
  2. Ensure that the mysqld service is not running on either of the source and target systems at the time of copying data:

    # systemctl stop mysqld.service
  3. Copy the data from the source location to the /var/lib/mysql/ directory on the RHEL 9 target system.
  4. Set the appropriate permissions and SELinux context for copied files on the target system:

    # restorecon -vr /var/lib/mysql
  5. Ensure that mysql:mysql is an owner of all data in the /var/lib/mysql directory:

    # chown -R mysql:mysql /var/lib/mysql
  6. Start the MySQL server on the target system:

    # systemctl start mysqld.service

    Note: In earlier versions of MySQL, the mysql_upgrade command was needed to check and repair internal tables. This is now done automatically when you start the server.

3.7. Replicating MySQL

MySQL provides various configuration options for replication, ranging from basic to advanced. This section describes a transaction-based way to replicate in MySQL on freshly installed MySQL servers using global transaction identifiers (GTIDs). Using GTIDs simplifies transaction identification and consistency verification.

To set up replication in MySQL, you must:

Important

If you want to use existing MySQL servers for replication, you must first synchronize data. See the upstream documentation for more information.

3.7.1. Configuring a MySQL source server

You can set configuration options required for a MySQL source server to properly run and replicate all changes made on the database server.

Prerequisites

  • The source server is installed.

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

3.7.2. Configuring a MySQL replica server

You can set configuration options required for a MySQL replica server to ensure a successful replication.

Prerequisites

  • The replica server is installed.

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

3.7.3. Creating a replication user on the MySQL source server

You must create a replication user and grant this user permissions required for replication traffic. This procedure shows how to create a replication user with appropriate permissions. Execute these steps only on the source server.

Prerequisites

Procedure

  1. Create a replication user:

    mysql> CREATE USER 'replication_user'@'replica_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';
  2. Grant the user replication permissions:

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replica_server_ip';
  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;

3.7.4. Connecting the replica server to the source server

On the MySQL replica server, you must configure credentials and the address of the source server. Use the following procedure to implement the replica server.

Prerequisites

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_ip_address',
        -> SOURCE_USER='replication_user',
        -> SOURCE_PASSWORD='password',
        -> SOURCE_AUTO_POSITION=1;
  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;

3.7.5. Verification steps

  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 SLAVE STATUS on the replica server.

3.7.6. Additional resources

3.8. Developing MySQL client applications

Red Hat recommends developing your MySQL client applications against the MariaDB client library. The communication protocol between client and server is compatible between MariaDB and MySQL. The MariaDB client library works for most common MySQL scenarios, with the exception of a limited number of features specific to the MySQL implementation.

The development files and programs necessary to build applications against the MariaDB client library are provided by the mariadb-connector-c-devel package.

Instead of using a direct library name, use the mariadb_config program, which is distributed in the mariadb-connector-c-devel package. This program ensures that the correct build flags are returned.