Personal blog of Yzmir Ramirez

Please join Percona Senior Support Engineer Juan Pablo Arruti as he presents his talk “Percona XtraBackup vs Mariabackup vs MySQL Enterprise Backup” on Tuesday, September 17th, 2019 at 10:00 AM PDT (UTC-7) / 1:00 PM EDT (UTC-4).

Register Now

There are few ways to take a backup. Some of the most used tools are Percona Xtrabackup, MariaBackup, and MySQL Enterprise Backup.

In this talk, the audience will get an in-depth overview of:

– Differences between the tools
– Comparison of features
– Which tool works on which MySQL/MariaDB flavor
– Supported Storage Engines
– Limitations

If you can’t attend, sign up anyways we’ll send you the slides and recording afterward.

Percona 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.

Percona announces the release of Percona XtraBackup 2.3.8 on April 18, 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

• Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
• Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
• The xbstream binary now supports parallel extraction with the --parallel option.
• The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.

Bugs Fixed:

• xtrabackup would not create fresh InnoDB redo logs when preparing incremental backup. Bug fixed #1669592.
• xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
• Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.

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

In this blog post, we’ll look at the performance of SST data transfer using encryption.

In my previous post, we reviewed SST data transfer in an unsecured environment. Now let’s take a closer look at a setup with encrypted network connections between the donor and joiner nodes.

The base setup is the same as the previous time:

• Database server: Percona XtraDB Cluster 5.7 on donor node
• Database: sysbench database – 100 tables 4M rows each (total ~122GB)
• Network: donor/joiner hosts are connected with dedicated 10Gbit LAN
• Hardware: donor/joiner hosts – boxes with 28 Cores+HT/RAM 256GB/Samsung SSD 850/Ubuntu 16.04

The setup details for the encryption aspects in our testing:

• Cryptography libraries: openssl-1.0.2, openssl-1.1.0, libgcrypt-1.6.5(for xbstream encryption)
• CPU hardware acceleration for AES – AES-NI: enabled/disabled
• Ciphers suites: aes(default), aes128, aes256, chacha20(openssl-1.1.0)

Several notes regarding the above aspects:

• Cryptography libraries. Now almost every Linux distribution is based on the openssl-1.0.2. This is the previous stable version of the OpenSSL library. The latest stable version (1.1.0) has various performance/scalability fixes and also support of new ciphers that may notably improve throughput, However, it’s problematic to upgrade from 1.0.2 to 1.1.0, or just to find packages for openssl-1.1.0 for existing distributions. This is due to the fact that replacing OpenSSL triggers update/upgrade of a significant number of packages. So in order to use openssl-1.1.0, most likely you will need to build it from sources. The same applies to socat – it will require some effort to build socat with openssl-1.1.0.
• AES-NI. The Advanced Encryption Standard Instruction Set (AES-NI) is an extension to the x86 CPU’s from Intel and AMD. The purpose of AES-NI is to improve the performance of encryption and decryption operations using the Advanced Encryption Standard (AES), like the AES128/AES256 ciphers. If your CPU supports AES-NI, there should be an option in BIOS that allows you to enabled/disable that feature. In Linux, you can check /proc/cpuinfo for the existence of an “aes” flag. If it’s present, then AES-NI is available and exposed to the OS.There is a way to check what acceleration ratio you can expect from it:

  # AES_NI disabled with OPENSSL_ia32cap
  OPENSSL_ia32cap="~0x200000200000000" openssl speed -elapsed -evp aes-128-gcm
  ...
  The 'numbers' are in 1000s of bytes per second processed.
  type             16 bytes     64 bytes    256 bytes   1024 bytes   8192 bytes
  aes-128-gcm      57535.13k    65924.18k   164094.81k   175759.36k   178757.63k
  # AES_NI enabled
  openssl speed -elapsed -evp aes-128-gcm
  The 'numbers' are in 1000s of bytes per second processed.
  type             16 bytes     64 bytes    256 bytes   1024 bytes   8192 bytes
  aes-128-gcm     254276.67k   620945.00k   826301.78k   906044.07k   923740.84k

  Our interest is the very last column: 178MB/s(wo AES-NI) vs 923MB/s(w AES-NI)

• Ciphers. In our testing for network encryption with socat+openssl 1.0.2/1.1.0, we used the following ciphers suites:
  DEFAULT – if you don’t specify a cipher/cipher string for OpenSSL connection, this suite will be used
  AES128 – suite with aes128 ciphers only
  AES256 – suites with aes256 ciphers onlyAdditionally, for openssl-1.1.0, there is an extra cipher suite:
  CHACHA20 – cipher suites using ChaCha20 algoIn the case of xtrabackup, where internal encryption is based on libgcrypt, we use the AES128/AES256 ciphers from this library.
• SST methods. Streaming database files from the the donor to joiner with the rsync protocol over an OpenSSL-encrypted connection:

  (donor) rsync | socat+ssl       socat+ssl| rsync(daemon mode) (joiner)

  The current approach of wsrep_sst_rsync.sh doesn’t allow you to use the rsync SST method with SSL. However, there is a project that tries to address the lack of SSL support for rsync method. The idea is to create a secure connection with socat and then use that connection as a tunnel to connect rsync between the joiner and donor hosts. In my testing, I used a similar approach.

  Also take a note that in the chart below, there are results for two variants of rsync: “rsync” (the current approach), and “rsync_improved” (the improved one). I’ve explained the difference between them in my previous post.

• Backup data on the donor side and stream it to the joiner in xbstream format over an OpenSSL encrypted connection

  (donor) xtrabackup| socat+ssl  socat+ssl | xbstream (joiner)

  In my testing for streaming over encrypted connections, I used the

  --parallel=4

   option for xtrabackup. In my previous post, I showed that this is important factor to get the best time. There is also a way to pass the name of the cipher that will be used by socat for the OpenSSL connection in the wsrep_sst_xtrabackup-v2.sh script with the

  sockopt

   option. For instance:

  [sst]
  inno-backup-opts="--parallel=4"
  sockopt=",cipher=AES128"

• Backup data on the donor side/encrypt it internally(with libgcrypt) and stream the data to the joiner in xbstream format, and afterwards decrypt files on the joiner

  (donor) xtrabackup | socat   socat | xbstream ; xtrabackup decrypt (joiner)

  The xtrabackup tool has a feature to encrypt data when performing a backup. That encryption is based on the libgcrypt library, and it’s possible to use AES128 or AES256 ciphers. For encryption, it’s necessary to generate a key and then provide it to xtrabackup to perform encryption on fly. There is a way to specify the number of threads that will encrypt data, along with the chunk size to tune process of encryption.

  The current version of xtrabackup supports an efficient way to read, compress and encrypt data in parallel, and then write/stream it. From the other side, when we accept a stream we can’t decompress/decrypt stream on the fly. At first, the stream should be received/written to disk with the xbstream tool and only after that can you use xtrabackup with

  --decrypt/--decompress

   modes to unpack data. The inability to process data on the fly and save the stream to disk for later processing has a notable impact on stream time from the donor to the joiner. We have a plan to fix that issue, so that encryption+compression+streaming of data with xtrabackup happens without the necessity to write stream to the disk on the receiver side.

  For my testing, in the case of xtrabackup with internal encryption, I didn’t use SSL encryption for socat.

Results (click on the image for an enlarged view):

Observations:

• Transferring data with rsync is very inefficient, and the improved version is 2-2.5 times faster. Also, you may note that in the case of “no-aes-n”, the rsync_improved method has the best time for default/aes128/aes256 ciphers. The reason is that we perform both data transfers in parallel (we spawn rsync process for each file), as well as encryption/decryption (socat forks extra processes for each stream). This approach allows us to compensate for the absence of hardware acceleration by using several CPU cores. In all other cases, we only use one CPU for streaming of data and encryption/decryption.
• xtrabackup (with hardware optimized crc32) shows the best time in all cases, except for the default/aes128/aes256 ciphers in “no-aes-ni” mode (where rsync_imporved showed the best time). However I would like to remind you that SST with rsync is a blocking operation, and during the data transfer the donor node becomes READ-ONLY. xtrabackup, on the other hand, uses backup locks and allows any operations on donor node during SST.
• On the boxes without hardware acceleration (no-aes-ni mode), the chacha20 cipher allows you to perform data transfer 2-3 times faster. It’s a very good replacement for “aes” ciphers on such boxes. However, the problem with that cipher is that it is available only in openssl-1.1.0. In order to use it, you will need a custom build of OpenSSL and socat for many distros.
• Regarding xtrabackup with internal encryption (xtrabackup_enc): reading/encrypting and streaming data is quite fast, especially with the latest libgcrypt library(1.7.x). The problem is decryption. As I’ve explained above, right now we need to get the stream and save encrypted data to storage first, and then perform the extra step of reading/decrypting and saving the data back. That extra part consumes 2/3 of the total time. Improving the xbstream tool to perform steam decryption/decompression on the fly would allow you to get very good results.

Testing Details

For purposes of the testing, I’ve created a script “sst-bench.sh” that covers all the methods used in this post. You can use it to measure all the above SST methods in your environment. In order to run the script, you have to adjust several environment variables at the beginning of the script:

joiner ip

, datadirs location on joiner and donor hosts, etc. After that, put the script on the “donor” and “joiner” hosts and run it as the following:

#joiner_host>
sst_bench.sh --mode=joiner --sst-mode=<tar|xbackup|rsync> --cipher=<DEFAULT|AES128|AES256|CHACHA20> --ssl=<0|1> --aesni=<0|1>
#donor_host>
sst_bench.sh --mode=donor --sst-mode=<tar|xbackup|rsync|rsync_improved> --cipher=<DEFAULT|AES128|AES256|CHACHA20> --ssl=<0|1> --aesni=<0|1>

Percona announces the GA release of Percona XtraBackup 2.4.6 on February 22, 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:

• Percona XtraBackup implemented a new --remove-original option that can be used to remove the encrypted and compressed files once they’ve been decrypted/decompressed.

Bugs Fixed:

• XtraBackup was using username set for the server in a configuration file even if a different user was defined in the user’s configuration file. Bug fixed #1551706.
• Incremental backups did not include xtrabackup_binlog_info and xtrabackup_galera_info files. Bug fixed #1643803.
• In case a warning was written to stout instead of stderr during the streaming backup, it could cause assertion in the xbstream. Bug fixed #1647340.
• xtrabackup --move-back option did not always restore out-of-datadir tablespaces to their original directories. Bug fixed #1648322.
• innobackupex and xtrabackup scripts were showing the password in the ps output when it was passed as a command line argument. Bug fixed #907280.
• Incremental backup would fail with a path like ~/backup/inc_1 because xtrabackup didn’t properly expand tilde. Bug fixed #1642826.
• Fixed missing dependency check for Perl Digest::MD5 in rpm packages. This will now require perl-MD5 package to be installed from EPEL repositories on CentOS 5 and CentOS 6 (along with libev). Bug fixed #1644018.
• Percona XtraBackup now supports -H, -h, -u and -p shortcuts for --hostname, --datadir, --user and --password respectively. Bugs fixed #1655438 and #1652044.

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

Percona announces the release of Percona XtraBackup 2.3.7 on February 22, 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

• Percona XtraBackup has implemented a new --remove-original option that can be used to remove the encrypted and compressed files once they’ve been decrypted/decompressed.

Bugs Fixed:

• XtraBackup was using username set for the server in a configuration file even if a different user was defined in the user’s configuration file. Bug fixed #1551706.
• Incremental backups did not include xtrabackup_binlog_info and xtrabackup_galera_info files. Bug fixed #1643803.
• Percona XtraBackup would fail to compile with -DWITH_DEBUG and -DWITH_SSL=system options. Bug fixed #1647551.
• xtrabackup --move-back option did not always restore out-of-datadir tablespaces to their original directories. Bug fixed #1648322.
• innobackupex and xtrabackup scripts were showing the password in the ps output when it was passed as a command line argument. Bug fixed #907280.
• Incremental backup would fail with a path like ~/backup/inc_1 because xtrabackup didn’t properly expand tilde. Bug fixed #1642826.
• Fixed missing dependency check for Perl Digest::MD5 in rpm packages. This will now require perl-MD5 package to be installed from EPEL repositories on CentOS 5 and CentOS 6 (along with libev). Bug fixed #1644018.
• Percona XtraBackup now supports -H, -h, -u and -p shortcuts for --hostname, --datadir, --user and --password respectively. Bugs fixed #1655438 and #1652044.

Other bugs fixed: #1655278.

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

This blog was originally published in February 2017 and was updated in August 2023.

In this blog post, I provide an in-depth introduction to MySQL Replication, answering what it is, how it works, its benefits and challenges, as well as reviewing some of the MySQL replication concepts that are part of the MySQL environment (and Percona Server for MySQL specifically). I will finish by also clarifying some of the common misconceptions people have about replication and how Percona can help.

Since I’ve been working on the Solution Engineering team, I’ve noticed that – although information is plentiful – replication is often misunderstood or incompletely understood.

What is MySQL Replication?

MySQL replication is a process in which data from a primary MySQL database is copied and sent to one or more secondary databases, known as replicas.

Replication guarantees information gets copied and purposely populated into another environment instead of only stored in one location (based on the transactions of the source environment).

The idea is to use secondary servers on your infrastructure for either reads or other administrative solutions. The below diagram shows an example of a MySQL replication environment.

 

Requirements for MySQL Replication Setup

In this section, we will cover the essential aspects of implementing MySQL replication:

Prerequisites and Requirements:

• Primary-Replica Configuration: Ensure you have a primary MySQL database and one or more replica MySQL databases.
• Network Connectivity: Establish a reliable network connection between the primary and replica servers.
• MySQL Version: Make sure that the MySQL versions on the primary and replica servers are compatible for replication.

Setting Up MySQL Replication – Step by Step:

• Backup Data: Before starting, create a backup of your primary database to prevent data loss during setup.
• Configure Primary Server:
  • Edit the MySQL configuration file on the primary server (my.cnf or my.ini) to enable binary logging.
  • Restart the MySQL service.
• Create a Replication User:
  • Log in to MySQL on the primary server and create a dedicated replication user with the necessary privileges.
• Record the Binary Log Coordinates:
  • Note down the current binary log file and position on the primary server.
• Configure Replica Server:
  • On each replica server, edit the MySQL configuration file to configure it as a replica.
  • Set the server-id to a unique value.
  • Specify the primary server’s hostname or IP address.
  • Restart the MySQL service on each replica.
• Initialize Replication:
  • On one of the replica servers, run the CHANGE MASTER TO command, specifying the primary server’s binary log file and position.
  • Start the replica server’s replication process.
• Verify Replication:
  • Check the replica’s status using SHOW SLAVE STATUS. Ensure that both “Slave_IO_Running” and “Slave_SQL_Running” show “Yes.”
• Add More Replicas (Optional):
  • If needed, repeat the configuration steps on additional replica servers.
• Monitoring and Maintenance:
  • Regularly monitor replication status and logs.
  • Perform routine maintenance and backups to ensure data integrity.
• Scaling and Load Balancing (Optional):
  • Implement load balancing and failover mechanisms if you have multiple replicas.

What are the potential advantages and disadvantages of MySQL Replication?

MySQL replication provides high availability, load balancing, and data redundancy, enhancing system reliability. It also enables geographically distributed databases for disaster recovery. However, it’s important to consider potential downsides, including increased complexity in managing replicas, the potential for replication lag, and the need for monitoring and maintenance to ensure consistency and reliability across replicas.

Advantages

There are many advantages of MySQL replication, but a few highlights include the enhancement of scalability by allowing read-intensive workloads to be distributed across multiple replicas, offloading a primary database server, and improving overall performance. Replication also provides for data availability by enabling failover to a replica in case the primary server becomes unavailable.

Lastly, in the event of a catastrophic scenario, data and databases can be quickly recovered, as replication provides for geographically dispersed duplicates of data.

Disadvantages

Despite all the benefits, there are some potential drawbacks associated with MySQL replication one can face. One very common issue is data consistency, especially in setups with high write activity. Replicas may lag behind the primary server, impacting any applications that depend on real-time data.

Another concern is the risk of single points of failure. If the primary server experiences a failure, the entire replication process could be disrupted. Implementing the failover mechanisms discussed earlier can mitigate this risk.

Regarding security, data transmitted between the primary and replica servers may be vulnerable if encryption and access controls are not correctly configured.

What are the Different Types of MySQL Replication?

You actually have several different choices:

Standard asynchronous replication

Asynchronous replication means that the transaction is completed in the local environment completely and is not influenced by the replicas themselves.

After completion of its changes, the primary populates the binary log with the data modification or the actual statement (the difference between row-based replication or statement-based replication – more on this later). This dump thread reads the binary log and sends it to the replica IO thread. The replica places it in its own preprocessing queue (called a relay log) using its IO thread.

The replica executes each change on the replica’s database using the SQL thread.

 

Semi-synchronous replication

Semi-synchronous replication means that the replica and the primary communicate with each other to guarantee the correct transfer of the transaction. The primary only populates the binlog and continues its session if one of the replicas provides confirmation that the transaction was properly placed in one of the replica’s relay logs.

Semi-synchronous replication guarantees that a transaction is correctly copied, but it does not guarantee that the commit on the replica actually takes place.

Important to note is that semi-sync replication makes sure that the primary waits to continue processing transactions in a specific session until at least one of the replicas has ACKed the reception of the transaction (or reaches a timeout). This differs from asynchronous replication, as semi-sync allows for additional data integrity.

Remember that semi-synchronous replication impacts performance because it needs to wait for the round trip of the actual ACK from the replica.

Group Replication

This new concept was introduced in the MySQL Community Edition 5.7 and was GA’ed in MySQL 5.7.17. It’s a rather new plugin build for virtual synchronous replication.

Whenever a transaction is executed on a node, the plugin tries to get consensus with the other nodes before returning it completed back to the client. Although the solution is a completely different concept compared to standard MySQL replication, it is based on the generation and handling of log events using the binlog.

Below is an example architecture for Group Replication.

Percona XtraDB Cluster / Galera Cluster

Another solution that allows you to replicate information to other nodes is Percona XtraDB Cluster. This solution focuses on delivering consistency and also uses a certification process to guarantee that transactions avoid conflicts and are performed correctly.

In this case, we are talking about a clustered solution. Each environment is subject to the same data, and there is communication between nodes to guarantee consistency.

Percona XtraDB Cluster has multiple components:

• Percona Server for MySQL
• Percona XtraBackup for performing snapshots of the running cluster (if recovering or adding a node).
• wsrep patches / Galera Library

This solution is virtually synchronous, which is comparable to Group Replication. However, it also has the capability to use multi-primary replication. Solutions like Percona XtraDB Cluster are a component to improve the availability of your database infrastructure.

Learn how to optimize your database setup for high availability with our ebook, “Percona Distribution for PostgreSQL: High Availability With Streaming Replication.” Read it here!

Row-Based Replication Vs. Statement-Based Replication

Statement-based replication

With statement-based replication, the SQL query itself is written to the binary log. For example, the exact same INSERT/UPDATE/DELETE statements are executed by the replica.

There are many advantages and disadvantages to this system:

• Auditing the database is much easier as the actual statements are logged in the binary log
• Less data is transferred over the wire
• Non-deterministic queries can create actual havoc in the replica environment
• There might be a performance disadvantage, with some queries using statement-based replication (INSERT based on SELECT)
• Statement-based replication is slower due to SQL optimizing and execution

Row-based replication

Row-based replication is the default choice since MySQL 5.7.7, and it has many advantages. The row changes are logged in the binary log, and it does not require context information. This removes the impact of non-deterministic queries.

Some additional advantages are:

• Performance improvements with high concurrency queries containing few row changes
• Significant data-consistency improvement

And, of course, some disadvantages:

• Network traffic can be significantly larger if you have queries that modify a large number of rows
• It’s more difficult to audit the changes on the database
• Row-based replication can be slower than statement-based replication in some cases

Handling Failures and Ensuring High Availability

Ensuring high availability in MySQL replication is important for uninterrupted database access, and several strategies can be used to achieve this goal. One approach is the implementation of failover mechanisms. Failover ensures that if the primary MySQL server becomes unavailable due to hardware failure or another issue, the system seamlessly switches to a standby replica. Setting up failover mechanisms can be accomplished using load balancers or proxy servers that continuously monitor the health of the primary server. When a problem is detected, these tools automatically redirect traffic to a standby replica.

Semi-synchronous replication is another technique that plays a vital role in ensuring data consistency in high-availability MySQL setups. This approach requires acknowledgment from at least one replica before a transaction is considered committed on the primary server, ensuring that data changes are safely replicated to at least one replica before being confirmed on the primary server. This reduces the risk of data loss in the event of a primary server failure. By prioritizing data consistency over raw performance, semi-synchronous replication provides an added layer of protection against data discrepancies, making it a valuable feature in high-availability configurations where maintaining data integrity is critical.

Discover high-availability strategies in our eBook, “Percona Distribution for MySQL: High Availability With Group Replication.” Read it now!

Answering Common Misconceptions About Replication

1. Replication is a cluster.

Standard asynchronous replication is not a synchronous cluster. Keep in mind that standard and semi-synchronous replication do not guarantee that the environments are serving the same dataset. This is different when using Percona XtraDB Cluster, where every server actually needs to process each change. If not, the impacted node is removed from the cluster. Asynchronous replication does not have this fail safe. It still accepts reads while in an inconsistent state.

2. Replication sounds perfect, I can use this as a manual failover solution.

Theoretically, the environments should be comparable. However, there are many parameters influencing the efficiency and consistency of the data transfer. As long as you use asynchronous replication, there is no guarantee that the transaction correctly took place. You can circumvent this by enhancing the durability of the configuration, but this comes at a performance cost. You can verify the consistency of your primary and replicas using the
pt–table–checksum tool.

3. I have replication, so I actually don’t need backups.

Replication is a great solution for having an accessible copy of the dataset (e.g., reporting issues, read queries, generating backups). This is not a backup solution, however. Having an offsite backup provides you with the certainty that you can rebuild your environment in the case of any major disasters, user error or other reasons (remember the Bobby Tables comic). Some people use delayed replicas. However, even delayed replicas are not a replacement for proper disaster recovery procedures.

4. I have replication, so the environment will now load balance the transactions.

Although you’ve potentially improved the availability of your environment by having a secondary instance running with the same dataset, you still might need to point the read queries towards the replicas and the write queries to the primary. You can use proxy tools, or define this functionality in your own application.

5. Replication will slow down my primary significantly.

Replication has only minor performance impacts on your primary. Peter Zaitsev has an interesting post on this here, which discusses the potential impact of replicas on the primary. Keep in mind that writing to the binary log can potentially impact performance, especially if you have a lot of small transactions that are then dumped and received by multiple replicas.

There are, of course, many other parameters that might impact the performance of the actual primary and replica setup.

See MySQL Replication in action

To ensure customer satisfaction and meet the demands of applications requiring high availability (HA) and extensive usage, database architecture and deployment must be carefully considered. This often involves achieving remarkable uptime levels, such as the coveted “five nines” (99.999%) availability. 

To delve deeper into this topic and explore Percona’s recommendations for HA architecture and deployment, we invite you to download our white paper. Inside, you’ll find a technical overview of a solution designed to offer exceptional availability, particularly tailored for scenarios involving high read and write applications.

 

Percona Distribution for MySQL: High Availability With Group Replication

Frequently Asked Questions

What is MySQL replication?

MySQL replication is a process where data from a primary MySQL database is duplicated and sent to one or more secondary databases called replicas.

How does MySQL replication work?

MySQL replication works by recording changes to the data on the primary server’s binary log and then replaying those changes on replica servers to keep them in sync.

Why is MySQL replication used?

MySQL replication is used for various purposes, including improving database performance, providing data redundancy for disaster recovery, and distributing database load for scaling.

Are there different types of MySQL replication?

There are different types of MySQL replication, including asynchronous and synchronous replication, statement-based and row-based replication, and group replication for high availability.

Can I replicate between different MySQL versions?

In general, it’s best to replicate between the same or compatible MySQL versions to ensure compatibility and consistency. However, some limited cross-version replication may be possible with certain configurations and precautions.

This blog post is a summary of the webinar Percona Software News and Roadmap Update – Q1 2017 given by Peter Zaitsev on January 12, 2017.

Over the last few months, I’ve had the opportunity to meet and talk with many of Percona’s customers. I love these meetings, and I always get a bunch of questions about what we’re doing, what our plans are and what releases are coming.

I’m pleased to say there is a great deal going on at Percona, and I thought giving a quick talk about our current software and services, along with our plans, would provide a simple reference for many of these questions.

A full recording of this webinar, along with the presentation slide deck, can be found here.

Percona Solutions and Services

Let me start by laying out Percona’s company purpose:

To champion unbiased open source database solutions.

What does this mean? It means that we write software to offer you better solutions, and we use the best of what software and technology exist in the open source community.

Percona stands by a set of principles that we feel define us as a company, and are a promise to our customers:

• 100% free and open source software
• Focused on finding solution to maximize your success
• Open source database strategy consulting and implementation
• No vendor lock-in required

We offer trusted and unbiased expert solutions, support and resource in a broad software ecosystem, including:

• MySQL
• Percona Server
• MariaDB
• Percona XtraDB Cluster
• Galera Cluster for MySQL
• MariaDB Galera Cluster
• MongoDB
• Percona Server for MongoDB
• Amazon RDS for MySQL/MariaDB/Aurora
• Google CloudSQL

We also have specialization options for PaaS, IaaS, and SaaS solutions like Amazon Web Services, OpenStack, Google Cloud Platform, OpenShift, Ceph, Docker and Kubernetes.

Percona’s immediate business focus includes building long-term partnership relationships through support and managed services.

The next few sections detail our current service offerings, with some outlook on our plans.

98% Customer Satisfaction Rating

Over the last six months, Percona has consistently maintained a 98% Customer Satisfaction Rating!

Customer Success Team

Our expanded Customer Success Team is here to ensure you’re getting most out of your Percona Services Subscription.

Managed Services Best Practices

• Unification based on best practices
• Organization changes to offer more personal service
• Increased automation

Ongoing Services

Consulting and Training. Our consulting and training services are available to assist you with whatever project or staff needs you have.

• Onsite and remote
• 4 hours to full time (weeks or months)
• Project and staff augmentation

Advanced HA Included with Enterprise and Premier Support. Starting this past Spring, we included advance high availability (HA) support as part of our Enterprise and Premier support tiers. This advanced support includes coverage for:

• Percona XtraDB Cluster
• MariaDB Galera Cluster
• Galera Cluster for MySQL
• Upcoming MySQL group replication
• Upcoming MySQL Innodb Cluster

Enterprise Wide Support Agreements. Our new Enterprise Wide Support option allows you to buy per-environment support coverage that covers all of the servers in your environment, rather than on a per-server basis. This method of support can save you money, because it:

• Covers both “MySQL” and “MongoDB”
• Means you don’t have to count servers
• Provides highly customized coverage

 

Simplified Support Pricing. Get easy to understand support pricing quickly.

To discuss how Percona Support can help your business, please call us at +1-888-316-9775 (USA),
+44 203 608 6727 (Europe), or have us contact you.

New Percona Online Store – Easy to Buy, Pay Monthly

• Purchase some of Percona’s services online
• Fast and easy credit card payments
• Recurring monthly payments
• https://www.percona.com/store

Percona Software

Below are the latest and upcoming features in Percona’s software. All of Percona’s software adheres to the following principles:

• 100% free and open source
• No restricted “Enterprise” version
• No “open core”
• No BS-licensing (BSL)

Percona Server for MySQL 5.7

Overview

• 100% Compatible with MySQL 5.7 Community Edition
• 100% Free and Open Source
• Includes Alternatives to Many MySQL Enterprise Features
• Includes TokuDB Storage Engine
• Focus on Performance and Operational Visibility

Latest Improvements

• Improved space allocation for TokuDB:
• Directory per database layout for TokuDB:
• Column compression (5.6 only)
• Security updates

Features about to be Released 

• Integration of TokuDB and Performance Schema
• MyRocks integration in Percona Server
• MySQL Group Replication
• Starting to look towards MySQL 8

Percona XtraBackup 2.4

Overview

• #1 open source binary hot backup solution for MySQL
• Alternative to MySQL Enterprise backup
• Parallel backups, incremental backups, streaming, encryption
• Supports MySQL, MariaDB, Percona Server

New Features

• Support SHA256 passwords and secure connection to server
• Improved Security (CVE-2016-6225)
• Wrong passphrase detection

Percona Toolkit

Overview

• “Swiss Army Knife” of tools
• Helps DBAs be more efficient
• Helps DBAs make fewer mistakes
• Supports MySQL, MariaDB, Percona Server, Amazon RDS MySQL

New Features

• Improved fingerprinting in pt-query-digest
• Pause file for pt-online-schema-change
• Provide information about transparent huge pages

Coming Soon

• Working towards Percona Toolkit 3.0 release
• Comprehensive support for MongoDB
• New tools are now implemented in Go

Percona Server for MongoDB 3.2

Overview

• 100% compatible with MongoDB 3.2 Community Edition
• 100% open source
• Alternatives for many MongoDB Enterprise features
• MongoRocks (RocksDB) storage engine
• Percona Memory Engine

New

• Percona Server for MongoDB 3.2 – GA
• Support for MongoRocks storage engine
• PerconaFT storage engine depreciated
• Implemented Percona Memory Engine

Coming Soon

• Percona Server for MongoDB 3.4
• Fully compatible with MongoDB 3.4 Community Edition
• Updated RocksDB storage engine
• Universal hot backup for WiredTiger and MongoRocks
• Profiling rate limiting (query sampling)

Percona Memory Engine for MongoDB

Benchmarks

Percona Memory Engine for MongoDB® is a 100 percent open source in-memory storage engine for Percona Server for MongoDB.

Based on the in-memory storage engine used in MongoDB Enterprise Edition, WiredTiger, Percona Memory Engine for MongoDB delivers extremely high performance and reduced costs for a variety of use cases, including application cache, sophisticated data manipulation, session management and more.

Below are some benchmarks that we ran to demonstrate Percona Memory Engine’s performance.

Percona XtraDB Cluster 5.7

Overview

• Based on Percona Server 5.7
• Easiest way to bring HA in your MySQL environment
• Designed to work well in the cloud
• Multi-master replication with no conflicts
• Automatic node provisioning for auto-scaling and self-healing

Goals

• Brought PXC development in-house to server our customers better
• Provide complete clustering solution, not set of LEGO pieces
• Improve usability and ease of use
• Focus on quality

Highlights

• Integrated cluster-aware load balancer with ProxySQL
• Instrumentation with Performance Schema
• Support for data at rest encryption (InnoDB tablespace encryption)
• Your data is safe by default with “strict mode” – prevents using features that do not work correctly
• Integration with Percona Monitoring and Management (PMM)

New in Percona XtraDB Cluster 5.7

• One option to secure all network communication: pxc-encrypt-cluster-traffic
• Zero downtime maintenance with ProxySQL and Maintenance Mode

Percona Monitoring and Management

Overview

• Comprehensive database-focused monitoring
• 100% open source, roll-your-own solution
• Easy to install and use
• Supports MySQL and MongoDB
• Version 1.0 focuses on trending and query analyses
• Management features to come

Examples of PMM Screens

What queries are causing the load?

Why are they causing this load?

How to fix them:

System information:

What happens on OS and hardware level:

As well as the database level:

New in Percona Monitoring and Management

• Continuing to improve and expand dashboards with every release
• Includes Grafana 4.0 (with basic Alerting)
• SSL support for server-agent communications
• Supports authentication for server-agent communication
• Added support for Amazon RDS
• Reduced space consumption by using advanced compression

Coming Soon 

• PMM server available as AMI and Virtual Appliance image
• Better MongoDB dashboards
• Continuing work on dashboards Improvement
• Query analytics application refinements
• Short term continuing focus on monitoring functionality

Check out the Demo

• http://pmmdemo.percona.com

Percona Live Open Source Database Conference 2017 is right around the corner!

The Percona Live Open Source Database Conference is the premier event for the diverse and active open source database community, as well as businesses that develop and use open source database software. The conferences have a technical focus with an emphasis on the core topics of MySQL, MongoDB, PostgreSQL and other open source databases. Tackling subjects such as analytics, architecture and design, security, operations, scalability and performance, Percona Live provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. This conference is an opportunity to network with peers and technology professionals by bringing together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience – all to help you learn how to tackle your open source database challenges in a whole new way

This conference has something for everyone!

• April 24-27, 2017
• Santa Clara, CA, USA
• MySQL, MongoDB, PostgreSQL, other open source databases
• https://www.percona.com/live/17/

Register now and get the early bird rate, but hurry prices go up Jan 31st.

Sponsorship opportunities are available as well. Become a Percona Live Sponsor, download the prospectus here.

 

In this blog post, we’ll look at how to replace MySQL with Percona Server for MySQL on a CPanel, WHM VPS or dedicated server.

In general, CPanel and WHM have been leaning towards support of MariaDB over other flavors. This is partly due to the upstream repos replacing the MySQL package with MariaDB (for example, on CentOS).

MySQL 5.6 is still supported though, which means they are keeping support for core MySQL products. But if you want to get some extra performance enhancements or enterprise features for free, without getting too many bells and whistles, you might want to install Percona Server.

I’ve done this work on a new dedicated server with the latest WHM and CPanel on CentOS 7, with MySQL 5.6 installed. Besides the backup, this is a fairly quick process.

It’s pretty simple. From the Percona Server for MySQL 5.7 installation doc, we can get the YUM repo. (Run commands as root if you can, otherwise as sudo.)

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Now that we have the repo, let’s install Percona XtraBackup in case we need to roll this back at any point:

yum install percona-xtrabackup

This server had a drive mounted at /backup, so I created the backup with the following commands:

xtrabackup --target-dir=/backup/xtrabackup --backup
xtrabackup --target-dir=/backup/xtrabackup --prepare

Now that we have a good backup, let’s remove MySQL:

service mysql stop
yum remove MySQL* mysql*

Depending on your dependency chain, this could remove Percona XtraBackup, but that can be fixed. Let’s accept this uninstall.

Let’s install Percona Server for MySQL 5.7 and Percona Toolkit:

yum install Percona-Server-server-57 percona-toolkit percona-xtrabackup

Now that it’s installed ensure the mysql service is running. If it isn’t, start it. Now let’s upgrade:

mysql_upgrade

NOTE. This works if you can log in as root without a password; if you can’t, you will need to specify the

-u

 and

-p

 flags.

Once you run the upgrade, restart the mysql service:

service mysql restart

And there you go, you are now running on Percona Server for MySQL 5.7. If your managed providers tell you it’s not supported, don’t worry! It works as long as CPanel supports MySQL 5.6.

If you have any issues, just restore the backup.

NOTE: One thing to keep in mind is that 5.7 breaks CPanel’s ability to create users in MySQL. I believe this is due to the changes to the mysql.user table. If this is an issue for you, you can always use Percona Server for MySQL 5.6.

Percona announces the release of Percona XtraBackup 2.3.6 on November 29, 2016. 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

• Percona XtraBackup now supports SHA256 passwords. Using the SHA256 algorithm requires either SSL encrypted connection, or using public key encryption for password exchange which is only available when both client and server are linked with OpenSSL.
• Percona XtraBackup now supports Command Options for Secure Connections.
• NOTE: Due to xbcrypt format changes, backups encrypted with this Percona XtraBackup version will not be recoverable by older versions.

Bugs Fixed:

• Fixed intermittent assertion failures that were happening when Percona XtraBackup couldn’t correctly identify server version. Bug fixed #1568009.
• Safe slave backup algorithm performed too short delays between retries which could cause backups to fail on a busy servers. Bug fixed #1624473.
• Fixed new compilation warnings with GCC 6. Bug fixed #1641612.
• xbcrypt was not setting the Initialization Vector (IV) correctly (and thus is was not using an IV). This was causing the same ciphertext to be generated across different runs (for the same message/same key). The IV provides the extra randomness to ensure that the same ciphertext is not generated across runs. Bug fixed #1643949.
• Backup would still succeed even if XtraBackup would fail to write the metadata. Bug fixed #1623210.
• xbcloud now supports EMC ECS Swift API Authorization requests. Bugs fixed #1638017 and #1638020 (Txomin Barturen).
• Percona XtraBackup would fail to backup MariaDB 10.2 with the unsupported server version error message. Bug fixed #1602842.

Other bugs fixed: #1639764 and #1639767.

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