Este conteúdo não está disponível no idioma selecionado.
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.
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:
# dnf install mysql-server
Start the
mysqld
service:# systemctl start mysqld.service
Enable the
mysqld
service to start at boot:# systemctl enable mysqld.service
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.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:# podman login registry.redhat.io
Skip this step if you are already logged in to the container registry.
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.
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.
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.
NoteThe 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:
# firewall-cmd --permanent --add-port={<host_port_1>/tcp,<host_port_2>/tcp,<host_port_3>/tcp,...} # firewall-cmd --reload
Verification
Display information about running containers:
$ podman ps
Connect to the database server and log in as root:
# mysql -u root -p -h localhost -P <host_port> --protocol tcp
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:# systemctl restart 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.
-
The private key of the server:
Procedure
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/
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.
Store the server’s private key in the
/etc/pki/tls/private/
directory:# mv <path>/server.example.com.key.pem /etc/pki/tls/private/
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.
Restore the SELinux context:
# restorecon -Rv /etc/pki/tls/
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 private key of the server:
- 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:
[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
If you have a Certificate Revocation List (CRL), configure the MySQL server to use it:
ssl_crl = /etc/pki/tls/certs/example.crl.pem
Optional: Reject connection attempts without encryption. To enable this feature, append:
require_secure_transport = on
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.
Restart the
mysqld
service:# systemctl restart 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:
# 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 | +---------------+------------------------+
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 | +---------------+-----------------+
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 | +-----------------+-------------------------------------------------+
Additional resources
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:
# 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
.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
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.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.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:[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 containsCipher 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 tolocalhost
:# 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 themysql(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:
# 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
NoteThe db_name database must exist at this point.
To see a list of the options that mysqldump supports, run:
$ mysqldump --help
Additional resources
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:# systemctl stop mysqld.service
Copy the data files to the required location:
# cp -r /var/lib/mysql /backup-location
Optional: Copy the configuration files to the required location:
# cp -r /etc/my.cnf /etc/my.cnf.d /backup-location/configuration
Optional: Copy the log files to the required location:
# cp /var/log/mysql/* /backup-location/logs
Start the
mysqld
service:# systemctl start mysqld.service
When loading the backed up data from the backup location to the
/var/lib/mysql
directory, ensure thatmysql:mysql
is an owner of all data in/var/lib/mysql
:# chown -R mysql:mysql /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.
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.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:# dnf install mysql-server
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
-
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:
# restorecon -vr /var/lib/mysql
Ensure that
mysql:mysql
is an owner of all data in the/var/lib/mysql
directory:# chown -R mysql:mysql /var/lib/mysql
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.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:
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.
ImportantThe 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:
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.
Restart the
mysqld
service:# systemctl restart 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.
ImportantThe 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:
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.
Restart the
mysqld
service:# systemctl restart 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:
mysql> CREATE USER 'replication_user'@'replica_server_hostname' IDENTIFIED WITH mysql_native_password BY 'password';
Grant the user replication permissions:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'replica_server_hostname';
Reload the grant tables in the MySQL database:
mysql> FLUSH PRIVILEGES;
Set the source server to read-only state:
mysql> SET @@GLOBAL.read_only = ON;
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:
mysql> SET @@GLOBAL.read_only = ON;
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';
Start the replica thread in the MySQL replica server:
mysql> START REPLICA;
Unset the read-only state on both the source and replica servers:
mysql> SET @@GLOBAL.read_only = OFF;
Optional: Inspect the status of the replica server for debugging purposes:
mysql> SHOW REPLICA STATUS\G;
NoteIf 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.
Optional: Stop the replica thread in the replica server:
mysql> STOP REPLICA;
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:
mysql> CREATE DATABASE test_db_name;
-
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:
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.NoteThe same set of GTIDs is displayed in the
Executed_Gtid_Set
row when you use theSHOW REPLICA STATUS
on the replica server.
3.8.5.1. Additional resources
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.