Jul
19
2022
--

Percona XtraBackup 8.0.29 and INSTANT ADD/DROP Columns

Percona XtraBackup 8.0.29 and INSTANT ADD:DROP Columns

Percona XtraBackup 8.0.29 and INSTANT ADD:DROP ColumnsOracle’s MySQL 8.0.29 release extended the support for ALTER TABLE … ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server. This new redo log format introduced a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

The corruption happens when InnoDB crash recovery takes place. InnoDB applies redo logs at startup. Percona XtraBackup copies the redo log during backup and applies it as part of the –prepare step to bring the backup to a consistent state.

Percona fixed the corruption issue and several other issues with the INSTANT ADD/DROP column feature in the upcoming Percona Server for MySQL 8.0.29 (check PS-8291PS-8292 / PS-8303 for more details) we also raised and provided patches for those issues as a contribution to Community MySQL (see 107613 / 107611 / 107854 for details). Percona XtraBackup 8.0.29 can take backups of Percona Server for MySQL 8.0.29 with tables that have INSTANT ADD/DROP COLUMNS. However, the current version of Community MySQL 8.0.29 still has this flaw, making it unsafe to take backups.

It is impossible for XtraBackup to deal with the corrupted redo log generated by Community MySQL 8.0.29 and, for this reason, XtraBackup 8.0.29 version will not take backups if it detects tables with INSTANT ADD/DROP columns and will create an error with a list of the affected tables and provide instructions to convert them to regular tables.

Please avoid ALTER ADD/DROP COLUMN without an explicit ALGORITHM=INPLACE. The default ALGORITHM is INSTANT, so ALTER TABLE without the ALGORITHM keyword uses the newly added INSTANT algorithm. For example:

Working:

ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INPLACE/COPY;

Not Working:

ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INSTANT;
ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=DEFAULT;
ALTER TABLE tb1 ADD/DROP COLUMN [...];

If you already have such tables (see below on how to find such tables), users are advised to run OPTIMIZE TABLE against these tables before taking backups.

Find all tables with INSTANT ADD/DROP COLUMNS:

mysql> SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
+---------+
| NAME    |
+---------+
| test/t1 |
| test/t2 |
| test/t3 |
+---------+
3 rows in set (0.02 sec)

If this query shows an empty result set, you are all good. Percona XtraBackup will take backups of your MySQL 8.0.29 servers. If not, please run OPTIMIZE TABLE on the list of tables before taking a backup.

Percona XtraBackup error message

If Percona XtraBackup detects that MySQL 8.0.29 server has tables with instant add/drop columns, it aborts with the following error message

2022-07-01T15:18:35.127689+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2022-07-01T15:18:35.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2022-07-01T15:18:35.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2022-07-01T15:18:35.127723+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2022-07-01T15:18:35.127730+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t1
2022-07-01T15:18:35.127737+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t2
2022-07-01T15:18:35.127744+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t3
2022-07-01T15:18:35.127752+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.

Summary

Algorithm INSTANT is the new default in 8.0.29. If you do not specify an algorithm, all ALTER TABLE ADD/DROP COLUMN statements will use the default algorithm. 

The INSTANT algorithm is considered unstable at this point.

Percona XtraBackup will refuse to take backups from MySQL 8.0.29 tables that have been modified using this algorithm. Running OPTIMIZE TABLE on affected tables will bring them back to a safe state.

Percona XtraBackup is able to take backups seamlessly from Percona Server for MySQL, as the corruption issues have been fixed in the upcoming release of Percona Server for MySQL 8.0.29

May
09
2022
--

MySQL 8.0.29 and Percona XtraBackup Incompatibilities

MySQL 8.0.29 and Percona XtraBackup Incompatibilities

MySQL 8.0.29 and Percona XtraBackup IncompatibilitiesEarlier last week, Oracle released their Q2 releases series. Unlike previous releases, backward compatibility has now been broken with previous versions of MySQL.

MySQL 8.0.29 extended the support for the online DDL algorithm INSTANT. Prior to 8.0.29 only adding columns to the end of the table was supported.

In 8.0.29, this functionality was extended to allow the INSTANT algorithm the ability to add columns in any position of the table as well to drop columns. This new functionality required the redo log version to increase and new redo log types to be added, thus making it incompatible with older versions of the MySQL server and also older versions of Percona Xtrabackup. Please note that an in-place minor version downgrade of the server is also not supported.

We are currently working on making Percona Xtrabackup compatible with those changes. Until then users relying on Percona Xtrabackup for backups are advised to not upgrade the MySQL server to 8.0.29.

Mar
10
2022
--

Using Percona Server for MySQL 8.0 and Percona XtraBackup 8.0 with HashiCorp Vault Enterprise KMIP Secrets Engine

Percona HashiCorp Vault Enterprise KMIP Secrets Engine

Percona HashiCorp Vault Enterprise KMIP Secrets EngineKMIP (Key Management Interoperability Protocol) is an open standard developed by OASIS (Organization for Advancement of Structured Information Standards) for the encryption of stored data and cryptographic key management.

Percona Server for MySQL 8.0.27 and Percona XtraBackup 8.0.27 now include a KMIP keyring plugin to enable the exchange of cryptographic keys between a key management server and the database for encryption purposes. The procedure to use them with HashiCorp Vault Enterprise is described below.

Install Hashicorp Vault Enterprise

We will first install Hashicorp Vault Enterprise on Ubuntu Linux “Bionic” and then enable the KMIP secrets engine. The KMIP secrets engine is only available with the Enterprise version of HashiCorp Vault, hence a valid license for it is required.

Add HashiCorp repository and install enterprise vault package:

curl -fsSL https://apt.releases.hashicorp.com/gpg | sudo apt-key add -
sudo apt-add-repository "deb [arch=amd64] https://apt.releases.hashicorp.com $(lsb_release -cs) main"
sudo apt-get update && sudo apt-get install vault-enterprise

Export the license as an environment variable:

export VAULT_LICENSE=XXXX

Create a configuration file to be used with the vault, vault_config.hcl:

disable_mlock = true
default_lease_ttl = "24h"
max_lease_ttl = "24h"
storage "file" {
  path    = "/home/manish.chawla/vault/data"
}

listener "tcp" {
  address     = "127.0.0.1:8200"
  tls_cert_file = "/home/manish.chawla/test_mode/certificates/vault.crt"
  tls_key_file = "/home/manish.chawla/test_mode/certificates/vault.key"
}

Note: Vault root certificates and key need to be created separately and are not covered here.

Start vault server with the configuration file:

vault server -config=$HOME/vault_config.hcl 2>&1 &

Note: To configure and start the vault using systemd, refer to the instructions here.

Initialize the vault:

vault operator init -address=https://127.0.0.1:8200

This will generate five unseal keys and the initial root token.

Unseal Key 1: rf4C6gY87tN0UkJbJY96Aq4+Zext1YqgwaDnm+0gBBAH
Unseal Key 2: f9KA7EdlF391cIUzDqaS1N21JjML/36sZFl1x/OfCPn4
Unseal Key 3: 8Nh3EFYF7S0S9qqzdUIilPPRZRmWaDG+3El4rr4FmZNX
Unseal Key 4: iJaCKBIzxulLvA/6vbF3fRK1RXVZ0zLEZoVdlv/s13Sc
Unseal Key 5: rA4pwT6EZLwmVXPQJfU9fjgeGwPaHl260qM9CVNiUw13

Initial Root Token: s.WXEZ26Yb3MtvzbvNMMIG8bve

Unseal the vault.

Use any three unseal keys to unseal the vault. Three keys are required to unseal the vault.

vault operator unseal -address=https://127.0.0.1:8200 rf4C6gY87tN0UkJbJY96Aq4+Zext1YqgwaDnm+0gBBAH
Key                Value
---                -----
Seal Type          shamir
Initialized        true
Sealed             true
Total Shares       5
Threshold          3
Unseal Progress    1/3
Unseal Nonce       aa1a25c8-aa90-49b3-b127-875524de38f8
Version            1.9.3+ent
Storage Type       file
HA Enabled         false

vault operator unseal -address=https://127.0.0.1:8200 f9KA7EdlF391cIUzDqaS1N21JjML/36sZFl1x/OfCPn4
Key                Value
---                -----
Seal Type          shamir
Initialized        true
Sealed             true
Total Shares       5
Threshold          3
Unseal Progress    2/3
Unseal Nonce       aa1a25c8-aa90-49b3-b127-875524de38f8
Version            1.9.3+ent
Storage Type       file
HA Enabled         false

vault operator unseal -address=https://127.0.0.1:8200 8Nh3EFYF7S0S9qqzdUIilPPRZRmWaDG+3El4rr4FmZNX
Key             Value
---             -----
Seal Type       shamir
Initialized     true
Sealed          false
Total Shares    5
Threshold       3
Version         1.9.3+ent
Storage Type    file
Cluster Name    vault-cluster-7d9b43de
Cluster ID      c047dcb5-5038-5a29-f4af-47c1ad560f9c
HA Enabled      false

The vault is unsealed.

To use the vault in any terminal, run:

export VAULT_ADDR=https://127.0.0.1:8200
export VAULT_TOKEN=s.WXEZ26Yb3MtvzbvNMMIG8bve
export VAULT_CACERT=/home/manish.chawla/test_mode/certificates/root.cer

Configure KMIP Secrets Engine in Vault

Enable KMIP secrets engine:

vault secrets enable kmip
Success! Enabled the kmip secrets engine at: kmip/

View the secrets list:

vault secrets list
Path          Type         Accessor              Description
----          ----         --------              -----------
cubbyhole/    cubbyhole    cubbyhole_ec12856f    per-token private secret storage
identity/     identity     identity_12d9670d     identity store
kmip/         kmip         kmip_5fb3d4c6         n/a
sys/          system       system_1733eece       system endpoints used for control, policy and debugging

Change the kmip server listening address and port:

vault write kmip/config listen_addrs=0.0.0.0:5696
Success! Data written to: kmip/config

Note: Here kmip is the default path of the secret engine and not the type of the engine.

By default, the kmip generates certificates in EC(Elliptic Curve). We need RSA for MySQL, so specify the certificate type (tls_ca_key_type) and bits (tls_ca_key_bits) to configure the kmip server.

vault write kmip/config tls_ca_key_type="rsa" tls_ca_key_bits=2048
Success! Data written to: kmip/config

vault read kmip/config
Key                            Value
---                            -----
default_tls_client_key_bits    256
default_tls_client_key_type    ec
default_tls_client_ttl         336h
listen_addrs                   [0.0.0.0:5696]
server_hostnames               [localhost]
server_ips                     [127.0.0.1 ::1]
tls_ca_key_bits                2048
tls_ca_key_type                rsa
tls_min_version                tls12

The KMIP secrets engine uses scopes to partition object storage into multiple named buckets. Within a scope, roles can be created with a set of allowed operations that the particular role can perform.

Create a scope:

vault write -f kmip/scope/my-service
Success! Data written to: kmip/scope/my-service

Create a role within the scope, specifying the set of operations to allow or deny.

vault write kmip/scope/my-service/role/admin operation_all=true
Success! Data written to: kmip/scope/my-service/role/admin

Client Certificate Generation for the scope and role created above.

Retrieve the generated CA certificate:

vault read kmip/ca

Copy and save the CA certificate as ca.pem.

Generate a certificate in PEM format, and save it in a JSON file named credential.json.

vault write -format=json \
    kmip/scope/my-service/role/admin/credential/generate \
    format=pem > credential.json

Extract the certificate from the credential.json using jq tool and save it in a file named cert.pem.

jq -r .data.certificate < credential.json > cert.pem

Extract the private key from the credential.json using jq tool and save it in a file named key.pem.

jq -r .data.private_key < credential.json > key.pem

The KMIP configuration is now complete.

Percona Server for MySQL 8.0.27 Configuration for KMIP

This section describes the KMIP configuration in Percona Server for MySQL. KMIP is configured as a component in Percona Server for MySQL.

Create the global manifest file(mysqld.my) in the mysqld installation directory.

{
  "components": "file://component_keyring_kmip"
}

Create the global configuration file, component_keyring_kmip.cnf in the directory, where the component_keyring_kmip library resides.

{ "path": "/home/manish.chawla/keyring_kmip", "server_addr": "0.0.0.0", "server_port": "5696", "client_ca": "/home/manish.chawla/cert.pem", "client_key": "/home/manish.chawla/key.pem", "server_ca": "/home/manish.chawla/ca.pem" }

Note: SElinux/AppArmor rules may have to be adjusted, so that Percona Server for MySQL and Percona XtraBackup can access the certificates.

Initialize and start mysqld with encryption options(add in my.cnf): 

--innodb-undo-log-encrypt --innodb-redo-log-encrypt --binlog-encryption --default-table-encryption=ON --log-replica-updates --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --source-verify-checksum=ON --binlog-checksum=CRC32 --table-encryption-privilege-check=ON

Check the KMIP component status:

8.0.27>SELECT * FROM performance_schema.keyring_component_status;
+---------------------+------------------------------+
| STATUS_KEY          | STATUS_VALUE                 |
+---------------------+------------------------------+
| Component_name      | component_keyring_kmip       |
| Author              | Percona Corporation          |
| License             | GPL                          |
| Implementation_name | component_keyring_kmip       |
| Version             | 1.0                          |
| Component_status    | Active                       |
| Server_addr         | 0.0.0.0                      |
| Server_port         | 5696                         |
| Client_ca           | /home/manish.chawla/cert.pem |
| Client_key          | /home/manish.chawla/key.pem  |
| Server_ca           | /home/manish.chawla/ca.pem   |
| Object_group        | <NONE>                       |
+---------------------+------------------------------+

Create some encrypted tables and add data in the Percona Server for MySQL.

Backup and Restore of Percona Server for MySQL 8.0.27 Using Percona XtraBackup 8.0.27

This section describes the procedure for taking backup and restore of Percona Server for MySQL 8.0.27 when the KMIP component is enabled and the KMIP vault server is running. Percona XtraBackup reads the KMIP configuration in Percona Server for MySQL automatically, and it is not required to pass this information separately.

Take full backup:

xtrabackup --user=backup --password=* --backup --target-dir=backup_directory

Prepare full backup:

xtrabackup --prepare --target_dir=backup_directory

Stop Percona Server for MySQL and move the data directory to another location. Disable SElinux/AppArmor before restoring the backup.

Restore full backup:

xtrabackup --copy-back --target-dir=backup_directory

Change the ownership of the copied files in the Percona Server for MySQL data directory to the MySQL user.

Start Percona Server for MySQL and check the data. Enable SElinux/AppArmor, if disabled previously.

Feb
28
2022
--

Backup/Restore Performance Conclusion: mysqldump vs MySQL Shell Utilities vs mydumper vs mysqlpump vs XtraBackup

MySQL Restore Backup Comparison

MySQL Restore Backup ComparisonA little bit ago, I released a blog post comparing the backup performance of different MySQL tools such as mysqldump, the MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. You can find the first analysis here:

Backup Performance Comparison: mysqldump vs. MySQL Shell Utilities vs. mydumper vs. mysqlpump vs. XtraBackup

However, we know the backups are just the first part of the story. What about the restore time? And which tool performs better for the complete operation (backup+restore)?

Let’s see the results and the conclusion in the following sections.

Benchmark Results

I ran the benchmark on an m5dn.8xlarge instance, with 128GB RAM, 32 vCPU, and two io1 disks of 600GB (one for backup and the other one for MySQL data) with 5000 provisioned IOPS. The MySQL version was 8.0.26 and configured with 89Gb of the buffer pool, 20Gb of redo log, and a sample database of 96 GB (more details below).

When we sum the backup time and the restore time, we can observe the results in the chart below:

MySQL Backup and Restore

And if we analyze the chart without mysqldump to have a better idea of how the other tools performed:


The backup size created by each tool:

MySQL Backup Size

Note that the backup size of XtraBackup (without compression) is the size of the datadir without the binary logs. Next, we can see the backup time:

Time to execute MySQL backup

And the restore time:

Time to restore MySQL

Analyzing The Results

When we sum backup and restore times, we observe that the fastest tool is Percona XtraBackup. The main point of XtraBackup is not even the speed but its capacity to perform PITR backups. Also, the tool supports compression and encryption.

We can also observe that mydumper/myloader and MySQL Shell utilities produce good results in both phases. The difference from Xtrabackup is that both tools perform logical backups, which means that these tools connect to MySQL and extract the data to dump files. Because they have to extract data from MySQL, these tools are more sensitive for the MySQL configuration and backup/restore parametrization. For example, MyDumper/MyLoader has some extra options that can improve the backup and restore performance, such as --rows, --chunk-filesize, and --innodb-optimize-keys.

Note that  XtraBackup, MyDumper, and mysqldump support stream restore, reducing overall timing to perform the backup and restore operation. 

The tool that has the most inconsistent behavior is mysqlpump where the tool can make speedy backups, but the restore performance is terrible since it is single-threaded the same way as mysqldump. 

Based on the tests, we can observe that compression, TLS, socket, or TCP/IP do not significantly impact the time needed to perform the whole operation. Because there is no significant impact, tools that can perform compression and use TLS like MySQL Shell, mydumper/myloader, and XtraBackup have a good advantage since their backups are safer and use less disk space (less disk space = fewer costs). The trade-off between the features of these tools and the time spent to backup and restore the database is something that all DBAs should evaluate.

And to answer some questions/comments about this topic:

The difference you see between MySQL Shell and mydumper can be explained by the use of SSL in one and clear transfer in the other. Encryption has a cost, unfortunately. 

A: Indeed, SSL has a cost. However, when we put the security benefits of the SSL and consider the whole process, it is a small cost (in the same way as compression).

Does XtraBackup support ZSTD? 

A: At this moment, no. However, there is a feature request for this (you can follow the JIRA ticket to receive updates about it):

https://jira.percona.com/browse/PXB-2669

Is there any difference substituting mysqldump | gzip with a different compression tool?

A: The difference is neglectable piping with gzip or sending the uncompressed dump to the disk. The mysqldump tool is the most inefficient option due to its single-thread nature, severely impacting performance. Because of its single-thread nature, the tool cannot extract maximum performance from hardware resources (in particular I/O).

How is the performance impact on MySQL when running the backups?

A: Unfortunately, I did not measure this. Based on my experience, there is a dedicated replica server for backup most of the time. If the MySQL community is interested in this test, I can write another post about this (leave in the comments your opinion). 

It is possible to squeeze more juice from MySQL in the restore phase. We can take some actions like disabling the binary log and making asynchronous writes. You can check the advice (pros and cons) in these two blog posts:

https://www.percona.com/blog/2020/05/14/tuning-mysql-innodb-flushing-for-a-write-intensive-workload/

https://www.percona.com/blog/2014/05/23/improve-innodb-performance-write-bound-loads/

To conclude, this blog post is intended to give an overall idea of how these tools perform. I tried to stick with the default options of each tool (except the number of threads) to keep the test as fair as possible. Also, time is not the only thing that companies consider to adopt a backup method (security, encryption, and data protection are very important). In my daily tasks, I use mydumper/myloader and XtraBackup because I’m more familiar with the commands, and I have used them for a long time. However, I would advise keeping an eye on the MySQL Shell utilities since it is becoming a fascinating tool to perform many tasks (backup and restore have excellent results).

Hardware and Software Specs

These are the specs of the benchmark:

  • 32 CPUs
  • 128GB Memory
  • 2x io1 disks 600 GB with 5000 IOPS each
  • Centos 7.9
  • MySQL 8.0.26
  • MySQL shell 8.0.26
  • mydumper 0.11.5 – gzip
  • mydumper 0.11.5 – zstd
  • Xtrabackup 8.0.26

Useful Resources

Finally, you can reach us through the social networks, our forum, or access our material using the links presented below:

Jan
20
2022
--

Percona XtraBackup Changing to Strict by Default

Percona XtraBackup Changing to Strict by Default

Percona XtraBackup Changing to Strict by Default

Backups are a key part of a disaster recovery strategy, making sure you can continue or restore your business in case of an unwanted event with your data.

We always work on trying to improve Percona XtraBackup reliability, always favoring consistency, attempting to make unwanted outcomes be noticed as earlier as possible in the process.

Enabling –strict by Default

As of the upcoming release of 8.0.27, XtraBackup will no longer accept invalid parameters. Since the beginning of the times, validation of parameters has been a difficult task for XtraBackup as it mixes server-side and XtraBackup only parameters.

Starting at Percona XtraBackup 8.0.7 we implemented PXB-1493 which added –strict option defaulting to false.

This option issues a warning for each parameter/option that XtraBackup does not recognize. 

Having this as a warning is not good enough for a few reasons:

  1. It’s easy for it to go unnoticed as we mostly never read all the lines from the output of the backup, paying attention only to the end of the backup to validate if it completed ok.
  2. Having it as a warning doesn’t prevent us from shooting ourselves in the foot. As an example, when applying a –prepare on an incremental backup, if we mistype –apply-log-only parameter on an incremental backup that is not the last one, we will be executing the rollback phase making it rollback in-fly transactions that might have been completed on the next incremental backup.

And there are more.

With all the above in mind, we have decided to enable –strict mode by default.

What Does it Mean For You?

From the 8.0.27 release going forward, you might see XtraBackup failing in case you use an invalid option, either from the [xtrabackup] group of your configuration file or passed as an argument on the command line.

For the next few releases, we will still allow you to change back to the old behavior by passing the parameter –skip-strict, –strict=0, or –strict=OFF.

In the future, –strict will be the sole option, and disabling it will be forbidden.

Aug
17
2021
--

First Packages for Debian 11 “bullseye” Now Available

Percona Debian 11 bullseye

Percona Debian 11 bullseyeOver the weekend, the Debian project announced the availability of their newest major distribution release, Debian 11 (code name “bullseye”). We’d like to congratulate the Debian project and the open source community for achieving this major milestone! With over two years in the making, it contains an impressive amount of new and updated software for a wide range of applications (check out the release notes for details). The project’s emphasis on providing a stable Linux operating system makes Debian Linux a preferred choice for database workloads.

The packaging, release, and QA teams here at Percona have been working on adding support for Debian 11 to our products for quite some time already.

This week, we’ve released Percona Server for MongoDB 4.4.8 and 5.0.2 (Release Candidate) as well as Percona Backup for MongoDB 1.6.0, including packages for Debian 11 as a new supported OS platform. Please follow the installation instructions in the respective product documentation to install these versions.

We’ve also rebuilt a number of previously released products on Debian 11. At this point, the following products are available for download from our “testing” package repositories:

  • Percona Server for MySQL 5.7 and 8.0
  • Percona XtraDB Cluster 5.7 and 8.0
  • Percona XtraBackup 2.4 and 8.0

As usual, you can use the percona-release tool to enable the testing repository for these products. Please follow the installation instructions on how to install the tool and proceed.

As an example, if you’d like to install the latest version of Percona Server for MySQL 8.0 on Debian 11, perform the following steps after completing the installation of the base operating system and installing the percona-release tool:

$ sudo percona-release enable ps-80 testing
$ sudo apt update
$ sudo apt install percona-server-server percona-server-client

Percona Distribution for MongoDB is a freely available MongoDB database alternative, giving you a single solution that combines the best and most important enterprise components from the open source community, designed and tested to work together.

Download Percona Distribution for MongoDB Today!

Jun
25
2021
--

Percona XtraBackup for Windows

Percona XtraBackup for Windows

Percona XtraBackup for WindowsDon’t Try This at Home!

Disclaimer: The procedure described in this blog post is not officially supported by Percona XtraBackup. Use it under your responsibility. I tested and used it successfully, but your mileage may vary.

Note from the author: Wikipedia defines clickbait as text (or a link) that is designed to attract attention and to entice users to read that online content, with a defining characteristic of being deceptive. Maybe the headline of this post is a bit deceptive, as there is no Percona XtraBackup for Windows, but I hope you will not feel deceived after reading this blog post, I just felt that it was funny to use a clickbait-style while writing it.

He Couldn’t Believe it When He Read That Post!

Pep had to migrate a client database from Windows to Linux and was considering all the available options to move the database with the minimal downtime possible. He found this blog post, Running Percona XtraBackup on Windows … in Docker from Vadim Tkachenko that describes a procedure to backup a Windows database using Percona XtraBackup and Docker.

There is also the post Running XtraBackup on Windows using WSL, which describes a similar process using Windows Services for Linux.

Out of an old version, compiled using Cygwin, and an inactive effort to build a Windows version Percona XtraBackup 1.6 for Windows “try me” edition, the only way to use XtraBackup to copy a database running on Windows involved some sort of virtualization. Was it possible to use Percona XtraBackup without involving virtualization?

Datafiles Then And Now: How They’ve Changed!

To perform a backup, XtraBackup does a lot of different things.

It connects to the database and retrieves information about it, and executes commands that are required during the process.

Then it copies all the datafiles and InnoDB redo logs. As the contents of the datafiles change during the backup, the copy is inconsistent. This is why the contents of redo logs are copied also. They are used to turn the inconsistent backup copy into a consistent one during the “prepare” stage.

A detailed description of this process is beyond the scope of this post, you just need to know that XtraBackup needs logical access to the database and physical access to the database files. Feel free to look at the documentation or watch my recent Percona Live talks about Percona XtraBackup if you need more information:

Dr. XtraBackup or: How I Learned to Stop Worrying and Love Backups I
Dr. XtraBackup or: How I Learned to Stop Worrying and Love Backups II

This DBA Wanted to Make a Backup and Did The Most Incredible Thing!

Could we execute XtraBackup from a remote Linux box to perform a backup of a Windows database? Regarding the database connection, you don’t need to connect locally to the database to perform a backup, it can be done remotely.

Could we access the database files remotely? The answer is yes, of course. You just need to create network shares for the folders that contain the database files. And then mount them on Linux to execute the backup there.

This cute network share will melt your heart!

Usually, you need to share only the “datadir” but, if the database you want to backup has the redo log files located in a different directory, then you’ll need to share that directory also.

If binary logging is enabled, XtraBackup also needs access to the directory where binary logs are located. So, in the best case, you need to create only one network share, otherwise, you will need to share every directory that contains database files.

My recommendation is that you create the Windows shares with read-only permissions, to avoid writing to them by mistake.

Once you create the network shares, you need to mount them on the Linux box that will effectively run the backup, for example:

mount -t cifs -o vers=3.11,cache=none,actimeo=0,ro //<server>/<folder /<mount_point>

The filesystem type is CIFS. Use the highest version supported by your server. You should see something like this if you run the mount command without parameters:

//<server>/datadir on /winbox/datadir type cifs (ro,relatime,vers=3,cache=none,username=<username>,domain=<domain>,uid=0,noforceuid,gid=0,noforcegid,addr=<ip_address>,file_mode=0755,dir_mode=0755,soft,nounix,serverino,mapposix,rsize=8388608,wsize=1048576,echo_interval=60,actimeo=0)

Check the mount options for the credential file and other parameters. Do not include the credentials in the mount command!

This is Why Configuration Parameters Exist!

Now we have access to the database, using database credentials. And we have access to the files, using the shared folders. But we need to tell XtraBackup how to access them.

To perform a backup you need a database user with the following privileges:

  • RELOAD and LOCK TABLES (unless the –no-lock option is specified) to be able to execute “FLUSH TABLES WITH READ LOCK” and “FLUSH ENGINE LOGS” before starting to copy the files.
  • LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP are privileges required to use backup locks.
  • REPLICATION CLIENT to obtain the binary log position.
  • CREATE TABLESPACE to import tables.
  • PROCESS to run SHOW ENGINE INNODB STATUS and to see all the threads running on the server.
  • SUPER to start/stop the replica threads in a replication environment (optional), and create, delete and select privileges on the PERCONA_SCHEMA (if it exists) to store incremental and history data.

Usually, we tend to grant all privileges to the user that will connect to the database to run the backup.

You can tell XtraBackup how to connect to the database using these parameters:

--user=<database_user>
--password=<database_password>
--host=<ip_address or hostname>
--datadir=<local mount point for remote share>
--log-bin-index=<location of log-bin index file>
--log-bin=<local mount point for remote share>

For example:

xtrabackup --user=root --password=<password> --host=192.168.0.1 --backup --datadir=/mnt/data --log-bin-index=/mnt/data/binlog.index --log-bin=/mnt/data --target-dir=<dest_dir> --innodb_log_file_size=<log_file_size> --strict

Remember to always use the –strict option, this will make xtrabackup fail if you make a typo and a parameter is not specified properly. And if there is any relevant InnoDB parameter that has been modified, then you can add it also as a parameter to xtrabackup (–innodb_log_file_size in the example)

He Thought It Was Over, but It Was Not!

Once we have the backup stored in the Linux server, we can prepare it following the standard procedure:

xtrabackup --prepare --target-dir=<dest-dir>

But now that we have a backup prepared and stored in Linux, is it possible to start that database on Linux? Yes! It is possible!

You only need to make sure that the database starts with lower_case_table_names enabled as the database comes from a case-insensitive filesystem.

And, if you enable binary logging in the new server, you may need to manually edit the binlog index file to replace the \ by a / as this is the directory delimiter on Linux.

I hope you will be able to use this procedure successfully and, as always, test restoring your backups frequently!

Apr
12
2021
--

Replay the Execution of MySQL With RR (Record and Replay)

MySql Record and Replay

MySql Record and ReplayChasing bugs can be a tedious task, and multi-threaded software doesn’t make it any easier. Threads will be scheduled at different times, instructions will not have deterministic results, and in order for one to reproduce a particular issue, it might require the exact same threads, doing the exact same work, at the exact same time. As you can imagine, this is not straightforward.

Let’s say your database is crashing or even having a transient stall.  By the time you get to it, the crash has happened and you are stuck restoring service quickly and doing after-the-fact forensics.  Wouldn’t it be nice to replay the work from right before or during the crash and see exactly what was happening?

Record and Replay is a technique where we record the execution of a program allowing it to be replayed over and over producing the same result. Engineers at Mozilla have created RR, and basically, this open source tool allows you to record the execution of the software and replay it under the well-known GDB.

A Backup Problem

To demonstrate how powerful the tool is, we will be walking through how we used it to narrow down the issue from PXB-2180 (Special thanks to Satya Bodapati, who helped with all the InnoDB internals research for this bug). 

In summary, we were seeing Percona XtraBackup crashing at the prepare stage (remember, always test your backup!). The crash was happening randomly, sometimes after the second incremental, sometimes after the 10th incremental, with no visible pattern.

The stack trace was also not always the same. It was crashing on different parts of InnoDB, but here we had one commonality from all crashes – it always happened while trying to apply a redo log record to the same block page and space id:

#12 0x00000000015ad05f in recv_parse_or_apply_log_rec_body (type=MLOG_COMP_REC_INSERT, ptr=0x7f2849150556 "\003K4G", '\377' <repeats 13 times>, end_ptr=0x7f2849150573 "", space_id=<optimized out>, page_no=<optimized out>, block=0x7f2847d7da00, mtr=0x7f286857b4f0, parsed_bytes=18446744073709551615) at /home/marcelo.altmann/percona-xtrabackup/storage/innobase/log/log0recv.cc:2002
2002         ptr = page_cur_parse_insert_rec(FALSE, ptr, end_ptr, block, index, mtr);
(gdb) p block->page->id
+p block->page->id
$3 = {
  m_space = 4294967294,
  m_page_no = 5
}

Our suspicion was that the page layout on this block diverged between MySQL and XtraBackup. When working with these types of bugs, the crash is always the consequence of something that happened earlier, eg.: a crash on the sixth incremental backup could be the consequence of an issue that happened on the fourth incremental. 

The main goal at this step is to prove and identify where the page layout has diverted.

With this information, we ran MySQL under RR and reran the backup until we saw the same issue at prepare. We can now replay the MySQL execution and check how it compares. Our idea is to:

  1. Read the LSNs for this same page before/after each backup prepare.
  2. Identify all changes to  m_space = 4294967294 & m_page_no = 5 at mysqld.

Before we progress further, let’s explain a few things:

  1. m_space = 4294967294 correspond to the MySQL data dictionary (mysql.ibd) – dict0dict.h:1146
  2. On disk page, LSN is stored at the 16th byte of the page and has a size of 8 bytes – fil0types.h:66
  3. Pages are written sequentially to disk, as an example, for the default 16k page size, from bytes 1 to 16384 will have the data for page 0, from byte 16385 to 32768 data from page 1, and so on. 
  4. Frame is raw data of a page – buf0buf.h:1358

Replaying the Execution

To start, let’s read what LSN we have on mysql.ibd for page five before the backup. We will be using od (check man od for more information) and the information explained above:

$ od -j $((16384 * 5 + 16)) -N 8 -t x1 full/mysql.ibd
0240020 00 00 00 00 01 10 21 85
0240030

And check if it matches an LSN stamp from mysqld. For that we will add a conditional breakpoint on the replay execution of MySQL at function buf_flush_note_modification:

$ rr replay .
. . .
(rr) b buf_flush_note_modification if block->page->id->m_space == 4294967294 && block->page->id->m_page_no == 5
+b buf_flush_note_modification if block->page->id->m_space == 4294967294 && block->page->id->m_page_no == 5
Breakpoint 1 at 0x495beb1: file /home/marcelo.altmann/percona-server/storage/innobase/include/buf0flu.ic, line 69.
(rr) c
[Switching to Thread 18839.18868]

Breakpoint 1, buf_flush_note_modification (block=0x7fd2df4ad750, start_lsn=17892965, end_lsn=17893015, observer=0x0) at /home/marcelo.altmann/percona-server/storage/innobase/include/buf0flu.ic:69
69     ut_ad(!srv_read_only_mode ||
++rr-set-suppress-run-hook 1
(rr) p/x block->frame[16]@8
+p/x block->frame[16]@8
$1 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x10,
  [0x6] = 0x21,
  [0x7] = 0x85}
(rr)

We can see the LSN stamp from before the preparation of full backup and the first stamp from the replay session match. Time to prepare the backup, advance the replay execution, and recheck:

xtrabackup --prepare --apply-log-only --target-dir=full/
. . .
Shutdown completed; log sequence number 17897577
Number of pools: 1
210402 17:46:29 completed OK!


$ od -j $((16384 * 5 + 16)) -N 8 -t x1 full/mysql.ibd
0240020 00 00 00 00 01 11 07 06
0240030


(rr) c
+c
Continuing.
[Switching to Thread 18839.18868]

Breakpoint 1, buf_flush_note_modification (block=0x7fd2df4ad750, start_lsn=19077332, end_lsn=19077382, observer=0x0) at /home/marcelo.altmann/percona-server/storage/innobase/include/buf0flu.ic:69
69     ut_ad(!srv_read_only_mode ||
++rr-set-suppress-run-hook 1
(rr) p/x block->frame[16]@8
+p/x block->frame[16]@8
$16 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x11,
  [0x6] = 0x7,
  [0x7] = 0x6}
(rr)

Same LSN stamp on both, server and backup. Time to move on and start to apply the incrementals:

xtrabackup --prepare --apply-log-only --target-dir=full/ --incremental-dir=inc1/
. . .
Shutdown completed; log sequence number 19082430
. . .
210402 18:12:20 completed OK!


$ od -j $((16384 * 5 + 16)) -N 8 -t x1 full/mysql.ibd
0240020 00 00 00 00 01 23 19 06
0240030


(rr) c
+c
Continuing.
Breakpoint 1, buf_flush_note_modification (block=0x7fd2df4ad750, start_lsn=20262758, end_lsn=20262808, observer=0x0) at /home/marcelo.altmann/percona-server/storage/innobase/include/buf0flu.ic:69
69     ut_ad(!srv_read_only_mode ||
++rr-set-suppress-run-hook 1
(rr) p/x block->frame[16]@8
+p/x block->frame[16]@8
$17 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x23,
  [0x6] = 0x19,
  [0x7] = 0x6}
(rr)

Once again, we have a matching LSN stamp on both sides. Moving to the next incremental:

xtrabackup --prepare --apply-log-only --target-dir=full/ --incremental-dir=inc2/
. . .
Shutdown completed; log sequence number 20269669
. . .
210402 18:15:04 completed OK!


$ od -j $((16384 * 5 + 16)) -N 8 -t x1 full/mysql.ibd
0240020 00 00 00 00 01 35 2f 98
0240030


(rr) c
+c
Continuing.

Breakpoint 1, buf_flush_note_modification (block=0x7fd2df4ad750, start_lsn=21449997, end_lsn=21450047, observer=0x0) at /home/marcelo.altmann/percona-server/storage/innobase/include/buf0flu.ic:69
69     ut_ad(!srv_read_only_mode ||
++rr-set-suppress-run-hook 1
(rr) p/x block->frame[16]@8
+p/x block->frame[16]@8
$18 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x35,
  [0x6] = 0x2f,
  [0x7] = 0x98}
(rr)

Incremental two applied and matching LSN stamp from mysqld. Let’s keep doing this until we find a mismatch:

xtrabackup --prepare --apply-log-only --target-dir=full/ --incremental-dir=inc3/
. . .
Shutdown completed; log sequence number 21455916
. . .
210402 18:18:25 completed OK!


$ od -j $((16384 * 5 + 16)) -N 8 -t x1 full/mysql.ibd
0240020 00 00 00 00 01 47 4d 3f
0240030


(rr) c
+c
Continuing.

Breakpoint 1, buf_flush_note_modification (block=0x7fd2df4ad750, start_lsn=25529471, end_lsn=25529521, observer=0x0) at /home/marcelo.altmann/percona-server/storage/innobase/include/buf0flu.ic:69
69     ut_ad(!srv_read_only_mode ||
++rr-set-suppress-run-hook 1
(rr) p/x block->frame[16]@8
+p/x block->frame[16]@8
$19 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x47,
  [0x6] = 0x4d,
  [0x7] = 0x3f}
(rr)


xtrabackup --prepare --apply-log-only --target-dir=full/ --incremental-dir=inc4/
. . .
Shutdown completed; log sequence number 23044902
. . .
210402 18:24:00 completed OK!

$ od -j $((16384 * 5 + 16)) -N 8 -t x1 full/mysql.ibd
0240020 00 00 00 00 01 5f a3 26
0240030


(rr) c
+c
Continuing.

Breakpoint 1, buf_flush_note_modification (block=0x7fd2df4ad750, start_lsn=27218464, end_lsn=27218532, observer=0x0) at /home/marcelo.altmann/percona-server/storage/innobase/include/buf0flu.ic:69
69     ut_ad(!srv_read_only_mode ||
++rr-set-suppress-run-hook 1
(rr) p/x block->frame[16]@8
+p/x block->frame[16]@8
$242 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x9f,
  [0x6] = 0x3f,
  [0x7] = 0xc9}
(rr)

Ok, here we have something. Backup files jumped from 0x01474d3f to 0x015fa326  when applying incremental four while the server moved from 0x01474d3f to 0x019f3fc9 . Perhaps we missed some other place where we can update the LSN stamp of a page? But now, we are at a point in the future with our replay execution of the MySQL server.

Replaying the Execution Backward

Here is (yet) another very cool feature from RR, it allows you to replay the execution backward. To eliminate the possibility of missing a place that is also updating the LSN of this block, let’s add a hardware watchpoint on the block->frame memory address and reverse the execution:

(rr) p block->frame
+p block->frame
$243 = (unsigned char *) 0x7fd2e0758000 "\327\064X["
(rr) watch *(unsigned char *) 0x7fd2e0758000
+watch *(unsigned char *) 0x7fd2e0758000
Hardware watchpoint 2: *(unsigned char *) 0x7fd2e0758000
(rr) disa 1
+disa 1
(rr) reverse-cont
+reverse-cont
+continue
Continuing.
Hardware watchpoint 2: *(unsigned char *) 0x7fd2e0758000

Old value = 215 '\327'
New value = 80 'P'

0x0000000004c13903 in mach_write_to_4 (b=0x7fd2e0758000 "P\257\"\347", n=3610531931) at /home/marcelo.altmann/percona-server/storage/innobase/include/mach0data.ic:135
135   b[0] = static_cast<byte>(n >> 24);
++rr-set-suppress-run-hook 1
++rr-set-suppress-run-hook 1
(rr) p/x buf_flush_init_for_writing::block->frame[16]@8
+p/x buf_flush_init_for_writing::block->frame[16]@8
$11 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x9f,
  [0x6] = 0x3f,
  [0x7] = 0xc9}
(rr) reverse-cont
+reverse-cont
+continue
Continuing.
Hardware watchpoint 2: *(unsigned char *) 0x7fd2e0758000

Old value = 80 'P'
New value = 43 '+'
0x0000000004c13903 in mach_write_to_4 (b=0x7fd2e0758000 "+k*\304", n=1353655015) at /home/marcelo.altmann/percona-server/storage/innobase/include/mach0data.ic:135
135   b[0] = static_cast<byte>(n >> 24);
++rr-set-suppress-run-hook 1
++rr-set-suppress-run-hook 1
(rr) p/x buf_flush_init_for_writing::block->frame[16]@8
+p/x buf_flush_init_for_writing::block->frame[16]@8
$12 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x1,
  [0x5] = 0x47,
  [0x6] = 0x4d,
  [0x7] = 0x3f}
(rr)

By replaying the execution backward we can see that indeed the server changed the LSN  from 0x01474d3f to 0x019f3fc9. This confirms the issue is at incremental backup four as the LSN 0x015fa326 we see at end of incremental four was never a valid LSN at the server execution.

Root Cause

Now that we have limited the scope from six backups to a single one, things will become easier.

If we look closely at the log messages from the –prepare of the backup we can see that LSN of mysql.ibd matches the LSN stamp at the end of the backup:

xtrabackup --prepare --apply-log-only --target-dir=full/ --incremental-dir=inc4/
. . .
Shutdown completed; log sequence number 23044902
. . .
210402 18:24:00 completed OK!

$ od -j $((16384 * 5 + 16)) -N 8 -t x1 full/mysql.ibd
0240020 00 00 00 00 01 5f a3 26
0240030


$ echo $(( 16#015fa326 ))
23044902

By checking the stack trace of the issue and examining further the block we have parsed we can see that this is innodb_dynamic_metadata index:

(gdb) f 13
+f 13
#13 0x00000000015af3dd in recv_recover_page_func (just_read_in=just_read_in@entry=true, block=block@entry=0x7f59efd7da00) at /home/marcelo.altmann/percona-xtrabackup/storage/innobase/log/log0recv.cc:2624
2624       recv_parse_or_apply_log_rec_body(recv->type, buf, buf + recv->len,
(gdb) p/x block->frame[66]@8
+p/x block->frame[66]@8
$4 =   {[0x0] = 0x0,
  [0x1] = 0x0,
  [0x2] = 0x0,
  [0x3] = 0x0,
  [0x4] = 0x0,
  [0x5] = 0x0,
  [0x6] = 0x0,
  [0x7] = 0x2}

You might be wondering where 66 came from; this is from examining position FIL_PAGE_DATA + PAGE_INDEX_ID. That gave us index ID 2. This is below 1024, which is reserved for Data Dictionary tables. By checking what is the second table on that list, we can see that it’s innodb_dynamic_metadata. With all this information summed up we can look at what the server does at shutdown, and it becomes clear what the issue is:
srv0start.cc:3965

/** Shut down the InnoDB database. */
void srv_shutdown() {
  . . .

  /* Write dynamic metadata to DD buffer table. */
  dict_persist_to_dd_table_buffer();
. . .
}

As part of the shutdown process, we are persisting dirty metadata back to the DD Buffer table (innodb_dynamic_metadata), which is wrong. Those changes will likely be persisted by the server and redo logged once the server performs a checkpoint. Also, more data can be merged together by the point of when the backup was taken and when the server itself persists this data to DD Tables. This is a result of the implementation of WL#7816 and WL#6204 which required Percona XtraBackup to change how it handles these types of redo records.

Summary

In this blog, we walked through the process of analyzing a real Percona XtraBackup bug. This bug exposes a challenge we face in various types of bugs, where the crash/malfunction is a consequence of something that happened way before, and by the time we have a stack trace/coredump, it is too late to perform a proper analysis. Record and Replay enabled us to consistently replay the execution of the source server, making it possible to narrow down the issue to where the root cause was. 


Percona XtraBackup is a free, open source, complete database backup solution for all versions of Percona Server for MySQL and MySQL

Apr
02
2021
--

Percona XtraBackup Point-In-Time Recovery for the Single Database

Percona XtraBackup Point-In-Time Recovery

Percona XtraBackup Point-In-Time RecoveryRecovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted DELETE without WHERE clause or any other harmful command.

PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s).

However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, mysql command-line client, and mysqlbinlog programs only. There is an alternative approach that involves creating a fake source server, that is described in MySQL Point in Time Recovery the Right Way. You may consider it, especially if you need to apply changes to a single table.

Percona XtraBackup Point-In-Time Recovery

For our example we will create data first, then run DROP and DELETE commands on two different tables. Then we will rollback these commands.

First, let’s assume we have a server with two databases: test and sbtest. We are using GTIDs and row-based binary log format. We also run the server with the option innodb_file_per_table=1 and all our InnoDB tables use individual tablespaces. Otherwise, the individual restore method would not work.

mysql> show tables from sbtest;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
+------------------+
8 rows in set (0.00 sec)

mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| bar            |
| baz            |
| foo            |
+----------------+
3 rows in set (0.00 sec)

We will experiment with tables foo and bar. We assume that at the time of our first backup, each of the tables contained five rows. Tables in the database sbtest also contain data, but it does not really matter for our experiment.

mysql> select count(*) from foo;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from bar;
+----------+
| count(*) |
+----------+
| 5        |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from baz;
+----------+
| count(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

Since we want to restore individual tables, we need to make a preparation before taking a backup: store database structure. We will do it with help of the mysqldump command. In this example, I store structure per database to make partial PITR easier, but you are free to use the option --all-databases.

mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events test > test_structure.sql
mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events sbtest > sbtest_structure.sql

Then we are ready to take the backup.

xtrabackup --parallel=8 --target-dir=./full_backup --backup

I am using the option --parallel to speed up the backup process.

Now let’s do some testing. First, let’s update rows in the table foo.

mysql> update foo set f1=f1*2;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0

mysql> select * from foo;
+----+------+
| id | f1   |
+----+------+
|  1 |    2 |
|  2 |    4 |
|  3 |    6 |
|  4 |    8 |
|  5 |   10 |
+----+------+
5 rows in set (0.00 sec)

And then drop it and delete all rows from the table bar.

mysql> drop table foo;
Query OK, 0 rows affected (0.02 sec)

mysql> delete from bar;
Query OK, 5 rows affected (0.01 sec)

Finally, let’s insert a few rows into the tables bar and baz.

mysql> insert into bar(f1) values(6),(7),(8),(9),(10);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into baz(f1) values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

Assume that the DROP TABLE and DELETE command was an accident and we want to restore the state of the tables foo and bar as they were before these unlucky statements.

First, we need to prepare the backup.

Since we are interested in restoring only tables in the database test we need to prepare the backup with a special option --export that exports tablespaces in a way that they could be later imported:

xtrabackup --prepare --export --target-dir=./full_backup

Now the directory for the database test contains not only table definition files (.frm, only before 8.0) and tablespace files (.ibd) but also configuration files (.cfg).

Since we want all changes that happened after backup and before the problematic DROP TABLE and DELETE statements were applied, we need to identify which binary log and position were actual at the backup time. We can find it in the xtrabackup_binlog_info file:

$ cat full_backup/xtrabackup_binlog_info
master-bin.000004 1601 0ec00eed-87f3-11eb-acd9-98af65266957:1-56

Now we are ready to perform restore.

First, let’s restore the table foo from the backup. Restoring individual tablespaces requires the ALTER TABLE ... IMPORT TABLESPACE command. This command assumes that the table exists in the server. However, in our case, it was dropped and therefore we need to re-create it.

We will recreate the full database test from the file test_structure.sql

Since we do not want these administrative tasks to be re-applied, I suggest disabling binary logging for the session which will recreate the database structure.

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source test_structure.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
....

Once tables are recreated discard their tablespaces. I will show an example for the table foo. Adjust the code for the rest of the tables.

mysql> alter table foo discard tablespace;
Query OK, 0 rows affected (0.01 sec)

Then, in another terminal, copy the tablespace and configuration files from the backup to the database directory:

cp full_backup/test/foo.{ibd,cfg} var/mysqld.1/data/test/

And, finally, import the tablespace:

mysql> alter table foo import tablespace;
Query OK, 0 rows affected (0.05 sec)

Repeat for the other tables in the database test.

Now you can enable binary logging back.

You can do the same task in a script. For example:

for table in `mysql test --skip-column-names --silent -e "show tables"`
> do
>   mysql test -e "set sql_log_bin=0; alter table $table discard tablespace"
>   cp full_backup/test/$table.{ibd,cfg} var/mysqld.1/data/test/
>   mysql test -e "set sql_log_bin=0; alter table $table import tablespace"
> done

Our tables are recovered but do not have the updates made after the backup.

mysql> select * from foo;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from bar;
+----+------+
| id | f1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
|  5 |    5 |
+----+------+
5 rows in set (0.00 sec)

mysql> select * from baz;
Empty set (0.00 sec)

Therefore, we need to restore data from the binary logs.

To do it we first need to identify the GTID of the disaster event. It can be done if we dump all binary logs updated and created after backup into a dump file and then search for the DROP TABLE and DELETE statements and skipping them.

First, let’s check which binary logs do we have.

mysql> show binary logs;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |   1527476 |
| master-bin.000002 |      3035 |
| master-bin.000003 |      1987 |
| master-bin.000004 |      2466 |
| master-bin.000005 |       784 |
+-------------------+-----------+
5 rows in set (0.00 sec)

So we need to parse them, starting from the log master-bin.000004 and position 1601:

mysqlbinlog --start-position=1601 -vvv --base64-output=decode-rows --database=test master-bin.000004 master-bin.000005 > binlog_test.sql

I used options -vvv that prints SQL representation of row events, so we can find the one which we want to skip and --base64-output=decode-rows to not print row events at all. We will not use this file for the restore, only for searching the DROP TABLE and DELETE events.

Here they are, at the positions 2007 and 2123, with GTID 0ec00eed-87f3-11eb-acd9-98af65266957:58 and 0ec00eed-87f3-11eb-acd9-98af65266957:59

# at 2007
#210321 13:29:58 server id 1 end_log_pos 2123 CRC32 0xd1eb9854 Query thread_id=138 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1616322598/*!*/;
DROP TABLE `foo` /* generated by server */
/*!*/;
# at 2123
#210321 13:30:08 server id 1 end_log_pos 2188 CRC32 0xfc9b2088 GTID last_committed=7 sequence_number=8 rbr_only=yes original_committed_timestamp=0 immediate_commit_timestamp=0 transaction_length=0
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET)
# immediate_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET)
/*!80001 SET @@session.original_commit_timestamp=0*//*!*/;
/*!80014 SET @@session.original_server_version=0*//*!*/;
/*!80014 SET @@session.immediate_server_version=0*//*!*/;
SET @@SESSION.GTID_NEXT= '0ec00eed-87f3-11eb-acd9-98af65266957:59'/*!*/;
# at 2188
#210321 13:30:08 server id 1 end_log_pos 2260 CRC32 0x1d525b11 Query thread_id=138 exec_time=0 error_code=0
SET TIMESTAMP=1616322608/*!*/;
BEGIN
/*!*/;
# at 2260
#210321 13:30:08 server id 1 end_log_pos 2307 CRC32 0xb57ecb73 Table_map: `test`.`bar` mapped to number 226
# at 2307
#210321 13:30:08 server id 1 end_log_pos 2387 CRC32 0x6770a7e2 Delete_rows: table id 226 flags: STMT_END_F
### DELETE FROM `test`.`bar`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `test`.`bar`
### WHERE
...

Note that decoded row event contains a DELETE command for each affected row.

We may also find to which binary log this event belongs if search for the "Rotate to" event. In our case “Rotate to master-bin.000005” happened after the found positions, so we only need file master-bin.000004 In your case, you may need to skip events from the previous log files too.

So to restore the data we need to run mysqlbinlog one more time, this time with parameters:

mysqlbinlog  --start-position=1601 --exclude-gtids=0ec00eed-87f3-11eb-acd9-98af65266957:58-59 --database=test --skip-gtids=true master-bin.000004 master-bin.000005 > binlog_restore.sql

I removed options -vvvbecause we are not going to examine this restore file and option --base64-output=decode-rows because we need row events to present in the resulting file. I also used option --exclude-gtids=0ec00eed-87f3-11eb-acd9-98af65266957:58-59 to exclude GTIDs that we do not want to re-apply. We also need to use --skip-gtids=true because otherwise updates will be skipped since such GTIDs already exist on the server.

Now binlog_restore.sql contains all updates to the database test made after the backup and before the DROP statement. Let’s restore it.

mysql test < binlog_restore.sql

Restore went successfully. Our tables have all past updates.

mysql> select * from foo;
+----+------+
| id | f1   |
+----+------+
|  1 |    2 |
|  2 |    4 |
|  3 |    6 |
|  4 |    8 |
|  5 |   10 |
+----+------+
5 rows in set (0.01 sec)

mysql> select count(*) from bar;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from baz;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

Conclusion

You may save the time required for PITR if use the per-database restore method. However, you need to take into account the following considerations:

  • mysqlbinlog does not support filtering per table, therefore you either need to restore the full database or use a fake server method, described in MySQL Point in Time Recovery the Right Way.
  • Per-database filters depend on the USE statement in the statement-based binary log format. Therefore option --database can only be considered safe with a row-based format.
  • If you do not use GTID you still can use this method. You will need to combine options --start-position and --stop-position to skip the event.

Percona XtraBackup is a free, open source database backup solution for Percona Server for MySQL and MySQL.

Jan
27
2021
--

Percona XtraBackup 8 Enables –lock-ddl by Default

Percona XtraBackup 8 Enables --lock-ddl by Default

Percona XtraBackup 8 Enables --lock-ddl by DefaultPercona XtraBackup 8.0.23 enables the “lock-ddl” option by default to ensure any DDL events do not corrupt the backups. Any DML events continue to occur, and only DDL events are blocked.

A DDL lock protects the definition of tables and views. With the “–lock-ddl” option disabled, Percona XtraBackup allows backups while concurrent DDL events continue to happen. These backups are invalid and fail at the Prepare stage. If DDL events have occurred without the backup user’s knowledge, they may be unaware the backup is corrupt.

MySQL 8.0 introduced “Lock Instance for Backup”. This backup lock is lightweight, and the performance for DML operations is almost the same with or without backup locks. This lock is not available in 5.7, because Percona Server for MySQL has its own lightweight backup lock for that version.

Percona XtraBackup 8 also has “Lock Tables for Backup”. An active “Lock Tables for Backup” metadata lock (MDL) blocks all DDL statements and updates to MyISAM, CSV, MEMORY, ARCHIVE, TokuDB, and MyRocks tables. The PERFORMANCE_SCHEMA or PROCESSLIST displays “Waiting for backup log” when this lock is active.

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