Jump to content


Photo

Database Exporting


  • Please log in to reply
15 replies to this topic

#1 Rascii

Rascii
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationUSA

Posted 25 January 2006 - 02:13 AM

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!
- Rascii
[br]» Creatures Caves

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 January 2006 - 07:44 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Rascii

Rascii
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationUSA

Posted 26 January 2006 - 01:15 AM

Thank you! You were extremely helpful. I'm going to give it a try tonight.
- Rascii
[br]» Creatures Caves

#4 gudmunson

gudmunson
  • Members
  • PipPip
  • Member
  • 17 posts

Posted 26 January 2006 - 02:34 AM

[!--quoteo(post=339990:date=Jan 25 2006, 06:15 PM:name=Rascii)--][div class=\'quotetop\']QUOTE(Rascii @ Jan 25 2006, 06:15 PM) View Post[/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.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 January 2006 - 03:59 AM

True -- you just have to be careful about any InnoDB namespaces, and be sure the tables are locked or the DB is offline.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 Rascii

Rascii
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationUSA

Posted 26 January 2006 - 04:24 AM

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?
- Rascii
[br]» Creatures Caves

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 26 January 2006 - 06:58 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 Rascii

Rascii
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationUSA

Posted 29 January 2006 - 05:14 PM

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

Any ideas on how I could do this?
- Rascii
[br]» Creatures Caves

#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 January 2006 - 06:37 PM

What specifically is the problem -- the export or the import?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 Rascii

Rascii
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationUSA

Posted 30 January 2006 - 01:03 AM

The import... It just freezes up the browser.
- Rascii
[br]» Creatures Caves

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 January 2006 - 07:26 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#12 Rascii

Rascii
  • Members
  • Pip
  • Newbie
  • 9 posts
  • LocationUSA

Posted 02 February 2006 - 01:23 AM

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. :-\
- Rascii
[br]» Creatures Caves

#13 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 February 2006 - 02:11 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#14 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 02 February 2006 - 02:22 AM

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.

#15 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 02 February 2006 - 04:01 AM

[!--quoteo(post=341956:date=Feb 1 2006, 09:22 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Feb 1 2006, 09:22 PM) View Post[/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.

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#16 guilliam

guilliam
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationSunny City Cebu, Philippines!

Posted 22 September 2006 - 10:32 PM

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).


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

- g
"I was one of those who wondered why people would pay so much $$$$ to do something that was so much fun!" -R. Harkrider, Fortran Code Engr.
^^If u read that in $GREEN, Argh! u missed the HIGHLIGHTS and all the FUN!
www.joomlancers.com | www.astang.com




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users