DANSNOTES

MySQL AWS Replication (Draft)

Note: anywhere you see a word or phrase in curly braces it indicates that you need to replace it with your own value. For example, {RDS Endpoint} should be replaced with the DNS domain of you RDS MySQL instance.

Important

You need to be very careful how you connect to your "MySQL" database. If you connect using a MySQL 8 client (and probably others), it will default to using a 'utf8mb4_0900_ai_ci' connection collation, which is not supported by MariaDB 5.7 and probably other MySQL variants. Any databases created will include that collation and that will cause an error [1] when your slave server attempts to create the database using a collation that doesn't exist on the slave. Note, the server 'collation_connection' setting does not affect this.

See [2] for more details.

You can force the use of a compatible connection collation by using the "default character set" command-line option. Note 'rds-combined-ca-bundle.pem' is a publicly available certificate file from AWS.

mysql --force -u root -p --default-character-set=utf8 \
      -h {RDS Endpoint} \
      --ssl-ca=/path/to/rds-combined-ca-bundle.pem \
      --ssl-mode=VERIFY_IDENTITY

Overview

Configure parameter group
Grant slave access to the master server
Change slave configuration files
Configure slave

Grant slave access to the master server

I'm not sure if REQUIRE x509 works - I need to test.

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO {Account}@'{Slave IP Address}' IDENTIFIED BY '{Password}' REQUIRE x509;

Change slave configuration files

As alluded to above, for compatability's sake, I think it is easier to standardise on using utf8 mb4 collation and character sets. You just need to make sure that '/path/to/mysql-replication/' is a valid directory and is owned by the 'mysql:mysql' account.

First stop the MySQL slave:

$ sudo service mysql stop

Then edit the '/etc/mysql/mariadb.conf.d/50-server.cnf' file. Add the following after the [mysqld] section marker.

collation-server     = utf8mb4_unicode_ci
init-connect         = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

server_id            = 11
read_only            = 1
log_bin              = /path/to/mysql-replication/mysql-bin.log
relay_log            = /path/to/mysql-replication/mysql-relay.log
log_slave_updates    = 1
sync_binlog          = 1

Then restart the MySQL slave:

$ sudo service mysql start

Configure slave

This guide pre-supposes that you are starting from a "empty" master database and "empty" slave database. Therefore there is no attempt to create a master backup snapshot and restore that to the slave before enabling replication. If you need to do that, see [3] for more details.

Assuming both databases are "empty", use the 'SHOW MASTER STATUS' command to find the current binary log position of the master. For example,

master> SHOW MASTER STATUS;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.000004 |  1170095 |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)

Copy the following into a text editor, then update the File and Position values you receive from MySQL to update the appropriate values:

CHANGE MASTER TO MASTER_HOST                   = '{Instance domain endpoint}',
                 MASTER_USER                   = '{Master account}',
                 MASTER_PASSWORD               = '{Master password}',
                 MASTER_LOG_FILE               = 'mysql-bin-changelog.000004',
                 MASTER_LOG_POS                = 1170095,
                 MASTER_SSL                    = 1,
                 MASTER_SSL_CA                 = '/path/to/rds-combined-ca-bundle.pem',
                 MASTER_SSL_KEY                = '',
                 MASTER_SSL_CAPATH             = '',
                 MASTER_SSL_VERIFY_SERVER_CERT = 1;

First ensure the slave has stopped:

slave> STOP SLAVE;

Then paste the above into the slave:

slave> CHANGE MASTER TO ...

Then restart the slave:

slave> START SLAVE;

To find out if things are working use the following command.

slave> SHOW SLAVE STATUS;

Hopefully the first line will say: