Database MySQL

[Brandon]

Does anyone know how to transfer the contents from one MySql database to another on the same server? Can some one give me the code to do it in Cron?

 

[Brandon]

Ahh... Im paying 50 points.

 

[Chris S]

Ok, I am going to take a stab in the dark and have you try this code

<?
/*  Database Information - Required!!  */
/* -- Configure the Variables Below --*/
$dbhost = 'localhost';
$dbusername = 'enigma_meh';
$dbpasswd = '******';
$database_name = 'saxattac_users';

$tablename_backup = "xxxxxx"; //talbe you want to backup
$tablename_restore = "xxxxxx"; //if you want to restore it to a different table 
			      //you do have to fill this in
$backupfile = "xxxxxx"; // the file you want to restore and create the backup into

/* Database Stuff, do not modify below this line */

$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd")
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");
?>

backup.php

<?php
$tableName = "$tablename_restore";
$backupFile = "$backupfile";
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);
?>

restore.php

<?php

$tableName = "$tablename_restore";
$backupFile = "$backupfile";
$query = "LOAD DATA INFILE "$backupFile" INTO TABLE $tableName";
$result = mysql_query($query);

?>

 

[Corey]

Use PHPAdmin You can export tables and the such, and just import the info my using the SQL feature of PHPMyAdmin on the other account.

-Corey

 

[Jake]

think he wanted it automatic, well you can use a script like above, should work well with cron, becasue there is no way to export the file in phpmyadmin every day, unless by hand...

 

[Chris S]

does it work?

 

[Chris S]

it shouldn't because i made some mistakes

(i was in a rush and trying to hide it from the media center people)

backup.php

<?php
include ('db.php');
$tableName = "$tablename_restore";
$backupFile = "$backupfile";
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);
?>

restore.php

<?php
include ('db.php');
$tableName = "$tablename_restore";
$backupFile = "$backupfile";
$query = "LOAD DATA INFILE "$backupFile" INTO TABLE $tableName";
$result = mysql_query($query);

?>

and name the first file db.php

 

[chitwa]

if you want to use cron, use something like this

#/bin/sh
mysql -u <mysql username> -p<mysql password> -e "<mysql query>" <mysql database>

Save the above code as a file in your cgi-bin as "dcopy.sh"
Remember to Insert your info between the <>. All that remains is the query to copy database. Unfortunately, i dont know it!
Create a cron job to point to it and it will be executed at the times you set.

NOw that I ve taken you halfway there how about 25 points

 

[Chris S]

if you see, I've taken him all the way there except I did just notice that I have made another error in my code.

backup.php:

<?php
include ('db.php');
$tableName = "$tablename_backup";
$backupFile = "$backupfile";
$query = "SELECT * INTO OUTFILE "$backupFile" FROM $tableName";
$result = mysql_query($query);
?>

that should be right FINALLY

 

[Brandon]

Thanks works.