CODEWITHSUNDEEP
phpmysql
Jiteen
Jiteen

Reputation: 427

MySQL Database Cloning using PHP

I have a database named yourFirstDatabase with few tables. I want to clone it to another database yourSecondDatabase along with the entire data.

Now I have following piece of code :

exec('mysqldump --user=root --password=root --host=localhost yourFirstDatabase > /var/www/dileep/jiteen/poc/shellPhp/file.sql');

This creates a Dump of the yourFirstDatabase (named file.sql). Now I want to fetch the database elements from file.sql into the yourSecondDatabase For this I am using a similar code :

exec('mysqldump --user=root --password=root --host=localhost yourSecondDatabase < /var/www/dileep/jiteen/poc/shellPhp/file.sql');

But it isn't working fine and no data is imported in the yourSecondDatabase.

However, if I manually IMPORT the data from file.sql, the data is imported perfectly fine. This means that MySQL dump (first code) is worlking fine but second code (to fetch the data) is having some issues.

PS : It is not showing any errors as such. I got these code from a similar question FROM HERE Any sort of help or suggestion is appreciated.

Upvotes: 0

Views: 866

Answers (2)

GDY
GDY

Reputation: 2941

Here is what i came up with for duplicating databases using PHP and MySQLi:

// Database variables

$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '1234';

$DB_SRC = 'existing_db';
$DB_DST = 'newly_created_db';



// MYSQL Connect

$mysqli = new mysqli( $DB_HOST, $DB_USER, $DB_PASS ) or die( $mysqli->error );



// Create destination database

$mysqli->query( "CREATE DATABASE $DB_DST" ) or die( $mysqli->error );



// Iterate through tables of source database

$tables = $mysqli->query( "SHOW TABLES FROM $DB_SRC" ) or die( $mysqli->error );

while( $table = $tables->fetch_array() ): $TABLE = $table[0];


    // Copy table and contents in destination database

    $mysqli->query( "CREATE TABLE $DB_DST.$TABLE LIKE $DB_SRC.$TABLE" ) or die( $mysqli->error );
    $mysqli->query( "INSERT INTO $DB_DST.$TABLE SELECT * FROM $DB_SRC.$TABLE" ) or die( $mysqli->error );


endwhile;

Upvotes: 0

Kaivosukeltaja
Kaivosukeltaja

Reputation: 15735

Use the command mysql instead of mysqldump. The latter is only for producing dumps, not importing them.

exec('mysql --user=root --password=root --host=localhost --database=yourSecondDatabase < /var/www/dileep/jiteen/poc/shellPhp/file.sql');

Upvotes: 1

Related Questions