Solved

clone mysql

Posted on 2007-04-03
10
1,008 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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

734 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