Need suggestion with REPLACING the MASTER Server !!!

systemali · December 30, 2020, 4:21pm

Hello,

My client has replaced his “Master” DB server and hence the replication server is not working any more.

I need to know, What are the new changes that i need to do on both the NEW DB and the Replication server !!!

Do i need to redo all the steps involved for setting up replication OR

Do i need to only change GRANT privileges on the master and replication server ?

Please suggest ?

Thank you

scott.nemes · December 30, 2020, 4:30pm

Did you create the new master with a backup from the slave or from an old backup of the original master? Before setting up replication again, you just need to make sure that your master and slave have the same data, either by restoring them both from the same backup or by restoring the master from a new backup of the slave if the slave has the most up to date data.

Once you give more details on what you actually did, I can try to give you more help.

systemali · December 30, 2020, 4:30pm

Hello Scott,

Thank god you replied

The NEW server has been created from the data from the original master.

It is 2 days that this new server is up and the replication has stopped since then.

So, does that mean i have to redo the whole replication server once again ?

Please assist !!!

Thank you a ton !!

scott.nemes · December 30, 2020, 4:30pm

At this point you are likely better off just rebuilding the slave from a fresh backup of the new master (http://www.percona.com/doc/percona-xtrabackup/howtos/setting_up_replication.html). If you knew what the log file and position of the new master was when originally setup, and if you knew for sure that the slave was synced when the master was rebuilt, it could be possible to restart replication that way. But the likelihood of replication errors and inconsistent data is pretty high going that route without having pretty solid knowledge of what you were doing and preparing for that ahead of time.

systemali · December 30, 2020, 4:30pm

Thanks a bunch Scott

Yes, well said, i do not have solid knowledge and hence rebuilding a new one is the best option

I’ll keep this thread update dated, If i need any further assistance in this regards.

Thank you !!

systemali · December 30, 2020, 4:30pm

Scott,

My first, probably a silly query, since i will be using the same “replication” server that i had used earlier,
Do you suggest i just drop the existing DB on this server or do i have a fresh installation of the new OS and redo the whole thing one by one ?

Thank you,

scott.nemes · December 30, 2020, 4:30pm

If you are using Xtrabackup and following the steps in the Percona guide I posted, then you should be able to just remove the data directory contents on the slave, copy in the prepared backup you took from the master, set proper ownership permissions, start MySQL, and then configure replication (the CHANGE MASTER TO part) based on the steps in the same guide.

systemali · December 30, 2020, 4:30pm

Oh Yes, I will be using Xtrabackup

Thank you for the brief

systemali · December 30, 2020, 4:30pm

Hello Scott,

I am getting this error, when i try to backup the Db on the Master server :-

Secondly, Do i need to shutdown the DB on the Master server, before taking the backup ?

Please suggest,

Thank you,

systemali · December 30, 2020, 4:30pm

Scott,

Can i just backup the DB on the Master server using this command :-

innobackupex --stream=tar ./ | gzip - > backup.tar.gz

Since the syntax in the above post did not work, I guess i need to STOP the DB before issuing any of the above commands.

Can you please confirm the same ?

Awaiting your update.

Thank you,

systemali · December 30, 2020, 4:30pm

My apologies , My apologies

My fault, I figured out the error with backup …i thought hot backup was not possible, But i now know/…and have managed to backup up my DB on the Master server

Thank you

scott.nemes · December 30, 2020, 4:30pm

Glad you got it worked out! Yes the main benefit of Xtrabackup is the ability to backup a running server with little impact (if using all InnoDB/XtraDB). =)

systemali · December 30, 2020, 4:30pm

Scott,

In in the link that describes the procedure for setting up replication, It talk about “SlavePass”,

Can you clarify, What password of the slave is it ? Is it the mysql or the root password of the slave ?

Thank you,

systemali · December 30, 2020, 4:30pm

Scott,

Secondly, when i issue “grant” statement on the MASTER server :-

TheMaster|mysql> GRANT REPLICATION SLAVE ON . TO ‘root’@’$slaveip’ IDENTIFIED BY ‘$slavepass’;

and then run the ‘show grants’ command on the MASTER, I DO NOT SEE the grants issues above, :-

What i see is :-

mysql> show grants;
±---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
±---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON . TO ‘root’@‘localhost’ IDENTIFIED BY PASSWORD ‘*B54DAEC9554BF364E94BE34320BB868A24446646’ WITH GRANT OPTION |
| GRANT PROXY ON ‘’@’’ TO ‘root’@‘localhost’ WITH GRANT OPTION |
±---------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

What could be the reason for this ?

Thank you

systemali · December 30, 2020, 4:30pm

Scott

Firstly, Thank you soooo very much for your continued assistance all this while

I’d like to inform you, I have finally FINISHED the setup of the replication server with quite a few glitches !!!

Thank you once again !!!

God Bless !!!

scott.nemes · December 30, 2020, 4:30pm

You are welcome; I am glad you got it worked out! Good luck and let us know if you have more questions. =)