Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 365.4Kb
HTML Download (TGZ) - 287.5Kb
HTML Download (Zip) - 298.6Kb

MySQL Utilities 1.5 Manual  /  ...  /  How Do I Make a Copy of a Database on the Same Server?

3.2.2 How Do I Make a Copy of a Database on the Same Server?

If you are working with a database and want to experiment with changes to objects or data either from direct manipulation (SQL commands) or as a result of interaction with an application, it is prudent to always have a copy to fall back to if something should go wrong.

Naturally, a full backup is key for any production server but what if you just want to do something as a test or as a prototype? Sure, you can restore from your backup when the test is complete but who has the time for that? Why not just make a copy of the database in question and use it?


The goal is to make a copy of a database and rename it to another name. We want to do this on a single database server without resorting to messy file copies and/or stopping the server.

In this case, we want to copy the world_innodb database in its entirety and rename the copy to word_innodb_clone.

The utility of choice here is named mysqldbcopy and it is capable of copying databases from server to another or on the same server. Lets have a look at the execution.

Example Execution

shell> mysqldbcopy --source=root:root@localhost \
          --destination=root:root@localhost world_innodb:world_innodb_clone
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world_innodb renamed as world_innodb_clone
# Copying TABLE world_innodb.City
# Copying TABLE world_innodb.Country
# Copying TABLE world_innodb.CountryLanguage
# Copying data for TABLE world_innodb.City
# Copying data for TABLE world_innodb.Country
# Copying data for TABLE world_innodb.CountryLanguage

shell> mysql -uroot -proot -e "SHOW DATABASES"
| Database           |
| information_schema |
| employees          |
| mysql              |
| world_innodb       |
| world_innodb_clone |


Notice we specified the source of the database we wanted to copy as well as the destination. In this case, they are the same server. You must specify it this way so that it is clear we are operating on the same server.

Notice how we specified the new name. We used the <old_name>:<new_name> syntax. You can do this for as many databases as you want to copy. That's right - you can copy multiple databases with a single command renaming each along the way.

To copy a database without renaming it (if the destination is a different server), you can omit the :<new_name> portion.

Permissions Required

The user must have SELECT privileges for the database(s) on the source server and have CREATE, INSERT, UPDATE on the destination server.

Tips and Tricks

You can copy all of the databases on a source server to the destination by using the --all option, although this option does not permit rename actions. To rename, you must specify the databases one at a time.

You can specify certain objects to exclude (skip) in the copy. Use the --skip option to omit the type of objects. For example, you may want to exclude copying of triggers, procedures, and functions. In this case, use the option '--skip=TRIGGERS,PROCEDURES,FUNCTIONS'. The values are case-insensitive and uppercased for emphasis.

The copy is replication and GTID aware and will take actions to preserve the binary log events during the copy.

You can set the locking type with the --locking option. Possible values include: no-locks = do not use any table locks, lock-all = use table locks but no transaction and no consistent read, snapshot (default): consistent read using a single transaction.


Should the copy fail in the middle, the destination databases may be incomplete or inconsistent. Should this occur, drop the destination database in question, repair the cause of the failure, and restart the copy.

Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
EPUB - 365.4Kb
HTML Download (TGZ) - 287.5Kb
HTML Download (Zip) - 298.6Kb
User Comments
Sign Up Login You must be logged in to post a comment.