Dec
01
2017
--

This Week in Data with Colin Charles 17: AWS Re:Invent, a New Book on MySQL Cluster and Another Call Out for Percona Live 2018

Colin Charles

Colin Charles Open Source Database evangelist for PerconaJoin Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

The CFP for Percona Live Santa Clara 2018 closes December 22, 2017: please consider submitting as soon as possible. We want to make an early announcement of talks, so we’ll definitely do a first pass even before the CFP date closes. Keep in mind the expanded view of what we are after: it’s more than just MySQL and MongoDB. And don’t forget that with one day less, there will be intense competition to fit all the content in.

A new book on MySQL Cluster is out: Pro MySQL NDB Cluster by Jesper Wisborg Krogh and Mikiya Okuno. At 690 pages, it is a weighty tome, and something I fully plan on reading, considering I haven’t played with NDBCLUSTER for quite some time.

Did you know that since MySQL 5.7.17, connection control plugins are included? They help DBAs introduce an increasing delay in server response to clients after a certain number of consecutive failed connection attempts. Read more at the connection control plugins.

While there are a tonne of announcements coming out from the Amazon re:Invent 2017 event, I highly recommend also reading Some data of interest as AWS reinvent 2017 ramps up by James Governor. Telemetry data from sumologic’s 1,500 largest customers suggest that NoSQL database usage has overtaken relational database workloads! Read The State of Modern Applications in the Cloud. Page 8 tells us that MySQL is the #1 database on AWS (I don’t see MariaDB Server being mentioned which is odd; did they lump it in together?), and MySQL, Redis & MongoDB account for 40% of database adoption on AWS. In other news, Andy Jassy also mentions that less than 1.5 months after hitting 40,000 database migrations, they’ve gone past 45,000 over the Thanksgiving holiday last week. Have you started using AWS Database Migration Service?

Releases

Link List

Upcoming appearances

  • ACMUG 2017 gathering – Beijing, China, December 9-10 2017 – it was very exciting being there in 2016, I can only imagine it’s going to be bigger and better in 2017, since it is now two days long!

Feedback

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

Nov
27
2017
--

autoxtrabackup v1.5.0: A Tool for Automatic Backups

autoxtrabackup

autoxtrabackupThere is a new version of the autoxtrabackup tool. In this post, I’ll provide some of the highlights available this time around.

autoxtrabackup is a tool created by PerconLabs. We’ve now put out the 1.5.0 version, and you can test it further.

Note: PerconaLabs is a repository for various open source scripts and tools created by Percona staff. Items added to the PerconaLabs repository are not official Percona products, and aren’t covered by support or services agreements. 

autoxtrabackup is written in Python3 and hosted in PerconaLab (forked from Shako’s repo). Basically, this tool automates backup/prepare/copy-back actions. I want to talk about recent changes and additions.

First of all, autoxtrabackup now has a --test_mode option, intended to test XtraBackup automation process.

Here is the brief flow for this:

  • Clone percona-qa repo
  • Clone Percona Server for MySQL 5.6 and 5.7 from github.
  • Build PS servers in debug mode.
  • Get 2.3 and 2.4 versions of XtraBackup
  • Generate autoxtrabackup .conf files for each version of PS and XtraBackup
  • Pass different combination of options to PS start command and initialize PS servers each time with different options
  • Run sysbench against each started PS server
  • Take backup in cycles for each started PS + prepare
  • If make_slaves is defined, then create slave1 server from this backup (i.e., copy-back to another directory and start the slave from it)
  • Then take a backup, prepare and copy-back from this new slave1 to create slave2
  • Run pt-table-checksum on the master to check backup consistency

I have prepared my environment, and now want to start --test_mode. Basically, it creates option combinations and passes them to the start script:

2017-11-15 22:28:21 DEBUG    Starting cycle1
2017-11-15 22:28:21 DEBUG    Will start MySQL with --innodb_buffer_pool_size=1G --innodb_log_file_size=1G
--innodb_page_size=64K --early-plugin-load=keyring_file.so
--keyring_file_data=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/mysql-keyring/keyring
--log-bin=mysql-bin --log-slave-updates --server-id=1 --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row

So as you see, it is starting MySQL with --innodb_buffer_pool_size=1G --innodb_log_file_size=1G --innodb_page_size=64K. In cycle2, it will likely pick --innodb_buffer_pool_size=1G --innodb_log_file_size=1G --innodb_page_size=32K, and so on. It depends what you have passed in config:

# Do not touch; this is for --test_mode, which is testing for XtraBackup itself.
[TestConf]
ps_branches=5.6 5.7
gitcmd=--recursive --depth=1 https://github.com/percona/percona-server.git
testpath=/home/shahriyar.rzaev/XB_TEST/server_dir
incremental_count=3
#make_slaves=1
xb_configs=xb_2_4_ps_5_6.conf xb_2_4_ps_5_7.conf xb_2_3_ps_5_6.conf
default_mysql_options=--log-bin=mysql-bin,--log-slave-updates,--server-id={},--gtid-mode=ON,--enforce-gtid-consistency,--binlog-format=row
mysql_options=--innodb_buffer_pool_size=1G 2G 3G,--innodb_log_file_size=1G 2G 3G,--innodb_page_size=4K 8K 16K 32K 64K

You can pass more options by changing the mysql_options in the config file. Also you can specify how many incremental backups you want by setting the incremental_count option. You can enable creating slaves from backup to test it as well, by enabling the make_slaves option. This is not recommended for daily usage. You can read more about it here: –test_mode.

For daily backup actions, I have added the --tag and --show_tags options, which can be quite useful. They help you to tag your backups. Take a full backup:

$ sudo autoxtrabackup --tag="My Full backup" -v
-lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log
-l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --backup

Take an incremental one:

$ autoxtrabackup --tag="First incremental backup" -v
-lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log
-l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --backup

Take a second incremental one:

$ autoxtrabackup --tag="Second incremental backup" -v
-lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log
-l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --backup

Now you can use the --show_tags to list tags:

$ sudo autoxtrabackup --show_tags
--defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf
Backup              Type    Status  TAG
-------------------------------------------
2017-11-16_20-10-53 Full        OK  'My Full backup'
2017-11-16_20-12-23 Inc         OK  'First incremental backup'
2017-11-16_20-13-39 Inc         OK  'Second incremental backup'

It would be quite nice if we could prepare those backups with a tag name. In other words, if I have a full backup and five incremental backups, what if I want to prepare until the second or third incremental, or just a full backup?

Pass the tag name with the --prepare option, and it will do the trick:

$ autoxtrabackup --tag="First incremental backup" -v
-lf /home/shahriyar.rzaev/autoxtrabackup_2_4_5_7.log
-l DEBUG --defaults_file=/home/shahriyar.rzaev/XB_TEST/server_dir/xb_2_4_ps_5_7.conf --prepare

It will prepare the full and “First incremental backup” – the remaining incremental backups will be ignored.

autoxtrabackup 1.5.0 also has a --dry_run option, which is going to show but not run exact commands. It is described here: –dry_run.

How about autoxtrabackup 1.5.0 installation? You can install it from the source or use pip3:

pip3 install mysql-autoxtrabackup

For more please read: Installation.

Do you want to enable encryption and compression for backups? Yes? You can enable this from the autoxtrabackup config as described here: Config file structure.

You can enable taking partial backups again by editing the config: partial backups.

autoxtrabackup 1.5.0 allows you to perform a partial recovery – i.e., restoring only a single specified table from a full backup. If the table was dropped,  autoxtrabackup will try to extract the create table statement from the .frm file using the mysqlfrm tool and then discard/import the tablespace from full backup. This is related to the transportable tablespace concept. You can read more here: restoring-single-table-after-drop.

For a full list of available options, read the DOC: autoxtrabackup DOC.

Thanks for reading! If you are going to try autoxtrabackup 1.5.0, don’t hesitate to provide some feedback!

Sep
26
2017
--

Pepper Turns to Percona to Ensure a Great Customer Experience at Pepper.com

Pepper.com

Pepper.comPepper.com, the world’s largest community deal platform, has selected Percona to manage its open source database performance.

Pepper.com’s around-the-clock community seeks and finds the best offers in fashion, electronics, traveling and much more. With 500 million page views, over 25 million users and over 65,000 user-submitted deals per month across communities in America, Europe and Asia, Pepper has quickly risen to be the largest community deal platform worldwide.

When Pepper.com’s primary MySQL database administrator left the company, Pepper decided to shift to a managed service to maintain uptime and responsiveness. Having previously attended Percona Live Europe, the premier European open source database conference, as well as being avid readers of the Percona Database Performance Blog, the Pepper team turned to Percona for open source database remote managed service expertise.

“Guaranteeing database performance is key to making sure our web applications are responsive and up-to-date,” said Pavel Genov, Head of Software Development at Pepper.com. “Percona Care Ultimate helps us to achieve these objectives.”

Pepper.comPepper was already using Percona Server for MySQL. Following a Percona Database Performance Audit to review the Pepper.com environment, architecture and setup, Percona XtraBackup was deployed to provide online non-blocking, tightly compressed, highly secure backups.

Check out the case study on Pepper.com and Percona’s engagement to improve and manage Pepper’s database environment.

Sep
05
2017
--

Webinar Wednesday, September 6, 2017: Percona Roadmap and Software News Update – Q3 2017

Percona Roadmap

Percona RoadmapCome and listen to Percona CEO Peter Zaitsev on Wednesday, September 6, 2017 at 10am PT / 1pm ET (UTC-7) discuss the Percona roadmap, as well as what’s new in Percona open source software.

 

During this webinar Peter will talk about newly released features in Percona software, show a few quick demos and share with you highlights from the Percona open source software roadmap. This discussion will cover Percona Server for MySQL and MongoDB, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster and Percona Monitoring and Management.

Peter will also talk about new developments in Percona commercial services and finish with a Q&A.

Register for the webinar before seats fill up for this exciting webinar Wednesday, September 6, 2017 at 10am PT / 1pm ET (UTC-7).

Peter ZaitsevPeter Zaitsev, Percona CEO and Co-Founder

Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With over 140 professionals in 30+ countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of internet giants, large enterprises and many exciting startups. Percona was named to the Inc. 5000 in 2013, 2014, 2015 and 2016. Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group. A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University, where he majored in Computer Science. Peter is a co-author of High Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Database Performance Blog. Fortune and DZone have both tapped Peter as a contributor, and his recent ebook Practical MySQL Performance Optimization is one of percona.com’s most popular downloads.
Aug
08
2017
--

Avoiding the “An optimized (without redo logging) DDL operation has been performed” Error with Percona XtraBackup

Percona XtraBackup

Percona XtraBackupThis blog discusses newly added options for Percona XtraBackup 2.4.8 and how they can impact your database backups.

To avoid issues with MySQL 5.7 skipping the redo log for DDL, Percona XtraBackup has implemented three new options (

xtrabackup --lock-ddl

,

xtrabackup --lock-ddl-timeout

,

xtrabackup --lock-ddl-per-table

) that can be used to place MDL locks on tables while they are copied.

So why we need those options? Let’s discuss the process used to get there.

Originally, we found problems while running DDLs: Percona XtraBackup produced corrupted backups as described in two reports:

After experimenting, it was clear that the core cause of those fails was MySQL 5.7 skipping redo logging for some DDLs. This is a newly added feature to MySQL named Sorted Index BuildsYou can read more from following links:

To prevent this we introduced a solution: wWhen Percona XtraBackup detects skipping the redo log), it aborts the backup to prevent creating a corrupted backup.

The scary error message you get with this fix is:

[FATAL] InnoDB: An optimized(without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
Percona XtraBackup will not be able to take a consistent backup. Retry the backup operation

We need to avoid aborting backup with this message. So how do we do that? Let’s create a test case first and reproduce the issue.

Prepare two tables:

sysbench /usr/share/sysbench/oltp_insert.lua --db-driver=mysql --mysql-db=db1 --mysql-user=msandbox --mysql-password=msandbox --table-size=2000000 --mysql-socket=/tmp/mysql_sandbox20393.sock prepare
sysbench /usr/share/sysbench/oltp_insert.lua --db-driver=mysql --mysql-db=db2 --mysql-user=msandbox --mysql-password=msandbox --table-size=2000000 --mysql-socket=/tmp/mysql_sandbox20393.sock prepare

Create a test.sh file and place it in the sandbox:

#!/bin/bash
echo "drop table if exists db1.sb1"|./use
echo "create table sb1 as select id,c from sbtest1 where id < 150000;"|./use db1
echo "create unique index ix on sb1 (id)"|./use db1
sleep 1
echo "drop table if exists db2.sb1"|./use
echo "create table sb1 as select id,c from sbtest1 where id < 150000;"|./use db2
echo "create unique index ix on sb1 (id)"|./use db2

Run the script in a loop while the backup is taken:

$ while true; do bash test.sh; done

Try to take a backup:

xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_18/master/my.sandbox.cnf
--user=msandbox --password='msandbox'  --target-dir=/home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-26_11-11-45
--backup --host=127.0.0.1 --port=20393 --binlog-info=AUTO --galera-info --parallel 4
--check-privileges --no-version-check

You will likely get something like:

InnoDB: An optimized (without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
PXB will not be able take a consistent backup. Retry the backup operation

Ok, now we have reproduced the error. To avoid this error, XtraBackup has the new options as mentioned above.

Using

--lock-ddl

:

xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_18/master/my.sandbox.cnf
--user=msandbox --password='msandbox'  --target-dir=/home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-26_11-16-56
--backup --host=127.0.0.1 --port=20393 --binlog-info=AUTO --galera-info --parallel 4
--check-privileges --no-version-check --lock-ddl

The new thing you should notice is:

170726 11:16:56 Executing LOCK TABLES FOR BACKUP...

And the backup status:

xtrabackup: Transaction log of lsn (2808294311) to (2808304872) was copied.
170726 11:20:42 completed OK!

Another new option is --lock-ddl-per-table:

xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_18/master/my.sandbox.cnf
--user=msandbox --password='msandbox'  --target-dir=/home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-26_11-31-56
--backup --host=127.0.0.1 --port=20393 --binlog-info=AUTO --galera-info --parallel 4 --check-privileges --no-version-check  --lock-ddl-per-table

The new output will look like this:

170726 11:32:33 [01] Copying ./ibdata1 to /home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-26_11-31-56/ibdata1
170726 11:32:33 Locking MDL for db1.sb1
170726 11:32:33 [02] Copying ./db1/sb1.ibd to /home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-26_11-31-56/db1/sb1.ibd
170726 11:32:33 Locking MDL for db1.sbtest1
170726 11:32:33 Locking MDL for db2.sb1
170726 11:32:33 [03] Copying ./db1/sbtest1.ibd to /home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-26_11-31-56/db1/sbtest1.ibd
170726 11:32:33 [04] Copying ./db2/sb1.ibd to /home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-26_11-31-56/db2/sb1.ibd
170726 11:32:33 [04]        ...done
170726 11:32:33 >> log scanned up to (2892754398)
170726 11:32:34 Locking MDL for db2.sbtest1

The result of the backup:

170726 11:35:45 Unlocking MDL for all tables
xtrabackup: Transaction log of lsn (2871333326) to (2892754764) was copied.
170726 11:35:45 completed OK!

The another thing I should add here is about using

--lock-ddl

 with non-Percona Server for MySQL servers. For example., using it with MariaDB:

2017-07-26 12:08:32 ERROR    FULL BACKUP FAILED!
2017-07-26 12:08:37 ERROR    170726 12:08:32 Connecting to MySQL server host: 127.0.0.1, user: msandbox, password: set, port: 10207, socket: /tmp/mysql_sandbox10207.sock
Using server version 10.2.7-MariaDB
170726 12:08:32 Error: LOCK TABLES FOR BACKUP is not supported.

But you can use

--lock-ddl-per-table

 with any server. Use

--lock-ddl-per-table

 with caution, it can block updates to tables for highly loaded servers under some circumstances. Let’s explore one:

Table: CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT);
  Cases:
  connection 1:
  - BEGIN; SELECT * FROM sb1 LIMIT 1; <--- MDL
  connection 2:
  - UPDATE sb1 SET c = '288' WHERE id = 34;    <--- completes OK
  connection 3:
  - CREATE INDEX sb1_1 ON sb1 (c(10));         <--- WAITING for MDL
  connection 2:
  - UPDATE sb1 SET c = '288' WHERE id = 34;    <--- WAITING for MDL
  connection 1:
  - COMMIT;
  connection 2 and 3 are able to complete now

If one connection holds an MDL lock, and another connection does ALTER TABLE (CREATE INDEX is mapped to an ALTER TABLE statement to create indexes), then updates to that table are blocked.

Testing this with the backup process is quite easy:

Sample table:

CREATE TABLE `sb1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  UNIQUE KEY `ix` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select count(*) from sb1;
+----------+
| count(*) |
+----------+
|   149999 |
+----------+
select * from sb1 limit 3;
+----+-------------------------------------------------------------------------------------------------------------------------+
| id | c                                                                                                                       |
+----+-------------------------------------------------------------------------------------------------------------------------+
|  1 | 83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330 |
|  2 | 38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630 |
|  3 | 33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817 |
+----+-------------------------------------------------------------------------------------------------------------------------+

So our “connection 1:” is an 

xtrabackup

 command:

xtrabackup --defaults-file=/home/shahriyar.rzaev/sandboxes/rsandbox_Percona-Server-5_7_18/master/my.sandbox.cnf
--user=msandbox --password='msandbox'  --target-dir=/home/shahriyar.rzaev/backup_dir/ps_5.7_master/full/2017-07-28_07-55-30
--backup --host=127.0.0.1 --port=20393 --binlog-info=AUTO --galera-info --parallel 4 --check-privileges --no-version-check
--lock-ddl-per-table

So after running the backup command and doing the same steps for “connection 2” and “connection 3,” the result is something like this in processlist:

show processlist;
+----+----------+-----------------+------+---------+------+---------------------------------+----------------------------------------+-----------+---------------+
| Id | User     | Host            | db   | Command | Time | State                           | Info                                   | Rows_sent | Rows_examined |
+----+----------+-----------------+------+---------+------+---------------------------------+----------------------------------------+-----------+---------------+
|  4 | root     | localhost       | db1  | Sleep   |   28 |                                 | NULL                                   |         0 |             1 |
| 10 | root     | localhost       | db1  | Query   |   26 | Waiting for table metadata lock | CREATE INDEX sb1_1 ON sb1 (c(10))      |         0 |             0 |
| 11 | root     | localhost       | db1  | Query   |    6 | Waiting for table metadata lock | UPDATE sb1 SET c = '288' WHERE id = 34 |         0 |             0 |
| 12 | root     | localhost       | NULL | Query   |    0 | starting                        | show processlist                       |         0 |             0 |
| 13 | msandbox | localhost:36546 | NULL | Sleep   |   31 |                                 | NULL                                   |         1 |           116 |
| 14 | msandbox | localhost:36550 | NULL | Sleep   |   17 |                                 | NULL                                   |         1 |             1 |
+----+----------+-----------------+------+---------+------+---------------------------------+----------------------------------------+-----------+---------------+
6 rows in set (0.00 sec)

Updates are only completed after the backup, as described. It should be clear now why you should use it with caution.

The last thing we should discuss is if you do not want to use any “hacks” with

xtrabackup

 , you can do things on the MySQL side such as:

  1. Avoiding bad DDLs ?
  2. Enabling old_alter_table. When this variable is enabled, the server does not use the optimized method of processing an ALTER TABLE operation. It reverts to using a temporary table, copying over the data and then renaming the temporary table to the original, as used by MySQL 5.0 and earlier. So it is going to use ALGORITHM=COPY for alters.

In conclusion, we do not have a panacea for this issue but, you can use some of the described tricks to get rid of this problem. Thanks for reading!

Jul
24
2017
--

Percona XtraBackup 2.4.8 is Now Available

Percona XtraBackup

Percona XtraBackup 2.4.8Percona announces the GA release of Percona XtraBackup 2.4.8 on July 24, 2017. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

New features:
Bugs Fixed:
  • xtrabackup would hang with Waiting for master thread to be suspended message when backup was being prepared. Bug fixed #1671437.
  • xtrabackup would fail to prepare the backup with 6th page is not initialized message in case server didn’t properly initialize the page. Bug fixed #1671722.
  • xbstream could run out of file descriptors while extracting the backup which contains many tables. Bug fixed #1690823.
  • When a table was created with the DATA DIRECTORY option xtrabackup would back up the .frm and .isl files, but not the .ibd file. Due to the missing .ibd files backup then could not be restored. Bug fixed #1701736.
  • Percona XtraBackup incorrectly determined use of master_auto_postion on a slave, and thus generated invalid xtrabackup_slave_info file. Bug fixed #1705193.
  • Percona XtraBackup will now print a warning if it encounters unsupported storage engine. Bug fixed #1394493.
  • Percona XtraBackup would crash while backing up MariaDB 10.2.x with --ftwrl-* options. Bug fixed #1704636.
  • xtrabackup --slave-info didn’t write the correct information into xtrabackup_slave_info file when multi-source replication was used. Bug fixed #1551634.
  • Along with xtrabackup_checkpints file, xtrabackup now copies xtrabackup_info file into directory specified by --extra-lsndir option. Bug fixed #1600656.
  • GTID position was not recorded when --binlog-info option was set to AUTO. Bug fixed #1651505.

Release notes with all the bugfixes for Percona XtraBackup 2.4.8 are available in our online documentation. Please report any bugs to the launchpad bug tracker.

Jul
24
2017
--

Percona XtraBackup 2.3.9 is Now Available

Percona XtraBackup

Percona XtraBackup 2.3.9Percona announces the release of Percona XtraBackup 2.3.9 on July 24, 2017. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

This release is the current GA (Generally Available) stable release in the 2.3 series.

New Features
Bugs Fixed:
  • Percona XtraBackup would crash when being prepared if the index compaction was enabled. Bug fixed #1192834.
  • Fixed build failure on Debian Stretch by adding support for building with OpenSSL 1.1. Bug fixed #1678947.
  • xbstream could run out of file descriptors while extracting the backup which contains many tables. Bug fixed #1690823.
  • Percona XtraBackup incorrectly determined use of master_auto_postion on a slave, and thus generated invalid xtrabackup_slave_info file. Bug fixed #1705193.
  • Percona XtraBackup would crash while backing up MariaDB 10.2.x with --ftwrl-* options. Bug fixed #1704636.
  • Along with xtrabackup_checkpints file, xtrabackup now copies xtrabackup_info file into directory specified by --extra-lsndir option. Bug fixed #1600656.
  • GTID position was not recorded when --binlog-info option was set to AUTO. Bug fixed #1651505.

Release notes with all the bugfixes for Percona XtraBackup 2.3.9 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

Jul
18
2017
--

Backups and Disaster Recovery

Backups and Disaster Recovery

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.

Note: I am giving a talk on Backups and Disaster Recovery Best Practices on July 27th.

When discussing disaster recovery, it’s important to take your business’ continuity plan into consideration. Backup and recovery processes are a critical part of any application infrastructure.

A well-tested backup and recovery system can be the difference between a minor outage and the end of your business.

You will want to take three things into consideration when planning your disaster recovery strategy: recovery time objective, recovery point objective and risk mitigation.

Recovery time objective (RTO) is how long it takes to restore your backups. Recovery point objective (RPO) is what point in time you want to recover (in other words, how much data you can afford to lose after recovery). Finally, you need to understand what risks you are trying to mitigate. Risks to your data include (but are not limited to) bad actors, data corruption, user error, host failure and data center failure.

Recommended Backup Strategies

We recommend that you use both physical (Percona XtraBackup, RDS/LVM Snapshots, MySQL Enterprise Backup) and logical backups (mysqldump, mydumper, mysqlpump). Logical backups protect against the loss of single data points, while physical backups protect against total data loss or host failure.

The best practice is running Percona XtraBackup nightly, followed by mysqldump (or in 5.7+, mysqlpump). Percona XtraBackup enables you to quickly restore a server, and mysqldump enables you to quickly restore data points. These address recovery time objectives.

For point-in-time recovery, it is recommended that you download binlogs on a regular basis (once an hour, for example).

Another option is binlog streaming. You can find more information on binlog streaming in our blog: Backing up binary log files with mysqlbinlog.

There is also a whitepaper that is the basis of my webinar here: MySQL Backup and Recovery Best Practices.

Delayed Slave

One way to save on operational overhead is to create a 24-hour delayed slave. This takes the place of the logical backup (mysqldump) as well as the binlog streaming. You want to ensure that you stop the delayed slave immediately following any issues. This ensures that the data does not get corrupted on the backup as well.

A delayed slave is created in 5.6 and above with:

CHANGE MASTER TO MASTER_DELAY = N;

After a disaster, you would issue:

STOP SLAVE;

Then, in order to get a point-in-time, you can use:

START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos;

Restore

It is a good idea to test your backups at least once a quarter. Backups do not exist unless you know you can restore them. There are some recent high-profile cases where developers dropped tables or schemas, or data was corrupted in production, and in one case five different backup types were not viable to use to restore.

The best case scenario is an automated restore test that runs after your backup, and gives you information on how long it takes to restore (RTO) and how much data you can restore (RPO).

For more details on backups and disaster recovery, come to my webinar.

May
30
2017
--

Percona XtraBackup 2.4.7-2 is Now Available

Percona XtraBackup 2.4.7-2

Percona XtraBackup 2.4.7-2Percona announces the GA release of Percona XtraBackup 2.4.7-2 on May 29, 2017. You can download it from our download site and apt and yum repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

Bug Fixed:
  • Fixed build failure on Debian 9.0 (Stretch). Bug fixed #1678947.

Release notes with all the bugfixes for Percona XtraBackup 2.4.7-2 are available in our online documentation. Please report any bugs to the launchpad bug tracker.

May
30
2017
--

Webinar May 31, 2017: Online MySQL Backups with Percona XtraBackup

Percona XtraBackup 2.4.7-2

Percona XtraBackupPlease join Percona’s solution engineer, Dimitri Vanoverbeke as he presents Online MySQL Backups with Percona XtraBackup on Wednesday, May 31, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Percona XtraBackup is a free, open source, complete online backup solution for all versions of Percona Server, MySQL® and MariaDB®. Percona XtraBackup provides:

  • Fast and reliable backups
  • Uninterrupted transaction processing during backups
  • Savings on disk space and network bandwidth with better compression
  • Automatic backup verification
  • Higher uptime due to faster restore time

This webinar will discuss the different features of Percona XtraBackup, including:

  • Full and incremental backups
  • Compression, streaming, and encryption of backups
  • Backing up to the cloud (swift)
  • Percona XtraDB Cluster / Galera Cluster
  • Percona Server specific features
  • MySQL 5.7 support
  • Tools that use Percona XtraBackup
  • Limitations

Register for the webinar here.

DimitriDimitri Vanoverbeke, Solution Engineer

At the age of seven, Dimitri received his first computer. Since then he has felt addicted to anything with a digital pulse. Dimitri has been active in IT professionally since 2003, when he took various roles from internal system engineering to consulting. Prior to joining Percona, Dimitri worked as a consultant for a leading open source software consulting firm in Belgium. During his career, Dimitri has become familiar with a broad range of open source solutions and with the devops philosophy. Whenever he’s not glued to his computer screen, he enjoys traveling, cultural activities, basketball and the great outdoors.

 

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com