Pop-Up shops are those short lived stores at malls and other places, often times they are kiosks. They serve to satisfy temporal demands like nano quadcopters or engraved keychains. In this context you can create MySQL instances that are short lived, easily provisioned, and easily disposed of.
Imagine you are a developer, or the DBA who has to tell a developer when their code breaks, and you would like an easy way to validate code against the production schema, but not impact your production systems?
This recipe makes some assumptions:
- You have a MySQL slave or a secondary Innodb Cluster instance to CLONE from
- You are using MySQL 8.0.17 or later
- You don’t have hundreds of gigabytes to terabytes of data
If you have a lot of data in your production environment, this won’t be a viable solution, but if your data is in the 10s of gigabytes, this could work for you.
I’m going to present 2 options: 1) A completely standalone transient instance of MySQL 2) A semi-persistent instance of MySQL that can live on an external encrypted SSD or other secured storage.
Setup
Both of the options I present will require similar configurations on the donor side of the CLONE operation. The proposed donor is an Innodb Cluster secondary (read-only) node, or an existing replication slave that keeps a current copy of your production data. I don’t advocate doing ad-hoc CLONE operations on a production server, such an operation could lead to a performance impact.
Setup is much the same as my previous article MySQL 8 Network Backup Using Docker and CLONE. You need to be careful in an Innodb Cluster because you can’t execute any operations which would create transactions on the secondary nodes. Because of this limitation you must execute the following statements on the Primary node, they will trickle down to the Secondary nodes via replication. For standalone asynchronous replication slaves, you can execute these statements on the slave, but it will create local transactions that form a subset of transactions that will not be part of the GTID set sent from the Master. If your replication Slave must be perfectly in sync with the Master, then you must execute the statements on the Master server.
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so'; Query OK, 0 rows affected (0.00 sec) mysql> create user docker_popup@'%' identified by 'docker'; Query OK, 0 rows affected (0.01 sec) mysql> grant backup_admin on *.* to docker_popup@'%'; Query OK, 0 rows affected (0.00 sec)
The above statements are fairly self-explanatory: we are installing the CLONE plugin and creating a user which can use the CLONE plugin to make ad-hoc Pop-Up Docker MySQL instances. If you want to learn more about the CLONE plugin, here is the link to the MySQL manual page: https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
Transient Pop-Up Instances
A transient Pop-Up instance is intended to be torn down as soon as the immediate need is fulfilled. You may want to use these type of instances if you don’t have a lot of data to manage, since the CLONE operation can be expensive if you have a lot data. Here is an example command for creating a transient Pop-Up MySQL instance:
docker run --name=mysql8popup --restart unless-stopped -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql/mysql-server:8.0.19
Let’s unpack that command a bit, since there are a few nuances that deserve explanation. The --restart unless-stopped option tells docker to restart the container if mysqld exits, but you didn’t use the docker stop command. This is done because the CLONE operation will kill the server and we want docker to automatically restart it after the CLONE completes. The MYSQL_ALLOW_EMPTY_PASSWORD variable sets the root password to empty, this will change after the CLONE operation. Finally, the exact MySQL version you want to execute is specified as 8.0.19. This can simply be 8 to indicate the latest release, or you can perform regression testing by intentionally choosing an older release.
Semi-Persistent Pop-Up Instances
This next example is what I call semi-persistent, it is not intended to be long-lived, but some schemas can contain a fair amount of data, for this reason it makes sense to persist this data in a secure and fast method. There are inexpensive USB SSDs which can hold 250GB or more of encrypted data. If you use a laptop for development there is a good chance you embrace #donglelife and hanging storage off a USB port is second nature ?.
To create a semi-persistent instance, the MySQL data directory is stored on an encrypted SSD, this way you can stop and start the docker instance and have a copy of the data in a secure place. When storing data in a location other than the default docker filesystem, you need to take a couple of preparatory steps to allow the dockerized MySQL to access the data directory.
mkdir -p /path/to/mysql/docker_popup chown mysql:mysql /path/to/mysql/docker_popup
The above example assumes you have mounted your encrypted SSD under /path/to/mysql and you will create a directory named docker_popup to hold the actual data directory. The mysqld binary inside the container runs as user mysql which has uid and gid 27. If you don’t have MySQL tools otherwise installed on your machine, the username mysql and group mysql will be unknown, so you will need to substitute 27 for both of those.
Creating the semi-persistent docker Pop-Up instance takes a little different syntax than the transient example:
docker run --name=mysql8popup --restart unless-stopped -v /path/to/mysql/docker_popup:/var/lib/mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql/mysql-server:8.0.19
Where our semi-persistent example differs is that we use the -v docker option to specify a bind mount. The --mount option is only available in docker 17.06 and later, which may not be available on your OS. We tell docker to use our encrypted SSD for the data directory and mount it as /var/lib/mysql inside the container.
Populating the Pop-Up with CLONE
This step is where the 2 options converge, because the CLONE operation is the same whether you are making a transient or semi-persistent Pop-Up instance. It is important to note that once you execute CLONE, the Pop-Up instance will have all the same data as the donor, including the privilege tables. This means your usernames, host restrictions, and passwords will be the same as the donor; the user created when the docker instance was run is gone and the privileges from the donor instance are in effect. You need to make certain that you have a username@host combination that will allow you to login to the Pop-Up CLONE, otherwise you will have locked yourself out!
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so'; Query OK, 0 rows affected (0.00 sec) mysql> set global clone_valid_donor_list='172.17.0.3:3306'; Query OK, 0 rows affected (0.00 sec) mysql> clone instance from 'docker_popup'@'172.17.0.3':3306 identified by 'docker' require no ssl; ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process). mysql>
The CLONE procedure above is fairly straightforward, but I want to point out a couple of options: When cloning from an Innodb Cluster Secondary node, you will most likely be using the read-only port of MySQL Router, which is 6447 by default. If you are cloning from a standalone asynchronous slave, the port will typically be 3306. Whichever donor you choose, you must change both occurrences of 172.17.0.3:3306 in the above example to match your Innodb Cluster read-only IP:port or your async slave IP:port.
You will note the error message at the end, ERROR 3707, this is the result of using CLONE inside of docker to replace the running data directory. MySQL doesn’t know it’s running inside docker and doesn’t know that we told docker to restart mysqld if it exits, so MySQL emits the above error. The container blinks out of existence briefly when MySQL restarts, so your mysql cli session exits too. You simply need to invoke the docker command again to attach to MySQL:
[pedward@pooh sql]$ docker exec -it mysql8popup mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 8.0.19 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 8 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.19 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 8 sec Threads: 2 Questions: 16 Slow queries: 0 Opens: 132 Flush tables: 3 Open tables: 35 Queries per second avg: 2.000 -------------- mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 5 rows in set (0.01 sec)
The above is from a Pop-Up instance that cloned a donor which contained the World example schema.
Conclusion
Docker can be an incredibly useful tool for developers when combined with MySQL, the CLONE plugin introduce in MySQL 8.0.17 is incredibly powerful and easy to use, combined there are many useful tasks that can be completed with little overhead. The intended purpose of this article is to demonstrate how workflow problems can be easily solved without heavy lifting, and with a little effort I’m sure a crafty DBA could script this into their environment. I find Docker to be incredibly useful for performing bracketed regression testing, what will you use Docker and MySQL for?