Solved

clone mysql

Posted on 2007-04-03
10
991 Views
Last Modified: 2008-02-01
Hello experts,

What is the best way to clone one mysql 4.1 server to another mysql 5.0 server. Both servers are on different hardware architecture so I assume that binary backup is not good procedure this time. Both servers are running on debian sarge. Is there anything I should keep in mind where clonning all databases? There is something like 2GB in around 200 databases running.

- I have just limitetd time
- i'm able to stop mysql server
- and it needs to be completelly transparent to clients

thank you
0
Comment
Question by:Luxana
  • 5
  • 3
10 Comments
 
LVL 30

Accepted Solution

by:
todd_farmer earned 500 total points
ID: 18847785
When you say different hardware architecture - do you mean 32 vs. 64-bit?  You should be able to:

1.  Stop the server.
2.  Copy the datadir to second server.
3.  Restart production server
4.  Start cloned server using 4.1
5.  Dump database contents using mysqldump
6.  Upgrade to 5.0
7.  Reload data.

It's possible that you could upgrade in place for steps 4-7 instead.  Just make sure you run mysql_upgrade.
0
 
LVL 10

Author Comment

by:Luxana
ID: 18847916
hi thanks for relpy...

the mysql 5.0 is on another server which i'm configuring right now and once the day come I do not want to upgrade from 4.1 to 5.0 on old server I just simply want to transfer everything what is on old server to new server

< When you say different hardware architecture - do you mean 32 vs. 64-bit?  
Yes

what dump command is the best for this stuff? I want to dump all databases including users and they passwords....

0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 18847934
probably:

mysqldump --all-databases --disable-keys --flush-privileges --single-transaction

(and your user name and password).

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
0
 
LVL 10

Author Comment

by:Luxana
ID: 18848030
mysqldump --all-databases --disable-keys --flush-privileges --single-transaction
It seems that this is for dump with 5.0 mysql

how about dump for 4.1
is it simple as :

mysqldump --all-databases --opt -p > db.sql
and on 5.0
mysql -p < db.sql

what if database does not exists? I remeber that there was some option to make sure that if database does not exists it will automatically create one.

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 30

Expert Comment

by:todd_farmer
ID: 18848039
Yes, that will work fine - the --all-databases option should include the CREATE DATABASE commands.
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 18848049
Also, you may want to use --add-drop-database to issue DROP DATABASE commands before the database is created (if it already exists).
0
 
LVL 10

Author Comment

by:Luxana
ID: 18848082
I think --opt will take care of that:
man mysql:

--opt  Same as  --add-drop-table --add-locks --all --extended-insert --quick --lock-tables

0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 18848105
no, --add-drop-table is different than --add-drop-database - but you can try and see!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now