Running Multiple MySQL Instances on One Machine

In some cases, you might want to run multiple instances of MySQL on a single machine. You might want to test a new MySQL release while leaving an existing production setup undisturbed. Or you might want to give different users access to different mysqld servers that they manage themselves. It is possible to use a different MySQL server binary per instance, or use the same binary for multiple instances, or any combination of the two approaches. For example, you might run a server from MySQL 5.0 and one from MySQL 5.1, to see how different versions handle a given workload. Or you might run multiple instances of the current production version, each managing a different set of databases. Whether or not you use distinct server binaries, each instance that you run must be configured with unique values for several operating parameters. This eliminates the potential for conflict between instances.   12.1.  Setting Up Multiple Data Directories Each MySQL Instance on a machine should have its own data directory. The location is specified using the --datadir=path option. There are different methods of setting up a data directory for a new instance:

• Create a new data directory
• Copy an existing data directory

  Note: 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.   Create a New Data Directory: 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.   Copy an Existing Data Directory: With this method, any MySQL accounts or user data present in the data directory are carried over to the new data directory.

1. 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.
2. Copy the data directory to the location where the new data directory should be.
3. Copy the my.cnf or my.ini option file used by the existing instance. This serves as a basis for the new instance.
4. 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.
5. Start the new instance, telling it to use the new option file.

  12.2.  Running Multiple MySQL Instances on Unix: One way is to run multiple MySQL instances on Unix is to compile different servers with different default TCP/IP ports and Unix socket files so that each one listens on different network interfaces. Compiling in different base directories for each installation also results automatically in a separate, compiled-in data directory, log file, and PID file location for each server. Assume that an existing 5.0 server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new 5.1.57 server to have different operating parameters, use a configure command something like this: shell> ./configure --with-tcp-port=port_number \ --with-unix-socket-path=file_name \ --prefix=/usr/local/mysql-5.1.57 Here, port_number and file_name must be different from the default TCP/IP port number and Unix socket file path name, and the --prefix value should specify an installation directory different from the one under which the existing MySQL installation is located. If you have a MySQL server listening on a given port number, you can use the following command to find out what operating parameters it is using for several important configurable variables, including the base directory and Unix socket file name: shell> mysqladmin --host=host_name --port=port_number variables You need not compile a new MySQL server just to start with a different Unix socket file and TCP/IP port number. It is also possible to use the same server binary and start each invocation of it with different parameter values at runtime. One way to do so is by using command-line options: shell> mysqld_safe --socket=file_name --port=port_number To start a second server, provide different --socket and --port option values, and pass a –datadir=path option to mysqld_safe so that the server uses a different data directory. Alternatively, put the options for each server in a different option file, then start each server using a --defaults-file option that specifies the path to the appropriate option file. For example, if the option files for two server instances are named /usr/local/mysql/my.cnf and /usr/local/mysql/my.cnf2, start the servers like this: command: shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf shell> mysqld_safe --defaults-file=/usr/local/mysql/my.cnf2   Using Client Programs in a Multiple-Server Environment: To connect with a client program to a MySQL server that is listening to different network interfaces from those compiled into your client, you can use one of the following methods:

• Start the client with --host=host_name --port=port_number to connect using TCP/IP to a remote server, with --host=127.0.0.1 --port=port_number to connect using TCP/IP to a local server, or with --host=localhost --socket=file_name to connect to a local server using a Unix socket file.

MySQL Instance Manager

MySQL Instance Manager is been deprecated in MySQL 5.1 and is removed in MySQL 5.5.   12.3  Using mysqld_multi for Managing Multiple MySQL Servers mysqld_multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports. It can start or stop servers, or report their current status. mysqld_multi searches for groups named [mysqldN] in my.cnf (or in the file named by the --config-file option). N can be any positive integer. This number is referred to in the following discussion as the option group number, or GNR.

1. Initialize the new datadir.
2. Change the option file.

Sample Option file is as below: [mysqld_multi] mysqld     = /usr/local/bin/mysqld_safe mysqladmin = /usr/local/bin/mysqladmin user       = multi_admin password   = multipass   [mysqld2] socket     = /tmp/mysql.sock2 port       = 3307 pid-file   = /usr/local/mysql/var2/hostname.pid2 datadir    = /usr/local/mysql/var2 language   = /usr/local/share/mysql/english user       = john [mysqld3] socket     = /tmp/mysql.sock3 port       = 3308 pid-file   = /usr/local/mysql/var3/hostname.pid3 datadir    = /usr/local/mysql/var3 language   = /usr/local/share/mysql/swedish user       = monty   To invoke mysqld_multi, use the following syntax: shell> mysqld_multi [options] {start|stop|report} [GNR[,GNR] ...] start, stop, and report indicate which operation to perform. You can perform the designated operation for a single server or multiple servers, depending on the GNR list that follows the option name. If there is no list, mysqld_multi performs the operation for all servers in the option file. Each GNR value represents an option group number or range of group numbers. The value should be the number at the end of the group name in the option file. For example, the GNR for a group named [mysqld17] is 17. To specify a range of numbers, separate the first and last numbers by a dash. The GNR value 10-13 represents groups [mysqld10] through [mysqld13]. Multiple groups or group ranges can be specified on the command line, separated by commas. This command starts a single server using option group [mysqld17]: shell> mysqld_multi start 17 This command stops several servers, using option groups [mysqld8] and [mysqld10] through [mysqld13]: shell> mysqld_multi stop 8,10-13 For an example of how you might set up an option file, use this command: shell> mysqld_multi --example   Options:   --example                               Display a sample option file. --mysqladmin=prog_name     The mysqladmin binary to be used to stop servers. --mysqld=prog_name             The mysqld binary to be used. --password=password            The password of the MySQL account to use when invoking mysqladmin. --user=user_name                 The user name of the MySQL account to use when invoking mysqladmin. Check Out Our Related Courses Sql Tutorials Oracle Pl SQL Tutorials Top 50 Sql Server Interview Questions SQL Server T-SQL Training In Bangalore