centos下一台机器多个Mysql实例

分类:CentOS运维 阅读:55126 次

一台机器运行多个mysql服务(mysqld_multi的使用)


|举报|字号订阅

*****************
***创建的准备***
*****************
根据Mysql管理手册中提到:每个Mysql的服务都可为独立的,所以它都调用一个my.cnf中各自不同的启动选项–就是下文中将提到的GNR值,使用不同的端口,生成各自的套接文件,服务的数据库都是独立的。
所需要的准备有:(其实都是现成的)
1.只需要安装一套mysql的服务器软件就ok
2.需要有mysqld_multi
3.需要修改/etc/my.cnf

解释:
1../configure –prefix=/usr/local/mysql–datadir=/usr/local/mysql/data1–sysconfdir=/etc
备注:–prefix将MYSQL安装到/usr/local/mysql,
–datadir将数据库生成/usr/local/mysql/data1
–sysconfdir是指定mysql使用到的my.cnf配置文件的搜索路径为/etc
其他mysql安装过程略.

2.mysqld_multi是管理多个mysqld的服务进程,这些服务进程使用不同的unix socket或是监听于不同的端口。他可以启动、停止和监控当前的服务状态。

3.程序在my.cnf(或是在–config-file自定义的配置文件)中搜索 [mysqld#]段,”#”可以是任意的正整数。这个正整数就是在下面提及的段序列,即GNR。段的序号做为mysqld_multi的参数,来区别不同的段,这样你就可以控制特定mysqld进程的启动、停止或得到他的报告信息。这些组里的参数就像启动一个mysqld所需要的组的参数一样。但是,如果使用多服务,必须为每个服务指定一个unix socket或端口

下面贴出我的/etc/my.cnf文件
(只贴出涉及到mysql多服务的部分)

[mysqld_multi] ###新增加的
mysqld = /home/usr/local/bin/mysqld_safe ###新增加的
mysqladmin = /home/usr/local/mysql/bin/mysqladmin###新增加的
user=root ###新增加的
password=root ###新增加的

[mysqld1]###将mysqld改为mysqld1,增加个GNR值
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_file_per_table
default-character-set=utf8
log=general-log
log-slow-queries=slow-log
log-bin=mysql-bin
server-id = 1
innodb_log_group_home_dir = /home/anmh/mysql_test/arch_mysql/
innodb_log_arch_dir = /home/anmh/mysql_test/arch_mysql/
innodb_lock_wait_timeout = 5

[mysqld2] ###新增加的,GNR值为2
port = 3307 ###修改为新的端口
socket = /tmp/mysql2.sock ###修改为新的socket
datadir=/home/anmh/mysql1/datadir/ ###修改为新的目录
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_file_per_table
default-character-set=utf8
log=/home/anmh/mysql1/logdir/general-log ###修改为新的目录
log-slow-queries=/home/anmh/mysql1/logdir/slow-log###修改为新的目录
log-bin=/home/anmh/mysql1/logdir/bin-log ###修改为新的目录
innodb_log_group_home_dir = /home/anmh/mysql1/datadir/idb###修改为新的目录
innodb_log_arch_dir = /home/anmh/mysql1/datadir/idb/archdir###修改为新的目录
innodb_lock_wait_timeout = 5 ###修改为新的目录

这里我的配置文件中有mysql1, mysqld2两个服务,分别使用3306,3307两个端口,每个datadir路径也不同。其他的一些设置可用my.cnf原来的内容

**************************
***创建目录和必要的库***
**************************
目录就是上面my.cnf中的路径
mkdir /home/anmh/mysql1
mkdir /home/anmh/mysql1/logdir /home/anmh/mysql1/datadir
mkdir /home/anmh/mysql1/datadir/idb
mkdir /home/anmh/mysql1/datadir/idb/archdir

必要的库指的是mysql库(原因数据权限)
这里就直接把mysql1中的copy过来
# cp -Rf /usr/local/var/mysql /home/anmh/mysql1/datadir
# chmod -R 777 /home/anmh/mysql1/datadir

*************************
*** mysqld_multi命令 ***
*************************
使用方法:# mysqld_multi [options] {start|stop|report} [GNR[,GNR]…]

start,stop和report是指你想到执行的操作。你可以在单独的服务或是多服务上指定一个操作,区别于选项后面的GNR列表。如果没有指定GNR列表,那么mysqld_multi将在所有的服务中根据选项文件进行操作。

每一个GNR的值是组的序列号或是一个组的序列号范围。此项的值必须是组名字最后的数字,比如说如果组名为mysqld17,那么此项的值则为17.如果指定一个范围,使用”-”(破折号)来连接二个数字。如GNR的值为10-13,则指组mysqld10到组mysqld13。多个组或是组范围可以在命令行中指定,使用”,”(逗号)隔开。不能有空白的字符(如空格或tab),在空白字符后面的参数将会被忽略。 (注:GNR值就是我们定义my.cnf中mysqld#中的值,我这里只有1-4).

# /usr/local/mysq/bin/mysqld_multi –config-file=/etc/my.cnf start 1 只启动 第一个mysql服务,相关文件由my.cnf中mysql1设定.

# /usr/local/mysq/bin/mysqld_multi –config-file=/etc/my.cnf stop 1 启止 第一个mysql服务

# /usr/local/mysq/bin/mysqld_multi –config-file=/etc/my.cnf start 1-4, 启动 第1至4mysql服务,其实就是我这里的全部.

# /usr/local/mysq/bin/mysqld_multi –config-file=/etc/my.cnf report 1-4

查看启动:

# ps aux

root 10467 0.0 0.2 2712 1300 pts/0 S 18:59 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –port=3306 –socket=/tmp/mysql.sock1

root 10475 0.0 0.2 2712 1300 pts/0 S 18:59 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –port=3307 –socket=/tmp/mysql.sock2

root 10482 0.0 0.2 2716 1300 pts/0 S 18:59 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –port=3308 –socket=/tmp/mysql.sock3

root 10487 0.0 0.2 2716 1300 pts/0 S 18:59 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –port=3309 –socket=/tmp/mysql.sock4

*****************
***遇到的问题***
*****************
一. 第2个server不能启动;
解决办法:
查看err日志(默认在datadir目录中)
报错信息为:
100919 11:02:07 [ERROR] Could not use /home/anmh/mysql1/general-log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
/usr/local/libexec/mysqld: File ‘/home/anmh/mysql1/slow-log’ not found (Errcode: 13)
100919 11:02:07 [ERROR] Could not use /home/anmh/mysql1/slow-log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
/usr/local/libexec/mysqld: File ‘/home/anmh/mysql1/bin-log.index’ not found (Errcode: 13)
100919 11:02:07 [ERROR] Aborting
100919 11:02:07 [Note] /usr/local/libexec/mysqld: Shutdown complete
查看具体的错误说明:
[root@dbadb1 bin]# /usr/local/bin/perror 13
OS error code 13: Permission denied

原因是:
1.设定的log目录错误:原本目录是/home/anmh/mysql1/logdir/。 但是这里都设成了:/home/anmh/mysql1/
修改/etc/my.cnf中的log*的目录设置
log=/home/anmh/mysql1/logdir/general-log
log-slow-queries=/home/anmh/mysql1/logdir/slow-log
log-bin=/home/anmh/mysql1/logdir/bin-log

2.文件的目录没有权限:
[root@dbadb1 mysql1]# ll
drwxr-xr-x 3 root root 4096 Sep 19 11:03 datadir
drwxr-xr-x 2 root root 4096 Sep 19 11:03 logdir
[root@dbadb1 mysql1]# chmod -Rf 777 logdir datadir
[root@dbadb1 mysql1]# ll
drwxr-xr-x 3 root root 4096 Sep 19 11:03 datadir
drwxr-xr-x 2 root root 4096 Sep 19 11:03 logdir

二. 如何连接进入指定的mysql服务器
1.使用socket:
在mysql服务器启动的时候,每个mysql服务器socket已经建立好了。
使用-S来指定socket登录
[root@dbadb1 bin]# mysql -uroot -p -S/tmp/mysql2.sock ####这里是要登录第2个mysql
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.40-log Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

2.使用tcp
需要指定ip或者host,并且加上port
[root@dbadb1 bin]# mysql -uroot -p -h10.254.3.44 -P3307 ####这里是要登录第2个mysql:3307是它的新端口。如果不加的话,默认是3306
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.40-log Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

全部介绍完毕,有问题欢迎留言

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

源码编译安装Mysql5.1,如何支持一台机器多个Mysql实例

2009-12-02 04:07

一、安装单实例Mysql5.1服务器
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql --with-charset=utf8 --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --disable-shared --with-mysqld-user=mysql --with-extra-charsets=gb2312,big5,gbk
shell> make
shell> make install
shell> cp support-files/my-medium.cnf /etc/my.cnf

cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --level 2345 mysqld on
PATH=/usr/local/mysql/bin:$PATH
echo "PATH=/usr/local/mysql/bin:\$PATH" >> /etc/profile

shell> cd /usr/local/mysql
shell> bin/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql var
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &


二、多实例安装Mysql5.1服务器


一、安装监听端口3306的Mysql服务器
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql3306 --with-unix-socket-path=/usr/local/mysql3306/var/mysql.sock --sysconfdir=/usr/local/mysql3306/etc --with-charset=utf8 --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --disable-shared --with-mysqld-user=mysql --with-extra-charsets=gb2312,big5,gbk
shell> make
shell> make install
shell> mkdir /usr/local/mysql3306/etc
shell> cp support-files/my-medium.cnf /usr/local/mysql3306/etc/my.cnf

cp support-files/mysql.server /etc/init.d/mysqld3306
chmod 755 /etc/init.d/mysqld3306
chkconfig --add mysqld3306
chkconfig --level 2345 mysqld3306 on
PATH=/usr/local/mysql3306/bin:$PATH
echo "PATH=/usr/local/mysql3306/bin:\$PATH" >> /etc/profile

shell> cd /usr/local/mysql3306
shell> bin/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql var
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &

修改my.cnf设置监听端口为3306

二、安装监听端口3307的Mysql服务器
shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql3307 --with-unix-socket-path=/usr/local/mysql3307/var/mysql.sock --sysconfdir=/usr/local/mysql3307/etc --with-charset=utf8 --without-debug --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --disable-shared --with-mysqld-user=mysql --with-extra-charsets=gb2312,big5,gbk
shell> make
shell> make install
shell> mkdir /usr/local/mysql3307/etc
shell> cp support-files/my-medium.cnf /usr/local/mysql3307/etc/my.cnf

cp support-files/mysql.server /etc/init.d/mysql3307
chmod 755 /etc/init.d/mysql3307
chkconfig --add mysql3307
chkconfig --level 2345 mysql3307 on
PATH=/usr/local/mysql3306/bin:$PATH
echo "PATH=/usr/local/mysql3306/bin:\$PATH" >> /etc/profile

shell> cd /usr/local/mysql3307
shell> bin/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql var
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &

修改my.cnf设置监听端口为3307


三、编译参数解析
编译参数解析
--prefix=/usr/local/mysql3306 指定安装路径
--with-unix-socket-path=/usr/local/mysql3306/var/mysql.sock 指定socket的套接字文件,多个实例时必须指定,否则多个实例会同时默认取/tmp/mysql.sock
--sysconfdir=/usr/local/mysql3306/etc mysql实例启动后的配置文件所在,默认是安装路径下的[PREFIX/etc]此处指定的目的是强调需为每个实例配置my.cnf,同时需要保证 /etc/my.cnf不存在,因为启动脚本一般会先去读/etc/my.cnf
--with-charset=utf8 默认字符集
--with-extra-charsets=gb2312,big5,gbk 支持的其他字符集,减少不必要的开销,Mysql默认支持20多种字符集
--without-debug 去掉调试模式,据说可以优化
--enable-assembler 使用一些字符函数的汇编版本,据说可以优化性能
--with-client-ldflags=-all-static 以纯静态方式编译客户端,如果服务器实例上不需要客户端,这项可以不要,不过建议还是留着,登录到数据库服务器上,直接mysql已经是默认习惯了。
--with-mysqld-ldflags=-all-static 以纯静态方式编译服务端,据说对性能优化很有用
--disable-shared 不提供动态链接库
--with-mysqld-user=mysql 启动用户设置为mysql

在同一台机器上运行多个 MySQL 服务器

Mysql中的mysqld_multi命令,可用于在一台物理服务器运行多个Mysql服务

一、准备工作

1.MySQL源码安装

[root@localhost ~]# tar zxvf mysql-5.0.80.tar.gz
[root@localhost ~]# cd mysql-5.0.80
[root@localhost mysql-5.0.80]# ./configure --prefix=/usr/local/mysql
[root@localhost mysql-5.0.80]# make
[root@localhost mysql-5.0.80]# make install

2.建立帐户

[root@localhost mysql-5.0.80]# adduser mysql
[root@localhost mysql-5.0.80]# chown -R mysql:root /usr/local/mysql

3.初始化授权表

[root@localhost mysql]# su mysql
[root@localhost mysql]# cd /usr/local/mysql
[root@localhost mysql]# ./bin/mysql_install_db

二、多个MySQL服务

创建my.cnf 

[root@localhost mysql]# touch /etc/my.cnf
[root@localhost mysql]#vi /etc/my.cnf

my.cnf的内容如下

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
user = mysql
password = d3.zone


[mysqld1]
port =3306
socket = /tmp/mysql.sock1
pid-file=/usr/local/mysql/var1/db-app1.pid
log=/usr/local/mysql/var1/db-app.log
datadir = /usr/local/mysql/var1
user = mysql


[mysqld2]
port =3307
socket = /tmp/mysql.sock2
pid-file = /usr/local/mysql/var2/db-app2.pid
datadir = /usr/local/mysql/var2
log=/usr/local/local/var2/db-app.log
user = mysql


[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

配置中不同mysql 服务建立各自文件夹和初始数据库等

[root@localhost mysql]# cd /usr/local/mysql
[root@localhost mysql]# mv var ./var1
[root@localhost mysql]# cp var1 ./var2 -R

现在,可以通过mysqld_multi的参数来启动mysqld_multi

[root@localhost mysql]#/usr/local/mysql/bin/mysqld_multi --config-file=/etc/my.cnf start 1-2 

用report参数查看运行状态

至此,我们的两个mysql服务就启动了,可以根据需要添加更多的mysql服务,这里我就不在??铝恕?/p>

在客户端访问通过mysqld_multi启动的mysql服务时,需要指定相应的mysql服务端口

在服务器本机上,还可以通过mysql的socket来指定相应mysql服务

[root@localhost mysql]# mysql --socket=/tmp/mysql.sock1 -uroot -p