Restore data to mysql table using PHP

[lexer]

Hi

I have a Mysql database with several tables, In this database I've got a table where I save information every day, I have a PHP script for backing up a Mysql database, This my code:

<?php
//Current Date
$Date = date('Y-m-d');


//My BD
$user = "root";
$passwd = "passsword";
$bd = "users";

//Make the backup
$execute = "c:\wamp\bin\mysql\mysql5.0.51a\bin\mysqldump.exe -u $user --password=$passwd --opt $db > c:\backup\mydb_$Date.sql";
system($execute, $result);



if ($result) { 
echo "<H1>Error on: $execute</H1>\n"; }
else { 
echo "Backup Ready: ". $Date; }

?>

I save the backup in a file and I can restore manually all my database using the followinmg command line:

mysqldump -u root -p --all --add-locks -e mydb > mydb_Date.sql

This works fine But When I restore the database from an old backup It just keep the information from the backup an the current database informaction is deleted, I just want to append the backup data to my database without deleting the current information (Or Restore the information from a selected table and append it)

Any ideas?

Thanks

 

[vacunita]

Since you are just calling the mysqldump executable, PHP really is not involved in the creation of the backup file.

You'll have to modify your backup file so it doesn't drop the existing table.

Use any text editor, and open your file, then take out the section that drops, and recreates the table.
example:

[red]DROP TABLE IF EXISTS `mytablename`;[/red]
CREATE TABLE `mytablename` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

the --opt switch that you have in the command should automatically insert the drop table statements.

if you do not want this you need to change the command to the following

$execute = "c:\wamp\bin\mysql\mysql5.0.51a\bin\mysqldump.exe -u $user --password=$passwd --skip-opt  --extended-insert --lock-tables --quick --set-charset $db > c:\backup\mydb_$Date.sql";

 

[lexer]

Thanks, Is it posible using a PHP script for restoring data calling mysqldump.exe to detect when the data being restoring (INSERT INTO) from a backup already exits in the table (for avoid duplicated data) and skips these fields and continue with the next fields, In my mysql table I have a field defined as UNIQUE.

 

[jpadie]

use this command instead to create your mysqldump

$execute = "c:\wamp\bin\mysql\mysql5.0.51a\bin\mysqldump.exe -u $user --password=$passwd [red]--replace [/red]--skip-opt  --extended-insert --lock-tables --quick --set-charset $db > c:\backup\mydb_$Date.sql";

the replace directive means that the dump will write out REPLACE statements rather than INSERT statements.

 

[lexer]

Thanks jpadie, I changed my code to:

$execute = "c:\wamp\bin\mysql\mysql5.0.51a\bin\mysqldump.exe -u $user --password=$passwd --replace --skip-opt --extended-insert --lock-tables --quick --set-charset $db > c:\backup\mydb_$Date.sql";

And I receive the following error:

Error on: c:\wamp\bin\mysql\mysql5.0.51a\bin\mysqldump.exe -u root --password= --replace --skip-opt --extended-insert --lock-tables --quick --set-charset users > c:\backup\mydb_2009-10-21.sql

 

[lexer]

I forgot to mention that I tried with mysql 5.1.39 and 5.0.51a Verions (Mysql reference manual says that --replace was introduce with Mysql 5.1.3)

Thanks,