MySQL Forums
Forum List  »  Replication

Newby resetting replication query
Posted by: ian diddams
Date: June 13, 2019 08:45AM

mariadb 5.5.60-MariaDB

I have a LIVE DB in a database "bugtracker". that mariadb instance also has these databases

information_schema mysql performance_schema test

I have a DR DB in a database "bugtracker". that mariadb instance also has these databases

information_schema mysql performance_schema

Replication is set up for bugtracker.

I am sorting a failover procedure.

Failover: stop replication on DR mysql> stop slave; mysql> reset master; mysql> change master to master_host='';

Stop MySQL database on DR

Edit /etc/my.cnf on DR to change server-id to 1 (from 2) Start MySQL database on DR

then use DR as the "live" site.

FAILBACK: mysqldump bugtracker on DR import bugtracker on live

Stop the MySQL database on LIVE Stop the MySQL database on DR

Edit /etc/my.cnf on DR to change server-id to 2 (back from 1)

Start the MySQL database on DR Start the MySQL database on LIVE

re-enable replication from LIVE to DR Initial setups/configurations are already done obviously so these steps are all that is needed to restart replication.

ON LIVE: use bugtracker; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

record the information displayed e.g. +----------------------+----------+-------------------+------------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+----------------------+----------+-------------------+------------------------+
| mysql-bin.000001 | 107 | bugtracker | |
+----------------------+----------+-------------------+------------------------+

reset the grant GRANT REPLICATION SLAVE ON *.* TO replication_user@DR_IP IDENTIFIED BY 'PASSWORD';

ON DR use bugtracker; CHANGE MASTER TO MASTER_HOST='LIVE_IP',MASTER_USER='replication_user',MASTER_PASSWORD='PASSWORD',MASTER_LOG_FILE='<see above>', MASTER_LOG_POS=<see above>;START SLAVE;

check slave status SHOW SLAVE STATUS\G Check for lines Slave_IO_Running: Yes Slave_SQL_Running: Yes

That seems logically sufficient to me.

But a colleague is suggesting that having dumped DR and imported to LIVE I now have to recreate the entire SLAVE DB stuff all from scratch - which seems overkill to me. ie delete the DR Dbs nd recreate them then dump the entire LIVE to import to DB?

??

cheers

didds

Options: ReplyQuote


Subject
Views
Written By
Posted
Newby resetting replication query
1506
June 13, 2019 08:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.