cancel
Showing results for 
Search instead for 
Did you mean: 

Restoring SQL data from Data Dump

N/A

Restoring SQL data from Data Dump

Hi

in my SQL admin section I can and have used DATA DUMP, but how do I go about restoring or repopulating my database with this file?


Thanks

Dave
11 REPLIES
N/A

RE: Restoring SQL data from Data Dump

perhaps if +net don't offer this utility you could download phpMyAdmin which i'm usre can manage it
N/A

RE: Restoring SQL data from Data Dump

Thanks

I have downloaded, edit the file as suggested in the readme, and uploaded to my area

However, when using phpMyAdmin and droping tables within it I get..

SQL Error : 1044 Access denied for user: '******@physweb2.cgi.plus.net' to database '******_pn'

where ****** is my username

Any ideas?

Thanks

> perhaps if +net don't offer this utility you could download phpMyAdmin which i'm usre can manage it

N/A

Re: RE: Restoring SQL data from Data Dump

So how would I go about giving myself access to physweb2.cgi.plus.net so I can drop tables?

Dave

Quote
Thanks

I have downloaded, edit the file as suggested in the readme, and uploaded to my area

However, when using phpMyAdmin and droping tables within it I get..

SQL Error : 1044 Access denied for user: '******@physweb2.cgi.plus.net' to database '******_pn'

where ****** is my username

Any ideas?

Thanks

> perhaps if +net don't offer this utility you could download phpMyAdmin which i'm usre can manage it

Community Veteran
Posts: 6,111
Thanks: 1
Registered: 05-04-2007

Restoring SQL data from Data Dump

Hi, I may be able to help. I've noticed this problem occurs as well, and I've discovered that you can only drop data into tables, you can't actually create tables from a data dump. Therefore, you'll have to create the tables yourself, open the text file of your data dump, remove any lines that don't begin with "#" or "INSERT INTO" (excluding ""), then restore that modified text file to your MySQL database using PHPMyAdmin or any other tool.

HTH!

Thomas
N/A

Restoring SQL data from Data Dump

Ta very much, i'll remember that

Thanks


Dave
N/A

Restoring SQL data from Data Dump

Soory to contadict,

But plusnet only prevent the drop statement..
Creating tables is not a problem assuming the table does not exist.

If you stall have a problem drop me the sql script and I will provide more details.

Cheers

Darren
N/A

Restoring SQL data from Data Dump

Ta

But I hear else where in the forum that PLUSnet will soon be enabling DROP

Hope this is true


Dave
N/A

Restoring SQL data from Data Dump

Yes, last time I heard, this is in the queue to be enabled.

Allthough a table may not exist, statments like

DROP TABLE xyz IF EXISTS

Cause the server to get mad.

The server will not even read the condiftional argument, until determining if it should commit its actions.
N/A

Restoring SQL data from Data Dump

Yes it causes a real problem when using dumps from other systems. although when you perform a dump using the plusnet MYSQL utilities they do not include DROP statements.

Therefore to recover a database from a plusnet MYSQL dump, you need to use the view/edit tables option select each table individually change the drop down box to delete and perform the action. (This takes forever if you have a large number of tables).

Following this process you can then use the Query Tables option and paste your dump file into the window or alternatively use the mysql command from a shell prompt to perform the same function.

Another alternative is to deactivate mysql and then reactivate mysql, this will destroy the database and provide you with a new account. You will then have to change your applications to use the newly issued password. and perform the restore as mentioned above.

Regards

Darren
N/A

Restoring SQL data from Data Dump

Try THIS SQL fix tool I fuged up.

It should strip all drop statments that cover one line, though no gurentee is made as to it's worthyness.

I may even get it to insert it for you someday, if details are provided, though when I can be bothered to do it, I don't know.
N/A

Restoring SQL data from Data Dump

To restore a backup of a database you need the backup to be in .sql format so when you do your dump if using the portal control then dump to email then copy all the text in email and paste into notepad then save as database.sql and set 'save as type' to 'all files' this can then be used to restore your database either using phpmyadmin or as I used 'MySQL Control Center' which runs from your computer so no uploading to do.

In MySQL Control Center
You need to highlight your database select "sql query" then "file" "Open" and select the database.sql file that you created earlier, then select "query" and "execute" now just sit back and wait as it may take a few minutes to do.

In phpmyadmin

Click on your database name in the left window, the click on the SQL tab in the right window, then click browse and select the database.sql file you created earlier then click go, this will take a few minutes depending on the size of your database so be patient.


Cheers Chris - G0TOO