backup and restore from script

[NorthStarDA]

hi,

I have mysql 5 running as the db for my coldfusion application. This particular application needs to make a copy of a certain db and restore it with a new name. I have seen mysqlhotcopy but i need something that i can use from a cfquery tag in CF, can this be done?

=========================================
Don't sweat the petty things and don't pet the sweaty things.

 

[Kozusnik]

I'm not familiar with CF. I use mysqlhotcopy in a shell script.
Why can't you just do...
Create table
Insert into newtable (select * from oldtable)
?

Is that possible?

Mark

 

[NorthStarDA]

it would be possible for me to do with a shell script, i can execute batch files etc.. from coldfusion - it's just not pretty and does not give me enough control as far as error checking and such.

however, your second option just might do the trick! i wonder if i can query the information_schema to get the table names - and loop over them to insert the tables into the new database...

=========================================
Don't sweat the petty things and don't pet the sweaty things.

 

[Kozusnik]

Here's some php code I use to get a list of tables...

$result = mysql_list_tables (databasename);
$i = 0;
while ($i < mysql_num_rows ($result)) {
$tb_names[$i] = mysql_tablename ($result, $i);
$i++;
}

This gives me a list of tables in databasename.

Mark