Page 1 of 2
transfer data from a DB to another
Posted: Thu Mar 01, 2007 6:23 am
by bouncer
hi there
here's my problem, i want to make a php script that get all the data from a table in DB1 and then put that same data in the same table but in a different DB
after this i want to run the same script with a bat file so i can associate this to a task, so i can run this script every 4hours for example
can someone give some tips and examples....i start with php a few weaks ago
thks in adv
Posted: Thu Mar 01, 2007 6:39 am
by jmut
I would assume you are using mysql as sql server.
mysqldump command is your friend. Don't think you need a php script for this.
Posted: Thu Mar 01, 2007 6:47 am
by bouncer
jmut wrote:I would assume you are using mysql as sql server.
mysqldump command is your friend. Don't think you need a php script for this.
yes i'm using mysql
how can i make this with mysqldump command ?
if i use mysqldump command i can still run that every 4h ?
where i add that command ?
sorry for my noob questions
thks in adv
Posted: Thu Mar 01, 2007 6:52 am
by jmut
mysqldump is for dumping mysql data. You can dump it in simple text file e.g. You could find the command within bin/ directory of your server. Cannot help you with this one. Read the help associated with it to understand how to use.
You should use some other means to do repetative tasks (every 4 hours as you requested).
Cannot give you complete solution because:
1. Don't know exact situation and it seems you use windows which I have problems with.
2. Try it yourself and if you have specific problems someone here will help you.
Posted: Thu Mar 01, 2007 8:48 am
by feyd
Look into the replication services MySQL offers.
Posted: Thu Mar 01, 2007 10:52 am
by bouncer
thks jmut, feyd
just one more question, can i create a SQL script (.sql file) that will gather all the data from a table in DB1 and export it to the same table in DB2, in the end i'll get the same result as if i use mysqldump ?
or this are different things ?
sorry for all this noob stuff
thks in advc
Posted: Thu Mar 01, 2007 4:50 pm
by feyd
With replication you don't have to create a file.
Posted: Mon Mar 05, 2007 12:52 pm
by bouncer
feyd wrote:With replication you don't have to create a file.
i want something like this....
Code: Select all
<?
$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'dbname';
mysql_select_db($dbname, $conn);
$tableName = 'tablename';
$backupFile = 'tablename.sql';
$query = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);
/* not sure about this last part ... just change the DB and query */
$dbname = 'dbname1'; //can i have the same var with a different value from the one above ?
mysql_select_db($dbname1, $conn);
$query = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query); //can i run different queries with the same var ?
mysql_close($conn);
?>
but it dont work i guess it is because of the $conn, or something wrong with query...dont have sure,any advice for this code ?
thks in advc
Posted: Mon Mar 05, 2007 1:20 pm
by RobertGonzalez
If you are going to do this with PHP code (instead of using the wonderful suggestions already offered) you are going to need to change your query syntax to use database-name.table-name syntax. Even if you select a database to use.
Posted: Tue Mar 06, 2007 3:50 am
by bouncer
Everah wrote:If you are going to do this with PHP code (instead of using the wonderful suggestions already offered) you are going to need to change your query syntax to use database-name.table-name syntax. Even if you select a database to use.
i dont use any of the wonderful sugestions already offered, because i cant find a good example for my kind of problem, all i have found was this example, can you give me some tips about this code ?
is this code better than the other ?
correct me if i'm wrong, this code makes a backup of all DB, what i have to change here so i can make only a table backup and make that happen every 4 hours.
Code: Select all
<?php
include 'config.php';
include 'opendb.php';
$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
include 'closedb.php';
?>
this code is already working, now i'm getting all the DB backup, but now i want to backup only a specific table and then i want to transfer that to the same table but in a different DB how can i do that with msqldump ?
thanks in advance
Posted: Tue Mar 06, 2007 6:19 am
by Rovas
Read here more information on mysqldump
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html. I think you should use the option
Posted: Tue Mar 06, 2007 6:55 am
by Jenk
bouncer wrote:i dont use any of the wonderful sugestions already offered, because i cant find a good example for my kind of problem, all i have found was this example, can you give me some tips about this code ?
http://www.google.com/search?q=mysql+replication
Posted: Tue Mar 06, 2007 8:30 am
by Begby
What exactly are you trying to do? Are you trying to backup your database every 4 hours?
If so then you want to use mysqldump as was suggested, but you don't need to use any php code for it. You can run it on the command line in windows or linux or macos or whatever. You can even run it from a different pc and connect to your mysql server on a network very easily.
If its on windows you can put the command directly into a batch file (without PHP) and add it to task scheduler.
On linux you can create a shell script to run the dump and make it a cron job.
Also, every 4 hours isn't a bad idea, but you will want multiple copies. Lets say you are coding and mess up and write a delete query and forget to use a WHERE clause. What if you don't notice for four hours and your backup gets overwriiten? Or what if your backup is scheduled to run 2 seconds after you make your mistake? You need to be able to go back further in time with several database images just in case.
Also, replication does not equal backup. You still need to backup the database.
Posted: Tue Mar 06, 2007 10:46 am
by bouncer
Begby wrote:What exactly are you trying to do? Are you trying to backup your database every 4 hours?
i'm trying to backup not a database but only a table from it, and then i want to export that backup to the same table (same name and fields) but in a different database.
but i'm searching for a mysqldump command/tag that allow me to select only one table and then reload the data to the same table but in another database...so far i just have this:
Code: Select all
$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
thanks in advance
Posted: Tue Mar 06, 2007 11:10 am
by RobertGonzalez
As long as your user has privileges for both databases, you can do this with PHP. I am not sure it is the best way (I think you can set up a job in SQLYog, for example, that will go as far as new records in a field of a table, and will do two way synchronization). But if you were going to do this in PHP, you would hit the server, then select from one table in one database and update a different table in a different database.
Code: Select all
<?php
if (!$con = mysql_connect('localhost', 'user', 'pass'))
{
die('Could not connect: ' . mysql_error());
}
// Get the stuff from the first table
$sql = "SELECT * FROM `database1`.`table1`";
if (!$result = mysql_query($sql))
{
die('Could not query: ' . mysql_error());
}
while ($row = mysql_fetch_array($result))
{
// Insert into the other table
$sql = "INSERT INTO `database2`.`table2` (`field1`, `field2`) VALUES ('{$row['field1']}', '{$row['field2']}')";
// yadda yadda, error handling, etc
}
?>
THIS IS NOT MEANT TO BE A SOLUTION BUT RATHER A CONCEPT THAT YOU MIGHT BE ABLE TO USE.