Chapter 4. Backup and Recovery

Backup and recovery are two distinct but related concepts in data protection. Backup is the process of making a copy of data and storing it in a safe location. Recovery, on the other hand, is the process of restoring data from a backup in the event of a data loss. The goal of backup and recovery is to ensure that data is available and usable when needed.

In this chapter, we will discuss the fundamental concepts of backup and recovery and then explore the various types available. We will also provide guidance on taking a backup, restoring a backup, and managing binary logs. Finally, we will learn how to manage your backup and recovery in order to ensure that your data is safe and easy to retrieve in the event of a disaster.

Before we dive into the specifics, it is essential to understand the basic concepts that underpin these critical activities.

As a database administrator, ensuring that your data is protected and recoverable in the event of a disaster is crucial. Data loss can occur due to a variety of reasons, including hardware failure, human error, natural disasters, or malicious attacks. Without proper backup and recovery mechanisms in place, a significant amount of time, money, and effort may be required to restore lost data or even recreate it from scratch.

The Factors to Consider When Choosing a Backup Strategy

Choosing a backup strategy that meets your business requirements can be challenging. Here are some factors to consider:

Recovery point objective (RPO)

The RPO is the maximum amount of data loss that can be tolerated in the event of a disaster. A shorter RPO requires more frequent backups or point in time recovery with streaming capabilities. An RPO of a few seconds or 0 typically can only be achieved with that.

Recovery time objective (RTO)

The RTO is the maximum amount of time it takes to recover from a disaster. A shorter RTO requires a faster recovery mechanism, such as a warm or hot standby system.

Backup type

MySQL supports several backup types, including physical backup, logical backup, incremental backup and differential backup. Each backup type has its advantages and disadvantages, and the choice of backup type depends on the business requirements.

Backup storage location

Backups should be stored in a secure location that is separate from the production system to avoid the risk of data loss due to a disaster affecting both the production system and the backup storage location.

In summary, we need to backup the entire data directory as a physical backup, including with binary logs. This backup will be essential in case of critical situations where we need to restore the instance or environment.

Difference Between Logical and Physical Backups

Logical backups and physical backups serve distinct purposes and are implemented differently.

A logical backup is a backup of the logical structure and contents of a database, such as tables, views, stored procedures, triggers, and data. It is created by dumping the data to a file using the utilities like mydumper or MySQL Shell’s Instance Dump Utility, Schema Dump Utility, and Table Dump Utility . Logical backups are portable and can be used to migrate a database from one server to another, or to restore specific tables or data to a database. However, they can be slower to create and restore than physical backups.

A physical backup, on the other hand, is a binary copy of the MySQL database files, including the data, indexes, and table structures. It is created by copying the physical files directly from the server to a backup storage device or server. Therefore they are faster to create and restore than logical backups.

Both types of backups are important for ensuring the availability and integrity of a MySQL database. Logical backups are useful for migrating data between servers, restoring specific tables or data, and for long-term archival purposes. Physical backups are useful for disaster recovery, system-level backups, and for high availability solutions such as database replication.

Physical Backups

The two most popular physical backups are MySQL Enterprise Backup and Percona XtraBackup Backup.

MySQL Enterprise Backup

To backup and restore a MySQL database, you can use the MySQL Enterprise Backup tool.

Backing up a MySQL instance is crucial for ensuring data recovery in case of any disaster or loss. It is essential to have a backup strategy in place that enables the restoration of data with minimal downtime.

Here is an example of how to configure a mysqlbackup (Figure 4-2).

Create a new directory named “backupdir” (Figure 4-1):

mkdir /backupdir
hmsa 0401
Figure 4-1. Create a directory
mysqlbackup --user=root --password --backup-image=/backupdir/my.mbi
--backup-dir=/backupdir/backup-tmp backup-to-image
hmsa 0402
Figure 4-2. mysqlbackup backup command

From the log, you can validate the server repository and backup configuration options (Figure 4-3).

hmsa 0403
Figure 4-3. Log - Server repository and backup configuration option

Upon the completion of the backup, you receive the status of completion as indicated in the log (Figure 4-4).

hmsa 0404
Figure 4-4. Log - Backup completion status

Here are the details of the various options used in the command:

--user

Specifies the MySQL user with sufficient privileges to access and backup the database.

--password

Provides the password for the MySQL user specified in the --user option.

--backup-image

Specifies the name and location of the backup file that will be created. This file will contain the entire MySQL instance.

--backup-dir

Specifies the temporary backup directory where the backup files will be stored during the backup process.

backup-to-image

Specifies the backup type.

Once the command is executed, the backup process will start, and the backup files will be stored in the specified backup directory. Upon completion of the backup process, the message mysqlbackup completed OK! will be displayed.

Upon completing a backup, it’s essential to verify the backup’s integrity and ensure that all data is correctly backed up. This can help avoid data loss due to corrupted backups.

The following command can be used to validate a backup image (Figure 4-5):

mysqlbackup --backup-image=/backupdir/my.mbi validate
hmsa 0405
Figure 4-5. Validate backup

Upon the completion of the validation, you will receive the backup completion status as displayed in the log (Figure 4-6).

hmsa 0406
Figure 4-6. Validate backup completion status

Let’s break this down further:

mysqlbackup

Validates the backup image.

--backup-image

Specifies the path to the backup image.

--validate

Specifies the validation operation.

If the validation is successful, the following message will be displayed:

mysqlbackup completed OK!

It’s also essential to check the backup logs to ensure that there were no errors during the backup process (Figure 4-7). For example:

cat MEB_2023-04-15.12-23-47_backup-to-image.log | grep "error"
hmsa 0407
Figure 4-7. Check the backup logs

These commands will display the backup-related entries in the backup log files.

Restoring a Database

Depending on the situation, such as a crash, human mistake and data loss, or disaster recovery, restoring a database can be a crucial activity.

Before restoring a backup, you need to shut down the MySQL instance. This is essential to avoid any data inconsistency issues. You can use the following command to stop the MySQL server (Figure 4-8):

systemctl stop mysqld
hmsa 0408
Figure 4-8. Shut down the MySQL instance

After shutting down the MySQL server, delete all the files located in the data directory (Figure 4-10). Additionally, delete all the files that you can find under the directory specified by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, if the directories are different from the data directory.

Prior to executing the rm -rf * command, please ensure that you are in the correct current directory and confirm that it is the data directory (Figure 4-9).

pwd
hmsa 0409
Figure 4-9. Print working directory
rm -rf *
hmsa 0410
Figure 4-10. Delete all files in the data directory

Once you have deleted all the necessary files, use the copy-back-and-apply-log option with the mysqlbackup command to restore the backup. This option is used to apply all the incremental changes from the backup to the target server. Here is a sample command for your reference (Figure 4-11):

mysqlbackup --datadir=/var/lib/mysql --backup-image=/backupdir/my.mbi
--backup-dir=/backupdir/backup-tmp copy-back-and-apply-log
hmsa 0411
Figure 4-11. Copy back and apply log

After restoring the backup, change the ownership of the restored files to the mysql user. You can use the following command to change the ownership (Figure 4-13). The restored files currently have ownership set to the root user (Figure 4-12):

cd /var/lib/mysql
ll
hmsa 0412
Figure 4-12. Change the current working directory and list files
chown -R mysql.mysql /var/lib/mysql
ll
hmsa 0413
Figure 4-13. Change the ownership

Once you have changed the ownership, start the MySQL server using the following command (Figure 4-14):

systemctl start mysqld
hmsa 0414
Figure 4-14. Start the MySQL server

In the case of setting up the replication to determine the binlog position from which to initiate replication, search for the file backup_variables.txt in the directory datadir/meta/ on the new replica server, where “datadir” represents the data directory path. Open the file and retrieve the most recent binary log position along with the corresponding log file number, which are both stored within the file. This information will be necessary for configuring the replica server to begin replication from the correct position in the binary log (Figure 4-15)`

pwd
cat backup_variables.txt | grep binlog_position
hmsa 0415
Figure 4-15. Find the binlog position
Note

The --replica-info option in MySQL Enterprise Backup 8.0.32 and later is used to capture the necessary information for setting up an identical replica server when backing up a replica server. The option --slave-info is valid for MySQL Enterprise Backup versions up to 8.0.31. This option generates a file named ibbackup_replica_info in the backup directory’s meta folder. The file contains a CHANGE REPLICATION SOURCE TO statement that includes the binary log file name and position of the source server. This information is also displayed in the output of the mysqlbackup command. To create a new replica for the source server, restore the backup data to a new server, start a replica server using the backup data, and execute the CHANGE REPLICATION SOURCE TO command with the binary log position specified in the ibbackup_replica_info file.

The option --slave-info has been deprecated since the MySQL Enterprise Backup version 8.0.32 and should now be replaced with --replica-info. In versions 8.0.31 and earlier, --slave-info was used to capture information necessary for setting up an identical replica server when backing up a replica server. This option would create a file called meta/ibbackup_slave_info within the backup directory that contained a CHANGE MASTER statement with the binary log position and name of the binary log file from the source server. This information would also be displayed in the mysqlbackup output. To set up a new replica using this information, you would restore the backup data on another server, start a replica server with the backup data, and issue a CHANGE MASTER command using the binary log position saved in the ibbackup_slave_info file.

Percona XtraBackup Backup

Percona XtraBackup is a open source tool used for backing up and restoring MySQL databases.

Before you start performing backups using Percona XtraBackup, there are certain prerequisites that must be met:

  • Percona XtraBackup must be installed on the server.

  • The MySQL instance being backed up must be running and accessible.

  • Sufficient disk space must be available to store the backup in a local share or in a remote server.

Backing up from a replication server:

Completing a full backup from a replica server using Percona XtraBackup is recommended as it reduces impact on the primary server. When initiating a backup on the primary server, it can cause some performance impact, which can affect the applications that rely on it. By taking the backup from a replica server, you can minimize the impact on the primary server, allowing it to continue serving the applications without interruption.

A full backup is the most comprehensive type of backup, where an entire database is backed up. The backup includes all the data, indexes, tables, views, triggers, and stored procedures. Percona XtraBackup is an open-source tool that is widely used for backups.

Here’s an example of completing a full backup (Figure 4-16):

xtrabackup --backup --target-dir=/root/backupdir -u root -p
hmsa 0416
Figure 4-16. xtrabackup command

The preceding command is just an example; refer to the Percona docs for a complete list of variable options available with the tool.

Here are the details of the various options used in the command:

  • To initiate a backup, execute xtrabackup with the --backup option.

  • The option --target-dir=DIRECTORY designates the backup’s destination directory. If the specified directory doesn’t exist, xtrabackup will create it. When the directory exists and is empty, the backup will proceed successfully. However, if the directory contains existing files, xtrabackup won’t overwrite them, resulting in a failure with operating system error 17 (file exists).

Note

The --slave-info option is particularly beneficial when creating a backup of a replication slave server. It not only displays the name and binary log position of the master server but also records this information in the xtrabackup_slave_info file as a CHANGE MASTER command. This allows you to conveniently set up a new slave server for the same master by starting a slave server on the backup and running the CHANGE MASTER command using the binary log position saved in the xtrabackup_slave_info file.

Restoring a Database

To restore a backup taken with Percona XtraBackup, you can follow these general steps:

Before restoring the backup, you need to prepare the backup files using the xtrabackup --prepare command. This step applies the pending changes to the backup data, so that it is in a consistent state (Figure 4-17):

sudo xtrabackup --prepare --target-dir=/root/backupdir
hmsa 0417
Figure 4-17. xtrabackup prepare command

Stop the MySQL server on the machine where you want to restore the backup (Figure 4-18):

sudo systemctl stop mysql
hmsa 0418
Figure 4-18. Stop MySQL server

Copy the backup files from their original backup location to the destination where you intend to perform the restoration. You can use a tool like rsync or scp to do this efficiently.

Copy the MySQL configuration files from the backup location to the MySQL configuration directory on the machine where you want to restore the backup. The location of the configuration directory may vary depending on your operating system.

If the backup was not copied to the data directory then we can use the xtrabackup --copy-back command to copy the files from the backup directory to the MySQL data directory (Figure 4-19):

sudo xtrabackup --copy-back --target-dir=/root/backupdir
hmsa 0419
Figure 4-19. Copy backup files to data directory

After copying the files, you need to set the ownership and permissions of the data directory to the mysql user (Figure 4-20):

sudo chown -R mysql:mysql /var/lib/mysql
hmsa 0420
Figure 4-20. Set the data directory ownership

Start the MySQL server with the following command (Figure 4-21):

sudo systemctl start mysql
hmsa 0421
Figure 4-21. Start the MySQL server

Incremental backups

Percona XtraBackup provides support for incremental backups, allowing it to copy only the data that has changed since the previous backup.

You can perform multiple incremental backups. This allows you to establish a backup schedule, such as performing a full backup once a week and incremental backups every day, or full backups every day with incremental backups every hour.

The incremental backup process works by leveraging the Log Sequence Number (LSN) that is associated with each InnoDB page. The LSN serves as a system version number for the entire database and indicates when a page was last modified.

During an incremental backup, only pages that have an LSN that is newer than the previous incremental or full backup’s LSN are copied. An algorithm is used to identify the pages that meet this criteria by reading the data pages and checking their respective LSN values.

To create an incremental backup using Percona XtraBackup, start with a regular full backup. After taking the full backup, the xtrabackup utility writes a file called xtrabackup_checkpoints to the backup’s target directory. This file contains a line showing the to_lsn, which represents the database’s Log Sequence Number (LSN) at the end of the backup process.

You can take a full backup by running the following command (Figure 4-22):

xtrabackup --backup --target-dir=/data/backups/base
hmsa 0422
Figure 4-22. Xtrabackup full backup

After completing the full backup, you can view the xtrabackup_checkpoints file to check the LSN value. The file will contain information similar to the following, depending on the LSN number (Figure 4-23):

pwd
cat xtrabackup_checkpoints
hmsa 0423
Figure 4-23. Check the LSN value

This output confirms that the full backup was successful and shows the to_lsn value that will be used as a reference point for the next incremental backup.

Once you have taken a full backup, you can create an incremental backup based on it using the xtrabackup command. The following command can be used to create an incremental backup (Figure 4-24):

xtrabackup --backup --target-dir=/data/backups/inc1
--incremental-basedir=/data/backups/base -u root -p
hmsa 0424
Figure 4-24. Incremental backup1

This command creates an incremental backup in the /data/backups/inc1/ directory, based on the previous full backup taken in /data/backups/base/. The delta files generated by this command represent the changes made to the database since the LSN value of the previous backup. For example, you may see files such as ibdata1.delta and test/table1.ibd.delta in the incremental backup directory.

After completing the incremental backup, you can check the xtrabackup_checkpoints file in the /data/backups/inc1/ directory. This file should show the new LSN value, as well as the incremental-basedir value that was used as the reference point for the backup process. The content of the xtrabackup_checkpoints file may look similar to the following (Figure 4-25):

pwd
cat xtrabackup_checkpoints
hmsa 0425
Figure 4-25. LSN value

The from_lsn value in the xtrabackup_checkpoints file represents the starting LSN of the incremental backup, and it should be the same as the to_lsn value of the previous or base backup’s checkpoint file.

After creating the first incremental backup, you can use it as the base for creating another incremental backup. You can use the following command to create an incremental backup based on the previous incremental backup (Figure 4-26):

xtrabackup --backup --target-dir=/data/backups/inc2
--incremental-basedir=/data/backups/inc1
hmsa 0426
Figure 4-26. Incremental backup2

This command creates a new incremental backup in the /data/backups/inc2/ directory, based on the previous incremental backup in /data/backups/inc1/. The xtrabackup_checkpoints file in this directory should show the new LSN value, which represents the end point of the new incremental backup (Figure 4-27).

You can continue creating multiple incremental backups in this way, with each new incremental backup based on the previous one. Each incremental backup will contain only the changes made since the previous backup, which helps to reduce the amount of time and space needed for backup and restore operations:

pwd
cat xtrabackup_checkpoints
hmsa 0427
Figure 4-27. LSN value

How to Prepare Incremental Backups

The --prepare step for incremental backups is different from that for full backups. In full backups, the --prepare step performs two types of operations to make the database consistent: it replays committed transactions from the log file against the data files, and rolls back uncommitted transactions. However, when preparing an incremental backup, you need to skip the rollback of uncommitted transactions. This is because transactions that were uncommitted at the time of your backup may still be in progress, and are likely to be committed in the next incremental backup.

To prevent the rollback phase during the --prepare step for incremental backups, you should use the --apply-log-only option. This option only applies the log files to the backup to update its pages and does not roll back any transactions. After applying the log files, the backup is ready to be restored to the point in time when the backup was taken.

Run the following command to prepare the base backup (Figure 4-28):

$ xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
hmsa 0428
Figure 4-28. Prepare Incremental Backups

After this command completes, the output should end with text similar to the following (Figure 4-29).

hmsa 0429
Figure 4-29. Completion status of xtrabackup

To apply the first incremental backup to the full backup, you can run the following command (Figure 4-30):

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1
hmsa 0430
Figure 4-30. Apply the first incremental backup

This command applies the delta files in /data/backups/inc1 to the files in /data/backups/base, which rolls them forward in time to the time of the incremental backup. It then applies the redo log as usual to the result. The final data is in /data/backups/base, not in the incremental directory.

When merging incremental backups, the --apply-log-only option should be used for all the intermediate incremental backups except for the last one. This is because the --apply-log-only option skips the rollback phase and prepares the backup only for applying the next incremental backup.

Therefore, for merging the last incremental backup, you should run the following command (Figure 4-31):

xtrabackup --prepare --target-dir=/data/backups/base
--incremental-dir=/data/backups/inc2
hmsa 0431
Figure 4-31. Merging the last incremental backup

Since this is the last incremental backup, you can run --prepare without the --apply-log-only option. The backup will still be consistent, but the server will perform the rollback phase. After the preparation, the backup is ready to be used.

Logical Backup

The most popular logical backup tools are MYSQL Dump Utility using MySQL Shell, mydumper and mysqldump.

MySQL Shell dump utilities

MySQL Shell is an advanced command-line client and scripting interface for MySQL. It provides a powerful set of utilities for database management and administration, including the Dump Utility, which is used for backing up and restoring databases.

Dump Utility is a feature-rich utility provided by MySQL Shell for backing up MySQL databases. It enables you to perform full instance backups or partial backups of selected schemas or tables. You can also customize the backup process using various options available in the utility.

Following is an example, once you have logged in, you can use the Dump Utility to perform backups (Figure 4-32):

mysqlsh --uri <mysql-username>@<mysql-host>:<mysql-port>
mysqlsh --uri root@localhost:3306
hmsa 0432
Figure 4-32. MySQL Shell

Before running the actual backup, it is recommended to perform a dryRun procedure to validate the backup process. The following command, util.dumpInstance can be used for a dry run procedure (Figure 4-33):

util.dumpInstance("/mysqlsh/backuputildump/", \{dryRun:"true"})
hmsa 0433
Figure 4-33. DryRun

A full instance backup ensures that you have a backup of the entire database instance in case of any unforeseen circumstances. You can use the following command to perform a full instance backup (Figure 4-34):

util.dumpInstance("<backup-location>")
util.dumpInstance("/backuputildump")
hmsa 0434
Figure 4-34. Full instance backup

To run the instance dump with more advanced options use the following command (Figure 4-35):

util.dumpInstance("<backup-location>",
\{threads:8,maxRate:"100M",consistent:true,chunking:true,bytesPerchunk:"64M",compression:"zstd"})

util.dumpInstance("/backuputildump/mysqlinstance",
\{threads:8,maxRate:"100M",consistent:true,chunking:true,bytesPerchunk:"64M",compression:"zstd"})
hmsa 0435
Figure 4-35. Instance dump with more advanced options

In this command, you can specify various options to control and enhance the dump process. For example, you can specify the number of threads to be used, the maximum rate at which the backup should be written, whether to perform a consistent backup or not, whether to perform chunking of the backup data or not, the size of each chunk, and the compression algorithm to be used.

Use the dumpSchemas util to perform partial backups of chosen schemas. You can use the following command, util.dumpSchemas, to backup specified schemas (Figure 4-36):

util.dumpSchemas("<backup-location>", "<schema1>", "<schema2>", ...)
util.dumpSchemas(["employees"],"/backupdir/employees",\{threads :2})
hmsa 0436
Figure 4-36. Backups of selected schemas

In this command, you can specify the backup location and the names of the schemas to be backed up.

To use the Dump Utility to perform partial backups of chosen tables. You can use the following command, util.dumpTables, to backup individual tables (Figure 4-37):

util.dumpTables("<backup-location>", "<schema>.<table1>",
"<schema>.<table2>", ...)
util.dumpTables("employees", [ "salaries"],
"/backupdir/employees/salaries",\{threads:2})
hmsa 0437
Figure 4-37. Partial backups of selected tables

In this command, you can specify the backup location and the names of the tables to be backed up along with their respective schema names.

Data load utility restores data to another or the same database after backing up the database. For this purpose, MySQL Shell includes a Data Load Utility. This utility helps you import data from a backup file into a database.

Before restoring the data, it is recommended to perform a dry run process to validate the backup file. Use the following command to perform a dry run process (Figure 4-38):

util.loadDump("<backup-location>", \{dryRun:true})
util.loadDump("/backuputildump/", \{dryRun:true})
hmsa 0438
Figure 4-38. LoadDump dry run

A simple loadDump command for dry running is shown here. Similarly, you can test the command depending on the requirement before loading the data to ensure it works properly.

The following is an example of how to use this utility for backup restoration (Figure 4-39):

util.loadDump("/backuputildump/", \{progressFile
:"/backuputildump/backuplog.json",threads:4,backgroundThreads:4,maxBytesPerTransaction:"4096"})
hmsa 0439
Figure 4-39. LoadDump backup restoration

The above command will restore the data from the backup located in the /backuputildump/ directory and use 4 threads and 4 background threads during the restore process. It will also create a progress file at /backuputildump/backuplog.json and set the maximum bytes per transaction to 4096.

mysqldump

The mysqldump is a command-line utility provided by MySQL for creating logical backups of MySQL databases. In this section, we will explore how to use mysqldump effectively for creating database backups.

Before we dive into the backup process, it is important to understand the mysqldump command and its options. The easiest way to access the mysqldump documentation is through the command line help command (Figure 4-40):

man mysqldump
hmsa 0440
Figure 4-40. mysqldump help

This command will bring up the mysqldump manual, which provides detailed information on the command syntax, options, and usage examples.

Backing up individual tables is useful when you only need to restore specific data sets. To use the mysqldump command to create backups of individual tables in a database use the following syntax:

mysqldump database_name table_name > backup_file.sql

For example, to create a backup of the continents table in the world database, use the following command (Figure 4-41):

mysqldump -uroot -p world_x city > city.sql
ll city.sql
hmsa 0441
Figure 4-41. mysqldump backup

Use the --where option to backup only a subset of data from a table. For example, to backup only the rows in the continents table where CountryCode is 5, execute the following command (Figure 4-42):

mysqldump world_x -u root -p city --where="CountryCode='USA'" >
city_USA.sql
ll city_USA.sql
hmsa 0442
Figure 4-42. Backup a subset of data from a table

Use the --ignore-table option to exclude specific tables from your backup. For example, you may use the following command to backup all tables in the world database except the city table (Figure 4-43):

mysqldump -u root -p world_x --ignore-table=world_x.city >
ignore_city_world_x.sql
hmsa 0443
Figure 4-43. Exclude specific tables from backup

To create a whole database backup, use the mysqldump command with the database name as an argument:

mysqldump database_name > backup_file.sql

For example, to backup the world database, use the following command (Figure 4-44):

mysqldump -u root -p world_x > world_x.sql
ll world_x.sql
hmsa 0444
Figure 4-44. Create a full database backup

Backup Multiple Databases

To backup several databases, specify them as a comma-separated list:

mysqldump --databases database1,database2 > backup_file.sql

To backup the mytestdb sakila world_x and example_db1 databases, for example, execute the following command (Figure 4-45):

mysqldump -u root -p --databases mytestdb sakila world_x example_db1 >
all_db_backup.sql
ll all_db_backup.sql
hmsa 0445
Figure 4-45. Backup multiple databases

Backup All Databases

To backup all databases on the MySQL server, use the --all-databases option (Figure 4-46):

mysqldump -u root -p --all-databases > all_databases_backup.sql
hmsa 0446
Figure 4-46. Backup all databases in a mysql instance

This will create a single backup file containing all databases on the server.

The mysqldump command-line tool creates a logical backup of a database by generating a set of SQL statements that can be used to recreate the database objects and data. Following is an example to create a backup of the sakila database with several options for a more efficient and consistent backup (Figure 4-47):

mysqldump -u root -p --single-transaction --quick --lock-tables=false
--routines --events --triggers --default-character-set=utf8mb4 sakila >
backup.sql

ll backup.sql
hmsa 0447
Figure 4-47. mysqldump advanced options

Let’s break down each option in the command: This command tells mysqldump to create a backup of the specified database (dbname) and save it to a file called backup.sql:

--single-transaction

Ensures that the backup is consistent, even if other transactions are occurring on the database at the same time.

--quick

Tells mysqldump to retrieve rows one at a time instead of retrieving the entire result set at once, which can help to reduce memory usage.

--lock-tables=false

Ensures that the tables are not locked during the backup process, which allows the database to continue functioning normally.

--routines, --events, and --triggers

Tell mysqldump to include stored routines, events, and triggers in the backup, respectively.

--default-character-set=utf8mb4

Allows you to enforce the use of the utf8mb4 character set in the generated SQL script. This can be useful when you need to ensure that the backup will support all the characters used in your database, especially if it includes emoji or other special characters that are outside the scope of the standard UTF-8 character set.

Once created a logical backup using mysqldump, you can use it to recreate the database on another server or to restore the database if it becomes corrupted. To restore a logical backup, you would simply run the SQL statements contained in the backup file using the MySQL client.

To backup and restore a replica node, you can utilize either the --source-data or --master-data option. Starting from MySQL 8.0.26, the --source-data option should be used, while for versions prior to MySQL 8.0.26, the --master-data option should be utilized. Both options have the same purpose, which is to generate a dump file that can be used to set up another server as a replica of the source server during replication.

When these options are used, the resulting dump output includes a CHANGE REPLICATION SOURCE TO statement (introduced in MySQL 8.0.23) or CHANGE MASTER TO statement (used before MySQL 8.0.23). This statement specifies the binary log coordinates (file name and position) of the dumped server, indicating where the replication source server should start replicating from after loading the dump file into the replica.

If the --source-data or --master-data options are set to 2, the CHANGE REPLICATION SOURCE TO or CHANGE MASTER TO statement in the dump file is commented out, preventing it from taking effect when the dump file is reloaded.

The option value 1 will write the statement uncommented and take effect when the dump file is reloaded. If no option value is explicitly supplied, the default value of 1 is used.

Setting Up a Backup Schedule

Create a shell script that will take the MySQL backup using Percona XtraBackup. You can create a new file using any text editor, for example, nano or vim, and save it with the .sh extension. In this script, you need to include the following commands (Figure 4-48):

#!/bin/bash
# Set the MySQL username and password
MYSQL_USER="root"
MYSQL_PASSWORD='Pass21word$$'

# Set the backup directory
BACKUP_DIR="/root/backupdir/newbackup"

# Take a full backup using Percona XtraBackup
xtrabackup --backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD
--target-dir=$BACKUP_DIR/full_backup_$(date +%Y-%m-%d_%H-%M-%S)
hmsa 0448
Figure 4-48. Shell script using Percona XtraBackup

In this script, you need to replace <your-username>, <mysql-user>, and <mysql-password> with the appropriate values for your system. Also, make sure to give execute permissions to this script using the command (Figure 4-49):

chmod +x backup_script.sh
hmsa 0449
Figure 4-49. Grant execute permissions to this script

The command ./backup_script.sh executes a shell script named backup_script.sh that is located in the current directory (Figure 4-50):

./backup_script.sh
hmsa 0450
Figure 4-50. Execute the backup script

Setting up a backup schedule is crucial to ensure that your database is backed up regularly. To set up a backup schedule use cron jobs or other scheduling tools. Here is an example of a cron job that runs a backup every day at 2:00 AM:

crontab -e
0 2 * * * /root/backupdir/newbackup/backup_script.sh

To set up a backup schedule, you can use a combination of crontab and your chosen backup method. Here’s an example configuration for setting up a daily backup schedule using mysqldump:

Create a backup directory to store the backup files (Figure 4-51):

sudo mkdir -p /mnt/backups/mysql/
hmsa 0451
Figure 4-51. Create a backup directory

Grant the necessary permissions to the backup directory (Figure 4-52):

sudo chown -R mysql:mysql /mnt/backups/mysql/
hmsa 0452
Figure 4-52. Grant ownership to the backup directory

The command sudo chmod -R 700 /mnt/backups/mysql/ is used to modify the permissions of the directory /mnt/backups/mysql/ and all of its subdirectories and files (Figure 4-53):

sudo chmod -R 700 /mnt/backups/mysql/
hmsa 0453
Figure 4-53. Grant permissions to the backup directory

Create a shell script (Figure 4-54):

#!/bin/bash
# Set the backup directory
backup_dir="/mnt/backups/mysql"

# Set the MySQL credentials
mysql_user="root"
mysql_password="password"

# Create the backup filename with date stamp
backup_filename="$\{backup_dir}/daily_backup_$(date +%Y%m%d).sql"

# Execute the mysqldump command and save output to file
mysqldump --all-databases --user="$\{mysql_user}"
--password="$\{mysql_password}" > "$\{backup_filename}"
hmsa 0454
Figure 4-54. Backup shell script

Open crontab configuration:

crontab -e

Add the following line to the crontab file to schedule a daily backup at 2 AM:

0 2 * * * /mnt/backups/mysql/backup_mysqldump.sh

Save and close the crontab file.

By following this example configuration, you can set up a daily backup schedule for a MySQL database using mysqldump and ensure that the backup files are stored securely and can be restored when needed. The provided backup scripts are basic examples. Depending on your requirements, you may need to write scripts using scripting languages such as Python, shell script, etc.

Selecting Backup Options

When taking a backup, select various backup options to customize the backup process. Here are some essential backup options:

Backup Type

Select either a full backup or an incremental backup. Full backups create a complete backup of the database, while incremental backups only back up changes made since the last backup.

Compression

Compress the backup file to save disk space. Xtrabackup, mydumper, and mysqldump support compression.

Encryption

Encrypt the backup file to protect sensitive data. xtrabackup supports encryption using the --encrypt option.

Monitoring the Backup Process

It is essential to monitor the backup process to ensure that it completes successfully. Here are some ways to monitor this process:

  • Check the backup log file for any errors or warnings.

  • Check the backup file size to ensure that it is not too small or too large.

  • To ensure the backup file’s validity, periodically perform restores using the production backup on the testing system.

Setting Up the Restore Environment

After selecting a restore method, set up the environment for the restore process. Ensure that the server configuration is compatible with the backup files, including the version of MySQL and the location of the data directory. If necessary, create a new MySQL instance with the same configuration as the original instance.

Assuming that the backup was taken from a MySQL 8.0.23 instance and you want to restore it to a new MySQL 8.0.25 instance, you can set up the environment for the restore process using the following steps:

  • Install MySQL 8.0.25 on the server where you want to restore the database.

  • Stop the MySQL service using the command systemctl stop mysqld.

  • Copy the configuration file from the original instance to the new instance using the command like scp.

  • Edit the configuration file /etc/my.cnf to match the configuration of the original instance, including the location of the data directory.

  • Depending upon the backup the restore and process will vary.

  • Start the MySQL service using the command systemctl start mysqld.

When choosing a backup method, consider the size of your database, the amount of data changes, and the recovery time objective (RTO) and recovery point objective (RPO) of your organization. For example, xtrabackup is an efficient backup method for large databases that require quick recovery time, while mydumper takes more time compared to xtrabackup restore, the mysqldump is suitable for smaller databases that can afford longer recovery times.

Point-in-Time Recovery

Point-in-time recovery (PITR) is a technique used to restore a database to a specific point in time, typically to recover from accidental deletion, data corruption, or other types of data loss. It involves restoring a database to a previous state by replaying transaction logs that were captured at specific points in time.

PITR is necessary when you need to recover your database to a specific point in time. For example, if you accidentally deleted important data from the database and only realized it a few hours later, you could use PITR to recover the database to a point in time just before the data was deleted. Similarly, if the database becomes corrupted, you can use PITR to restore it to a point in time before the corruption occurred.

PITR is necessary in the following situations:

Disaster recovery

If you experience data loss due to a disaster such as hardware failure, power outages, or a natural calamity, PITR helps you to restore the data to a specific point in time before the disaster occurred.

Human error

If you accidentally delete or modify data, PITR allows you to restore the data to the point in time before the error occurred.

Compliance

PITR is often required to meet compliance regulations such as HIPAA, SOX, or GDPR. These regulations may require that you keep backups of data and that you be able to restore the data to a specific point in time.

Instance Level Recovery

Here’s a straightforward example of how to execute PITR. However, the steps may differ based on the backup tool used, such as xtrabackup,MySQL Shell Dump, mydumper, and so on, and the backup and scenario of the production environment, so it’s essential to take appropriate actions accordingly.

Let’s say you have a database for an online store, and accidentally deleted a customer’s order. You want to recover that order, but don’t want to lose any data that has been added since the order was deleted.

Here’s how you could use PITR to recover the order up to the moment before it was deleted:

  1. To ensure regular backups of the database, it is important to schedule them at specific intervals. One way to perform a full backup of the database is by utilizing the Xtrabackup command, which was already detailed in the previous chapter.

  2. Create a binary log backup of the database using the mysqlbinlog command. To ensure regular backups, it is important to create a script and schedule them at specific intervals.

    When backing up a server’s binary log files with mysqlbinlog, you need to provide the actual file names that exist on the server. You can use the SHOW BINARY LOGS statement to see the current names if you are not sure what they are:

    mysql> SHOW BINARY LOGS;
    +---------------+-----------+-----------+
    | Log_name      | File_size | Encrypted |
    +---------------+-----------+-----------+
    | binlog.000001 |     35421 |        No |
    | binlog.000002 |     12416 |        No |
    | binlog.000003 |     52273 |        No |
    +---------------+-----------+-----------+

    To create a static backup of binlog.000001 through binlog.000003, you can use either of these commands:

    mysqlbinlog --read-from-remote-server --host=host_name --raw
    binlog.000001 binlog.000002 binlog.000003
    
    mysqlbinlog --read-from-remote-server --host=host_name --raw
    --to-last-log binlog.000001

    The first command explicitly lists every file name, while the second names only the first file and uses --to-last-log to read through the last. One difference between these commands is that if the server opens binlog.000133 before mysqlbinlog reaches the end of binlog.000003, the first command does not read it, but the second command does.

    To create a live backup where mysqlbinlog starts with binlog.000001 to copy existing log files and then stays connected to copy new events as the server generates them, you can use:

    mysqlbinlog --read-from-remote-server --host=host_name --raw
    --stop-never binlog.000001

    With --stop-never, it is not necessary to specify all the binary log files that already exist.

    Note

    It is possible to create a script that regularly backs up the binlog by utilizing the available options.

  3. Restore the full backup.

    The previous chapter contains a detailed explanation of the steps required for restoring the full backup of Xtrabackup. You might need to refer to it.

  4. Apply incremental backups to restore the database up to the desired point in time:

    mysqlbinlog --start-datetime='2023-03-01 12:00:00'
    --stop-datetime='2023-03-01 13:00:00' /var/log/mysql/binlog.000001 |
    mysql -u root -p mydatabase
Note

This example demonstrates the use of xtrabackup. Depending on the backup system employed in production, we have the option to utilize either MySQL Shell Dump, mydumper, Xtrabackup or MySQL Enterprise Backup.

In this example, we are restoring the database up to a specific hour on March 1st, 2023, which is the hour before the order was deleted.

By following these steps, you can recover the deleted order without losing any data that has been added since the deletion. PITR gives you greater control over your data recovery process and can be a valuable tool in disaster recovery scenarios.

Table Level Recovery

To restore the table alone to a new instance ensure that you have a valid backup of the table’s tablespace files.

Connect to the MySQL instance where you want to restore the table and create the table structure (Figure 4-55):

CREATE TABLE `authors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) COLLATE utf8mb3_unicode_ci NOT NULL,
  `last_name` varchar(50) COLLATE utf8mb3_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb3_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72639469 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci
hmsa 0455
Figure 4-55. Create the authors table

Discard the tablespace of the author’s table created (Figure 4-56). When a tablespace is discarded, it means that the association between the table and its underlying tablespace is removed. The table structure and metadata remain intact, but the tablespace files are no longer associated with the table. This operation is typically performed when you want to replace the tablespace files with a different set of files or when you want to restore the table from a backup:

select count(*) from authors;
ALTER TABLE authors DISCARD TABLESPACE;
hmsa 0456
Figure 4-56. DISCARD TABLESPACE

Copy the table space from the backup (Figure 4-57):

scp authors.ibd 34.203.202.5:/var/lib/mysql/adddb/
hmsa 0457
Figure 4-57. Copy the table space

Change the ownership of the file authors.ibd located in the /var/lib/mysql/adddb directory. It assigns the user and group “mysql” as the new owners of the file (Figure 4-58):

chown mysql.mysql /var/lib/mysql/adddb/authors.ibd
hmsa 0458
Figure 4-58. Change ownership of authors.ibd to mysql user and group

The statement ALTER TABLE authors IMPORT TABLESPACE; is used in MySQL to import a tablespace for the “authors” table within the “adddb” database (Figure 4-59).

This command allows you to restore or replace the tablespace of the “authors” table using a previously exported or backup tablespace file (typically with a .ibd extension). By executing this statement, you instruct MySQL to associate the imported tablespace file with the “authors” table in the specified database.

Importing the tablespace enables you to recover or migrate a table along with its data and index structures. It establishes the link between the table definition and the underlying tablespace file, allowing the table to be fully accessible and operational within the MySQL database instance:

ALTER TABLE adddb.authors IMPORT TABLESPACE;
hmsa 0459
Figure 4-59. IMPORT TABLESPACE

Verify the data in the table following the import of a tablespace for the “authors” table (Figure 4-60):

select count(id) from adddb.authors;
hmsa 0460
Figure 4-60. Verify the table data

After importing the table as part of the point-in-time recovery process, the next step involves utilizing the binlog backups to restore the remaining data from the time of the initial backup to the current time. To accomplish this, follow the steps outlined below in sequence.

Copy the required binlog and create the index:

sudo cp or scp mysql-bin.* /data/
sudo chown mysql.mysql /data/mysql-bin.*

Update the my.cnf with:

relay-log = mysql-bin

Prepare the index using the copied binlogs:

ls ./mysql-bin.0* >mysql-bin.index

With the IMPORT TABLESPACE we have already imported the table from the existing backup.

And configure this as a dummy replica:

mysql> CHANGE REPLICATION SOURCE TO
RELAY_LOG_FILE='mysql-bin.000001',RELAY_LOG_POS=4, SOURCE_HOST='dummy';
Query OK, 0 rows affected (0.08 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (adddb.authors);
Query OK, 0 rows affected (0.00 sec)

Validate the output and start the replication thread (Only SQL thread is needed):

mysql> START REPLICA SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

PITR is a crucial process that requires expertise from a Database Administrator (DBA). In MySQL, it empowers you to restore your database to a precise point in time during critical situations, including accidental data deletion, data corruption, human errors, and disaster recovery. The skillful implementation of PITR ensures data integrity and minimizes potential data loss in these critical scenarios.

By enabling binary logging, taking a full backup, restoring the backup to a new location, determining the point-in-time to recover to, and recovering the database to the point-in-time, you can ensure data integrity and protect against data loss.

Managing Binary Logs

MySQL provides a robust and reliable logging mechanism for tracking changes made to the database, known as binary logging. In this section, we will discuss the significance of binary logs and provide a comprehensive guide on managing binary logs.

Binary logs are a type of log file generated by MySQL to record all changes made to the database. These changes include any data modifications, schema alterations, and administrative operations such as user management. Binary logs are stored in a binary format, which makes them platform-independent and allows for easy replication of changes across multiple servers.

Binary logs are a critical component of MySQL’s high availability and disaster recovery mechanisms. They are used to restore the database in the event of a server crash, data corruption, or user error. Binary logs are also required for replication, where they are used to synchronize data changes between source and replica servers.

Enabling binary logging

In previous versions of MySQL, binary logging had to be manually enabled by specifying the --log-bin option since it was disabled by default. However, as of MySQL 8.0, binary logging is enabled by default regardless of whether the --log-bin option is specified or not. The only exception is when initializing the data directory manually using mysqld and the --initialize or --initialize-insecure option, which disables binary logging by default. To enable binary logging in this case, the --log-bin option must be specified. Enabling binary logging sets the log_bin system variable to ON, indicating the status of binary logging on the server.

To enable binary logging, add the following line to the MySQL configuration file (my.cnf or my.ini):

[mysqld]
log-bin=mysql-bin

This configuration will create a binary log file with a name of mysql-bin in the data directory specified in the MySQL configuration file.

Configuring Binary Logging

MySQL provides a range of configuration options for binary logging. These options can be set in the MySQL configuration file, or dynamically using the SET GLOBAL command.

Some of the key configuration options for binary logging include:

binlog_format

This option specifies the format used for binary logging. MySQL supports three different formats: ROW, STATEMENT, and MIXED. The default format is ROW.

expire_logs_days

This option sets the number of days after which binary logs will be automatically purged. The default value is 0, which means that binary logs will not be automatically deleted.

max_binlog_size

This option specifies the maximum size of a binary log file before a new file is created. The default value is 1073741824 bytes (1 GB).

binlog_row_image

This option specifies the format used for binary logging of row-based events. MySQL supports different formats: FULL, MINIMAL and NOBLOB. The default format is FULL.

For example, to set the binlog_format option to ROW, add the following line to the MySQL configuration file:

[mysqld]
binlog_format=ROW

Purging Binary Logs

Over time, binary logs can consume a significant amount of disk space. To prevent this, MySQL provides the option to purge old binary logs. There are two ways to purge binary logs: manually or automatically.

To manually purge binary logs, use the PURGE BINARY LOGS command:

PURGE BINARY LOGS TO 'mysql-bin.000003';

This command will purge all binary logs up to and including the file mysql-bin.000003.

To automatically purge binary logs, set the expire_logs_days option in the MySQL configuration file.

Here, 604800 seconds are equivalent to 7 days (60 seconds/minute*60 minutes/hour*24 hours/day*7 days):

[mysqld]
binlog_expire_logs_seconds=604800;

This configuration will automatically purge binary logs that are older than 7 days.

Here’s an example of how to use the PURGE BINARY LOGS command to delete old binary log files in MySQL:

  1. Connect to the MySQL server using the MySQL client.

    mysql -u root -p
  2. List the binary log files currently in use (Figure 4-61):

    SHOW BINARY LOGS;
    hmsa 0461
    Figure 4-61. List the binary log

    This command will display a list of the binary log files currently in use, along with their sizes and creation timestamps.

  3. Use the PURGE BINARY LOGS command to delete old binary log files. For example, to delete all binary log files older than seven days, use the following command (Figure 4-62):

    PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);
    hmsa 0462
    Figure 4-62. Purge old binary log files

    You can adjust the interval as needed to delete files older or newer than a certain number of days.

  4. Verify that the old binary log files have been deleted by running the SHOW BINARY LOGS command again (Figure 4-63):

    SHOW BINARY LOGS;
    hmsa 0463
    Figure 4-63. Verify the binary log

    This command will display a list of the binary log files currently in use, excluding any files that were deleted by the PURGE BINARY LOGS command.

By using the PURGE BINARY LOGS command, you can keep your binary log files under control and prevent them from consuming too much disk space over time.

Binary logs are a critical component of MySQL’s high availability and disaster recovery mechanisms. They provide a reliable way to track changes made to the database and allow for easy replication of changes across multiple servers. By following the guidelines outlined in this section, you can ensure that your binary logs are properly configured and managed.

Best Practices for Backup and Recovery

In the world of databases, backup and recovery are essential to ensure the integrity of data. A single database failure can lead to loss of important data, which can negatively impact business operations. Therefore, it is important to implement best practices for backup and recovery in MySQL.

Regular backups are critical to ensure that your data is safe and recoverable. A regular backup schedule depends on the size of your data and the rate of change in the data. A general rule of thumb is to perform backups daily for high-volume transactional databases, and weekly or monthly backups for databases with lower data change rates:

Create a backup strategy

Define a backup strategy that meets your recovery requirements, such as backup frequency, backup types (full or incremental), and retention periods.

MySQL backup from a replica node

Taking a MySQL backup from a replica node is recommended because it reduces the load on the primary node and ensures a consistent backup. By taking a backup from a replica node, the primary node is not burdened with additional workload, which can impact the live system’s performance. Additionally, since the replica node is in sync with the primary node, taking a backup from it guarantees consistency and reduces the risk of data corruption or loss. Overall, taking backups from a replica node is a best practice for MySQL database administrators to ensure the availability and integrity of their data.

Confirming the integrity of the data in the replica is also very important and is not necessarily guaranteed solely by having replication running. It is a task for the Database Administrator to ensure that it is an exact copy by running tools such as pt-table-checksum.

MySQL binlog, or binary log

A log file that records all modifications made to a database, including insertions, updates, and deletions. Backing up the MySQL binlog is essential because it can help recover data from a specific point in time, which can be useful in several scenarios, such as disaster recovery, replication, and auditing. In the case of a disaster, such as a hardware failure or data corruption, the binlog can be used to restore the database to a specific point in time before the disaster occurred, minimizing data loss. Additionally, backing up the binlog is critical for replication, as it enables replica databases to synchronize with the primary database. Finally, the binlog can be used for auditing purposes, providing a detailed record of all modifications made to the database, which can be useful for compliance and security purposes. Overall, backing up the MySQL binlog is an essential practice for ensuring data availability, integrity, and security.

Use MySQL Enterprise Backup

MySQL Enterprise Backup is a commercial backup solution that provides enterprise-grade backup and recovery features for MySQL databases. It offers fast, reliable, and scalable backups, along with support for point-in-time recovery and partial backups.

Percona Xtrabackup

Xtrabackup is a popular open-source tool used for backing up and restoring MySQL databases. One of the primary reasons to use Xtrabackup is that it performs hot backups of InnoDB and XtraDB storage engines, which means it can take backups without locking the database or causing any downtime. Xtrabackup also offers several other useful features, such as parallel compression and incremental backups, which can further improve backup performance and efficiency. Additionally, Xtrabackup is highly customizable, allowing database administrators to tailor backups to their specific needs. Overall, Xtrabackup is a reliable and flexible tool that simplifies the backup and recovery process for MySQL databases.

MySQL Shell Dump Utility

MySQL Shell Dump is a command-line tool used to backup and restore MySQL databases. One of the key benefits of using MySQL Shell Dump is that it provides a more comprehensive backup solution than traditional backup methods. With MySQL Shell Dump, it is possible to backup not only the database schema and data but also stored routines, triggers, and user accounts. MySQL Shell Dump also offers support for parallel backups, enabling faster and more efficient backups of large databases. Additionally, MySQL Shell Dump is highly customizable, allowing database administrators to specify backup options, such as compression, and selective backups. Overall, MySQL Shell Dump is a powerful and flexible tool that simplifies the backup and recovery process for MySQL databases.

Use mysqldump for small databases

For small databases, mysqldump is a reliable and cost-effective backup solution. It can backup MySQL databases in SQL format, making it easy to restore them in case of data loss.

Test your backups

Backing up data is not sufficient if the backups are not verified to ensure that they are restorable. Therefore, it is important to test your backups regularly to ensure that they can be restored in the event of a disaster. You can test your backups by restoring them to a test environment and checking the data for accuracy and consistency.

Store your backups offsite

Storing backups offsite is important in the event of a disaster that may affect your primary data center. Keeping backups in a different location ensures that they are safe and recoverable. You can store backups in different cloud storage solutions, such as Amazon S3, Google Cloud Storage, or Microsoft Azure, or on physical storage devices, such as tapes or external hard drives.

Storing your backups offsite is important to protect your data from disasters like fire, theft, or natural calamities. Here are some best practices for storing your backups offsite:

Use cloud storage

Cloud storage providers like Amazon S3, Google Cloud Storage, or Microsoft Azure offer reliable and secure storage options for your backups.

Use Amazon S3 to store your backups offsite. You can use the AWS CLI to automate the backup and restore process. For example, you can use the following command to upload your backup to S3:

aws s3 cp /backup/full/backup-to-image s3://mybucket/backups/

This command uploads your backup image to the S3 bucket named mybucket/backups.

Use secure transport protocols

Use secure transport protocols like SSL or SSH to transfer your backups to the offsite location to protect them from unauthorized access.

Storing your backups offsite is important to protect your data from disasters. Here’s an aws s3 example how you can store your backups offsite using cloud storage:

Use SSL or SSH to transfer your backups to the offsite location to protect them from unauthorized access. For example, you can use the following command to transfer your backup image to S3 using SSL:

The AWS CLI employs SSL as the default communication protocol with AWS services, and for every SSL connection, SSL certificates are verified by the AWS CLI:

aws s3 cp /backup/full/backup-to-image s3://mybucket/backups/ --sse

The --sse parameter specifies server-side encryption for an object in S3 and can accept the values AES256 or aws:kms. If no value is provided for the parameter, the default AES256 encryption is used.

Use Encryption to Protect Your Backups

Encrypting backups ensures that they are secure and protected from unauthorized access. You can use different encryption methods, such as symmetric encryption or asymmetric encryption, to encrypt your backups. MySQL provides encryption options, such as the --ssl* option for mysqldump or the --encrypt option for MySQL Enterprise Backup, to encrypt your backups.

Encryption is an essential security measure to protect your backups from unauthorized access. Here are some best practices for encrypting your backups:

Use MySQL Enterprise Backup

MySQL Enterprise Backup offers encryption features that allow you to encrypt your backups using AES-256 encryption. It also supports key management using key management systems like Oracle Key Vault.

Use third-party encryption tools

You can use third-party encryption tools like GnuPG or OpenSSL to encrypt your backups before storing them offsite.

Encrypting your backups is important to protect your data from unauthorized access. Here’s how you can encrypt your backups using MySQL Enterprise Backup:

Use encryption plug-ins

MySQL Enterprise Backup provides encryption plug-ins that you can use to encrypt your backups. For example, you can use the AES256 encryption plug-in to encrypt your backups using the following command:

mysqlbackup --backup-dir=/backup/full
--backup-image=/backup/full/backup-to-image \
--encrypt=AES256 --key-file=/path/to/keyfile --host=mydb.example.com \
--port=3306 --user=admin --password backup-to-image

This command encrypts your backup image using AES256 encryption and the key file located at /path/to/key file.

xtrabackup Encryption

Since MySQL 8.0 and Percona Server 8.0 are not supported by Percona XtraBackup 2.4.x, you’ll need to use Percona XtraBackup 8.0.x for backups and restores. Here are the updated steps to install Percona XtraBackup 8.0.x:

As mentioned before, you’ll need to add the Percona repository to your package manager’s list of available repositories. Open a terminal or command prompt and run the following commands:

For Debian/Ubuntu, download the Debian package file using wget (Figure 4-64):

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
hmsa 0464
Figure 4-64. Download the Percona repository

The command sudo dpkg is used to install the Debian package (Figure 4-65).

sudo dpkg -i percona-release_latest.generic_all.deb
hmsa 0465
Figure 4-65. Installs a Debian package

Update the package lists for available software repositories (Figure 4-66).

sudo apt-get update
hmsa 0466
Figure 4-66. Refresh the local package index

To install version 80 of the Percona XtraBackup tool on a system, execute the following command (Figure 4-67):

sudo apt-get install percona-xtrabackup-80
hmsa 0467
Figure 4-67. Install percona-xtrabackup-80

Verify the version of the installed XtraBackup (Figure 4-68):

xtrabackup --version
hmsa 0468
Figure 4-68. Check the xtrabackup version

To create an encrypted backup using Percona XtraBackup, you can follow these steps:

1. Determine the encryption algorithm

Percona XtraBackup supports three encryption algorithms: AES128, AES192, and AES256. Choose the algorithm you want to use for encryption.

2. Generate an encryption key

You have two options to specify the encryption key when creating encrypted backups: the xtrabackup --encrypt-key option and the xtrabackup --encrypt-key-file option.

Here’s an example command to generate an encryption key (Figure 4-69):

openssl rand -base64 24
hmsa 0469
Figure 4-69. Generates a random sequence of 24 bytes

Here’s an example of the xtrabackup command utilizing the xtrabackup --encrypt-key option (Figure 4-70):

xtrabackup --backup --target-dir=/root/backups/
--encrypt=AES256 --encrypt-key="TpnrqfGQuAPHM1aGR+WAEDyEWjC/oZhW"
hmsa 0470
Figure 4-70. Xtrabackup encrypt

After completing the backup, check the log to confirm its successful completion. You should get a log similar to Figure 4-71.

hmsa 0471
Figure 4-71. Xtrabackup encrypt completion log

How to Decrypt Encrypted Backups

The --decrypt option in Percona XtraBackup allows for the decryption of backups (Figure 4-72):

#xtrabackup --decrypt=AES256
--encrypt-key="TpnrqfGQuAPHM1aGR+WAEDyEWjC/oZhW"
--target-dir=/root/backups/ --remove-original
hmsa 0472
Figure 4-72. Xtrabackup Decrypt

Following the decryption process, examine the log to ensure it has been successfully completed. You should observe a log resembling Figure 4-73.

hmsa 0473
Figure 4-73. Xtrabackup Decrypt completion log

Use a secure key management solution to store and manage your encryption keys. For example, you can use the AWS Key Management Service (KMS) to generate and manage your encryption keys.

How to Prepare the Decrypted Backups

The command xtrabackup --prepare is used to prepare the backup files in the specified directory (Figure 4-74):

xtrabackup --prepare --target-dir=/root/backups/
hmsa 0474
Figure 4-74. Xtrabackup prepare

After completing the preparation process, review the log to confirm its successful execution (Figure 4-75).

hmsa 0475
Figure 4-75. Xtrabackup prepare completion log

mysqldump encryption

mysqldump encryption secures database backups by converting data into unreadable cipher-text, preventing unauthorized access. A decryption key is needed to revert the cipher-text back to its original, readable form (Figure 4-77).

The command mysqldump is used to create a backup of a MySQL database named “mytestdb” and save it to a file named backup.sql (Figure 4-76):

# Create the backup
mysqldump -u root -p mytestdb > backup.sql
hmsa 0476
Figure 4-76. mysqldump backup

After taking the backup use the openssl command to encrypt a file named backup.sql using the AES-256-CBC encryption algorithm (Figure 4-77):

# Encrypt the backup using OpenSSL
openssl aes-256-cbc -salt -in backup.sql -out backup.sql.enc
hmsa 0477
Figure 4-77. Encrypt the backup

This code will create a backup of the mydatabase database using the mysqldump command and encrypt it using OpenSSL with the AES-256-CBC algorithm (Figure 4-78).

To decrypt the backup, you can use the following command:

# Decrypt the backup using OpenSSL
openssl aes-256-cbc -d -in backup.sql.enc -out backup.sql
hmsa 0478
Figure 4-78. Decrypt the backup

This code will decrypt the backup file backup.sql.enc using the AES-256-CBC algorithm and output the decrypted file to backup.sql.

Conclusion

Backup and recovery are critical activities in any MySQL environment. By following these guidelines, you can help ensure that your data is protected and easily recoverable in the event of a disaster.

Get Hands-On MySQL Administration now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.