Restoring a backup?

Posted: Fri Jul 30, 2004 11:16 am

I completed a db backup from the Admin menu and I have been searching for direction on how to restore this backup. Confused

I have very limited PHP experience and no knowledge of MySQL or PHPMYADMIN although I am willing to try if someone could guide me in the right direction. Neutral

I found an old backup with some stories that were lost when I was hacked and combined them into my current backup. My stories were all that were lost when my site was hacked.

Thanks

PS I am running 7.0

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Do you have shell access to your site? If so you can use mysql command line statements.

If the database is not too large, you can use phpMyAdmin. Basically you would drop all tables and then reload them in the SQL window from your backup file.

Most frontend panels, like cPanel, have options to do this for you.

Posted: Mon Aug 02, 2004 8:13 am

I opened the old and new savedfiles.sql and found the lines I was looking for and updated them by copying in the stories I want to add without affecting the rest of the site. I re-saved the file under a different name just in case.

I am not familiar with "shell access" I do have CPanel, which allows me to access MYSQL data base which then gives me access to PHPMyAdmin, which I'm assuming is where I need to go.

Quote:

If the database is not too large, you can use phpMyAdmin. Basically you would drop all tables and then reload them in the SQL window from your backup file.

I'm sorry, but I don't understand what you mean Confused

I guess basically is beyond my current knowledge right now. Would you have time to be a little more specific?

Posted: Mon Aug 02, 2004 8:17 am

When you are in phpMyAdmin, you can DROP selected tables. Once they are DROPPED, you can run your SQL to recreate them.

Posted: Mon Aug 02, 2004 9:03 am

Thanks for the quick response Raven, with everything going on I will not be offended in any way if you do not have the time to help me with this.

In PHPMyAdmin it lists the nuke_filenames on the left. I selected nuke_stories since that is the main change I would like to make. It lists:
Structure Browse SQL Search Insert Export Operations Empty Drop
then:
Field Type Attributes Null Default Extra Action
I see under action the change, drop, etc options.
Am I on the right page? How do I drop the file then upload the edited .sql file?

Sorry for my limited knowledge of this, I have only been in PhpMyAdmin once before to clear the out the users. Embarassed

and I was very nervous doing that Wink

Posted: Mon Aug 02, 2004 9:13 am

Click the DROP tab and it should prompt you to drop the table. Then select the SQL tab and copy and paste the sql to create the table.

Posted: Mon Aug 02, 2004 9:45 am

I looked at what was in the SQL tab to see what I will be doing before I drop anything.

I have a couple more questions Embarassed

before I drop it

The saved file I have is the whole .sql backup from the Admin/Backup DB.
In the SQL tab it gives me the option "Location of the text file" is this where I upload the saved.sql file I have? Or do I need to create a separate .sql file with just the "stories" part in it to upload? from

Quote:

# --------------------------------------------------------
#
# Table structure for table 'nuke_stories'
#

to

Quote:

# Table structure for table 'nuke_stories_cat'
#

CREATE TABLE nuke_stories_cat (
catid int(11) NOT NULL auto_increment,
title varchar(20) NOT NULL,
counter int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (catid),
KEY catid (catid)
);

#
# Dumping data for table 'nuke_stories_cat'
#

Sorry for all these basic questions, I just want to make sure I don't lose all the stories again.

Posted: Mon Aug 02, 2004 10:04 am

You can do a copy/paste or point it to the file on your PC to do a full restore. If you are just doing a table it would make more sense to just do a copy/paste of those lines of code.

Posted: Mon Aug 02, 2004 1:46 pm

Amazingly enough it worked Exclamation

Not that I doubted you, just that I actually understood Wink

I narrowed my loses down to a couple months with that change.

Thanks a lot for your help RavensScripts

Steve

Involved Joined: Sep 22, 2003 Posts: 252

Raven, my sql file is about 10.8 mb and I pointed to it, but after awhile it said "no sql query!" I am assuming that the file was to large to do it. So now I am trying to paste it but that doesn't seem to be working. It won't paste. Screen seems to freeze (unless it is a long paste process.)

Posted: Sat Aug 07, 2004 10:13 am

You can only paste about 5000 lines at a time. Depending on your PC resources, you may have to use smaller chunks. See this post also for another alternative [ Only registered users can see links on this board! Get registered or login! ]

Posted: Sat Aug 07, 2004 11:57 pm

Hi,

Considereing that a mySQL database dump, or parts of it, would exceed, say +10MB or so, in size, you may actually encounter difficulties in form of TIMEOUT. Hence, the *phpMyAdmin* is incapable of accomplishing the backup upload due to set restrictions on resource consumption on the server side.

So, how to overcome this phpMyAdmin vs *HUGE DUMP* upload obstacles ?

Here's an effective method - regardless of being a MAIN server root or not:

PART#1
- upload the the mySQL dump file via FTP/SFTP to your server premisses
- login to your SSH account and head to the mySQL dump file location
PART#2

In command prompt, issue the following command with your mySQL preferences (you should know them by now):

bash$> mysql -u username -p database_name < mysql_dump_file.sql

username == your mySQL username
database_name == your mySQL database name
mysql_dump_file.sql == your uploaded mySQL dumpfile

After issuing this above line command, you'll be issued by a prompt to get authenticated with your mySQL password:

NEXT:
--> Type your mySQL server password and hit <ENTER>

Done. Off we go - a fast way to perform it without a mandatory need to split the database in slices. BTW, it's especially useful when switching forth and back with database tests with different configurations.

NOTE: Please observe - you should use this method with care. You must understand the content of your dump file - i.e whether or not the some of the tables are expected to be created by the dump feed. So, in that particular case, there must be NO matching TABLES/ENTRIES present regarding the uploaded to mySQL dump. Otherwise, the procedure will get in most terminated with intermediate status, i.e termination on first ERROR resulting to partial success upon the performed upload. (very rough example, but I hope this sheds some light upon it)

Kind regards,

-beetraham

Posted: Sun Aug 08, 2004 12:00 am

I totally agree. Unfortunately, most do not have shell access and that's why we don't usually even go down that path. That certainly is a SURE way to do the restore. Thanks for a great explanation!