Jump to content

Archived

This topic is now archived and is closed to further replies.

Rascii

Database Exporting

Recommended Posts

Okay so I'm moving hosts right now and I have tons of mysql stuff that I need to move from one host to the next. I figured I could just use the "Export" feature in phpMyAdmin, save it to my hard drive, then use the SQL feature to run the INSERTs in the phpMyAdmin for the new host. Turns out, it isn't working.

I think the problem is that there is a "(Max: 2,048KB)". The database that I exported is "2.58 MB (2,712,543 bytes)".

So I have a few questions:
1. Is my best option to export and import table by table?
2. When exporting, what are the best options to use? I don't understand what the following are for or if they are necessary:
a. "Enclose export in a transaction"
b. "Disable foreign key checks"
c. "Add DROP TABLE"
d. "Add IF NOT EXISTS"
e. "Complete inserts"
f. "Extended inserts"
g. Maximum length of created query
h. "Use delayed inserts"
i. "Use ignore inserts"
3. What type of compression is best? (Right now I am using "None".)

I would like to say thanks ahead of time to anyone who helps me out with this. I appreciate it!

Share this post


Link to post
Share on other sites
That max size is simply for pasting into PHPMyAdmin -- you can always do it in "chunks".

To answer most of your questions.

1 - Probably, yes, provided that you can't get your hands on the raw table files.

2a - This only matters for InnoDB tables, or if you're importing into a live site.
2b - Same thing -- InnoDB allows for FK constraints, which complain if the records are inserted in the "wrong" order.
2c - Drops the table before creating it, in case it already exists.
2d - Only creates the table if it doesn't already exist.
2e - Add any default values to be explicitly specified in the VALUES() part of the INSERT (e.g. uid).
2f - Combines many INSERTs together -- i.e. many VALUE()s are added in each insert (this is faster).
2g - So that you don't exceed the max query size (e.g. 2048 KB).
2h - Runs the INSERTs delayed, so that the client doesn't wait to get confirmation of each insert.
2i - Runs the insert with the ignore option, so errors are supressed (use with caution).

3 - Whatever you can get your hands on, at least for the import/export.

Good luck.

Share this post


Link to post
Share on other sites
Thank you! You were extremely helpful. I'm going to give it a try tonight.

Share this post


Link to post
Share on other sites
[!--quoteo(post=339990:date=Jan 25 2006, 06:15 PM:name=Rascii)--][div class=\'quotetop\']QUOTE(Rascii @ Jan 25 2006, 06:15 PM) [snapback]339990[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Thank you! You were extremely helpful. I'm going to give it a try tonight.
[/quote]

If you have root access to both the machines, you can just copy the databases over!

Any subdirectories in /usr/local/mysql/var (my default database location) are actually databases. Just copy those directories+files from the old machine to the same location on your new machine and you should havea copy of your database.

Share this post


Link to post
Share on other sites
True -- you just have to be careful about any InnoDB namespaces, and be sure the tables are locked or the DB is offline.

Share this post


Link to post
Share on other sites
One last question:

The database for my forums has 27,093 rows (3.2 MB) and I'm having trouble moving it. Should I just start the forums afresh or keep trying?

Share this post


Link to post
Share on other sites
Even if you "start again", there's no reason for you to lose all of that data! There's a huge difference between archiving and deleting. Good luck.

Share this post


Link to post
Share on other sites
Well I can't seem to export the gigantic tables and import them again.

Any ideas on how I could do this?

Share this post


Link to post
Share on other sites
What specifically is the problem -- the export or the import?

Share this post


Link to post
Share on other sites
That's probably just a browser timeout and/or PHPMyAdmin's fault, because it's not sending anything back to the browser. Just import MUCH smaller chunks at a time.

Share this post


Link to post
Share on other sites
There are 27,070 rows taking up 2.6 MB and 598.9 KB of space. I feel like it would be impossible to do all of this chunk by chunk so I guess I am just going to forget about moving the forum threads.

If anyone can think of any suggestions please let me know. :-\

Share this post


Link to post
Share on other sites
Feel free to give up at any time -- PHPMyAdmin has a 2MB insert limit, but the browser usually times out much before that. You might be better off writing a quick-and-dirty PHP script to run the import, and that way, you can set the timeout to whatever you want, and output progress text to the browser to keep it from hanging. Good luck.

Share this post


Link to post
Share on other sites
Your backup file is just over the 2 meg limit. Open it up in a text editor, it is just a lot of MySQL INSERT commands.

Cut & Paste half of the commands into another file, import the first one into phpMyAdmin, then import the second one. As long as each file is under 2 megs, you're fine.

OR

If you have ssh access, you can log into the mysql command line client and import phpMyAdmin's backup file from there with no maximum size limit.

to get into the command line client type
mysql -h localhost -u root -p
it will prompt you for the password (you don't have to be root, and localhost could be a remote address too)

once in the client type:
source backupfile.sql;

(backupfile.sql should be replaced with whatever your file is called)

when it finishes, you're done. type 'exit' to quit the client.

Good luck.

Share this post


Link to post
Share on other sites
[!--quoteo(post=341956:date=Feb 1 2006, 09:22 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 1 2006, 09:22 PM) [snapback]341956[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Cut & Paste half of the commands into another file, import the first one into phpMyAdmin, then import the second one. As long as each file is under 2 megs, you're fine.
[/quote]

As I said above, the browser can still timeout waiting for a response from the server, so it could look like it failed/stalled even though the script is still running.

Share this post


Link to post
Share on other sites
[quote]
a. "Enclose export in a transaction" - This only matters for InnoDB tables, or if you're importing into a live site.

b. "Disable foreign key checks" - Same thing -- InnoDB allows for FK constraints, which complain if the records are inserted in the "wrong" order.

c. "Add DROP TABLE" - Drops the table before creating it, in case it already exists.

d. "Add IF NOT EXISTS" - Only creates the table if it doesn't already exist.

e. "Complete inserts" - Add any default values to be explicitly specified in the VALUES() part of the INSERT (e.g. uid).

f. "Extended inserts" - Combines many INSERTs together -- i.e. many VALUE()s are added in each insert (this is faster).

g. Maximum length of created query - So that you don't exceed the max query size (e.g. 2048 KB).

h. "Use delayed inserts" - Runs the INSERTs delayed, so that the client doesn't wait to get confirmation of each insert.

i. "Use ignore inserts" - Runs the insert with the ignore option, so errors are supressed (use with caution).
[/quote]

^^just to consolidate the definition side by side. this is very helpful to all those new to phpmyadmin and importing/exporting.

- g

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.