注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

good luck guys

welcome

 
 
 

日志

 
 

利用mysqld_multi配置单机多实例_Web开发_新浪博客  

2011-08-30 09:12:11|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 

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

工具

mysql_install_db

mysqld_multi

配置过程

mysql_install_db --datadir=/var/lib/mysql2 --user=mysql

mysql_install_db --datadir=/var/lib/mysql3

……

mysql_install_db命令会自动创建datadir目录。

[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.

#

#

#The script can be found from support-files/mysqld_multi.server.sh

#in MySQL distribution. (Verify the script before using)

#

上面是mysqld_multi工具的简单说明,下面是根据实际情况,修改的配置文件。为方便测试,将多实例的配置文件命名为

[mysqld_multi]

mysqld= /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

[mysqld2]

/mysql2.sock

port= 3307

#pid-file= /var/lib/mysql2/hostname.pid2

datadir= /var/lib/mysql2

#language= /usr/share/mysql/mysql/english

[mysqld3]

3/mysql3.sock

port= 3308

#pid-file= /var/lib/mysql3/hostname.pid3

datadir= /var/lib/mysql3

#language= /usr/share/mysql/mysql/swedish

注:我做测试的时候,把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)

mysql> show variables like '%pid%';

+---------------+---------------------------------+

| Variable_name | Value|

+---------------+---------------------------------+

+---------------+---------------------------------+

1 row in set (0.00 sec)

发评论

以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

新浪BLOG意见反馈留言板 不良信息反馈 电话:4006900000 提示音后按1键(按当地市话标准计费) 欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

Copyright ? 1996 - 2011 SINA Corporation, All Rights Reserved




引文来源  利用mysqld_multi配置单机多实例_Web开发_新浪博客
  评论这张
 
阅读(636)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017