Welcome to WebmasterWorld Guest from 54.225.38.53

Forum Moderators: travelin cat

Message Too Old, No Replies

Moving MySQL databases from one server to another

     
10:14 pm on Mar 16, 2005 (gmt 0)

Full Member

10+ Year Member

joined:May 3, 2004
posts:300
votes: 0


This is trickier than I thought. I'm trying to get 27 MySQL tables onto our new server. At first, I tried PhPmyAdmin, but that doesn't seem to like the file size. Now I'm trying Cocoa with more success, but it stops loading when it comes across errors in the bigger databases. Our biggest table is 130 Megs.

I'm wondering if I should try to load the structure first and then put the data in from a text file using PhpmyAdmin.

Any thoughts on how to get this database moved to our new server are appreciated. It's a G5 dual-processor Xserve that we're anxious to get up and running.

Thanks.

11:20 pm on Mar 16, 2005 (gmt 0)

Preferred Member

10+ Year Member

joined:Dec 3, 2004
posts:445
votes: 0


Go to terminal and issue the following command...

% mysqldump --all-databases -u root -p > ~/Desktop/databases.sql

This will create a text file called "databases.sql" on your desktop, and the file will contain all of the commands you need to re-create your databases on your new server.

Then, to load the new databas, just copy the *.sql files over, set up mysql, and use this command in terminal:

% mysql -u root -p < /path/to/mysql_databases.sql

That will get your data back into database. You can then use your mysql terminal to set up the definitions that are required.

For more info, read the manual for mysqldump. I have only done this once, when we moves from our tower to the Xserve, but the manual proved to be priceless.

Hope this helps.

-- Zak

11:24 pm on Mar 16, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1187
votes: 6


MySQL databases are stored in a very simple method:

dir = database names
files in dir are tables and indexes etc.

so locate where your databases are located - possibly something like:

/var/lib/mysql

In here you'd have all your database dir's, in those dir's the tables.

Now that you have found them stop MySQL and just tar zip the directory structure.

tar -zcf mysqldatabases.tar.gz *

Now you have all your MySQL stuff in one zipped file. Copy this to the new server's data directory and unzip / untar it.

Voila.

1:20 am on Mar 17, 2005 (gmt 0)

Full Member

10+ Year Member

joined:May 3, 2004
posts:300
votes: 0


We're moving from a shared server to a colocated Xserve at the same hosting company. I've never had to do much with the database besides simple queries and inserting data, so your solutions are a bit out of my league - I might not have the access required, but I can probably ask my hosting company to do it.

I'll try to dig into the manual to learn more about data management in MySQL now that we're on our own box. For updates, I'm used to emptying the tables and loading new data as text files in PhPmyAdmin. If you guys have any suggestions for a better system, please let me know. I store our data locally in Filemaker.

4:35 pm on Mar 17, 2005 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 1, 2003
posts:815
votes: 0


You might want to try out the MySQL front-end called Navicat -- it's got some easy data transfer tools. (It's not free but has a demo period.)

Loading data into and from text files would probably be OK, but it gives you the opportunity to make more mistakes than SQL dump files do.

4:53 pm on Mar 17, 2005 (gmt 0)

Full Member

10+ Year Member

joined:May 3, 2004
posts:300
votes: 0


I have tried Navicat and found it very slow and somewhat unreliable, but it seems to work well for smaller files. I appreciate the suggestion.

When you guys work with data, how would you pull it from the MySQL server, edit it, and put it back on the server? My method at the moment is exporting a CSV file into Filemaker, editing the data, exporting a new CSV and using PhPmyAdmin to import the new text file.

5:11 pm on Mar 17, 2005 (gmt 0)

Preferred Member

10+ Year Member

joined:Dec 3, 2004
posts:445
votes: 0


Musicarl, I completely missed the fact you didn’t have access to this machine.

CSV files are a good way to go in this case, if your a good DBA. If doing this, and you are going to edit the data in the interim, you should be aware of all links, etc. Editing things such as duplicate records without knowing what is linked to what, can be potentially disastrous. All I have to say is “If you’re going to do it that way, BE CAREFUL! And be sure you KNOW your database before 'correcting data'.” ;0)

-- Zak

8:58 am on Mar 19, 2005 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Dec 9, 2003
posts:3416
votes: 0


My method at the moment is exporting a CSV file into Filemaker, editing the data, exporting a new CSV and using PhPmyAdmin to import the new text file.

If it makes you feel any better, that's exactly how I do it. Of course, the second JDBC comes online for the Mac version of Filemaker 7, neither of will have to worry about this ever again. :)

4:27 am on Mar 23, 2005 (gmt 0)

Full Member

10+ Year Member

joined:May 3, 2004
posts:300
votes: 0


That was quite an adventure, but our data is now safely on our own server. I learned a lot about data management from this, including the pitfalls of dumping and importing information.

You would think filemaker would have a better system by now, but until then, I'll do the PhpMyAdmin method.

4:53 am on Mar 23, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:July 16, 2001
posts:2015
votes: 3


It also doesn't hurt to splice loading of your data, especially if you have a big table. Load the first 10%, another 10%, and so on. I also recommend dropping any indexes associated with a specific table before loading data, then re-create them after. This will load data faster than if you had indexes for the table.
8:31 pm on Mar 24, 2005 (gmt 0)

Full Member

10+ Year Member

joined:Dec 22, 2002
posts:334
votes: 0


MySQL has just recently released a free administrative tool for Mac OS X too.
Details and download here: [mysql.com...] . At the first glance I've had at it it looks great!