resetting a database

matbennett

I'm trying to work through some changes to a fairly sizeable database and have to keep resetting it to a known state.

At the moment I am doing this by running a large (22mb) dump file on my local system in PHPmyadmin on the server. This is taking forever.

I would like to be able to upload the dump file and have a php page execute that file when it is loaded, meaning that I can reset the database far more quickly.

The problem is that I don't know how to execute mySQL statements from a seperate file. Can anyone help.

Thanks.

nnichols

Just upload the dump file to a tmp folder and then use mysql_query to run the usual "LOAD DATA INFILE" to load the data. Make sure you give the full path to the uploaded file.

Hope this helps 😉

matbennett

Thanks for the quick reply. Not sure about the LOAD DATA INFILE bit though. Could you give an example?

Thanks

nnichols

Having uploaded the file and stored it somewhere on the remote server you then execute something like -

$sql = "LOAD DATA INFILE $file";
$mysql_query($sql);

$file needs to be the full system path to the file and the process that db_user that you use to run the query must have the 'File_priv' permission.

Hope this helps 😉

matbennett

That was easier than expected! Thanks for the help.

matbennett

From what I can see of the above this will load data only into a specified table.

Is there a similar method to execute sql statements containing creates, drops & inserts? For instance to rebuild an entire database from the schema that myphpadmin produces?

I know that it must be somewhere there in the manual, but I am going boss-eyed trying to find it.

nnichols

try -

exec("pathtomysql/mysql -uroot -pturkey test < $file");

just replace the path to the mysql executable

Hope this works :rolleyes: