Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
EPUB - 366.2Kb
HTML Download (TGZ) - 289.0Kb
HTML Download (Zip) - 301.1Kb


MySQL Utilities 1.5 Manual  /  ...  /  How do you make a copy of a database on the same server?

3.1.2 How do you 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 in the experiment/test?

Objectives

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 database in its entirety and rename the copy to world_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. The following is an example of using the utility.

Example Execution

shell> mysqldbcopy --source=root:root@localhost \
          --destination=root:root@localhost world:world_clone
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world renamed as world_clone
# Copying TABLE world.city
# Copying TABLE world.country
# Copying TABLE world.countrylanguage
# Copying data for TABLE world.city
# Copying data for TABLE world.country
# Copying data for TABLE world.countrylanguage
#...done.

shell> mysql -uroot -p -e "SHOW DATABASES"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| world              |
| world_clone        |
+--------------------+
        

Discussion

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 using the <old_name>:<new_name> syntax.

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 written in uppercase 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, and snapshot (default): consistent read using a single transaction.

Risks

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


User Comments
Sign Up Login You must be logged in to post a comment.