Backup percona-server:8.3 from a docker container on Ubuntu 20.04

MySQL & MariaDB Percona XtraBackup
,
1

Welcome everyone at this time of day

I apologize in advance, but I wrote this text using a translator. Now let’s get down to business.

Maybe someone has encountered such a problem or can help / explain / give some ideas (thanks in advance). The configuration is like this:

  • There is an Ubuntu 20.04 VM, percona-server:8.3 is deployed on it in docker, I run it via docker compose with the following parameters:

db_cp:
user: mysql
image: percona/percona-server:8.3
restart: always
cap_add:
- SYS_NICE # CAP_SYS_NICE
volumes:
- ./docker/mysql1/db:/var/lib/mysql
- ./docker/mysql1/config1.cnf:/etc/my.cnf/
- ./docker/mysql1:/var/log/mysql
# - ./docker/mysql1/logs/mysqld.error.log:/var/log/mysqld.error.log
ports:
- 0.0.0.0:3389:3306
environment:
MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
MYSQL_DATABASE: yii2-starter-kit
MYSQL_USER: ${DB_USERNAME}
MYSQL_PASSWORD: ${DB_PASSWORD}
TZ: ${TZ}

  • Percona server configuration file:
    [client]
    default-character-set = utf8mb4

[mysqld]
max_allowed_packet=1073741824
init-connect=‘SET NAMES utf8mb4’
collation-server = utf8mb4_unicode_ci
character_set_server=utf8mb4
#skip-character-set-client-handshake
default_authentication_plugin= mysql_native_password
group_concat_max_len=100000
log_error_verbosity = 1
log_error_suppression_list=MY-013360

innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
sort_buffer_size = 1048576

innodb_io_capacity = 1000
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 11
innodb_log_file_size = 512M

information_schema_stats_expiry = 0

binlog_expire_logs_seconds = 259200

bind-address = 0.0.0.0

log_bin=/var/log/mysql/mysql-bin.log
server-id=1
gtid_mode=ON
enforce-gtid-consistency=ON

  • I deployed percona xtrabackup on Ubuntu itself and I want to configure backup to it, and not to a container. The config with which I launch the backup is like this:
    [xtrabackup]
    user = root
    host = 127.0.0.1
    port = 3389
    password = root
    target-dir = /home/anonymous/app/backups/mysql1
    datadir = /home/anonymous/app/crm/docker/mysql1/db
    log-bin = /home/anonymous/app/crm/docker/mysql1/
    log-bin-index = /home/anonymous/app/crm/docker/mysql1/mysql-bin.index

The problem is the following: I was able to make a backup using percona, prepare it and deploy it, but when I start the docker-compose service I get this message:

anonymous@anonymous:~/app/crm$ docker compose up db_cp
[+] Running 2/2
:heavy_check_mark: Network crm_default Created 0.1s
:heavy_check_mark: Container crm-db_cp-1 Created 0.0s
Attaching to db_cp-1
db_cp-1 | 2024-05-20T10:25:44.933490Z 0 [System] [MY-015015] [Server] MySQL Server - start.
db_cp-1 | 2024-05-20T10:25:45.167045Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0-1) starting as process 1
db_cp-1 | 2024-05-20T10:25:45.174591Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
db_cp-1 | 2024-05-20T10:25:46.439586Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
db_cp-1 | mysqld: File ‘/home/anonymous/app/crm/docker/mysql1/.000004’ not found (OS errno 2 - No such file or directory)
db_cp-1 | 2024-05-20T10:25:46.541508Z 0 [ERROR] [MY-010958] [Server] Could not open log file.
db_cp-1 | 2024-05-20T10:25:46.541523Z 0 [ERROR] [MY-010041] [Server] Can’t init tc log
db_cp-1 | 2024-05-20T10:25:46.541528Z 0 [ERROR] [MY-010119] [Server] Aborting
db_cp-1 | 2024-05-20T10:25:48.642699Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.3.0-1) Percona Server (GPL), Release 1, Revision fcee26ff.
db_cp-1 | 2024-05-20T10:25:48.642715Z 0 [System] [MY-015016] [Server] MySQL Server - end.
db_cp-1 exited with code 0

I don’t understand why he doesn’t see the file in docker Volume, but it’s there. Maybe it’s broken or something. I look forward to your advice and will consider any help. Thank you in advance and have a nice day everyone!

1 Like
2

The issue you’re encountering is likely due to path discrepancies or permissions problems between your host system and the Docker container. Here are several steps you can take to troubleshoot and resolve the problem:

  1. Verify Volume Mounting: Ensure that the volume paths specified in your docker-compose.yml file are correctly mounted and that the paths are accessible within the container. The key volumes in your configuration are:
volumes:
  - ./docker/mysql1/db:/var/lib/mysql
  - ./docker/mysql1:/var/log/mysql

Check Permissions: Make sure the user running the Docker container has the necessary permissions to access the files in the mounted volumes. You can adjust the permissions using chmod and chown commands on the host system.

  • Validate File Paths: Confirm that the file ./docker/mysql1/.000004 exists on the host system. The error message suggests that the MySQL server cannot find this file.
  • Check Docker Logs: Inspect Docker logs for more details. Sometimes, additional context can be found by looking at the logs:
docker logs <container_id>
  1. Adjust the Configuration: The log-bin and log-bin-index paths in your Percona XtraBackup configuration might need to be adjusted to match the actual file locations in the container. Ensure these paths are correct relative to the Docker container’s filesystem.
  2. MySQL Configuration: Ensure that your MySQL configuration file (config1.cnf) does not have any misconfigurations related to file paths. You might want to temporarily simplify your configuration to isolate the issue.

Steps to Verify and Adjust:

  1. Verify Mounted Volumes in Container: Start a temporary shell session inside the container to inspect the file system directly:
docker run -it --rm --entrypoint /bin/bash percona/percona-server:8.3

Once inside, navigate to the mounted directories and check for the presence of the expected files.
Check for Missing Files: Verify if the file ./docker/mysql1/.000004 is present:

ls -l ./docker/mysql1/.000004

Permissions: Adjust permissions if necessary:

sudo chown -R $(whoami):$(whoami) ./docker/mysql1
sudo chmod -R 755 ./docker/mysql1

Recheck Docker Volumes: Confirm that the volume mounts correctly within the Docker container:

docker inspect <container_id> | grep -i mount

Modify docker-compose.yml: As a temporary measure, you can try removing/commenting out specific volume mounts to see if the issue persists. For instance, comment out the log_bin path to see if it starts correctly without it:

# log_bin=/var/log/mysql/mysql-bin.log

By methodically checking these areas, you should be able to identify where the discrepancy lies. If the issue persists, try sharing the complete output of the Docker logs and the exact structure of your mounted volumes on the host system for a more detailed analysis.

1 Like
3

Good day.

Thanks for the answer, but I was already able to solve this problem on my own: the problem was that xtrabackup did not have enough permissions to read binary log files, which were located along the path ./docker/mysql1:/var/log/mysql. Having solved this problem, I was even able to set up a script that automates the process of creating a backup copy of my Percona-server) But, if it is not difficult for you, I would like to clarify a slightly different question. Specifically, I am now interested in restoring a backup over a certain period of time when I have a full, differential, and incremental copy of my database.

Question context: Every morning at 2 am I do a full backup of my database. Next, from 4 a.m. to 11 p.m. every hour I start creating a differential copy based on my full backup, and from 7 a.m. to 10 p.m. at 20 and 40 minutes of every hour I start an incremental backup based on the last differential backup.

Question: if I first wanted to restore a backup, say, at 7 a.m. and had already performed the appropriate operations (unzipped a full backup and a differential backup at 7 a.m. + prepared them with the

xtrabackup -prepare

command), but then I decided that I wanted to deploy the backup for 6:40, then in this case, how can I return the full backup to its original state, that is, before applying the differential backup at 7 am? So far I haven’t found information on this anywhere. Or is such functionality not provided at all, and in order for me to deploy several copies at different periods of time, I must constantly make several copies of a full backup of my database?

I would be very grateful if you could clarify this point for me.

4

Restoring backups to a specific point in time using Percona XtraBackup can indeed be challenging, especially when you need to move between different backup stages (full, differential, and incremental). To answer your question, once you apply a differential or incremental backup to a full backup, you cannot revert the full backup to its original state directly. You would need to maintain separate copies of the full backup if you plan to restore to various points in time without re-running the full backup process.

1 Like
5

All clear. So I will take this feature into account when deploying backups