MySQL replication slave using percona and docker

dockerperconareplication

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 percona image).

[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 Slave_IO_Running is 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

(databasename and 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.

Best Answer

The root cause of this issue was the absence of the relay-log option in the mysql.cnf file (or in this case, due to the docker volume mounts, the docker.cnf file). This lead to the creation and usage of files such as 89726507f176-relay-bin.000002 initially, where 89726507f176 is the host name of the machine (randomly assigned by the docker daemon when an image is created). When the container was stopped, removed and recreated, a new set of files was created and used (e.g. be0c801d95bc-relay-bin.000407) but this caused sync issues.

By explicitly specifying a value for relay-log in the docker.cnf file the container was able to be removed and recreated without problems.

As a side note, I suggested also that there was a problem with the /var/log/mysql directory not being mounted - this is not the case. If however you specify a value of log_bin = /var/log/mysql/mysql-bin.log for example, then this is a requirement. If you do not specify this path, it seems the binary logs are stored locally in /var/lib/mysql which is already mounted outside the container.

My final docker.cnf file is as follows:

[mysqld]
skip-host-cache
skip-name-resolve
bind-address        = 0.0.0.0

binlog-ignore-db = mysql
replicate-ignore-db = mysql

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

relay-log   = replication-1
server_id   = 1

Note: server_id = 2 on the replication slave.

Also note that without the relay-log option the command SHOW MASTER STATUS; returned no results on the master database container.

There is a possible outstanding issue yet which is that by default when you use docker stop it asks the container to terminate (by sending a SIGHUP to the docker entrypoint command) and if it doesn't terminate within 10 seconds it is forcefully stopped. I need to ensure taht this is given sufficient time to shut down as it could take a little while to sort itself out while under load, possibly resulting in data loss as a result.

Related Solutions

Mysql – Percona XtraDB Cluster and MySQL Replication

it seems the binlog doesn't include the new inserts

I'm not sure whether you're saying the binlog actually doesn't include them, and you have confirmed this with mysqlbinlog, or that it "seems" like it doesn't, because they don't replicate.

PXC needs log_slave_updates turned on at the node serving as master to the asynchronous slave, otherwise, not everything will be written to the master's binary log. This is very different than an ordinary MySQL server as master, where log_slave_updates will do nothing at all (unless the master is actually a slave to another master).

If that's not it, remove replicate_do_db and binlog_do_db and all of their related options from your configuration and then remove them from your brain. They should never be added unless you know exactly how they work, in your sleep. The simplest and by far most reliable replication configuration is, and will always be, replicate everything, which is the default.

Forget about binlog_format on the slave. It makes absolutely no difference unless the slave, itself, has other, subtended slaves... and if the master is using ROW format, the slave will still log in ROW format if you do indeed have it configured with subtended slaves. Also, the slave's binlogs (not to be confused with the relay logs) will not log statements received from an upstream master unless log_slave_updates is enabled on the slave.

The same thing goes for innodb_flush_log_at_trx_commit. It does not impact actual replication. It's a setting the determines a tradeoff between ACID compliance and performance.

Mysql – re-enabling a table for thesql replication

I don't use pt-table-sync because I don't agree with the approach it uses:

When synchronizing a server that is a replication slave with the –replicate or –sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly. This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place. However, the changes it makes on the master should be no-op changes that set the data to their current values, and actually affect only the replica. Please read the detailed documentation that follows to learn more about this.

^{— http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html}

My philosophy is "no, that's okay, you guys can go ahead and stay away from the master."

While it's true that a common cause of replication errors are related to things being changed directly on the slave, it's also true that if two servers have an identical set of data at an identical set of binlog coordinates, that's a valid place to begin replication. (I would also suggest that the other common cause of replication errors is people trying to replicate only some of their tables and not all, which is almost never a good idea, unless you really know what you're doing, in which case, you'll know better than to try it.)

If you do use it, and I'm sure a lot of people do, then I don't think you actually want to stop the slave. I think you need to keep the slave running to it can make those "no-op changes to the master" and watch them replicate over to fix the inconsistent tables. I don't know what it does with errors, perhaps it skips over them.

Here is one of the alternative approaches that I use.

You need three console windows for this. Do not disconnect your session from the master or you will lose the global lock. You need a console connection to the master and one to the slave, and you need to have your commands to do mysqldump set up and ready to go in a third window.

First, check the master's binlog coordinates and the slave's binlog coordinates to make sure the slave is not lagging.

master-mysql> SHOW MASTER STATUS;

+-----------------------+-----------+--------------+------------------+
| File                  | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+-----------+--------------+------------------+
| pri-master-bin.004421 | 142052212 |              |                  |
+-----------------------+-----------+--------------+------------------+

slave-mysql> SHOW SLAVE STATUS\G

    Relay_Master_Log_File: pri-master-bin.004421
      Exec_Master_Log_Pos: 142052212
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
    Seconds_Behind_Master: 0

There are more values included with SHOW SLAVE STATUS but these are the important ones. The Exec_Master_Log_Pos needs to be close to or identical to the master, and the log file the same.

Next, obtain a brief read lock on the master. This locks all of the tables for write allows the master to settle down long enough that we know the slave and master are at the same place in the binlog at this point in time.

master-mysql> flush tables with read lock;
Query OK, 0 rows affected (24.26 sec)

master-mysql>

Note that this sometimes takes a few seconds before your prompt returns.

Do not disconnect this session. You need this session active because it is holding the lock on the master. You can use it for the following, but don't close it.

Now, repeat the SHOW MASTER STATUS; on the master and SHOW SLAVE STATUS\G on the slave. You should find that they have settled down to the same set of binlog coordinates. Wait until they do. If they don't settle down quickly, you might want to drop the read lock on the master to figure out why not.

slave-mysql> STOP SLAVE SQL_THREAD;

This will stop the slave from executing any more events from the master, which is still locked.

In the third window, start a backup of the tables from the master.

shell> mysqldump --verbose --single-transaction database_name table1 table2 table3 table4 > dump.sql

-- Connecting to *hostname*...
-- Starting transaction...
-- Retrieving table structure for table...

As soon as you see "Retrieving table structure..." you can release the global read lock on your master, so your application can start writing to it again.

mysql-master> unlock tables;
Query OK, 0 rows affected (0.01 sec)

If your tables are InnoDB, the --single-transaction option should get a consistent backup from the master at a point in time during the few seconds that you had the global read lock on the master. Since you also stopped the slave at that point, the slave should be positioned at precisely the appropriate point in time for applying that backup.

When the backup of those tables is complete, apply it to the slave:

shell> mysql --host=slave.host.name database_name < dump.sql

At this point, you have restored the tables onto the slave in a form that should be identical to the way they appeared on the master at the time when the slave was stopped, and replication should resume when you remove the configuration not to replicate those tables, and then:

slave-mysql> START SLAVE SQL_THREAD;

Be sure your backups are solid before you begin.

Best Answer

Related Solutions

Mysql – Percona XtraDB Cluster and MySQL Replication

Mysql – re-enabling a table for thesql replication

Related Question