Solved

clone mysql

Posted on 2007-04-03
10
1,004 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Fine Tune your automatic Updates for Ubuntu / Debian
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
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…

830 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