Database Exporting

[Rascii]

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!

[fenway]

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.

[Rascii]

Thank you! You were extremely helpful. I'm going to give it a try tonight.

[gudmunson]

[!--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.

[fenway]

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

[Rascii]

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?

[fenway]

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.

[Rascii]

Well I can't seem to export the gigantic tables and import them again.

Any ideas on how I could do this?

[fenway]

What specifically is the problem -- the export or the import?

[Rascii]

The import... It just freezes up the browser.

[fenway]

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.

[Rascii]

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. :-\

[fenway]

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.

[wickning1]

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.

[fenway]

[!--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.

[guilliam]

[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