MySQL Backups - Percona

2 downloads 407 Views 1MB Size Report
Starting as a PHP programmer, he soon got involved with the LAMP stack. Before joining Percona, Jervin worked with sever
MySQL Backups Vol. 1

By Peter Zaitsev, Vadim Tkachenko Tushar Katarki and Jervin Real

In this Percona eBook, the first in a series on MySQL backups, Jervin Real elaborates on encrypted and incremental MySQL backups with Percona XtraBackup; Tushar Katarki details backup and restore of MySQL to OpenStack Swift; Vadim Tkachenko offers some TokuDB tips for MySQL backups; and Peter Zaitsev introduces the popular MySQL Backup Service from Percona.

Copyright © 2006-2014 Percona LLC

MySQL Backups

Vol. 1

Table of Contents Chapter 1: Encrypted and incremental MySQL backups with Percona XtraBackup Chapter 2: Backup and restore of MySQL to OpenStack Swift Chapter 3: TokuDB tips: MySQL backups Chapter 4: Percona Backup Service for MySQL

3 6 9 11

About Percona Percona was founded in August 2006 and now employs a global network of experts with a staff of more than 120 people. Our customer list is large and diverse, including Fortune 50 corporations, popular websites, and small startups. We have over 2,000 customers and, although we do not reveal all of their names, chances are we're working with nearly every large MySQL user you've heard about. To put Percona's MySQL expertise to work for you, please contact us.

Is this an emergency? Get immediate assistance from Percona Support 24/7. Click here Skype: oncall.percona GTalk: [email protected] AIM (AOL Instant Messenger): oncallpercona Telephone direct-to-engineer: +1-877-862-4316 or UK Toll Free: +44-800-088-5561 Telephone to live operator: +1-888-488-8556 Customer portal: https://customers.percona.com/

Copyright © 2006-2014 Percona LLC

MySQL Backups Chapter 1: Encrypted and incremental MySQL backups with Percona

Encrypted and incremental MySQL backups with Percona XtraBackup By Jervin Real

This chapter explains how to implement incremental MySQL backups alongside encryption with Percona XtraBackup, an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases. Some users I’ve worked with thought it was not initially possible because with the default –encrypt options with Percona XtraBackup, all files will be encrypted, but alas, that is not the case. This is where the option –extra-lsn-dir becomes useful, because it allows you to save LSN (Log Sequence Number) information to another directory and exclude it from encryption, allowing you to use the same information needed by incremental backups. Enough talk, let me show you. Because you would want to usually script your MySQL backup and restore procedure, I’d use variables here as well to make you more familiar. First, I’d create 3 folders, where my backups will be stored, ‘full’ for full backups, ‘incr’ for incremental backups, and ‘lsns’ to store an extra copy of my xtrabackup_checkpoints file with –extra-lsn-dir .

Second, to have better control of where my backups would go, I prefer assigning timestamped folders instead and use the –no-timestamp option to innobackupex.

Then manually create the specific directory where the backup’s xtrabackup_checkpoints file would be saved:

Of course, I need an encryption key for my encrypted backups, in this case, taking the example from the manual, I used openssl to generate a random key. You can use your own key string as long as its size conforms to the size required by the –encrypt algorithm you chose.

3

MySQL Backups Chapter 1: Encrypted and incremental MySQL backups with Percona

Next, I would run my full backup:

The output says my full backup is saved to:

Now here’s the trick: Because the full backup is encrypted, we will use the xtrabackup_checkpoints file separately saved by xtrabackup to the –extra-lsn-dir path we specified above to get the LSN and use that for our next incremental backup.

Above, we get the LSN value and assign it to a variable. Similarly, we created a new CURDATE string for our incremental backup to use and created a new directory for the xtrabackup_checkpoints file. If you plan to create another incremental backup based off of what we are about to take now, you will use this next xtrabackup_checkpoints file to get LAST_LSN. With the up and coming Percona XtraBackup 2.2.1, you will not need –extra-lsn-dir anymore nor parse the xtrabackup_checkpoints file anymore for this purpose. A feature that will allow the user to save backup metadata to an InnoDB table will be available. So, now that we got our $LAST_LSN value, we execute our incremental backup with the command:

4

MySQL Backups Chapter 1: Encrypted and incremental MySQL backups with Percona

Again, based on the output, my backup was created at:

Now we have a full backup and an incremental backup, of course to make sure our backups are usable, we’d like to validate them. To do that, our first step is to decrypt both full and incremental backups. innobackupex has another handy –decrypt option for that, you can even use –parallel to to make it faster.

Once the backups are decrypted, we can go through the usual process of preparing a full and incremental backups as described in the manual.

5

MySQL Backups Chapter 2: Backup and restore of MySQL to OpenStack Swift

Backup and restore of MySQL to OpenStack Swift By Tushar Katarki

MySQL database usage is popular in OpenStack. Core OpenStack services for Compute (Nova), Storage (Cinder), Neutron (Networking), Image (Glance) and Identity (Keystone) all use MySQL database. MySQL – as the world’s most popular database, runs inside OpenStack Virtual Machines and serves as database backend to OpenStack cloud based applications. The MySQL instances can be configured to run in virtual machines manually (by simply installing MySQL inside a VM and running it) or can be created in an on-demand fashion by OpenStack Database-as-a-Service (Trove). In either case, the MySQL data is mission-critical. OpenStack cloud administrators and cloud guests/tenants need the ability to backup and restore their MySQL databases. mysqldump is traditional way of doing MySQL backups and restores. However, based on previous experiences of the MySQL community, it is widely known that mysqldump has limitations especially when it comes to speed of backup and restore as databases grow larger. Percona XtraBackup overcomes these limitations and enables faster, non-blocking MySQL backups and restores on larger datasets. It also offers other important abilities such as streaming and encryption. It works with MySQL as well as all the variants such as Percona Server and MariaDB. And all this is open source and free. Percona XtraBackup is popular and widely used in the MySQL community. More recently it has seen adoption within OpenStack context. It is at the heart of backup/restore for Rackspace Cloud DbaaS. OpenStack Database-as-a-Service (Trove) implementation uses Percona XtraBackup as a pluggable backend for database backup and restore. Until now, however, Percona XtraBackup did not have the ability to do streaming backup and restores of MySQL databases to cloud storage targets such as OpenStack Swift or Amazon Web Services S3. xbcloud tool is a new contribution from Percona that changes this. At a basic level, the tool enables MySQL backups to OpenStack Swift. is the Object Service of OpenStack. It offers a high availability data storage platform that scales horizontally and offers disaster recovery of data across multiple datacenters and geographies. It is the default and popular choice for storing backups of infrastructure and tenant data in OpenStack clouds. How does xbcloud tool work? xbcloud uploads and downloads full or part of xbstream archive to/from OpenStack Swift. So what is xbstream? xbstream is a streaming format available in Percona XtraBackup that overcomes some limitations of traditional archive formats such as tar, cpio and others which did not allow streaming dynamically generated files, for example dynamically

6

MySQL Backups Chapter 2: Backup and restore of MySQL to OpenStack Swift

compressed files. Archive uploading will employ multipart upload for Large Objects on Swift. Along with this, the xbstream archive index file will be uploaded which contains list of files and their parts and offsets of those parts in xbstream archive. This index is needed for downloading only part of archive (one or several tables from backups) on demand. Details of usage and examples follow. Backup:

Example:

Restore:

Example:

7

MySQL Backups Chapter 2: Backup and restore of MySQL to OpenStack Swift

Interested? Try it out. Percona XtraBackup and xbcloud are open source and free. You can find instructions to download, usage and release notes for PXB 2.3 Alpha (which includes xbcloud) here. So what is next? We at Percona are excited about the possibilities with this new xbcloud tool and Percona Xtrabackup in general. Our most immediate next steps would be to act on community feedback, address bugs and issues and move towards GA soon. Beyond that we are looking to enhance xbcloud to support other cloud platforms such as AWS S3 and Google Cloud Engine. We would love to hear from you on this. You could either leave feedback directly as comments to this post or can file enhancement requests and bugs .

8

MySQL Backups Chapter 3: TokuDB tips: MySQL backups

TokuDB tips: MySQL backups By Vadim Tkachenko In a recent post on the MySQL Performance Blog titled “TokuDB gotchas: slow INFORMATION_SCHEMA TABLES,” I saw a couple questions and tweets asking if we use TokuDB in production. Actually I mentioned it in that post and we also blogged about it in a couple of other recent posts: Getting to know TokuDB for MySQL Percona Server with TokuDB: Packing 15TB into local SSDs So, yes, we are using Percona Server + TokuDB as a main storage engine in Percona Cloud Tools to store timeseries data. And, yes, Percona Server + TokuDB is available GA Percona Server 5.6.19-67.0 with TokuDB (GA). Just having good performance is not enough to make it into production – there are also operational questions and one such question is about backups. I want to explain how we do backups for Percona Server + TokuDB in Percona Cloud Tools. To support transactions that involve both TokuDB and InnoDB engines, TokuDB uses a two-phase commit mechanism in MySQL. When involved, the two-phase commit requires binary logs presented for a proper recovery procedures. But now we need to take a look at how we setup a binary log in Percona Cloud Tools. We used SSD for the main data storage (LVM partition is here) and we use a Hardware RAID1 over two hard-drives for binary logs. We choose this setup as we care about SSD lifetime. In write-intensive workloads, binary logs will produce a lot of write operations and in our calculation we will just burn these SSDs, so we have to store them on something less expensive. So the problem there is that when we take an LVM snapshot over main storage, we do not have a consistent view of binary logs (although it is possible to modify backup scripts to copy the current binary log under FLUSH TABLES WITH READ LOCK operation, this is probably what we will do next). But binary logs are needed for recovery, without them we face these kind of errors during restoring from backup:

9

MySQL Backups Chapter 3: TokuDB tips: MySQL backups

The error message actually hints a way out. Unfortunately it seems that we are the first ones to have ever tried this option, as tc-heuristic-recover is and not supposed to work… and it would be noticed if someone really tried it before us (which gives me an impression that Oracle/MySQL never properly tested it, but that is a different story). We will fix this in . So the way to handle a recovery from LVM backup without binary logs is to start mysqld with –tc-heuristic-recover switch (unfortunately I did not figure out yet, should it be COMMIT or ROLLBACK value, hehe). The proper way to use LVM backup is to have a corresponding binary log file, like I said it will require a modification to mylvmbackup script. I should say this is not the only way we do backups in Percona Cloud Tools. In this project we use Percona Backup Service for MySQL provided by the Percona Managed Services team, and our team also uses to perform a logical backup of data. While it works acceptably to backup hundreds of gigabytes worth of data (it is just a sequential scan, which should be easy for TokuDB), the full recovery is painful and takes unacceptably long. So mydumper backup (recovery) will be used if we ever need to perform a fine-grained recovery (i.e only small amount of specific tables). So I hope this tip is useful if you are looking for info about how to do backups for TokuDB.

10

MySQL Backups Chapter 4: Percona Backup Service for MySQL

Percona Backup Service for MySQL By Peter Zaitsev The Percona Backup Service managed service ensures properly configured backups run successfully as scheduled on customer-provided backup storage – whether on premise, in the cloud, or a hybrid. Backup issues or production data recovery are efficiently handled by Percona Managed Services technicians with deep knowledge of MySQL. As we state in our white papers, “MySQL backup and recovery are a foundational piece of any infrastructure. A well-tested backup and recovery system can be the difference between a minor outage and the end of a business.” While MySQL backups are “foundational,” they still require constant management, and the most important use of a backup, namely recovery, is often complex. The Percona MySQL Backup Service is available for companies using any variant of single-node or clustered MySQL — on premise or in the cloud. This includes MySQL, Percona Server, MariaDB, and Percona XtraDB Cluster.

Reliable MySQL Backup Services Developed by MySQL experts with decades of operational experience, the Percona MySQL Backup Service is based on widely adopted open source database backup software solutions such as Percona XtraBackup. Monitoring software is installed to ensure MySQL backups run as expected and alert the Percona Managed Services team to any issues. Percona experts on duty 24x7x365 resolve problems before the availability of backups is impacted and can implement a full, partial, or point in time recovery to minimize downtime and data loss on production servers. MySQL backup data sets are secure and compliant with regulatory requirements. 256 bit encryption meets or exceeds common security and compliance requirements. Internal procedures ensure that backups are managed with extreme care and are only stored safely on secure servers. Backups are available for on-demand recovery to comply with HIPAA, SOX, or PCI requirements. The status of current and past backups is easily accessible through the Percona MySQL Backup Service customer portal. The portal includes instructions on how to use the backups to restore data for routine purposes such as restoration of development databases.

Efficient MySQL Data Recovery Percona Backup Service technical experts respond within 30 minutes to ensure that production data is recovered quickly and as completely as possible. We can recover: Data back to the last full capture image

11

MySQL Backups Chapter 4: Percona Backup Service for MySQL

Specific tables which saves significant time when only a single table or set of tables needs to be recovered Full data to a specific point in time which ensures an application can be recovered to the same state as when the data was lost Unlike database-as-a-service solutions, the Percona Backup Service can recover specific tables and full data to a specific point in time because we create additional backup points.

Cost Effective and Highly Flexible Our MySQL backup-as-a-service solution costs less than managing backups in-house. Our 24x7x365 team serves multiple customers so the cost of the Percona MySQL Backup Service is lower than having someone on staff to manage backups. We also use a proprietary backup approach which significantly reduces the size of backup data sets, requiring much less storage space than conventional methods. The Percona MySQL Backup Service accommodates any combination of MySQL server and backup server locations. We can work with MySQL server and backup servers on premise or in the cloud and can even configure the process to store one backup set on premise and another in the cloud for additional protection. This configuration flexibility means we can meet a wide range of data availability and budget needs.

MySQL Backup Service Features The Percona Backup Service is designed so backups will run smoothly and reliably. The following features are included: Customer portal for anytime access to current and past backup status as well as instructions on how to restore the MySQL backups for non-production purposes Efficient data recovery for production issues with full, partial, and point in time recovery options A high level of security with 256 bit encryption and backups only stored on the customer’s secure servers Regulatory compliance with backups available for on-demand recovery to comply with HIPAA, SOX, and PCI requirements Lower cost than managing MySQL backups in-house with 24x7x365 monitoring and issue resolution combined with a proprietary backup approach which significantly reduces the size of the backup data set versus conventional methods Ability to accommodate any combination of MySQL server and backup server locations – on premise, in the cloud, or a hybrid Flexible configuration options which enable the service to meet a wide range of data availability and budget requirements

12

MySQL Backups Chapter 4: Percona Backup Service for MySQL

Learn More Learn more about our MySQL Backup Service solution by viewing our recorded webinar, “MySQL Backup and Recovery Best Practices,” and see how we can ensure your backups are ready when you need them!

13 Powered by TCPDF (www.tcpdf.org)

About the authors Peter Zaitsev, Percona's CEO and founder, is arguably the world's foremost expert in MySQL performance and scaling, with a special expertise in hardware and database internals. Peter's work has contributed to dozens of MySQL appliances, storage engines, replication systems, and other technologies. Peter co-authored High Performance MySQL along with two other Percona experts. He is a frequently invited guest at open source conferences, and has been a sell-out speaker at the yearly MySQL User Conference since its inception. Peter currently lives in North Carolina with his wife and their two children. Vadim Tkachenko co-founded Percona and serves on the executive team as CTO. He leads Percona's development group, which produces Percona Server, Percona XtraDB, and Percona XtraBackup. He is an expert in LAMP performance, especially optimizing MySQL and InnoDB internals to take full advantage of modern hardware using his multi-threaded programming background. Source code patches authored by Vadim have been incorporated by Oracle Corporation and its predecessors into the mainstream MySQL and InnoDB products. He lives in California with his wife and their two children. Tushar Katarki is a director of product management at Percona. He has 15+ years of experience as a software developer, software architect project manager and product manager in IT datacenter and enterprise systems. Prior to Percona he worked at Red Hat, Polycom, Oracle, Sun and Sycamore Networks. He lives in the Boston area with his wife and two children. Jervin Real is a senior MySQL consultant at Percona. He partners with customers on building reliable and high-performance MySQL infrastructures. Starting as a PHP programmer, he soon got involved with the LAMP stack. Before joining Percona, Jervin worked with several hosting companies, providing care for customer hosted services and data on both Linux and Windows.

Visit Percona's ever-growing MySQL eBook library for more great content at http://www.percona.com/resources/mysql-ebooks Copyright © 2006-2014 Percona LLC