How do I restore from a backup?

[bdunning]

I foolishly truncated a table yesterday. Is there a way to restore just that one table from PowWeb's automatic backups? The table is way too large to download and then import - 139MB of data. Thanks

[Pig]

AFAIK, you cannot restore just one table if the back up is in a file. I would make sure the file is gzipped, and then download it. Once you have it, you can find the section that applies to the table you need, and run just that.

Why is your database do large? Do you use it to store binary files?

[bdunning]

No, just a lot of data - 1.7 million records.

Apparently I no longer have privileges to use LOAD DATA INFILE so I am at a complete loss for how to get my business back up & running.

[YvetteKuhns]

http://purdy.info/technotes/restoring_mysql.html
This person managed to restore one table for his even bigger database!

[mitchind]

That's a person using command line utilities - I wouldn't recommend that to this user, Yvette.

I would suggest taking Pig's approach - get the full backup, unzip it, open it in a text editor that can handle large files, and copy/paste the insert statements for your particular table into another text file.

Then you can use extras MiniSQL tool if you want to (see ReadMe at top)

[YvetteKuhns]

I would make sure the file is gzipped, and then download it. Once you have it, you can find the section that applies to the table you need, and run just that.

That is how I do it.

AFAIK, you cannot restore just one table if the back up is in a file.

I was just curious if someone DID do this and that is why I posted the link.

That's a person using command line utilities - I wouldn't recommend that to this user, Yvette.

You are probably right. It is interesting to know there ARE other ways to do the same thing. ;)

[bdunning]

I already have the data - both locally, and on my server as a CSV, and also on my server broken down into 10 smaller CSV's. Getting the data is not the problem - getting it into MySQL is. LOAD DATA INFILE apparently no longer works - tech support told me we don't have permissions to do that any longer, and when I try it fails with a 1045 permissions error.

[YvetteKuhns]

LOAD DATA INFILE apparently no longer works - tech support told me we don't have permissions to do that any longer, and when I try it fails with a 1045 permissions error.

Is this a security measure?

[bdunning]

I don't know, see my post here -
http://forum.powweb.com/showthread.php?t=61769

When I was finally able to get a PW support rep on the phone he said we now have to have them import our data for us. If true that's a major show-stopper for me. I can't wait 48 hours every time I have to get something done, and then only to get an email that there was some question or problem.

[mitchind]

As I said recently - this thread I think... ;)
Then you can use extras MiniSQL tool if you want to (see ReadMe at top)

[bdunning]

I tried that - it just returned errors no matter what I did. Couldn't get past it.

I'm now building some multiple-row insert files. What's the best/easiest way to run them?

[extras]

LOAD DATA LOCAL INFILE still works.

--------------
LOAD DATA LOCAL INFILE 'phpbb_words.dat' INTO TABLE phpbb_words
--------------

Query OK, 1 row affected (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0


Maybe the problem was caused by the lack of 'LOCAL' which tells mysql client to use the file on the clients' server.
Without it, the file on MySQL server is searched, and fails (as MySQL runs on different server at PowWeb).

And the 'Access denied' message iss't saying for the particular command.
Probably wron password, username, host, or dbname caused it.