One of the shortcomings of MySQL GPL is that it does not come with a first party online backup solution. With the release of MySQL 8.0.17 the CLONE plugin was introduced, this essentially integrated online backup as a plugin to the MySQL Server.
The MySQL 8.0 Reference Manual describes how to use the CLONE plugin to perform local clones (backups) here: https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-local.html
Doing local clones is incredibly useful and a really fast way of making an image backup. I would argue that the CLONE plugin is better for local image backups than competing solutions simply because the syntax is more brief and efforts were made to integrate CLONE into the server, thereby reducing the impact of performing CLONE operations.
The CLONE plugin can either clone to the server’s default data directory or to another directory specified in the CLONE command. I will demonstrate the latter usage for making online remote backups without modifying the data directory of the container.
You might be asking “but how do I restore CLONE backups?” The way online backups are done traditionally is no secret: make an image copy of the data directory and track the redo logs during the backup process. When restoring the backup, the redo logs are reconciled, then the server performs a recovery operation to apply the redo logs. To restore a clone, you simply delete all data from the destination data directory and copy the cloned data into the empty directory.
The Setup
My example assumes you have a MySQL 8.0.17 [or later] instance already running, the remaining setup will focus on enabling the CLONE plugin and creating a backup user on the source server.
Source server configuration:
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so'; Query OK, 0 rows affected (0.01 sec) mysql> create user backupuser@'%' identified by 'foo'; Query OK, 0 rows affected (0.01 sec) mysql> grant backup_admin on *.* to backupuser@'%'; Query OK, 0 rows affected (0.01 sec)
For this example I used backupuser@’%’, this is not a strict host definition and should be avoided, I used it for the sake of simplicity and to make testing with docker instances more simple. Follow security best practices when using this concept in the real world!
Creating the remote docker instance and setting permissions on the temporary backup directory:
# This is the destination directory where the backup will be stored [pedward@pooh ~]$ mkdir /path/to/mysql/backups # This is a throwaway docker instance, THE ROOT PASSWORD IS UNSET!!! [pedward@pooh ~]$ docker run --name=mysql8clone -v /path/to/mysql/backups:/tmp/clonebackup -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql/mysql-server:8.0.19 # The publicly available MySQL docker instances run mysqld as user mysql # We must make the bind mounted directory owned by the mysql user [pedward@pooh ~]$ docker exec -i mysql8clone chown -R mysql.mysql /tmp/clonebackup
The example above takes liberties with security practices, if you use docker for performing remote backups, you should take proper precautions to secure your docker instance!
Setting up and running the backup on the docker instance:
# Open a mysql session [pedward@pooh ~]$ docker exec -it mysql8clone mysql -uroot mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so'; Query OK, 0 rows affected (0.01 sec) # MySQL requires any CLONE source servers to be in the donor list mysql> set global clone_valid_donor_list='172.17.0.3:3306'; Query OK, 0 rows affected (0.00 sec) # SSL should be used in an environment where security is important mysql> clone instance from 'backupuser'@'172.17.0.3':3306 identified by 'foo' data directory = '/tmp/clonebackup/backup03192020' require no ssl; Query OK, 0 rows affected (0.63 sec)
Those are the basic steps of doing a network based backup of MySQL with the CLONE plugin, you can tune and tweak the idea to have persistent backup instances, etc. You could even automate the process by creating an event that runs in the docker instance to kick off nightly backups!