Mysql 多实例multi_利用mysqld_multi配置单机多实例

目的

在一台服务器上做主从复制，充分利用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 AddressForeign

AddressStatePID/Program

name

tcp00

192.168.122.1:530.0.0.0:*LISTEN3808/dnsmasq

tcp00

:::3307:::*LISTEN24335/mysqld

tcp00

:::3308:::*LISTEN24241/mysqld

tcp00

:::22:::*LISTEN3537/sshd

udp00

192.168.122.1:530.0.0.0:*3808/dnsmasq

udp00

0.0.0.0:670.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)