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; (Slave_SQL_Running is 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?


I went with master-master replication. To prevent connections to the "passive" copy of the master I used a script to delete all users (except the DBA and the replication one) which makes sure to prevent writes on the "passive" copy.

As part of the failover process the users will be transferred to the "passive" and dropped from the "active".

This basically achieves what I was looking for.

  • Simpler is to SET read_only = ON;. It still allows SUPER do writes, but no one else. – Rick James Aug 17 '18 at 0:23
  • And, yes, "Master-Master" is the right way to have it set up without having to do any CHANGE MASTER during failover. – Rick James Aug 17 '18 at 0:27
  • You can use innodb-read-only = on to prevent writes even from SUPER – Kedare Nov 27 '18 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.