Jump to content

Database Exporting


Rascii

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!
Link to comment
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.
Link to comment
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.
Link to comment
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. :-\
Link to comment
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.
Link to comment
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.
Link to comment
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.
Link to comment
Share on other sites

  • 7 months later...
[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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.