Each MySQL Instance on a machine should have its own data
directory. The location is specified using the
There are different methods of setting up a data directory for a new instance:
Create a new data directory.
Copy an existing data directory.
The following discussion provides more detail about each method.
Normally, you should never have two servers that update data in the same databases. This may lead to unpleasant surprises if your operating system does not support fault-free system locking. If (despite this warning) you run multiple servers using the same data directory and they have logging enabled, you must use the appropriate options to specify log file names that are unique to each server. Otherwise, the servers try to log to the same files.
Even when the preceding precautions are observed, this kind of
setup works only with
MERGE tables, and not with any of the other
storage engines. Also, this warning against sharing a data
directory among servers always applies in an NFS environment.
Permitting multiple MySQL servers to access a common data
directory over NFS is a very bad idea. The
primary problem is that NFS is the speed bottleneck. It is not
meant for such use. Another risk with NFS is that you must
devise a way to ensure that two or more servers do not interfere
with each other. Usually NFS file locking is handled by the
lockd daemon, but at the moment there is no
platform that performs locking 100% reliably in every situation.
With this method, the data directory will be in the same state as when you first install MySQL. It will have the default set of MySQL accounts and no user data.
On Unix, initialize the data directory by running mysql_install_db. See Section 2.18.1, “Unix Postinstallation Procedures”.
On Windows, the data directory is included in MySQL distributions.
If you obtain a distribution in Windows Zip archive format, you
can unpack it into a temporary location, then copy the
data directory from this location to where
you are setting up the new instance.
With this method, any MySQL accounts or user data present in the data directory are carried over to the new data directory.
Stop the existing MySQL instance using the data directory. This must be a clean shutdown so that the instance flushes any pending changes to disk.
Copy the data directory to the location where the new data directory should be.
my.ini option file used by the existing
instance. This serves as a basis for the new instance.
Modify the new option file so that any pathnames referring to the original data directory refer to the new data directory. Also, modify any other options that must be unique per instance, such as the TCP/IP port number and the log files. For a list of parameters that must be unique per instance, see Section 5.3, “Running Multiple MySQL Instances on One Machine”.
Start the new instance, telling it to use the new option file.