MySQL Utilities

3.5.1 How can you create a temporary copy (running instance) of a server for testing?

When diagnosing a problem or needing to experiment with a server for developing new features or testing modifications, you often need a duplicate of your running server so that you can ensure your solution works for the actual server. It would be really convenient if we had a process to make a copy of a running server for such processes.

Although it is possible and indeed popular to use replication to replicate all of your data to multiple slaves and use one of the slaves for these purposes, for cases where you are working with a particular server or if replication is not in use, you need some way to duplicate not only the data but also the server and its startup parameters.


Create a new instance of a running server complete with the same options and the same data.

Example Execution

To meet this objective, we need to use several utilities. But before we get started, we need to know what specific options the host server is using. To do this, we use the mysqlserverinfo utility to discover the configuration file and the my_print_defaults tool to print the defaults. We can also show the process id to see what command-line options are being used. We get this from using the --show-servers option with mysqlserverinfo. On POSIX systems, we can use the ps command to find the command line options.

shell> mysqlserverinfo --format=vertical --show-servers
# The following MySQL servers are active on this host:
#  Process id:   2377, Data path: /usr/local/mysql/data
#  Process id:   2478, Data path: /Volumes/Source/source/temp_13001
#  Process id:   2487, Data path: /Volumes/Source/source/temp_13002
# Source on localhost: ... connected.
*************************       1. row *************************
         server: localhost:3306
        version: 5.1.50-log
        datadir: /usr/local/mysql/data/
        basedir: /usr/local/mysql-5.1.50-osx10.6-x86_64/
     plugin_dir: /usr/local/mysql-5.1.50-osx10.6-x86_64/lib/plugin
    config_file: /etc/my.cnf
     binary_log: my_log.000287
 binary_log_pos: 106
      relay_log: None
  relay_log_pos: None
1 row.

shell> my_print_defaults mysqld /etc/my.cnf

shell> ps -f 2377
   74  2377  2300   0 10:56AM ??      0:02.04 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql \
                                              --datadir=/usr/local/mysql/data --user=mysql \
                                              --log-error=/logs/me.local.err --pid-file=/logs/ \

Notice we now have all of the options from the configuration file as well as the startup options. We can now construct the proper options for creating a clone (a running instance) of this server using the mysqlserverclone utility. Specifically, we can set the following options using the --mysqld option:

  • --log-bin=my_log

  • --general_log

  • --slow_query_log

  • --user=mysql

  • --log-error=path

Using these options and choosing a new data directory, we can create a new instance of the host server using the following command.

shell> mysqlserverclone --server=root:root@localhost \
          --new-data=/source/temp_clone --new-port=3307 --root=root --delete \
          --new-id=123 --mysqld="--log-bin=my_log --general-log --slow-query-log \
          --user=mysql --log-error=/source/temp_clone"
# Cloning the MySQL server running on localhost.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Setting the root password...
# Connection Information:
#  -uroot -proot --socket=/source/temp_clone/mysql.sock

Now that we have a running instance, we can export all of the data from the host to the clone.

shell> mysqldbexport --server=root:root@localhost:3306 --export=both --all > data.sql
shell> mysqldbimport --server=root:root@localhost:3307 --import=both data.sql
# Source on localhost: ... connected.
# Importing definitions and data from data.sql.


As you can see, this is a multiple step process. We saw examples of using the mysqlserverinfo, mysqlserverclone, mysqldbexport, and mysqldbimport utilities.

Notice in the example we used port 3307 for the clone which is reflected in the mysqldbimport utility --server option.

Permissions Required

The user must have permission to read all databases. Since we are using the root account for these examples (and you typically would), permissions are not generally a problem.

You also need permissions to create the new data directory and write data to it.

Tips and Tricks

If you want to copy all of the users and their permissions, check out the mysqluserclone utility.