Hi Jason:
I've searched back and can't find an answer to this question, and considered contacting you one-on-one to ask, but thought the answer might be useful reference material for other users.
I've got a LARGE MySQL database (SMF forum with half a million posts, + backup tables from past forum upgrades, + 600 MB search index = 1.2 GB uncompressed, or 250 MB gzipped),
Of course I want to pare this down, and I've done so successfully in a test environment I have running on my laptop, but I do NOT want to run a DROP TABLE statement against my database until I know I can restore it if necessary. I've also got about 20 SMF mods I would like to try, but again, I'm not going to risk making a database change I can't undo. My question is: How do you restore a large MySQL database?
Here's the situation so far: I have successfully restored a database that is ~150 MB in size (~20-30 MB when gzipped) through the CPanel "Restore Database" dialog. But when trying to restore a larger version of the database (All of the posts in my main forum, but with backup tables and search index removed - ~500 MB. or 120 MB when gzipped) it shows the "Restoring database" HTML page, but then the SQL to restore the database is not displayed, and the database is not restored. I'm guessing the file is just too big for the cpanel interface to handle (perhaps some size limit). Restore function within SMFitself seems even more limited.
Here's what I'm thinking of trying:
1. Upload gzipped .sql file to server.
2. Un-gzip and restore using a shell script triggered by a Cron job (I don't have shell access set up on my IP address, although I think it might be set up on my account by the previous person who did the tech stuff - not sure - so cron job is the only way I can run scripts).
Will that work? Also, what's the command to un-GZIP something, and is there any way I can do that without creating a 1.3 GB temp file and going over my size quota? (As in, can I send the output of the un-gzip command to the MySQL restore command rather than a file, or do I have to create a file and then restore from that file? I have the intuition that linux is capable of sending output from one command to another, but I'm not a linux guru). If I have to create the file, and that will take me over my size limit (which it will) what happens when I'm over my size limit? Will the forum cease functioning?
Also, I do have a static IP address (although I don't generally use it, because I have a dynamic account with unlimited downloading while the static IP is capped at 50 GB/month. So it might be worthwhile to get shell access on that static IP, if that can be arranged. But, I don't know how to USE shell access (I'm sure I can figure it out :) ).
Lots of questions! But once I get it working, I'll be happy to advise others who might need help with large databases in future, so that might reduce your workload :)
This is going to take some time to respond to :)
Let me try to get something posted later today.
I would definitly not mess with a database that size via cpanel or web-based utility. I'd be curious to know when you tried those attempts that didn't work because your server had a few massive load spikes last week that could very well be related of mysql was struggling. That's a pretty large database -- it may need to be watched while run to ensure it can be killed if something goes wrong.
Shell is the way to go. It doesn't require you to have a static IP. It's based on your cPanel account, not your home connection.
That being said, the database is backed up daily as part of our nightly server backups.
I'll respond more later. Thanks!
Re: load spikes: I've been doing full website backups through Cpanel daily, and I notice that when I do those, the server load goes up to 10% ish.
Ok, I'm working on figuring out how to connect using shell. So far I've created a pair of keys, and tried using both public key and passphrase authentication, with no luck (it says publickey authentication failed). This is why I thought it might also need to be filtered by IP address. I was trying to connect through SSHTerm. I also tried converting the private key to .ppk format and importing into PuTTY (which I have no experience with, except that I did have to use it briefly 10 years ago as part of a computer science class) but while the .ppk file is generated just fine, when the PuTTY import process asks me for a passphrase and I type in the one I used to "unlock" in cpanel, it rejects it.
Ok, so apparently PuTTY will allow me to connect with just a username and password....
I now have shell access. What's next?
What do you want to do? Run a db backup?
You'd just run the command adjusted as needed:
mysqldump -uDATABASEUSERNAME -pDATABASEUSERNAMEPASSWORD DATABASENAME > /home/CPANELUSERNAME/BACKUPFILENAME.sql
Be careful in shell, you can wipe things out quite easily.
If you're going to do that now, I'd prefer you email me so I can watch the server as it runs.
What I'd like to do is a DB restore. I have a GZIPPED version of a database I would like to restore, sitting on the server currently. It was generated by a mysqldump of the database username_forum (my main database), and I would like to restore it to a test database called username_tishtest. Don't want to overwrite the main database with this restore, because there is activity in that database 24/7, and I don't want people to lose their posts.
Once I'm confident that I can do that, then I can start deleting stuff from the main _forum database, which will reduce its size.
I think perhaps the easiest thing to do (maybe?) would be to purchase an extra gig of space, for a month, which would allow me to have an uncompressed .sql dump sitting on the server. Once I have that, I think I can get a restore done (I can do it on my laptop, and I assume mysql commands from a Windows command prompt aren't that different from those on a Linux command prompt). And once I know I can do that, I can drop tables and such to reduce the database to a manageable size.
The command I run to restore from a sql dump file on my Windows laptop is:
mysql -u username -p databasename < "path to dump file"
Thoughts?
Quote from: Myron on June 12, 2011, 03:28:04 PM
I think perhaps the easiest thing to do (maybe?) would be to purchase an extra gig of space, for a month, which would allow me to have an uncompressed .sql dump sitting on the server. Once I have that, I think I can get a restore done (I can do it on my laptop, and I assume mysql commands from a Windows command prompt aren't that different from those on a Linux command prompt). And once I know I can do that, I can drop tables and such to reduce the database to a manageable size.
If you want to coordinate the timing with me to where we're doing it within a day or two, I don't have a problem with temporarily giving you more space for free. Otherwise, yes, I don't see an issue with the concept you mentioned above.
Quote
The command I run to restore from a sql dump file on my Windows laptop is:
mysql -u username -p databasename < "path to dump file"
Depending on your syntax, we may be looking at more or less the same thing. Same command as I posted previously to dump (minus the "dump") with the arrow facing the other direction:
mysql -uDATABASEUSERNAME -pDATABASEUSERNAMEPASSWORD DATABASENAME < /home/CPANELUSERNAME/whereveryourfileis/yourdatabase.sql
Yep, that's about right. -p without a password prompts you for the password, which is how I usually do it, out of habit (I save useful commands in a text file, and don't like having passwords stored in plaintext).
I think I'd like an extra gig for a month, but today was my programming day for this week, and I'll be busy for the next week or two, so I'll open a ticket when I would like the space added.
Thanks for your help! :)
Sounds like a plan - thanks! :)