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?
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. The following is an example of using the utility.
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 #...done. 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.
The user must have SELECT privileges for the database(s) on the source server and have CREATE, INSERT, UPDATE on the destination server.
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
You can specify certain objects to exclude (skip) in the copy.
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.