MySQL Backup - Part 1

Join Lois Houston and Nikita Abraham as they kick off a two-part episode on MySQL backups with MySQL expert Perside Foster. In this conversation, they explore the critical role of backups in data recovery, error correction, data migration, and more.   Perside breaks down the differences between logical and physical backups, discussing their pros and cons, and shares valuable insights on how to create a reliable backup strategy to safeguard your data.   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 Lois: Welcome to the Oracle University Podcast! I’m Lois Houston, Director of Innovation Programs with Oracle University, and with me is Nikita Abraham, Team Lead of Editorial Services.  Nikita: Hi everyone! This is Episode 6 in our series on MySQL, and today we’re focusing on how to back up our MySQL instances. This is another two-parter and we’ve got Perside Foster back with us.    00:49 Lois: Perside is a MySQL Principal Solution Engineer at Oracle and she’s here to share her insights on backup strategies and tools. In this episode, we’ll be unpacking the types of backups available and discussing their pros and cons. Nikita: But first let’s start right at the beginning. Perside, why is it essential for us to back up our databases? 01:10 Perside: The whole point of a database is to store and retrieve your business data, your intellectual property. When you back up your data, you are able to do disaster recovery so that your business can continue after some catastrophic event. You can recover from error and revert to a previous known good version of the data. You can migrate effectively from one system to another, or you can create replicas for load balancing or parallel system. You can retain data for archival purposes. Also, you can move large chunks of data to other systems, for example, to create a historical reporting application. And then you can create test environments for applications that are in development and that need real world test data. 02:10 Lois: Yes, and creating a robust backup strategy takes planning, doesn’t it? Perside: As with any complex business critical process, there are challenges with coming up with a backup strategy that you can trust. This requires some careful planning. Any backup process needs to read the data. And in a production system, this will involve adding input/output operations to what might be an already busy system. The resources required might include memory or disk I/O operation and of course, you'll want to avoid downtime, so you might need to schedule the backup for a time when the system is not at peak usage. You'll also need to consider whether the backup is on network storage or some local storage so that you don't exceed limitations for those resources. It isn't enough just to schedule the backup. You'll also need to ensure that they succeed, which you can do with monitoring and consistency check. No backup is effective unless you can use it to restore your data, so you should also test your restore process regularly. If you have business requirements or regulatory commitments that control your data storage policies, you need to ensure your backup also align with those policies. Remember, every backup is a copy of your data at that moment in time. So it is subject to all of your data retention policies, just like your active data. 04:02 Nikita: Let’s talk backup types. Perside, can you break them down for us? Perside: The first category is logical backup. A logical backup creates a script of SQL statements that will re-create the data structure and roles of the live database. Descript can be moved to another server as required. And because it's a script, it needs to be created by and executed on a running server. Because of this, the backup process takes up resources from the source server and is usually slower than a physical media backup.  04:45 Nikita: Ok… what’s the next type? Perside: The next category is physical backup. This is a backup of the actual data file in the server. Bear in mind that the file copy process takes time, and if the database server is active during that time, then the later parts of the copy data will be inconsistent with those parts copied earlier. Ideally, the file must be stable during the backup so that the database state at the start of the copy process is consistent with the state at the end. If there is inconsistency in the data file, then MySQL detects that when the server starts up and it performs a crash recovery. From MySQL’s perspective, there is no difference between a database backup copied from a running server and restarting a server after a crash. In each case, the data files were not saved in a consistent state and crash recovery can take a lot of time on large databases. 06:02 Lois: I see… how can MySQL Enterprise Backup help with this? Perside: MySQL Enterprise Backup has features that enable a consistent backup from a running server. If you create file system copies, either by copying the data files or by performing a file system snapshot, then you must either shut the server down before the copy and undergo crash recovery on the server that starts with those copied files. 06:35 Lois: And aside from logical and physical backups, are there other techniques to back up data? Perside: The binary log enables point-in-time recovery. You can enable replication in a couple of ways. If you start replication and then stop it at a particular time, the replica effectively contains a live backup of the data at the time that you stopped replication. You can also enable a defined replication lag so that the replica is always a known period of time behind the production database. You can also use transportable tablespaces, which are tables or sets of tables in a specific file that you can copy to another server. 07:34 AI is being used in nearly every industry…healthcare, manufacturing, retail, customer service, transportation, agriculture, you name it! And it’s only going to get more prevalent and transformational in the future. It’s no wonder that AI skills are the most sought-after by employers. If you’re ready to dive in to AI, check out the OCI AI Foundations training and certification e that’s available for free! It’s the perfect starting point to build your AI knowledge. So, get going! Head over to mylearn.oracle.com to find out more. 08:14 Nikita: Welcome back! I want to return to the topic of crafting an effective backup strategy. Perside, any advice here? Perside: We can use the different backup types to come up with an effective backup strategy based on how we intend to restore the data. A full backup is a complete copy of the database at some point in time. This can take a lot of time to complete and to restore. An incremental backup contains only the changes since the last backup, as recorded in the binary log files. To restore an incremental backup, you must have restored the previous full backup and any incremental backups taken since then. For example, you might have four incremental backups taken after the last full backup. Each incremental backup contains only the changes since the previous backup. If you want to restore to the point at which you took the fourth incremental backup, then you must restore the full backup and each incremental backup in turn. A differential backup contains all changes since the last full backup. It contains only those portions of the database that are different from the full backup. Over time, the differential backup takes longer because it contains more changes. However, it is easier to restore because if you want to restore to the point at which you took a particular differential backup, you must restore the last full backup and only the differential backup that you require. You can ignore the intermediate differential backups. 10:13 Lois: Can you drill into the different types of backups and explain how each technique is used in various situations? Perside: One of the physical backup techniques is taking a snapshot of the storage medium. The advantages of a snapshot include its quickness. A snapshot is quick to create and restore. It is well-suited to situations where you need to quickly revert to a previous version of the database. For example, in a development environment. A storage snapshot is often a feature of the underlying file system. Linux supports logical volume management or LVM, and many storage area networks or network-attached storage platforms have native snapshot features. You can also use a storage snapshot to supplement a more scheduled logical backup structure. This way, the snapshot enables quick reversion to a previous type, and the logical backup can be used for other purposes, such as archiving or disaster recovery. 11:28 Nikita: Are there any downsides to using snapshots? Perside: First one includes issues with consistency. Because taking a snapshot is quick and does not cause a database performance hit, you might take the snapshot while the system is running. When you restore such a snapshot, MySQL must perform a crash recovery. If you want a consistent snapshot, you must shut down MySQL in advance. Another problem is that the snapshot is a copy of the file system and not of the database. So if you want to transfer it to another system, you must create a database backup from the storage. This adds step in time. A snapshot records the state of the disk at a specific point in time. Initially, the snapshot is practically empty. When a data page changes, the original version of that page is written to the snapshot. Over time, the snapshot storage grows as more data pages are modified. So multiple snapshots result in multiple writes whenever a snapshot data page is changed. To avoid performance deterioration, you should remove or release snapshots when they are no longer in use. Also, because snapshots are tied to the storage medium, they're not suited to moving backups between systems. 13:03 Lois: How about logical backups? How do we create those? Perside: The mysqldump utility has long been a standard way to create logical backups. It creates a script made up of the SQL statement that creates the data and structure in a database or server.  13:21 Nikita: Perside, what are the advantages and disadvantages of mysql dump? Perside: It is an excellent solution for preserving the database structure or for backing up small databases. Logical backups naturally require that the server is running. And they use system resources to produce the SQL statements, so they are less likely for very large databases. The output is a human-readable text file with SQL statements that you can edit as a text file. It can be managed by a source code management system. This allows you to maintain a known good version of the database structure, one that matches your application source code version, which can also include sample data. The mysqldump disadvantages are it needs to run against an active server. So if your production server is busy, you must take action to ensure a consistent backup. This requires locking tables or using the single transaction option, which can result in application delays as the backup completes in a consistent way. Mysqldump does not track changes since the last backup, so it has no way of recording only those rows that have changed. This means it's not suited to perform differential or incremental backups. The scripts must be executed against a running server, so it is slower to restore than using a data dump or physical backup. Additionally, if the database structure has indexes of foreign keys, these conditions must be checked and updated as the data is imported. You can disable these checks during the import but must handle any risks that come from doing so. Because the backup is nothing more than an SQL script, it is easy to restore. You can simply use the MySQL client or any other client tool that can process scripts.  15:46 Nikita: Is there an alternative tool for logical backups? Perside: MySQL Shell is another utility that supports logical backup and restore. Unlike mysqldump, it dumps data in a form that can be processed in parallel, which makes it much faster to use for larger data sets. This enables it to export to or import from remote storage where it can stream data without requiring the whole file before starting the input. It can process multiple chunks of imported data in parallel, and you can monitor progress as it completes. You can also pause import and resume later. For example, in the event of network outage. You can dump and restart table structure, including indexes and primary keys. The utilities in MySQL Shell are exposed through functions. The dumpInstance and dumpSchema utilities back up the whole server or specified schemas respectively. And loadDump is how you restore from such a dump. 17:07 Lois: Thanks for that rundown, Perside! This concludes our first part on MySQL backups. Next week, we’ll take a look at advanced backup methods and the unique features of MySQL Enterprise Backup. Nikita: And if you want to learn more about everything we discussed today, head over to mylearn.oracle.com and explore the MySQL 8.4 Essentials course. Until then, this is Nikita Abraham… Lois: And Lois Houston signing off! 17:37 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.