Duplicating MySQL databases may be performed with ease. Every installation of MySQL contains all required tools to master this task successfully. It doesn’t matter whether you have to duplicate complete databases at local or remote hosts. Even if you are required to transfer just dedicated contents between two database instances.

This article will show you how to convert a database – including all data – into a serialized data stream and how to connect this data stream with another database instance.

The Theory

The simplest way to dump from local or remote databases is to utilize the tool ‘mysqldump‘. It accepts some optional command line parameters to specify details like…

• host names
• user names
• passwords
• names of databases and tables

When you were successful to connect to your source database ‘mysqldump‘ is creating a datastream that may be directed to other (local or remote) databases or database instances or you may save the database dump to disk. Basics regarding pipelining and redirecting data streams are described in article Introduction to Pipes and FIFOs.

To fill the new and empty database with contents of your data stream you have to open the database with the database client called ‘mysql‘. Then you have to link your data stream to the standard input channel of that tool.

That’s it. As you can see, the steps required are quite simple. Problems you may encounter are:

• impropper configured access permissions
• network restrictions to access remote databases
• conversion problems between character sets
• incompatible changes between major releases of MySQL 

However, problems do encounter very seldom. But if they do you will receive meaningful error messages.

If you do need some support, please contact the Linux Support Help Desk to receive immediate assistance.

Dump a Database

To dump databases you have to execute the command line tool ‘mysqldump‘. All tools required do share identical command line parameters, so it is quite simple to utilize the introduced set of tools when working with MySQL databases.

# to get an overview of available databases, try...
$ mysqlshow -u user_name -p [--host=name] 
 
# dump a database (create a data stream)
$ mysqldump -u user_name -p [--host=name] database_name 
 
# dump a database to a file
$ mysqldump -u user_name -p [--host=name] database_name > db.dump
 

Populate a Database

To populate a database you have to provide a new/empty database. All required steps are illustrated in the next example.

# provide an empty database to be populated with data
$ mysqladmin create -u user_name -p [--host=name] database_clone_name
 
# populate a database from a dump file
$ mysql -u user_name -p [--host=name] database_clone_name < db.dump
# or alternatively execute...
$ cat db.dump | mysql -u user_name -p [--host=name] database_clone_name
 
# duplicate a database on-the-fly
$ mysqldump -u user_name -p [--host=name] database_name |  
  mysql -u user_name -p [--host=name] database_clone_name
 

Examples

Local Databases

When dumping and populating local databases you should not need to add too much command line parameters, because local databases are accessed by default (but this depends on your local database setup). If your local user account has been configured to be able to interface with the database engine, you should not be required to provide any credentials.

# take a look at the available databases
$ mysqlshow 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| database_name      |
| conf_mgmt_db       |
| high_avail_db      |
| global_monitor_db  |
| mysql              |
| mytestdb           |
| mytestdb2          |
| cia_server_list    |
| fbi_account_mig    |
| bnd_im             |
+--------------------+
 
# provide an empty database to be populated with data
$ mysqladmin create database_clone_name
 
# if you want to store the database dump to a file before populating the new database
$ mysqldump mytestdb > db.dump
$ cat db.dump | mysql database_clone_name
 
# duplicate a database on-the-fly
$ mysqldump mytestdb | mysql database_clone_name
 

Remote Databases

When you are required to dump or populate remote databases you just have to add additional command line parameters to the previous examples. Please keep in mind, that several security restrictions may apply. So it could be required to reconfigure network devices or remote database instances.

Note: The presented command line tools do support the encryption of network connecitons. So you do not have to care about creating secure network tunnels.

Incoming search terms:

• mysql db