Not the answer you need?
Register and ask your own question!

Need suggestion with REPLACING the MASTER Server !!!

systemalisystemali AdvisorCurrent User Role Beginner
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

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
  • systemalisystemali Advisor Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
  • systemalisystemali Advisor Current User Role Beginner
    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 !!
  • systemalisystemali Advisor Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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.
  • systemalisystemali Advisor Current User Role Beginner
    Oh Yes, I will be using Xtrabackup

    Thank you for the brief :)
  • systemalisystemali Advisor Current User Role Beginner
    Hello Scott,

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

    [[email protected] mysql]# innobackupex --user=root --password=mypass123 /root/home/

    InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
    and Percona Ireland Ltd 2009-2012. All Rights Reserved.

    This software is published under
    the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

    130516 14:19:33 innobackupex: Starting mysql with options: --password=xxxxxxxx --user='root' --unbuffered --
    130516 14:19:33 innobackupex: Connected to database with mysql child process (pid=29230)
    innobackupex: Error: mysql child process has died: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

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

    Please suggest,

    Thank you,
  • systemalisystemali Advisor Current User Role Beginner
    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,
  • systemalisystemali Advisor Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    systemali wrote: »
    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

    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). =)
  • systemalisystemali Advisor Current User Role Beginner
    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,
  • systemalisystemali Advisor Current User Role Beginner
    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 [email protected] |
    +
    +
    | 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
  • systemalisystemali Advisor Current User Role Beginner
    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 !!!! :D

    Thank you once again !!!

    God Bless !!!
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    systemali wrote: »
    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 !!!! :D Thank you once again !!! God Bless !!!

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

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.