MariaDB/MySQL External Replication
In rare cases, it may be useful to maintain a replica instance of your MySQL/MariaDB database outside of Platform.sh.
Normally an automated backup is better for short-term usage and a
mysqldump for longer term storage, but in some cases the data set is large enough that
mysqldump is prohibitive. In that case, you can enable external replication using an extra permission.
Note that this guide covers the Platform.sh side; you will need to set up and maintain your own replica instance. Consult the MySQL or MariaDB documentation for steps to do so.
In order to set up replication you need to create a replication-enabled user. For each database that you’d like to replicate, you need to assign a
replication permission/role, under a corresponding
db: type: mysql:10.4 disk: 1024 configuration: schemas: - main endpoints: # Restate the default user to be used by your application. mysql: default:schema: main privileges: main: admin replicator: privileges: main: replication
This will create a
replicator user, and will grant read-only and table locking rights on the
main database (namely
Lock_tables_priv privileges) along with global replication rights (namely
Repl_client_priv privileges) and flushing rights (
Reload_priv used for flushing before reading the binlog position). If there is at least one
replication permission defined, the bin-logging will be enabled on the primary server, which is essential for the replication.
Even if you won’t be accessing the replication endpoint from your application, you still need to expose it to an application as a relationship so that you can connect to it over SSH. Add a new relationship to your application container:
relationships: database: db:mysql replication: db:replicator
Open the MySQL CLI to the
replication relationship, either by accessing the credentials while on the app container or using the following command.
platform sql -r replication
Now you need to prevent any changes to the data while you view the binary log position. You’ll use this to tell the replica at exactly which point it should start replicating from. On the primary server, flush and lock all tables by running
FLUSH TABLES WITH READ LOCK. Keep this session running - exiting it will release the lock. Get the current position in the binary log by running
SHOW MASTER STATUS:
mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.016 sec) mysql> SHOW MASTER STATUS; +-----------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-----------------+----------+--------------+------------------+ | binlogs.000002 | 1036 | dflt | | +-----------------+----------+--------------+------------------+
Record the File and Position details. If binary logging has just been enabled, these will be blank. Now, with the lock still in place, copy the data from the primary to the replica.
Login to the app container, then run:
# Dump the data from primary. Note that it will dump only the databases, which "replicator" user has access to. $ mysqldump --all-databases --single-transaction -h database.internal -P 3306 -u replicator -p > /path/to/dump.sql
Download the dump file, then move it to the server where your replica lives to import it.
# Copy the dump to your replica $ mysql -u root < /path/to/dump.sql
Note for live databases: You just need to make a local copy of the data, you don’t need to keep the primary locked until the replica has imported the data. Once the
mysqldump has completed, you can release the lock on the primary by running
mysql> UNLOCK TABLES;
As mentioned above you have to set up a replica on your own. Assuming that you have a running MariaDB/MySQL replica instance, give the replica a unique
server_id (distinct from primary). You can find out primary’s
server_id by running:
mysql> SHOW VARIABLES LIKE 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+
Then set a distinct
server_id number (e.g. server_id+1) in your replica config (e.g.
And reload the replica instance for the changes to take an effect.
You will need to set up an SSH tunnel from the replica server to the primary, tunneled through the application. To do so using the Platform.sh CLI, use
platform tunnel:open -p your-project-id -e master
Which will open local SSH tunnels to all services accessible from the application. In practice, you may be better served by setting up the tunnel manually using SSH. Consult the SSH documentation for the best way to do so.
The SSH connection will be interrupted every time the environment redeploys. For replication to continue you must setup an auto-restart for the connection. There are many ways to do so that are out of the scope of this documentation.
Once the data has been imported, you are ready to start replicating. Begin by running a
CHANGE MASTER TO, making sure that
MASTER_LOG_FILE matches the file and
MASTER_LOG_POS the position returned by the earlier
SHOW MASTER STATUS on the Platform.sh database. For example:
mysql> CHANGE MASTER TO MASTER_HOST='<the.host>', MASTER_USER='replicator', MASTER_PASSWORD='<your_replicator_password>', MASTER_PORT=3306, MASTER_LOG_FILE='binlogs.000002', MASTER_LOG_POS=1036, MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;
<the.host> will vary depending on the SSH tunneling configuration you have, and the
<your_replicator_password> can be obtained by running
Now start the replica with the
START SLAVE command:
mysql> START SLAVE;
Check that the replication is working by executing the
SHOW SLAVE STATUS command:
mysql> SHOW SLAVE STATUS \G
If replication is working correctly, the values of both
Slave_SQL_Running should be
Slave_IO_Running: Yes Slave_SQL_Running: Yes
In some cases, after applying primary’s dump to the replica and starting the replica, you might experience replication errors (
Slave_SQL_Running: No and
Error: <smth> in the output of
SHOW SLAVE STATUS \G above). Each of such errors needs a careful inspection, but you might be able to just skip some of them. For example:
mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; mysql> SHOW SLAVE STATUS \G
In case you have multiple errors you would need to repeat the steps above (preferred) or set
SQL_SLAVE_SKIP_COUNTER (which corresponds to skipping the next N events from the primary) to something greater.