I am trying to run a MySQL replication slave in a docker container. We are running MySQL 5.7.24-27-log in production and it's from the percona repository (Ubuntu 18.04).
I have used
xtrabackup to backup, prepare and ship a starting data set for replication, then I started the percona docker image (
docker pull percona) like so:
$ docker run --name mysql-replication -v /replication/data:/var/lib/mysql -v /replication/docker.cnf:/etc/mysql/docker.cnf:ro -e MYSQL_ROOT_PASSWORD=xxxx -P -d percona
My docker.cnf simply notes the server-id (I copied it from the
[mysqld] skip-host-cache skip-name-resolve bind-address = 0.0.0.0 server-id = 4
After then using
CHANGE MASTER etc. I have the replication running just fine.
My intention (as per the volume mount
-v /replication/data:/var/lib/mysql) is to keep all of the MySQL data on the host machine, and treat the replication docker container as ephemeral, i.e. no state held in the container. It should also be easy to start up another replication container should I need one by stopping the existing container, copying the data elsewhere, changing the
server-id and running a new container.
To test this, after it was set up and running properly (I watched
Seconds_Behind_Master drop down to
0), I figured I should be able to delete the container and recreate it, and the replication would still work fine. I therefore tried this:
$ docker stop mysql-replication $ docker rm mysql-replication $ docker run ... // same command as before
When I do this and connect to MySQL running in the container I find that
No, and after starting it (
START SLAVE;) I get the following (as seen in
SHOW SLAVE STATUS;):
Last_Error: Could not execute Update_rows event on table databasename.tablename; Can't find record in 'tablename', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000681, end_log_pos 9952
tablename are real database and table names)
At first I thought that I had probably mucked something up but I have tried this a number of times now to try and solve the problem. Using
docker diff mysql-replication shows no changes to the running container that seem to be of significance:
$ docker diff mysql-replication C /run C /run/mysqld A /run/mysqld/mysqld.pid C /var C /var/log A /var/log/mysql
Googling has suggested that I need to use
RESET SLAVE; and
START SLAVE; but this doesn't seem to resolve it - it's like the data (outside the container) is no longer in sync with the master and replication therefore cannot continue.
Can anyone pick holes in what I'm doing please?
Thanks so much.