About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/MySQL backup with Percona Monitoring and Management (PMM) seems useful

MySQL backup with Percona Monitoring and Management (PMM) seems useful

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
 

Introduction

If you have experience using MySQL, you may have heard of Percona.
This company develops tools such as Percona-Toolkit, which makes MySQL operations easier, and XtraBackup, a backup tool.
Percona has releasedPercona Monitoring and ManagementI tried out this tool because the backup management function looked useful.
 

What is Percona Monitoring and Management (PMM)?

Introducing PMM

Percona Monitoring and Management(PMM)is a monitoring and management tool developed by Percona.
It can manage not only MySQL but also PostgreSQL and MongoDB.
It is a highly versatile management tool that also includes query analysis and alerting functions.
 
officialDemo pageYou can try out and experience what kind of management tool it is.
There are two versions of PMM: version 1.x, which has been released since around 2016, and PMM2, which was released as a general release in October 2019.
As of February 2023, the latest release is 2.34.0.
 

Backup management function

Today, I will try out the backup management feature, which has been released as a technical preview feature since version 2.18.0.
As of February 2023, the MySQL backup feature remains in technical preview.
 
The limitations of the MySQL backup management feature technical preview are as follows:
  • Physical backup only. Logical backup is not supported.
  • The destination is S3 (or S3-compatible API-compatible storage) and cannot be output locally or to another server
 
MongoDB's backup function will be generally available in 2.32.0, released in November 2022, and will no longer have the above limitations.
I will introduce MongoDB in a separate article at a later date.
 

PMM installation instructions

Verification environment

hostnameroleOSMySQL
DB1sauceCentOS 5.78.0.28
DB2replicaCentOS 5.78.0.28
PMM01PMM ServerCentOS 5.7
 

Building a PMM Server

Official documentationFollow the instructions for building using Docker.
 
[At PMM01]
Docker installation
 
Starting the PMM Server container
 
The management console is now up and running, and you can view it in your browser.
https://サーバのIP
 

Installing the PMM Client and adding nodes

[DB1, DB2]
 
Install the Client on the MySQL server to be managed.
 
Create a user who can connect to MySQL from the PMM client.
 
Add a node using the CLI.
 
*You can also add nodes from the GUI, but in that case the MySQL user you create must be accessible from the PMM Server.
To add a node from the GUI, click the > arrow in the top left menu.
 
Select [Configuration] → [Add Instance to PMM].
 
Select MySQL.
 
Enter the required information such as connection information and click Add service.
 
Now you can add nodes from the GUI.

Performing backups and restores

Enabling the backup management feature of PMM Server

The MySQL backup management feature is in technical preview and must be enabled separately.
Select the gear icon on the left menu bar → [PMM Settings]
 
Enable the button in Advanced Settings → Backup Management.
 

Prerequisites for the PMM Backup Feature

To use the backup management function, severalPrerequisitesThere is.
  • PMM Client is installed
  • The user who connects to MySQL through PMM Client has BACKUP_ADMIN privileges
  • MySQL is running with systemd
  • The mysql user and mysql group exist
  • The data directory is /var/lib/mysql
  • Allow the pmm-agent user to read and write to the /var/lib/mysql directory
  • Once the package is installed, you can run the following command:
    • xtrabackup
    • xbcloud
    • xbstream
    • qpress
 
The PMM Client has already been installed in the previous steps, and the MySQL user has the necessary permissions, so we will now focus on the parts that need to be addressed.
 
Allow the pmm-agent user to read and write to the /var/lib/mysql directory.
 
Install the package so that you can run the following command:
 

Performing a Backup

First, register the location where you want to store the backup.
Currently, only S3 (or a compatible API) is available, so we will register S3.
*Please create an S3 bucket and issue an IAM key that can access that S3.
 
Open Backup in the left menu, open the [Storage Locations] tab, and click [Add storage location].
 
Enter the following information in the form that appears next:
Name[Enter an easy-to-understand name]
TypeS3
Endpoint 【s3.ap-northeast-1.amazonaws.com】
Bucket[Bucket name created in S3]
Access Key[IAM key to use]
Secret Key[IAM secret key to use]
 
Once you've completed the registration, make a backup.
Open [Backup] in the left menu and select [Create Backup].
 
Enter the necessary information.
Backup NameEnter the host name, date, etc. for easy understanding.
Service NameSpecify the server to get the backup from
Data modelOnly Physical is available at this time (2023/02)
LocationSelect the S3 you just created.
Once you have finished entering the information, press [Backup].
If successful, you will see a screen like this.
 
If the backup fails, the following message will be displayed, but you cannot check the cause of the error from the GUI, so check the journal log of the server from which the backup was taken.
 

Try a restore

After taking the backup, create a new schema in the DB to which you are restoring, and then check that it has been properly deleted after the restore.
 
Restore.
Select ... on the far right of the backup list you want to restore and select [Restore from backup].
 
A menu will open, so press [Restore].
If Same service is selected in Service selection, the data will be restored to the same server from which the backup was taken.
When I select Compatible services, I should be able to select the restore destination, but it doesn't show me another server as an option. I'm currently investigating this.
 
Once the restore is complete, the results will be displayed in the Restores tab.
 
Make sure the schema you just created has disappeared.
 
It was deleted safely, so it has been restored.

Conclusion

It was a feature that I felt had a lot of potential.
There are still many restrictions, so I hope that we will be able to change the settings more flexibly.
I would especially like to have local backups and backups to another server.
This has been achieved using MongoDB, so I would like to try it out again once the MySQL backup function is released for general availability.
 
SRG is looking for people to work with us. If you're interested, please contact us here.