?
Solved

clone mysql

Posted on 2007-04-03
10
Medium Priority
?
1,018 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 30

Accepted Solution

by:
todd_farmer earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

752 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