Chapter 3. Using MySQL Red Hat Enterprise Linux 9 | Red Hat Customer Portal (2023)

The MySQL server is an open source fast and robust database server. This part describes 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.Getting started with MySQL

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 plug-ins, as well as geographic information system (GIS) and JavaScript Object Notation (JSON) features.

This part describes:

  • How to install the MySQL server in Installing MySQL.
  • How to adjust MySQL configuration in Configuring MySQL.
  • How to back up MySQL data in Backing up MySQL data.
  • How to migrate from a RHEL 8 version of MySQL 8.0 to a RHEL 9 version of MySQL 8.0 in Migrating to a RHEL 9 version of MySQL 8.0.
  • How to replicate a MySQL database in Replicating MySQL.

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

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)

Note

The MySQL and MariaDB database servers cannot be installed in parallel in RHEL 9 due to conflicting RPM packages. In RHEL 9, different versions of database servers can be used in containers.

3.3.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.4.Backing up MySQL data

There are two main ways to back up data from a MySQL database in RedHat EnterpriseLinux 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.4.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 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 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.4.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.4.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.

3.5.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.6.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:

  • 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.6.1.Configuring a MySQL source server

This section describes 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_name1binlog_do_db=db_name2binlog_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.6.2.Configuring a MySQL replica server

This section describes 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_name1binlog_do_db=db_name2binlog_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.6.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

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

  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.6.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.6.6.Additional resources

References

Top Articles
Latest Posts
Article information

Author: Otha Schamberger

Last Updated: 17/12/2023

Views: 5680

Rating: 4.4 / 5 (75 voted)

Reviews: 90% of readers found this page helpful

Author information

Name: Otha Schamberger

Birthday: 1999-08-15

Address: Suite 490 606 Hammes Ferry, Carterhaven, IL 62290

Phone: +8557035444877

Job: Forward IT Agent

Hobby: Fishing, Flying, Jewelry making, Digital arts, Sand art, Parkour, tabletop games

Introduction: My name is Otha Schamberger, I am a vast, good, healthy, cheerful, energetic, gorgeous, magnificent person who loves writing and wants to share my knowledge and understanding with you.