Rascii Posted January 25, 2006 Share Posted January 25, 2006 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 queryh. "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! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 25, 2006 Share Posted January 25, 2006 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. Quote Link to comment Share on other sites More sharing options...
Rascii Posted January 26, 2006 Author Share Posted January 26, 2006 Thank you! You were extremely helpful. I'm going to give it a try tonight. Quote Link to comment Share on other sites More sharing options...
gudmunson Posted January 26, 2006 Share Posted January 26, 2006 [!--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. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 26, 2006 Share Posted January 26, 2006 True -- you just have to be careful about any InnoDB namespaces, and be sure the tables are locked or the DB is offline. Quote Link to comment Share on other sites More sharing options...
Rascii Posted January 26, 2006 Author Share Posted January 26, 2006 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? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 26, 2006 Share Posted January 26, 2006 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. Quote Link to comment Share on other sites More sharing options...
Rascii Posted January 29, 2006 Author Share Posted January 29, 2006 Well I can't seem to export the gigantic tables and import them again.Any ideas on how I could do this? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 29, 2006 Share Posted January 29, 2006 What specifically is the problem -- the export or the import? Quote Link to comment Share on other sites More sharing options...
Rascii Posted January 30, 2006 Author Share Posted January 30, 2006 The import... It just freezes up the browser. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 30, 2006 Share Posted January 30, 2006 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. Quote Link to comment Share on other sites More sharing options...
Rascii Posted February 2, 2006 Author Share Posted February 2, 2006 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. :-\ Quote Link to comment Share on other sites More sharing options...
fenway Posted February 2, 2006 Share Posted February 2, 2006 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. Quote Link to comment Share on other sites More sharing options...
wickning1 Posted February 2, 2006 Share Posted February 2, 2006 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.ORIf 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 typemysql -h localhost -u root -pit 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. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 2, 2006 Share Posted February 2, 2006 [!--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. Quote Link to comment Share on other sites More sharing options...
guilliam Posted September 22, 2006 Share Posted September 22, 2006 [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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.