MySQL Backup - Part 2

Lois Houston and Nikita Abraham continue their conversation with MySQL expert Perside Foster, with a closer look at MySQL Enterprise Backup. They cover essential features like incremental backups for quick recovery, encryption for data security, and monitoring with MySQL Enterprise Monitor—all to help you manage backups smoothly and securely.   MySQL 8.4 Essentials: https://mylearn.oracle.com/ou/course/mysql-84-essentials/141332/226362 Oracle University Learning Community: https://education.oracle.com/ou-community LinkedIn: https://www.linkedin.com/showcase/oracle-university/ X: https://x.com/Oracle_Edu   Special thanks to Arijit Ghosh, David Wright, Kris-Ann Nansen, Radhika Banka, and the OU Studio Team for helping us create this episode.   --------------------------------------------------------   Episode Transcript:   00:00 Welcome to the Oracle University Podcast, the first stop on your cloud journey. During this series of informative podcasts, we’ll bring you foundational training on the most popular Oracle technologies. Let’s get started! 00:25 Nikita: Welcome to the Oracle University Podcast! I’m Nikita Abraham, Team Lead: Editorial Services  with Oracle University, and with me is Lois Houston, Director of Innovation Programs.  Lois: Hi there! Last week was the first of a two-part episode covering the different types of backups and why they're important. Today, we’ll look at how we can use MySQL Enterprise Backup for efficient and consistent backups. 00:52 Nikita: And of course, we’ve got Perside Foster with us again to walk us through all the details. Perside, could you give us an overview of MySQL Enterprise Backup?  Perside: MySQL Enterprise Backup is a form of physical backup at its core, so it's much faster for large data sets than logical backups, such as the most commonly used MySQL Dump. Because it backs up the data files, it's non-locking and enables either complete system backup or partial backup, focusing only on specific databases. 01:29 Lois: And what are the benefits of using MySQL Enterprise Backup? Perside: You can back up to local storage or direct-to-common-cloud storage types. You can perform incremental backups, which can speed up your backup process greatly. Incremental backups enable point-in-time recovery. It's useful when you need to restore to a point in time before some application or human error occurred. Backups can be compressed to save archival storage requirements and encrypted for regulatory compliance and offline data security. 02:09 Nikita: So we know MySQL Enterprise Backup is an impressive tool, but could you talk more about some of the main features it supports for creating and managing backups? Specifically, which tools are integrated within MySQL Enterprise to support different backup scenarios? Perside: MySQL Enterprise Backup supports SBT, implemented by many common Tape storage systems. MySQL Enterprise Backup supports optimistic backup. This process deals with busy tables separately from the rest of the database. It can record changes that happen in the database during the backup for consistency. In a large data set, this can make a huge difference in performance. MySQL Enterprise Backup runs on all supported platforms. It's available when you have a MySQL Enterprise Edition license. And it comes with Enterprise Edition, but it also is available as a separate package. You can get the most recent version from eDelivery, where you can also get a trial version. If you need a previous release, you can get that from My Oracle Support. It's also available in all versions of MySQL, whether you run a Long-Term support version or an Innovation Release. For LTS releases, MySQL Enterprise Backup supports MySQL instances of the same LTS release. For Innovation releases, it supports the previous LTS release and any subsequent Innovation version within the same LTS family. 04:03 Nikita: How does MySQL Enterprise Monitor manage and track backup processes? Perside: MySQL Enterprise Monitor has a dashboard for monitoring MySQL Enterprise Backup. The dashboard monitors the health of backup process and usage throughout the entire Enterprise fleet, not just a single server. It supports drilling down into specific sub-operations within a backup job. You can see information about full backups, partial backups, and incremental backups. You can configure alerts that will notify you in the event of delays, failures, or backups that have not been performed in some configuration time period. 04:53 Lois: Ok…let’s get into the mechanics. I understand that MySQL Enterprise Backup uses binary logs as part of its backup process. Can you explain how these logs fit into the bigger picture of maintaining database integrity? Perside: MySQL Enterprise Backup is a utility designed specifically for backing up MySQL systems in the most efficient and flexible way. At its simplest, it performs a physical backup of the data files, so it is fast. However, it also records the changes that were made during the time it took to do the backup. So, the result is that you get a consistent backup of the data at the time the backup completed. This backup is not tied to the host system and can be moved to other hosts. It can be used for archiving and is fully supported as part of the MySQL Enterprise Edition. It is, however, tied to the specific version of MySQL from which the backup was taken. So, you cannot use it for upgrades where the destination server is an upgrade from the source. For example, if you take a backup from MySQL 5.7, you can't directly restore it to MySQL 8.0. As a part of MySQL Enterprise Edition, it's not part of the freely available Community Edition. 06:29 Lois: Perside, how do MySQL's binary logs track changes over time? And why is this so critical for backups? Perside: The binary logs record changes to the database. These changes are recorded in a sequential set of files numbered incrementally. MySQL logs changes either in statement-based form, where each log entry records the statement that gives rise to the change, or in row-based form where the actual change row data is recorded. If you select mixed format, then MySQL records statements for most operations and records row for changes where the statement might result in a different row value each time it's run, for example, where there's a generated value like autoincrement. The current log file grows as changes are recorded. When it reaches its maximum configured size, that log file is closed, and the next sequential file is created for new logs. You can make this happen automatically by using the FLUSH BINARY LOGS command. This does not delete any existing log files. 07:59 Nikita: But what happens if you want to delete the log files? Perside: If you want to delete all log files, you can do so manually with the PURGE BINARY LOGS command, either specifying a file or a date time.  08:14 Lois: When it comes to tracking transactions, MySQL provides a couple of methods, right? Can you explain the differences between Global Transaction Identifiers and the traditional log file sequence? Perside: Log files positioning is one of two formats, either legacy, where you specify transactions with a log file in a sequence number, or by using global transaction identifiers, or GTIDs, where each transaction is identified with a universally unique identifier or UUID. When you apply a transaction to the source server, that is when the GTID is attached to the transaction. This makes it particularly useful in replication topologies so that each transaction is uniquely identified by both its server ID and the transaction sequence number. When such a transaction is replicated to other hosts, the transaction retains its original GTID so that you can track when that transaction has propagated to the replicas and has been applied. The global transaction identifier is unique across the entire network. 09:49 Have you mastered the basics of AI? Are you ready to take your skills to the next level? Unlock the potential of advanced AI with our OCI Generative AI Professional course and certification that covers topics like LLMs, the OCI Generative AI Service, and building Q&A chatbots for real-world applications. Head over to mylearn.oracle.comand find out more. 10:19 Nikita: Welcome back! Let’s move on to replication. How does MySQL’s legacy log format handle transactions, and what does that mean for replication timing across different servers? Perside: Legacy format binary logs are non-transactional. This means that a transaction made up of multiple modifications is logged as a sequence of changes. It's possible that different hosts in a replication network apply those changes at different times. Each server that uses legacy binary logging maintain the current applied log position as coordinates based on a combination of binary log files in the position within that log file. 11:11 Nikita: Troubleshooting with legacy logs can be quite complex, right? So, how does the lack of unique transaction IDs make it more difficult to address replication issues? Perside: Because each server has its own log with its own transactions, these modification could have entirely different coordinates, making it challenging to find the specific modification point if you need to do any deep dive troubleshooting, for example, if one replica fell partway through applying a transaction and you need to partially roll it back manually. On the other hand, when you enable GTIDs, the transaction applied on the source host has that globally unique identifier attached to the whole transaction as a sequence of unique IDs. When the second or subsequent servers apply those transactions, they have exactly the same identifier, making it both transaction-safe for MySQL and also easier to troubleshoot if you need to. 12:26 Lois: How can you use binary logs to perform a point-in-time recovery in MySQL? Perside: First, you restore the last full backup. Once you've restarted the restart server, find the current log position of that backup. Either it's GTID or log sequence number. The SHOW BINARY LOG STATUS command shows this information. Then you can use the MySQL binlog utility to replay events from the binary log files, specifying the start and stop position containing the range of log operations that you wish to apply. You can pipe the output of the MySQL bin log to the MySQL client if you want to execute the changes immediately, or you can redirect the output to a script file if you want to examine and perhaps edit the changes. 13:29 Nikita: And how do you save binary logs? Perside: You can save binary logs to use in disaster recovery, for point-in-time restores, or for incremental backups. One way to do this is to flush the logs so that the log file closes and ready for copying. And then copy it to a different server to protect against hardware media failures. You can also use the MySQL binlog utility to create a copy of a set of binary log files in the same format, but to a different file or set of files. This can be useful if you want to run MySQL binlog continuously, copying from the source server binary log to a new location, perhaps in network storage. If you do this, remember that MySQL binlog does not run as a service or daemon, so you'll need to monitor it to make sure it's running continually.  14:39 Lois: Can you take us through how the MySQL Enterprise Backup process works? What does it do when performing a backup? Perside: First, it performs a physical file copy of necessary data and log files. This can be done while the server is fully operational, and it has minimal impact on performance. Once this initial copy is taken, it applies a low impact backup lock on the instance. If you have any tables that are not using InnoDB, the backup cannot guarantee transaction-safe consistency for those tables. It applies a weed lock to those tables so that it can guarantee consistency. Then it briefly locks all logging activity to take a consistent view of the current coordinates of various logs. It releases the weed lock on non-transactional tables. Using the log coordinates that were taken earlier in the process, it gathers all logs for transactions that have occurred since then. Bear in mind that the backup process takes place while the system is active. So, for a consistent backup, it must record not only the data files, but all changes that occurred during the backup. Then it releases the backup lock. The last piece of information recorded is any metadata for the backup itself, including its timing and contents in the final redo log. That completes the backup operation. 16:30 Nikita: And where are the files stored? Perside: The files contained in the backup are saved to the backup location, which can be on the local system or in network storage. The files contained in the backup location include files from the MySQL data directory. Some raw files include InnoDB tablespace, plus any InnoDB file per table tablespace files, and InnoDB log files. Other files might include data files belonging to other storage engines, perhaps MyISAM files. The various log files in instance configuration files are also retained. 17:20 Lois: What steps do you follow to restore a MySQL Enterprise Backup, and how do you guarantee consistency, especially when dealing with incremental backups? Perside: To restore from a backup using MySQL Enterprise Backup, you must first remove any previous files from the data directory. The restore process will fail if you attempt to restore over an existing system or backup. Then you restore the database with appropriate options. If you only restore a single backup, you can use copy, back, and apply log to ensure that the restored system has a consistency state. If you perform a full backup in subsequent incremental backups, you might need to restore multiple times using copy-back, and then use copy-back-and-apply-log only for the final consistent restore operation. The restart server might be on the same host or might be a different host with different configuration. This means that you might have to change some configuration on the restored server, including the operating system ownership of the restored data directory and various MySQL configuration files. If you want to retain the MySQL configuration files from the source server to reproduce on a new server, you should copy those files separately. MySQL Enterprise Backup focuses on the data rather than the server configuration. It does, however, produce configuration files appropriate for the backup. These are similar to the MySQL configuration files, but only contain options relevant for the backup process itself. There's also variables that have been changed to non-default values and all global variable values. These files must be renamed and possibly edited before they are suitable to become configuration files in the newly restored server. For example, the mysqld-auto.cnf file contains a JSON-formatted set of persisted variables. The backup process stores this as the newly named backup mysqld-auto.cnf. If you want to use it in the restored server, you must rename it and place it in the appropriate location so that the restored server can read it. This also applies in part to the auto.cnf file, which contain identifying information for the server. If you are replacing the original server or restoring on the same host, then you can keep the original values. However, this information must be unique within a network. So, if you are restoring this backup to create a replica in a replication topology, you must not include that file and instead start MySQL without it so that it creates its own unique identifying information. 21:14 Nikita: Let’s discuss securing and optimizing backups. How does MySQL Enterprise Backup handle encryption and compression, and what are the critical considerations for each? Perside: You can encrypt backups so that they are secure while moving them around or archiving them. The encrypt option performs the encryption. And you can specify the encryption key either on the command line as a string or a key file that has been generated with some cryptographic algorithm. Encryption only applies to image files, not to backup directories. You can also compress backup with different levels of compression, with higher levels requiring more CPU, but resulting in greater savings in storage. Compression only works with InnoDB data files. If your organization has media management software for performing backups, perhaps to a tape array, then you can use the SBT interface supported in MySQL Enterprise Backup. 22:34 Lois: Before we wrap up, could you share how MySQL Enterprise Backup facilitates the management of backups across a multi-server environment? Perside: As an enterprise solution, it's easy to run MySQL Enterprise Backup in a multi-server environment. We've already mentioned backing up to cloud storage, but you can, of course, back up to a directory or image on network storage that can be mounted locally, perhaps with NFS or some other file system. The "with time" option enables multiple backups within the same backup directory, where each in its own subdirectory named with the timestamp. This is especially useful when you want to run the same backup script repeatedly.  23:32 Lois: Thank you for that detailed overview, Perside. This wraps up our discussion of the various backup types, their pros and cons, and how to select the right option for your needs. In our next session, we’ll explore the different MySQL monitoring strategies and look at the features as well as benefits of Heatwave. Nikita: And if you want to learn more about the topics we discussed today, head over to mylearn.oracle.com and take a look at the MySQL 8.4 Essentials course. Until then, this is Nikita Abraham… Lois: And Lois Houston signing off! 24:06 That’s all for this episode of the Oracle University Podcast. If you enjoyed listening, please click Subscribe to get all the latest episodes. We’d also love it if you would take a moment to rate and review us on your podcast app. See you again on the next episode of the Oracle University Podcast.