0

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?

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
3
  • Simpler is to SET read_only = ON;. It still allows SUPER do writes, but no one else.
    – Rick James
    Commented Aug 17, 2018 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
    Commented Aug 17, 2018 at 0:27
  • You can use innodb-read-only = on to prevent writes even from SUPER
    – Kedare
    Commented Nov 27, 2018 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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