How to duplicate a MySQL database on the same server

Mysql Problem Overview

I have a large MySQL database, lets call it live_db, which I want to replicate on the same machine to provide a test system to play around with (test_db), including table structure and data. In regular intervals I want to update the test_db with the content of the live_db; if possible incremental.

Is there some built-in mechanism in MySQL to do that? I think that master-slave replication is not the thing I want since it should be possible to alter data in the test_db. These changes do not have to be preserved, though.

Regards,

CGD

Mysql Solutions

Solution 1 - Mysql

The mysql command line client will accept a stream of SQL statements from standard input. You can therefore pipe the output of mysqldump directly into mysql on the command line. Doing this as a cron job will regularly overwrite your test data with updated live data:

mysql --user=username --password=passwd -e 'DROP DATABASE test_db;'
mysql --user=username --password=passwd -e 'CREATE DATABASE test_db;'
mysqldump --user=username --password=passwd live_db | mysql --user=username --password=passwd test_db

Note that since your data is large, it will take a long time.

Solution 2 - Mysql

Michaels answer abowe works well but does not copy events, stored procedures or triggers.

To copy those a few more switches is needed for mysqldump: --events --triggers --routines

To complement an already made copy:

mysqldump --user=username --password=passwd --no-data --no-create-info --no-create-db --events --triggers --routines live_db | mysql --user=username --password=passwd test_db

Solution 3 - Mysql

I have been doing this for several years in different contexts, with small to medium databases (1 G to 100 G). The quick and dirty mysqldump works for smaller data sets; the smaller they are, the better it goes.

When you exceed 5-10 GB, depending on the MySQL load, quick and dirty does not cut it anymore.

why `mysqldump` might not be enough

The problem with MySQLdump is that while it dumps, the live database is either unusable, very awkward to use, or the backup will not be consistent. Unless you have a wide enough time window when the unusability of the live database is not important because the database needs not be in use anyway (for example, late at night).

The default options (here a discussion of the why) make the database next to unusable while it's being dumped, unless the usage is just reading data and little of that. On a busy ecommerce site, you're looking at a client pile-up crash.

So you use InnoDB and the modern options (not defaults, as far as I know)

--single-transaction --skip-lock-tables

which allow the site to run, albeit slower than normal, during the dump. Depending on what the usage is, it might be noticeably slower.

While you're at it, also dump other data which might be important:

--events --triggers --routines

(...oh, and this still won't have dumped user permissions. To use as a test perhaps it wasn't so important).

There is a workaround I've found "advised" (!) as a "great hack", which basically disables transactional integrity allowing the database to run at full speed while it's being dumped. Somewhat like removing the brakes from your car to lighten it and have it run faster, yeah it will work, but it'll have some side effects that you might not notice immediately. You will almost surely notice them sooner or later - and just like brakes, it will be when you'll need them most, and it won't be pretty.

However, for a test database, it could still work.

Xtrabackup

If you have a "serious" database, what's the reason not to have a "serious" backup?

Slave replication

Another possibility if you have space to spare - and, nowadays, 20 Gb is not that much - is that of using an ancillary database.

You can install a second copy of MySQL Server on the same server on a different port, and have it be the slave (the server will take a performance hit, storage-speed-wise). Then you will have two identical databases (live master, live slave). The first time you will still have to run a full dump to get them in sync, with all the problems it involves.

When you need to clone the test database, stop the slave replication - the live slave will now remain "frozen" in time - and backup the live slave to the test db, using MySQLbackup or just copying the data files. Once done, you restart the replication.

The impact on the live master is negligible, and the slave can actually be used for non-update-critical selects.

Solution 4 - Mysql

In case you prefer MySQL Migration Toolkit, you may double click on schema name in Data Mapping step and change target schema name.

Solution 5 - Mysql

For all the mac users, with sequel pro all you need to do is go to database (menu) -> Duplicate database. Done!

Solution 6 - Mysql

This solution works fine, but it won't do the deal if you are using PHPunit for unit testing.

Using password in command line generates a warning which is catch by PHPUnit and generate an exception (yeah quite a big deal...)

The way to work around this is to use configuration file.

In my case, I don't want to maintain password and user both in configuration files and PHP code, so I generate the configuration file from the code and check out if it exists (otherwise I use the username and password directly in command line as a fall back option).

Here's an example, in PHP, how to copy a setup database to create a new one with a different name (if you are, by example, managing a main domain with a different subdomain/database for each of your customer):

/**
* If the $dbName doesn't exist, then create it.
* 
* @param $errorMessage String to return the error message.
* @param $dbName String name of the database to create.
* @param $cleanExisting Boolean if the database exist, clear it to recreate it.
*
* @return boolean ($dbExists)
*/
private function createDatabase(&$errorMessage, $dbName, $clearExisting = false){
	
	$command = "";
	$configurationString = "[client]" . "\r\n" . "user=" . parent::$support_user . "\r\n" . "password=" . md5(parent::$support_pass);
	$dbExist = false;
	$path = realpath(dirname(__FILE__));
	
	$connectionString = " --defaults-extra-file=" . $path . "\mysql.cnf ";
	
	$dbName = strtolower($dbName);
	
	if ($this->isDestinationDbNameValid($errorMessage, $dbName)) {
		
		$dbExist = $this->isDestinationDbExist($errorMessage, $dbName);
		
		if (empty($errorMessage) and ($dbExist === false or $clearExisting === true)) {
			
			if (file_put_contents($path . '/mysql.cnf', $configurationString) === false) {
				
				$connectionString = " --user=" . parent::$support_user . " --password=" . md5(parent::$support_pass). " ";
			}
	
			if ($dbExist and $clearExisting) {
					
				$command = $path . '/../../../mysql/bin/mysql ' . $connectionString . ' --execute="DROP DATABASE ' . $dbName  .';" &';
			}
				
			$command .= '"' . $path . '/../../../mysql/bin/mysql" ' . $connectionString . ' --execute="CREATE DATABASE ' . $dbName . ';" &"' .
						$path . '/../../../mysql/bin/mysqldump" ' . $connectionString . ' --events --triggers --routines setup | "' .
						$path . '/../../../mysql/bin/mysql" ' . $connectionString . $dbName;
			
			exec($command);

			$dbExist = $this->isDestinationDbExist($errorMessage, $dbName);
			
			if (!$dbExist) {
				
				$errorMessage = parent::getErrorMessage("COPY_SETUP_DB_ERR", "An error occurred during the duplication process of the setup database.");
			}
		}
	}
	
	return $dbExist;
}

Additional note:

I had to use double quote (") instead of single quote (') around my SQL statements.
I had to use the ampersand (&) to separate my different command.
This example doesn't include the validation for the new database name (isDestinationDbNameValid() method). No need to mention that you should never trust user input...
You also have to write your custom method to validate that the database copy worked as expected (isDestinationDbExist() method). You should at least validate that the database exist, a table from your setup exist and, optionally, validate for stored programs.

Use the force wisely my friends,

Jonathan Parent-Lévesque from Montreal

Content Type	Original Author	Original Content on Stackoverflow
Question	Christoph Grimmer-Dietrich	View Question on Stackoverflow
Solution 1 - Mysql	Michael Berkowski	View Answer on Stackoverflow
Solution 2 - Mysql	Samuel Åslund	View Answer on Stackoverflow
Solution 3 - Mysql	LSerni	View Answer on Stackoverflow
Solution 4 - Mysql	Vasili	View Answer on Stackoverflow
Solution 5 - Mysql	Priyank	View Answer on Stackoverflow
Solution 6 - Mysql	Jonathan Parent Lévesque	View Answer on Stackoverflow

How to duplicate a MySQL database on the same server

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

why `mysqldump` might not be enough

Xtrabackup

Slave replication

Solution 4 - Mysql

Solution 5 - Mysql

Solution 6 - Mysql

package javax.mail and javax.mail.internet do not exist

How do SETLOCAL and ENABLEDELAYEDEXPANSION work?

Attributions

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

why mysqldump might not be enough

Xtrabackup

Slave replication

Solution 4 - Mysql

Solution 5 - Mysql

Solution 6 - Mysql

package javax.mail and javax.mail.internet do not exist

How do SETLOCAL and ENABLEDELAYEDEXPANSION work?

Attributions

why `mysqldump` might not be enough