{"id":95127,"date":"2022-11-26T18:19:30","date_gmt":"2022-11-26T18:19:30","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95127"},"modified":"2022-11-26T18:20:55","modified_gmt":"2022-11-26T18:20:55","slug":"backing-up-mysql-part-5-big-data","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/backing-up-mysql-part-5-big-data\/","title":{"rendered":"Backing Up MySQL Part 5: Backing Up Big Data"},"content":{"rendered":"

According to research made by Statista in 2022<\/a>, the amount of data that is being worked with globally is expected to reach more than 180 zettabytes. Part of that is because more and more data is being introduced to the online world \u2013 but another part has its roots in software development. There are approximately 24.3 million software developers in the world (source: InApps<\/a>), and with the number increasing every year, there\u2018s bound to be a lot of work with data.<\/p>\n

The one thing that has always helped software developers work with data are databases \u2013 databases are not all made equal though. Some are said to be a fit for working with bigger data sets (MongoDB comes to mind), some are fit for timescale data (think TimescaleDB), some are a good fit for other use cases.<\/p>\n

However, MySQL is MySQL \u2013 the RDBMS has been here for ages, and undeniably, it\u2018s here to stay. As with every database management system, MySQL has its upsides and downsides \u2013 one of the biggest notable downsides of MySQL is that many people think that it\u2018s not a fit for working with big data<\/em>.<\/p>\n

However, that’s not exactly true – MySQL is powering some of the biggest websites in the world from Facebook (for Facebook, MySQL is one of the main database management systems of choice as the social network stores almost all of the necessary data in it), to some of the biggest & fastest data breach search engines in the world letting people secure themselves from identity theft.<\/a><\/p>\n

MySQL is a powerful, but a complex beast and in this blog, we will tell you how to back up big data sets within that beast.<\/p>\n

The Infrastructure of MySQL<\/span><\/h2>\n

Before working with bigger data sets inside MySQL, you should familiarize yourself with its infrastructure. The most frequently used storage engine specific to MySQL is InnoDB and its counterpart XtraDB developed by Percona. Both InnoDB and XtraDB have a couple of properties unique to themselves:<\/p>\n

    \n
  1. The functionality of InnoDB is based on the InnoDB buffer pool \u2013 the buffer pool is an area used to cache the data and indexes when they are accessed. The bigger it is, the faster certain types of queries that are using the buffer pool or its data (SELECT<\/code>, LOAD DATA INFILE<\/code>) will complete.<\/li>\n
  2. InnoDB supports the ACID model guaranteeing data atomicity, consistency, transaction isolation, and durability. Note that even though InnoDB is known to offer support for ACID properties by default, it can also be made to exchange ACID for speed (and vice versa.)<\/li>\n
  3. InnoDB has a couple of acquaintances \u2013 while InnoDB data is stored in the ibdata1 file, InnoDB also has a couple of log files \u2013 ib_logfile0<\/code> and ib_logfile1<\/code> \u2013 that are used to store undo and redo logs. Undo logs contain information about undoing changes made by a specific transaction, while redo logs store information about how to re-do them (hence the names.) Both files can be found either in the \/var\/lib\/mysql\/mysql*.*.**\/data<\/code> directory if Linux is in use, or in the \/bin\/mysql\/mysql*.*.**\/data<\/code> directory if you find yourself using Windows (*.*.** represents the MySQL version):
    \n\"\"<\/li>\n
  4. All of the settings relevant to InnoDB can be configured via the settings present in my.cnf.<\/li>\n<\/ol>\n

    Now as far as backups are concerned, we don\u2018t need to know much about the internals of InnoDB (or any other MySQL storage engine for that matter) because we can simply take them using mysqldump<\/a>, mydumper<\/a>, or through phpMyAdmin by heading over to the Export tab:<\/p>\n

    \"\"<\/p>\n

    All of those tools will provide us with logical backups that can be used to restore data whenever we please, however, for those of us that work with bigger data sets, neither of those tools are an option \u2013 as INSERT<\/code> statements contained in the backups derived from those tools usually come with a lot of overhead, developers frequently find themselves needing to dip their toes in other waters instead.<\/p>\n

    Backing Up Big Data<\/span><\/h2>\n

    Logical Backups<\/span><\/h3>\n

    In order to back up data sets that are bigger in size, developers need to keep a couple of things in mind:<\/p>\n

      \n
    1. First off, if a logical backup is being taken, we should avoid creating a backup filled with INSERT INTO<\/code> statements \u2013 this article should provide you with a little more information what these are<\/a>, but the main reason such statements are to be avoided is because such statements come with a lot of overhead that MySQL has to consider. In order to execute a single INSERT<\/code> statement, MySQL has to check for permissions, open the necessary tables, initialize the query, acquire all locks that are needed for the query to execute, run the query, finish the work (let the query run until it\u2019s finished), close tables and perform additional clean up operations. Of course, these might be relatively quick to complete in the grand scheme of things, but we need to consider that we would be working with hundreds of millions of rows at a time.<\/li>\n
    2. Working with big data sets would require a dedicated server, or at the very least, a VPS. This one is probably already out of the question (you\u2019ve done your investigation into the hosting provider before choosing one, right<\/a>?), but nonetheless, choosing the right server for our big data project is always a necessity.<\/li>\n
    3. How many databases are we backing up? How many tables are inside of those databases? Do all of those need to be backed up? You would be surprised how many times we can limit the amount of data that is being backed up by simply thinking ahead.<\/li>\n
    4. Did we work with bigger data sets before? Our experience in the past teaches us more lessons than we could imagine \u2013 however, if we didn\u2019t have much experience beforehand, we can also just ask around \u2013 if our colleagues are well-versed in the field, the answers to most pressing questions shouldn\u2019t be too far away.<\/li>\n<\/ol>\n

      Once the basics are covered and we\u2019ve decided what we\u2019re backing up, it\u2019s time to move on. The best way to take a logical backup of a bigger data set is to use the LOAD DATA INFILE<\/code> query \u2013 since the query comes with many of its own bells and whistles, it\u2019s significantly faster than ordinary INSERT<\/code> statements:<\/p>\n