Use mysqld_multi to configure multiple MySQL Server instances in a single system.

Use mysqld_multi to configure multiple MySQL Server instances in a single system.

This article describes how to use MySQL source code to install MySQL and use mysqld_multi to configure multiple instances.

1. Download the MySQL source code installation version.

Go to the MySQL official website to find the corresponding version to download. This article downloads version 5.1.

Copy codeThe Code is as follows:
Ftp://ftp.mirrorservice.org/sites/ftp.mysql.com/Downloads/MySQL-5.1/mysql-5.1.60.tar.gz wget-c

2. Unzip and install

Copy codeThe Code is as follows:
Tar-zxv-f mysql-5.1.60.tar.gz

./Configure -- prefix =/usr/local/mysql -- with-charset = utf8
-- With-extra-charset = all -- enable-thread-safe-client -- enable-validator
-- With-readline -- with-big-tables
-- With-named-curses-libs =/usr/lib/libncursesw. so.5

Make & make install

3. Add users and user groups

Copy codeThe Code is as follows:
Groupadd mysql # Add a mysql user group
Useradd-g mysql # create a mysql user and put it in the mysql Group
Chown-R root: mysql/usr/local/mysql # modify mysql file attributes

4. initialize the data directory

Copy codeThe Code is as follows:
# Add the tools used to the/usr/bin directory
Ln-s/usr/local/mysql/bin/mysqld_multi/usr/bin/mysqld_multi
Ln-s/usr/local/mysql/bin/mysql_install_db/usr/bin/mysql_install_db

# Initialize four Data Directories
Mysql_install_db -- datadir =/usr/local/var/mysql1 -- user = mysql
Mysql_install_db -- datadir =/usr/local/var/mysql2 -- user = mysql
Mysql_install_db -- datadir =/usr/local/var/mysql3 -- user = mysql
Mysql_install_db -- datadir =/usr/local/var/mysql4 -- user = mysql

# Modifying attributes
Chown-R mysql/usr/local/var/mysql1
Chown-R mysql/usr/local/var/mysql2
Chown-R mysql/usr/local/var/mysql3
Chown-R mysql/usr/local/var/mysql4

5. Configure the multi-instance Startup Script

Copy to the/etc/init. d/directory from the MySQL source code

Copy codeThe Code is as follows:
Cp/usr/local/src/mysql-5.1.60/support-files/mysqld_multi.server/etc/init. d/mysqld_multi.server

# Modify basedir and bindir to the installation path
Basedir =/usr/local/mysql
Bindir =/usr/local/mysql/bin

6. Configure the multi-instance Database Configuration File

Use mysqld_multi to view the template method of the configuration file. The command is mysqld_multi -- example.
Create a file/etc/mysqld_multi.cnf in the/etc/directory, paste the file generated by mysqld_multi -- example, and modify the corresponding attributes, such as mysqld, mysqladmin, socket, port, pid-file, datadir, user, etc.
Copy codeThe Code is as follows:
[Mysqld_multi]
Mysqld =/usr/local/mysql/bin/mysqld_safe
Mysqladmin =/usr/local/mysql/bin/mysqladmin
# User = mysql
# Password = my_password

[Mysqld1]
Socket =/usr/local/var/mysql1/mysql1.sock
Port = 3306
Pid-file =/usr/local/var/mysql1/mysql1.pid
Datadir =/usr/local/var/mysql1
# Language =/usr/local/mysql/share/mysql/english
User = mysql

[Mysqld2]
Socket =/usr/local/var/mysql2/mysql2.sock
Port = 3307
Pid-file =/usr/local/var/mysql2/mysql2.pid
Datadir =/usr/local/var/mysql2
# Language =/usr/local/mysql/share/mysql/english
User = mysql

[Mysqld3]
Socket =/usr/local/var/mysql3/mysql3.sock
Port = 3308
Pid-file =/usr/local/var/mysql3/mysql3.pid
Datadir =/usr/local/var/mysql3
# Language =/usr/local/mysql/share/mysql/english
User = mysql

[Mysqld4]
Socket =/usr/local/var/mysql4/mysql4.sock
Port = 3309
Pid-file =/usr/local/var/mysql4/mysql4.pid
Datadir =/usr/local/var/mysql4
# Language =/usr/local/mysql/share/mysql/english
User = mysql

7. Start a multi-instance Database

Copy codeThe Code is as follows:
Add/usr/local/mysql/bin to the $ PATH environment variable.
Export PATH =/usr/local/mysql/bin: $ PATH

# Viewing database status
Mysqld_multi -- defaults-extra-file =/etc/mysqld_multi.cnf report
# None of the results are running
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

# Start
Mysqld_multi -- defaults-extra-file =/etc/mysqld_multi.cnf start
# Result:
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

# To start an instance, you can add data such as 1, 2, and 3 after start and stop.

8. View running results

Copy codeThe Code is as follows:
# Check whether the corresponding port has been listened on
Netstat-tunlp

# Check whether an active process exists
Ps-aux | grep mysql

9. log on to the database

Copy codeThe Code is as follows:
# Enter the database with port 3306
Mysql-uroot-p-h127.0.0.1-P3306

# Log on through the sock File
Mysql-uroot-p-S/usr/local/var/mysql1/mysql1.sock

# Viewing socket files
Mysql> show variables like 'socket ';

# Viewing pid files
Mysql> show variables like '% pid % ';

How does MySQL Start Multiple instances on a db server?

I. Upload RPM package to the server II. Install mysql Server rpm-ivhMySQL-server-5.5.8-1.rhel5.x86_64.rpm III. Install mysql client rpm-ivhMySQL-client-5.5.8-1.rhel5.x86_64.rpm IV. Create a directory to create a directory under/data, move the original data directory/var/lib/mysql to/data/mysql/data1 and/data/mysql/data2, and modify the mysql directory and sub-directory permissions. PS: several important directories by default after installation/var/lib/mysql data files, log Files, etc./usr/bin client programs and scripts/usr/share/mysql error messages and Character Set files/etc/rc. d/init. d/mysql start script file create directory modify permission 5. Modify and upload the configuration file my. cnf/usr/share/mysql from the server Copy the my-innodb-heavy-4G.cnf to the local directory. Edit the contents to add directories of data files and log files, and modify the socket directory. 6. Start multiple services with mysql_multi: mysqld_multi -- defaults-file =/etc/my. cnf start1, 2 & check whether startup: mysqld_multi -- defaults-file =/etc/my. cnfreport 1 7. Check whether the port is enabled 8. Connect to the specified mysql Server mysql-u root-S/tmp/mysql1.sock or mysql-uroot-P3307-h127.0.0.1 9. Disable the firewall.

Configure a single machine to start multiple mysql instances and run/mysqld_multi -- defaults-extra-file =/etc/mysqld_multicnf start 2

You have started it with root. Add a parameter to -- user = mysql.
------------------------------------------------------------------
./Mysqld_multi -- user = mysql -- defaults-extra-file =/etc/mysqld_multi.cnf start 2