Digital Fortress Logo
Digital Fortress

A coders paradise

Duplicate a Mysql Database – Tutorial

Reading Time 2 min

Learn how to duplicate a mysql database in 2 simple steps. Many a times, you want to duplicate a database to create a backup, to test against real data or just because you want to. Lets quickly checkout how this can be done step-wise.

We will be using the command line utility to duplicate the database.
Duplicate Mysql Database

Duplicate a Mysql database –

Assumptions

  • Lets assume that the database you wish to duplicate is named originalDB
  • You wish to duplicate this database into another database named duplicateDB that contains exactly the same data.

1. Create the Duplicate Database

To duplicate the Database originalDB into a database duplicateDB, the database duplicateDB must first exist. So lets go ahead and create this database.

// Create the Database which must have duplicated content
mysql -u username --password='yourpassword' -e 'DROP DATABASE IF EXISTS `duplicateDB`; CREATE DATABASE `duplicateDB`;

The above statement checks if a database named duplicateDB exists. If it does, then that database is dropped/deleted and a new empty database is created in its place.

2. Copy the Database

Now we will simply copy all the data from the originalDB into the duplicateDB to have an exact same copy of the originalDB.
To do this, we use a mysql utility named mysqldump that comes bundled with mysql by default.

/* 
* Copy the data from originalDB to the duplicateDB
* -Q or --quote-names => Quotes database, table, and column names within ` characters
* -q or --quick => Retrieves 1 row at a time rather than retrieving the entire row set and 
*                  buffering it in memory before writing it out. Useful for duplicating large tables
*/

mysqldump --quote-names -q -u username1 --password='password1' originalDB | mysql -u username2 --password='password2' duplicateDB

As seen, we retrieve all the data of the original database using mysqldump and pipe it directly into another database, effectively duplicating/replicating the database.

Copying from one database to another

Alternatively, you could also create a mysql dump file containing all the data and then import it to another database.

shell> mysqldump db1 > dump.sql    // exports all tables of db1 to dump.sql
shell> mysqladmin create db2       // creates new database db2
shell> mysql db2 < dump.sql        // imports the dump.sql into the new database

Once you create the dump.sql file, you can also use any graphical tool like mysqlworkbench or phpmyadmin to import the data.

Bonus: Automating Backups –

Find below a small script that will create a backup of your database each time it is executed.

Note that this is a POC. In real world scenarios, ensure that you do not pass your mysql database credentials in plain text. (On executing the script, MYSQL will display a similar warning as well.)

<?php
// my-backup-script.php

custom_shell_exec(
        "mysql -u username --password='password' -e 'DROP DATABASE IF EXISTS `duplicateDB`; CREATE DATABASE `duplicateDB`;'",
        $stdout,
        $stderr
    );


custom_shell_exec(
        "mysqldump --quote-names -q -u username1 --password='password1' originalDB | mysql -u username2 --password='password2' duplicateDB",
        $stdoutdump,
        $stderrdump
    );


// $stdout, $stdoutdump contains output
// $stderr, $stderrdump contains the error if any.

/**
 * Function to exec shell commands with precise granularity
 * @param $cmd
 * @param null $stdout
 * @param null $stderr
 * @return int
 */
function custom_shell_exec($cmd, &$stdout=null, &$stderr=null) {
    $proc = proc_open($cmd, [
        1 => ['pipe','w'],
        2 => ['pipe','w'],
    ],$pipes);
    $stdout = stream_get_contents($pipes[1]);
    fclose($pipes[1]);
    $stderr = stream_get_contents($pipes[2]);
    fclose($pipes[2]);
    return proc_close($proc);
}

The above script is written in PHP, but can be adapted to another language as necessary. Here is a Shell Script that does the same thing without using mysqldump.

Cloning only the Database schema

To clone the database schema without copying the data therein, use the --no-data flag of mysqldump utility.

/*
* -r or --result-file => The name of the result file
*
*/
mysqldump -u username --password='password1' -r duplicateDBSchema.sql --no-data

You can now import this duplicateDBSchema.sql file as needed.

Hope it helps!

References

Leave a Reply