How to Copy an Entire Database in MySQL

Written by Rowan Lumb in Cloud Services,SQL

I was trying to do this the other day and realized that the top result on Google wasn’t actually correct. While there is some correct information on this out there (and I will link it in this article), I figured this was a good opportunity to write my own little helpful guide.

Code Summary

Option 1: Generate all of the CREATE TABLE statements for your database and copy the data

# You will have a command for each table in your database
show create table plays;
show create table users;
show create table referrals;
...

Take this output and add to the top of the code below.

## Make new database. 
# Replace "new_database" with your desired name.
CREATE DATABASE new_database;
USE new_database;

## Add all of you CREATE TABLE statements here
CREATE TABLE constellations (
	id INT NOT NULL AUTO_INCREMENT,
	data_collected_by_id INT NOT NULL,
	last_update DATE NOT NULL,
	name VARCHAR ( 250 ),
    FOREIGN KEY(data_collected_by_id) 
	    REFERENCES users(id)
	    ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE words (
	id INT NOT NULL AUTO_INCREMENT,
	text VARCHAR (50) NOT NULL,
	PRIMARY KEY(id)
) ENGINE=INNODB;
# Run every create table statement you have
.....

## Copy data from old database to new database 

# Replace "old_database" and "new_database" with the appropiate names
# Replace the table names with the appropiate table names for your database
INSERT INTO new_database.constellations SELECT * FROM old_database.constellations;
INSERT INTO new_database.words SELECT * FROM old_database.words;
INSERT INTO new_database.constellation_words SELECT * FROM old_database.constellation_words;

# There will be a line for each table in your database(s)
.....

Option 2: Use sqldump to copy the database

How you do a sqldump really depends on where your database is being stored. If it is on a local server or virtual machine, then you can run the following in Powershell or the command line.

mysqldump -u root -p mydatabase > d:\db\mydatabase.sql

If your database is stored in the cloud with a PAAS service, then you should check the documentation for your specific service regarding a sqldump. The documentation for the three largest PAAS providers is available here:

How to Copy an Entire Database in MySQL

In MySQL, there is no pure MySQL command that will copy over all of the tables in one database to another. You will need to create a copy of the table definition into your new database and then insert all of the database table data in your newly created table.

There are three basic types of commands you will run:

  • Create the new database
  • Create a replica of the table in your new database
  • Copy your data into the new table

What will this look like?

Well if you have only one table, it could look like this:

/* Make new database */
create database my_database_2;

/* Create table identical to my_database_2 */
[DISCLAIMER: Don't Use This Code Segment]
create table my_database_2.users LIKE my_database.users;

/* Copy data from old database to new database */
INSERT INTO my_database_2.users SELECT * FROM my_database.users;

This will work, BUT using the LIKE operator will not copy over your foreign key constraints so those will need to add later which can take hours of tedious work if you have a complicated database.

Also, the queries you need to run will increase in proportion to the number of tables in your database. Unfortunately, this means that for a complex database with many tables, the number of queries you will need to run can become wieldy so it can be best to automate that with a little script.

Let’s look at the correct way to copy a database in MySQL. First, let’s get all of the CREATE TABLE statements for your database:

Option 1: Generate CREATE TABLE statements and copy data

There are a couple of ways to do this. I will show you how to do this assuming you’re using a Windows machine.

Run SHOW CREATE TABLE statements

In MySQL Workbench you’ll need to copy the results from each query in the Result tabs just above the Output panel towards the bottom of your screen.

This is the more tedious approach, but it is suitable if your database is small. You’ll likely need to copy and paste some results on your own. I did this using MySQL Workbench but you can use any tool where you can connect to your database and run MySQL code.

The code will basically look like this:

# You will have a command for each table in your database
show create table plays;
show create table users;
show create table referrals;
...

Then you’ll need to compile all of the results from this code to get ready to execute it in the next step. This will include either copy and paste or maybe a clever little script.

Create the new database

Now let’s create your new database.

# Make new database. Replace "new_database" with your desired name.
create database new_database;

Next, you will run your CREATE TABLE queries. It will look something like this.

# Note that this is just example code
CREATE TABLE constellations (
	id INT NOT NULL AUTO_INCREMENT,
	data_collected_by_id INT NOT NULL,
	last_update DATE NOT NULL,
	name VARCHAR ( 250 ),
    FOREIGN KEY(data_collected_by_id) 
	    REFERENCES users(id)
	    ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE words (
	id INT NOT NULL AUTO_INCREMENT,
	text VARCHAR (50) NOT NULL,
	PRIMARY KEY(id)
) ENGINE=INNODB;

CREATE TABLE constellation_words (
	constellation_id INT NOT NULL,
	word_id INT NOT NULL,
	INDEX(constellation_id),
    FOREIGN KEY(constellation_id) 
	    REFERENCES constellations(id)
	    ON DELETE CASCADE,
	INDEX(word_id),
    FOREIGN KEY(word_id) 
	    REFERENCES words(id)
	    ON DELETE CASCADE
) ENGINE=INNODB;

# Run every create table statement you have
.....

Copy over your data

Lastly, you’ll need to copy over all of the data you have in your old database to your new database tables. The statements you need to run will be essentially a line of code for each table you need to copy data over from.

The code will look like this.

# Copy data from old database to new database 

# Replace "old_database" and "new_database" with the appropiate names
# Replace the table names with the appropiate table names for your database
INSERT INTO new_database.constellations SELECT * FROM old_database.constellations;
INSERT INTO new_database.words SELECT * FROM old_database.words;
INSERT INTO new_database.constellation_words SELECT * FROM old_database.constellation_words;

# There will be a line for each table in your database(s)
.....

2. MySQL Dump

How you run a SQL Dump command will depend where your database is being stored. On a local server or virtual machine you will be able to run code like this from the command terminal in windows:

mysqldump -u root -p mydatabase > d:\db\mydatabase.sql

If your database is hosted in the Cloud with a PAAS such as Microsoft Azure, AWS, or Google Cloud then you should look up the specific way to get a sqldump file for each of these respective services. Sometimes there will be a GUI interface and you can click a button that generates the file.

For example, let’s look at how to generate a sqldump file with Google Cloud and their Cloud SQL service.

Google Cloud

Here are the links for each PAAS’s individual instruction on how to perform a sqldump:

Tips

1. Don’t Use the LIKE Operator to copy tables in MySQL

I mentioned this earlier but want to reiterate it here. The LIKE operator in MySQL will not copy over any constraints you have on a table like foreign keys and unique constraints.

This means you will need to use a SQL dump or list of SHOW CREATE TABLE statements to generate the code to copy your database.

Doing this will save you a lot of headache.

Trust me.

2. If your list of CREATE TABLE statements is out of order, disable foreign key checks to run the statements.

You will receive an error if you create a table that has a foreign key referring to a table that you haven’t created in the new database yet. You can set the foreign key checks off and then back on after you copy all of your database tables.

SET foreign_key_checks = 0;

/* Your SQL Code */

SET foreign_key_checks = 1;

Disclaimer: You need to be confident that the constraints you have in the CREATE TABLE statements don’t reference any keys or tables that don’t exist.

You won’t need to worry about having erroneous CREATE TABLE statements if you just copied the SHOW CREATE TABLE output for your database provided that your database doesn’t have any of these errors in the first place.

Also, understand that MySQL won’t check your foreign keys retroactively after you turn on foreign_key_checks. This shouldn’t be an issue if you are only copying over all of the already existing tables in a database that contains no design errors.

Conclusion

Thanks for reading and look out for more content from RTL Coding. Hope this article was useful and if you would like to support this blog and this content feel free to donate via Paypal to help support more helpful content.

Sources

Recent Posts