About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/MySQL backup using Percona Monitoring and Management (PMM) seems useful

MySQL backup using 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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.
I hope this helps in some way.
 

Introduction

If you have experience using MySQL, you may have heard of Percona.
This is a company that 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 also has query analysis and alerting functions, making it an extremely versatile management tool.
 
officialDemo pageYou can touch and experience what kind of management tool it is.
There are two versions of PMM: 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 that has been released as a technical preview feature in version 2.18.0.
The MySQL backup feature remains in technical preview as of February 2023.
 
The limitations of the MySQL backup management technical preview are as follows:
  • Physical backup only. Logical backup is not supported.
  • The destination is S3 (or storage compatible with S3 API) and cannot be output locally or to another server
 
The MongoDB backup function will be GA released in 2.32.0 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 from your browser.
https://サーバのIP
 

Installing PMM Client and adding nodes

[DB1, DB2]
 
Install the Client on the MySQL server to be managed.
 
Create a user that 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, press 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.
 
You can now add nodes from the GUI.

Performing backups and restores

Enabling the Backup Management feature of the 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 PMM Backup Feature

To use the backup management function, you need toPrerequisitesThere is.
  • PMM Client is installed
  • The user that PMM Client connects to MySQL has the BACKUP_ADMIN privilege.
  • 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 step, and the MySQL user has the necessary permissions, so we will now take care of the parts that require attention.
 
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:
 

Running 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[Name of the bucket created in S3]
Access Key[IAM key to use]
Secret Key[IAM secret key to be used]
 
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
Data modelOnly Physical is available at this time (February 2023)
LocationSelect the S3 you just created.
Once you have finished entering the information, press [Backup].
If successful, you will see this screen.
 
If the backup fails, the following message will be displayed, but you will not be able to 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 destination database and check that it has been properly deleted after restoration.
 
Restore.
Select ... on the far right of the list of backups 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 on which the backup was taken.
When I select Compatible services, I should be able to choose a restore destination, but it doesn't show me another server as an option. This is under investigation.
 
Once the restore is complete, the results will be displayed in the Restores tab.
 
Check to see if 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.
In particular, local backups and backups to another server are desirable.
This has been achieved when using MongoDB, so I would like to try it again once the MySQL backup function is released GA.
 
SRG is looking for people to work with us. If you are interested, please contact us here.