mysql单机多实例 java_利用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)