I'm trying to configure a DR instance of MariaDB (10.1.13) using master-slave replication.
I was able to establish the master-slave relationship with GTID by locking the tables on master, backing up the database, restoring the database on the slave and running this on the slave:
CHANGE MASTER TO MASTER_HOST='master.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='masterlogfile', MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;
I was able to confirm that the replication is working is expected. However, I'm struggling with figuring out the process of converting the current slave to a master and master to the slave (reversing the replication flow).
When I run the following on the ex-master I get errors in the
SHOW SLAVE STATUS \G; (
No and the error is
Error 'Operation CREATE USER failed for 'replication_user'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replication_user'@'%' IDENTIFIED BY 'somepassword'', I'm not sure why it's trying to create this user after running the query below):
CHANGE MASTER TO MASTER_HOST='slave.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='slavelogfile', MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;
I have a feeling I'm doing something wrong and that my scenario is not supported. If it's not supported, what's the best way of implementing asynchronous kind of replication (analog of AlwaysOn in MS SQL) for MariaDB to support failing over to the slave and failing back?