目的 在一台服务器上做主从复制，充分利用CPU资源。下面的文档首先说明如何在一台服务器开启多个MySQL
环境 MySQL 5.5
工具
mysql_install_db
mysqld_multi
配置过程
- 利用mysql_install_db生成数据库
mysql_install_db --datadir=/var/lib/mysql2 --user=mysql
mysql_install_db --datadir=/var/lib/mysql3 --user=mysql
……
mysql_install_db命令会自动创建datadir目录。
- 利用mysqld_multi工具生成配置文件
[root@ora01 ~]# mysqld_multi --example
# This is an example of a my.cnf file for mysqld_multi.
# Usually this file is located in home dir ~/.my.cnf or /etc/my.cnf
#
# SOME IMPORTANT NOTES FOLLOW:
#
# 1.COMMON USER
#
# Make sure that the MySQL user, who is stopping the mysqld services, has
# the same password to all MySQL servers being accessed by mysqld_multi.
# This user needs to have the 'Shutdown_priv' -privilege, but for security
# reasons should have no other privileges. It is advised that you create a
# common 'multi_admin' user for all MySQL servers being controlled by
# mysqld_multi. Here is an example how to do it:
#
# GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'password'
#
# You will need to apply the above to all MySQL servers that are being
# controlled by mysqld_multi. 'multi_admin' will shutdown the servers
# using 'mysqladmin' -binary, when 'mysqld_multi stop' is being called.
#
# 2.PID-FILE
#
# If you are using mysqld_safe to start mysqld, make sure that every
# MySQL server has a separate pid-file. In order to use mysqld_safe
# via mysqld_multi, you need to use two options:
#
# mysqld=/path/to/mysqld_safe
# ledir=/path/to/mysqld-binary/
#
# ledir (library executable directory), is an option that only mysqld_safe
# accepts, so you will get an error if you try to pass it to mysqld directly.
# For this reason you might want to use the above options within [mysqld#]
# group directly.
#
# 3.DATA DIRECTORY
#
# It is NOT advised to run many MySQL servers within the same data directory.
# You can do so, but please make sure to understand and deal with the
# underlying caveats. In short they are:
# - Speed penalty
# - Risk of table/data corruption
# - Data synchronising problems between the running servers
# - Heavily media (disk) bound
# - Relies on the system (external) file locking
# - Is not applicable with all table types. (Such as InnoDB)
# Trying so will end up with undesirable results.
#
# 4.TCP/IP Port
#
# Every server requires one and it must be unique.
#
# 5.[mysqld#] Groups
#
# In the example below the first and the fifth mysqld group was
# intentionally left out. You may have 'gaps' in the config file. This
# gives you more flexibility.
#
# 6.MySQL Server User
#
# You can pass the user=... option inside [mysqld#] groups. This
# can be very handy in some cases, but then you need to run mysqld_multi
# as UNIX root.
#
# 7.A Start-up Manage Script for mysqld_multi
#
# In the recent MySQL distributions you can find a file called
# mysqld_multi.server.sh. It is a wrapper for mysqld_multi. This can
# be used to start and stop multiple servers during boot and shutdown.
#
# You can place the file in /etc/init.d/mysqld_multi.server.sh and
# make the needed symbolic links to it from various run levels
# (as per Linux/Unix standard). You may even replace the
# /etc/init.d/mysql.server script with it.
#
# Before using, you must create a my.cnf file either in /usr/my.cnf
# or /root/.my.cnf and add the [mysqld_multi] and [mysqld#] groups.
#
# The script can be found from support-files/mysqld_multi.server.sh
# in MySQL distribution. (Verify the script before using)
#
上面是mysqld_multi工具的简单说明，下面是根据实际情况，修改的配置文件。为方便测试，将多实例的配置文件命名为
mysqld_multi.cnf。未指定的其他MySQL参数，将使用默认的。
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
#user = multi_admin
#password = my_password
[mysqld2]
socket = /var/lib/mysql2/mysql2.sock
port = 3307
#pid-file = /var/lib/mysql2/hostname.pid2
datadir = /var/lib/mysql2
#language = /usr/share/mysql/mysql/english
user = mysql
[mysqld3]
socket = /var/lib/mysql3/mysql3.sock
port = 3308
#pid-file = /var/lib/mysql3/hostname.pid3
datadir = /var/lib/mysql3
#language = /usr/share/mysql/mysql/swedish
user = mysql
注：我做测试的时候，把pid-file和language选项注释掉了。
- 启动
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start
等一会儿…………，注意start后面跟具体的数字，比如start 2，则单独开启mysqld2这个实例。
# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
[root@ora01 mysql2]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.122.1:53 0.0.0.0:* LISTEN 3808/dnsmasq
tcp 0 0 :::3307 :::* LISTEN 24335/mysqld
tcp 0 0 :::3308 :::* LISTEN 24241/mysqld
tcp 0 0 :::22 :::* LISTEN 3537/sshd
udp 0 0 192.168.122.1:53 0.0.0.0:* 3808/dnsmasq
udp 0 0 0.0.0.0:67 0.0.0.0:* 3808/dnsmasq
连接
mysql -uroot -p -h127.0.0.1 -P3307
- 测试
如果--defaults-extra-file选项指定的配置文件中，未具体设置路径socket，则会用mysql默认的路径。
mysql> show variables like 'socket';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| socket | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+
1 row in set (0.00 sec)
但是pid文件，会自动在数据目录内生成
mysql> show variables like '%pid%';
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| pid_file | /var/lib/mysql2/ora01.dh.cn.pid |
+---------------+---------------------------------+
1 row in set (0.00 sec)