利用mysqld_multi配置单机多实例

目的 在一台服务器上做主从复制，充分利用CPU资源。下面的文档首先说明如何在一台服务器开启多个MySQL

环境 MySQL 5.5

工具

mysql_install_db

mysqld_multi

配置过程

1. 利用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目录。

2. 利用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选项注释掉了。

3. 启动

# 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

4. 测试

如果--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)