Restoring mysql database

[tritium]

Hey guys ... I accidentally erased my entire datbase ... I had it backed up though ... how do I restore from the backup -- I tried using the admin tool, no luck (internal server error) ... I tried using mysql, but the syntax that was listed on the admin tool seemed to be giving a problem (I'm using mysql control center) ... assume that my backed up database name is setup.sql ... thanks

[sch]

Hi,

I'm assuming setup.sql has been produced by the backup tool? In theory it should just be a text file with a list of sql commands that will recreate the db, so it should just be a case of executing those commands.

 

I don't know mysql control center since I use phpmyadmin, but most mysql admin tools have a way of executing sql commands so you just need to get the commands executed - either by way of copy/paste or you might be able to specify a file containing the commands (in this case setup.sql on your local machine).

 

Simon

[241]

check the sql file for tables without insert data, look for semicolon sitting by itself ;

 

example:

DROP TABLE IF EXISTS counter_history;

CREATE TABLE counter_history (

  month char(8) default NULL,

  counter int(12) default NULL

) TYPE=MyISAM;

 

--

-- Dumping data for table `counter_history`

--

 

;

-- --------------------------------------------------------

 

--

 

I have marked the semicolon above red, this can cause issues when restoring a backup, with the error being syntax.

 

Fix: Remove the semicolon marked red from the sql, this only happens where a table is empty.