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 will 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.
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
option with mysqlserverinfo. On POSIX
systems, we can use the
ps command to find
the command line options.
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. #...done. shell>
my_print_defaults mysqld /etc/my.cnf--port=3306 --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --server_id=5 --log-bin=my_log --general_log --slow_query_log --innodb_data_file_path=ibdata1:778M;ibdata2:50M:autoextend shell>
ps -f 2377UID PID PPID C STIME TTY TIME CMD 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/me.local.pid \ --port=3306
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
Using these options and choosing a new data directory, we can create a new instance of the host server using the following command.
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 #...done.
Now that we have a running instance, we can export all of the data from the host to the clone.
mysqldbexport --server=root:root@localhost:3306 --export=both --all > data.sqlshell>
mysqldbimport --server=root:root@localhost:3307 --import=both data.sql# Source on localhost: ... connected. # Importing definitions and data from data.sql. #...done.
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.
If you want to copy all of the users and their permissions, check out the mysqluserclone utility.