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 Install MySQL server packages: Start the mysqld service: Enable the mysqld service to start at boot: Recommended: To improve security when installing MySQL , run the following command: 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.2. 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 Use your Red Hat Customer Portal account to authenticate to the registry.redhat.io registry: Skip this step if you are already logged in to the container registry. Run MySQL 8.0 in a container: For more information about the usage of this container image, see the Red Hat Ecosystem Catalog . Run MariaDB 10.5 in a container: For more information about the usage of this container image, see the Red Hat Ecosystem Catalog . Run MariaDB 10.11 in a container: 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. To ensure that clients can access the database server on the network, open the host ports in the firewall: Verification Display information about running containers: Connect to the database server and log in as root: Additional resources Building, running, and managing containers Browse containers in the Red Hat Ecosystem Catalog 3.3. Configuring MySQL To configure the MySQL server for networking, use the following procedure. Procedure 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. Restart the mysqld service: 3.4. 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.4.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 Store the CA and server certificates in the /etc/pki/tls/certs/ directory: Set permissions on the CA and server certificate that enable the MySQL server to read the files: 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. Store the server's private key in the /etc/pki/tls/private/ directory: Set secure permissions on the server's private key: If unauthorized users have access to the private key, connections to the MySQL server are no longer secure. Restore the SELinux context: 3.4.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 Create the /etc/my.cnf.d/mysql-server-tls.cnf file: Add the following content to configure the paths to the private key, server and CA certificate: If you have a Certificate Revocation List (CRL), configure the MySQL server to use it: Optional: Reject connection attempts without encryption. To enable this feature, append: Optional: Set the TLS versions the server should support. For example, to support TLS 1.2 and TLS 1.3, append: By default, the server supports TLS 1.1, TLS 1.2, and TLS 1.3. Restart the mysqld service: Verification To simplify troubleshooting, perform the following steps on the MySQL server before you configure the local client to use TLS encryption: Verify that MySQL now has TLS encryption enabled: If you configured the MySQL server to only support specific TLS versions, display the tls_version variable: Verify that the server uses the correct CA certificate, server certificate, and private key files: Additional resources Placing the CA certificate, server certificate, and private key on the MySQL server 3.4.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 Connect as an administrative user to the MySQL server: If your administrative user has no permissions to access the server remotely, perform the command on the MySQL server and connect to localhost . Use the REQUIRE SSL clause to enforce that a user must connect using a TLS-encrypted connection: Verification Connect to the server as the example user using TLS encryption: 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. Attempt to connect as the example user with TLS disabled: The server rejected the login attempt because TLS is required for this user but disabled ( --ssl-mode=DISABLED ). Additional resources Configuring TLS on a MySQL server 3.5. 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.5.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: 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: 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 : Additional resources The --ssl* parameter descriptions in the mysql(1) man page on your system 3.6. Backing up MySQL data There are two main ways to back up data from a MySQL database: Logical 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 only be performed if the mysqld.service is running. Logical backup does not include log and configuration files. Physical backup 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.6.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: To dump multiple databases at once, run: To dump all databases, run: To load one or more dumped full databases back into a server, run: To load a database to a remote MySQL server, run: To dump a literal,subset of tables from one database, add a list of the chosen tables at the end of the mysqldump command: To load a literal,subset of tables dumped from one database, run: Note The db_name database must exist at this point. To see a list of the options that mysqldump supports, run: Additional resources Logical backup with mysqldump 3.6.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 Stop the mysqld service: Copy the data files to the required location: Optional: Copy the configuration files to the required location: Optional: Copy the log files to the required location: Start the mysqld service: 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 : 3.6.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 MySQL replication documentation 3.7. 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 Ensure that the mysql-server package is installed on the RHEL 9 system: Ensure that the mysqld service is not running on either of the source and target systems at the time of copying data: Copy the data from the source location to the /var/lib/mysql/ directory on the RHEL 9 target system. Set the appropriate permissions and SELinux context for copied files on the target system: Ensure that mysql:mysql is an owner of all data in the /var/lib/mysql directory: Start the MySQL server on the target system: 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.8. Replicating MySQL with TLS encryption 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: Configure a source server Configure a replica server Create a replication user on the source server Connect the replica server to the source server Important If you want to use existing MySQL servers for replication, you must first synchronize data. See the upstream documentation for more information. 3.8.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 through the TLS protocol. Prerequisites The source server is installed. The source server has TLS set up . Important The source and replica certificates must be signed by the same certificate authority. Procedure 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: Optional: binlog_ignore_db= db_name Use this option to exclude a specific database from replication. Restart the mysqld service: 3.8.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. The replica server has TLS set up . Important The source and replica certificates must be signed by the same certificate authority. Procedure 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: Optional: binlog_ignore_db= db_name Use this option to exclude a specific database from replication. Restart the mysqld service: 3.8.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 The source server is installed and configured as described in Configuring a MySQL source server . Procedure Create a replication user: Grant the user replication permissions: Reload the grant tables in the MySQL database: Set the source server to read-only state: 3.8.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 The source server is installed and configured as described in Configuring a MySQL source server . The replica server is installed and configured as described in Configuring a MySQL replica server . You have created a replication user. See Creating a replication user on the MySQL source server . Procedure Set the replica server to read-only state: Configure the replication source: Start the replica thread in the MySQL replica server: Unset the read-only state on both the source and replica servers: Optional: Inspect the status of the replica server for debugging purposes: 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: and try to start the replica server again. Optional: Stop the replica thread in the replica server: 3.8.5. Verifying replication on a MySQL server After you configured replication among multiple MySQL servers, you should verify that that it works. Procedure Create an example database on the source server: Verify that the test_db_name database replicates on the replica server. 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: 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. 3.8.5.1. Additional resources MySQL Replication documentation Replication with Global Transaction Identifiers 3.9. 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.

Commands:
dnf install mysql-server
systemctl start mysqld.service
systemctl enable mysqld.service
mysql_secure_installation
podman login registry.redhat.io
podman run -d --name <container_name> -e MYSQL_ROOT_PASSWORD= <mysql_root_password> -p <host_port_1> :3306 rhel9/mysql-80
podman run -d --name <container_name> -e MYSQL_ROOT_PASSWORD= <mariadb_root_password> -p <host_port_2> :3306 rhel9/mariadb-105
podman run -d --name <container_name> -e MYSQL_ROOT_PASSWORD= <mariadb_root_password> -p <host_port_3> :3306 rhel9/mariadb-1011
firewall-cmd --permanent --add-port={ <host_port_1> /tcp, <host_port_2> /tcp, <host_port_3> /tcp,...} firewall-cmd --reload
podman ps
mysql -u root -p -h localhost -P <host_port> --protocol tcp
systemctl restart mysqld.service
mv <path> /server.example.com.crt.pem /etc/pki/tls/certs/ mv <path> /ca.crt.pem /etc/pki/tls/certs/
chmod 644 /etc/pki/tls/certs/server.example.com.crt.pem /etc/pki/tls/certs/ca.crt.pem
mv <path> /server.example.com.key.pem /etc/pki/tls/private/
chmod 640 /etc/pki/tls/private/server.example.com.key.pem chgrp mysql /etc/pki/tls/private/server.example.com.key.pem
restorecon -Rv /etc/pki/tls/
[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
ssl_crl = /etc/pki/tls/certs/example.crl.pem
require_secure_transport = on
tls_version = TLSv1.2 , TLSv1.3
systemctl restart mysqld.service
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 | +---------------+------------------------+
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'tls_version';" +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tls_version | TLSv1.2,TLSv1.3 | +---------------+-----------------+
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 | +-----------------+-------------------------------------------------+
mysql -u root -p -h server.example.com
MySQL [(none)]> ALTER USER 'example '@ '%' REQUIRE SSL;
mysql -u example -p -h server.example.com MySQL [(none)]>
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)
[client] ssl-mode=VERIFY_IDENTITY ssl-ca=/etc/pki/tls/certs/ca.crt.pem
mysql -u root -p -h server.example.com -e status SSL: Cipher in use is TLS_AES_256_GCM_SHA384
mysql -u root -p -h localhost -e status ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed
mysqldump [ options ] --databases db_name > backup-file.sql
mysqldump [ options ] --databases db_name1 [ db_name2 ...] > backup-file.sql
mysqldump [ options ] --all-databases > backup-file.sql
mysql < backup-file.sql
mysql --host= remote_host < backup-file.sql
mysqldump [ options ] db_name [ tbl_name ... ] > backup-file.sql
mysql db_name < backup-file.sql
mysqldump --help
systemctl stop mysqld.service
cp -r /var/lib/mysql / backup-location
cp -r /etc/my.cnf /etc/my.cnf.d / backup-location /configuration
cp /var/log/mysql/* /backup-location /logs
systemctl start mysqld.service
chown -R mysql:mysql /var/lib/mysql
dnf install mysql-server
systemctl stop mysqld.service
restorecon -vr /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld.service
binlog_do_db= db_name1 binlog_do_db= db_name2 binlog_do_db= db_name3
systemctl restart mysqld.service
binlog_do_db= db_name1 binlog_do_db= db_name2 binlog_do_db= db_name3
systemctl restart mysqld.service
mysql> CREATE USER ' replication_user'@'replica_server_hostname ' IDENTIFIED WITH mysql_native_password BY ' password ';
mysql> GRANT REPLICATION SLAVE ON *.* TO ' replication_user '@' replica_server_hostname ';
mysql> FLUSH PRIVILEGES;
mysql> SET @@GLOBAL.read_only = ON;
mysql> SET @@GLOBAL.read_only = ON;
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 ';
mysql> START REPLICA;
mysql> SET @@GLOBAL.read_only = OFF;
mysql> SHOW REPLICA STATUS\G;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> STOP REPLICA;
mysql> CREATE DATABASE test_db_name ;
mysql> SHOW MASTER STATUS;