MySQL Database Cloning using PHP

Question

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.

score 1 · Accepted Answer · answered Sep 17 '14 at 07:41

1

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');

answered Sep 17 '14 at 07:41

Kaivosukeltaja

15,541
4
40
70

I am waiting to MARK it as ACCEPTED. It says 'you need to wait 10 minutes to accept'. So waiting for same. I will surely do that, don't worry :) – Jiteen Sep 17 '14 at 07:47

score 0 · Answer 2 · answered Sep 13 '19 at 06:28

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;

MySQL Database Cloning using PHP

2 Answers2