Wednesday, February 3, 2010

Mirroring MySQL database

As I was learning Data Mining , where we study the importance of commercial data , and mirroring as a backup measure , I thought of "googling" for a simple application , which could mirror my database onto another host on the network . The below given example , can transfer a complete database to another , within any host. My next post shall help mirroring data to another network host.

The code is in PHP:

$adminc=@mysql_connect("localhost","admin","pesit123") OR die('Couldn\'t connect to MySql:'.mysql_error());

$result = mysql_list_tables ("admin");

while ($table_row = mysql_fetch_row($result)) {

$table = $table_row[0];
$qry="drop table if exists adminmirror".$table;
mysql_query($qry)or die("Failed to delete:".mysql_error());

$query= "create table adminmirror.".$table." LIKE admin.".$table;
mysql_query($query)or die("Table create failed:".mysql_error());
$query="Insert INTO adminmirror.".$table." SELECT * FROM admin.".$table;
mysql_query($query)or die("Table copy failed:".mysql_error());
echo "$table copy done...";
? >

Please incorporate the required changes in the database names , so as to mirror the database.

Please feel free to Comment .