Streamlining MySQL Backup and Restoration with Docker, Percona Tools, and Remote Connections

In modern development environments, especially those adopting microservices or containerized architectures, handling MySQL backups and restorations across multiple hosts is crucial. In this blog post, we’ll walk through an advanced setup for managing backups from one machine to a MySQL instance running on a remote server, leveraging Docker and Percona tools. This setup is ideal for anyone in a distributed environment where databases are hosted across different machines or cloud services.

Project Overview

We have two critical machines in this scenario:

1. Host A: This is where we run Percona tools to handle MySQL backups. Host A contains all necessary files and configurations to connect to a remote MySQL instance. The tools used on Host A run within a Docker container, providing a clean and isolated environment for managing database tasks.

2. PC B: This machine runs MySQL 5.7 within a Docker container. It hosts the MySQL database that we need to back up and restore. PC B is exposed to Host A over the network, allowing remote database management.

Key Steps in Our Setup

We’ll go through the following steps to achieve our goal:

1. Setting up MySQL 5.7 in Docker on PC B.

2. Configuring network access allows Host A to connect to MySQL on PC B.

3. Setting up Percona tools on Host A to connect to the remote MySQL instance on PC B.

4. Running backups using Percona’s XtraBackup tool.

5. Restoring backups from Host A to PC B.

Step 1: Setting Up MySQL in Docker on PC B

To begin with, we need to ensure that PC B is running MySQL 5.7 in a Docker container and that it’s configured to allow remote connections. Here’s the docker run command to set up MySQL 5.7 on PC B:

docker stop mysql57  # Stop if any existing container is running
docker run --name mysql57 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=setupnewpassword -d percona/percona-server:5.7

This command creates and starts a MySQL 5.7 container with the root password set to 123mrsoft and exposes it to port 3306. This is necessary to make the MySQL service accessible over the network.

To verify that MySQL is running and accessible:

sudo netstat -tuln | grep 3306

You should see that MySQL is listening on the port 3306. If not, ensure that Docker is appropriately mapping the port.

Step 2: Configure MySQL for Remote Access

By default, MySQL may only allow connections from localhost. To make MySQL accessible remotely, we need to edit the MySQL configuration on PC B.

1. Log into the MySQL container:

    sudo docker exec -it mysql57 bash
   

2. Open the MySQL configuration file and allow MySQL to listen on all interfaces:

    nano /etc/mysql/my.cnf
   

3. Find the line that begins with bind-address And change it to:

    bind-address = 0.0.0.0
   

4. Restart the MySQL service within the container:

    service mysql restart
   

Step 3: Access MySQL from Host A

Now that MySQL is accessible over the network, the next step is to confirm that Host A can connect to the MySQL instance running on PC B.

On Host A, use the following command to test the connection to MySQL:

mysql -h <PC-B-IP-ADDRESS> -P 3306 -u root -p

If successful, you’ll be prompted for the root password (which we set as 123mrsoft), and you’ll gain access to the MySQL instance.

Step 4: Configure Percona Tools on Host A

On Host A, we have a Docker setup running Percona tools. Here’s the docker-compose.yml File on Host A, which sets up the environment:

version: '3.8'

services:
  percona-tools:
    container_name: percona-tools
    build:
      context: ./percona-tools
    environment:
      MYSQL_HOST: <PC-B-IP-ADDRESS>  # IP address of PC B
      MYSQL_USER: root
      MYSQL_PASSWORD: makenewpassword
      MYSQL_DATABASE: testdb
    volumes:
      - ./data/percona_data:/data
    networks:
      - backend

We set the environment variables here to connect Percona tools to the remote MySQL database on PC B. The key variables are:

• MYSQL_HOST: IP address of PC B, where MySQL is hosted.

• MYSQL_USER: MySQL root user.

• MYSQL_PASSWORD: Password for the MySQL root user.

• MYSQL_DATABASE: Name of the database we want to back up (e.g., testdb).

Step 5: Running Backups with Xtrabackup

Percona’s XtraBackup tool creates non-blocking, consistent backups of MySQL databases. Once the setup is complete, you can execute the following command on Host A to take a backup of the MySQL instance on PC B:

sudo docker exec -it percona-tools bash -c "xtrabackup --backup --target-dir=/data/backup --host=<PC-B-IP-ADDRESS> --user=root --password=123mrsoft"

This command will back up the MySQL database from PC B and store the backup in the /data/backup directory on Host A.

Step 6: Restoring the Backup to PC B

If you ever need to restore the backup from Host A to PC B, you can use the following command:

xtrabackup --copy-back --target-dir=/path/to/backup --host=<PC-B-IP-ADDRESS> --user=root --password=123mrsoft

Make sure that the target-dir is the path where the backup was saved on Host A.

Step 7: Automating the Backup Process

You can automate the backup process for efficient and reliable backups by adding a cron job on Host A. Here’s an example cron job that runs a backup every day at midnight:

0 0 * * * docker exec -it percona-tools bash -c "xtrabackup --backup --target-dir=/data/backup --host=<PC-B-IP-ADDRESS> --user=root --password=123mrsoft"

This cron job will ensure a fresh backup is created daily without requiring manual intervention.

Step 8: Security Considerations

While this setup is functional, there are several security concerns you should address:

1. Secure Passwords: Avoid hardcoding passwords in configuration files or Dockerfiles. Use environment variables or secret management tools.

2. Firewall Configuration: Ensure that PC B’s firewall only allows access to the MySQL port (3306) from trusted IPs.

3. SSL Connections: For secure data transmission, consider enabling SSL connections between the hosts. You can configure MySQL to require SSL for remote connections.

Conclusion

This setup provides a scalable and containerized solution for managing MySQL backups remotely. Using Percona tools in Docker on one Host and connecting to a remote MySQL server ensures that your backups are consistent, reliable, and isolated from your production environment.

You now have a powerful, automated backup system that can quickly adapt to various scenarios, including cloud-based environments or multi-database setups. With a combination of Percona tools and Docker, this system can easily be expanded to handle multiple database management tasks.

If you need further help with Docker, MySQL, or Percona tools, please reach out for more detailed guidance!