Migration of live MySql database via replication

Good day

I have to migrate live MySql database to the new physical server with no downtime
After researching various scenarios I found that best solution would be to create a replicated database on the new server,
make sure that databases are in sunc and bring old database server down
We may experience slight delay, but no more then that
However, new server had a MySql database already, which is a Master database and it has a replicated slave

My question: is it possible to reconfigure new server to be a slave to old server while remaining to serve as a master to it's replicated server
-- or --
is it possible to have master-to-master replication between old and new servers
--or--
there is another scenario

Thank you for your help,

Alex

mattyinteractiveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wolfgang_93Commented:
It is possible to set up MASTER/MASTER or MASTER/MASTER/SLAVE in MySQL.

But it may not be desirable, unless you absolutely trust MySQL replication to never
allow either of the 2 masters to get out of sync.

Should 2 masters get out of sync you are going to have to track down every transaction
that is out of sync and fix it manually (Example: did a new record get added to master 1 and
missed by master 2 or did a record get deleted by master 2 and missed by master 1?)

0
mattyinteractiveAuthor Commented:
Hi Wolfgang,
thank you for reply

May I ask, in your opinion - what is the best way to migrate live MySql database with minimum downtime?

Database size is about 140 GB (actually, there are several databases)

How about that:
- run mysqldump of Old server
- restore that dump on New server
- keep sending binary logs from Old to New and keep restoring those binary logs on New
- cut off Old server

Please advise

Thank you

0
wolfgang_93Commented:
Do you currently do a regular mysqldump of all your databases on the live
MySQL server?

What you describe should work (test first to make sure you get this down
smoothly):
-  restore the dump to the new server
-  stop the current server
-  copy over the binary logs from old to new
-  roll forward the logs to latest transaction
-  switch over the application to the new server
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mattyinteractiveAuthor Commented:
Hi Wolfgang,

Thank you for your advise

Sincerely,

Alex
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.