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

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

|举报|字号

********************创建的准备********************根据Mysql管理手册中提到:每个Mysql的服务都可为独立的，所以它都调用一个my.cnf中各自不同的启动选项–就是下文中将提到的GNR值,使用不同的端口,生成各自的套接文件,服务的数据库都是独立的。所需要的准备有：(其实都是现成的)1.只需要安装一套mysql的服务器软件就ok2.需要有mysqld_multi3.需要修改/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            = 3306socket          = /tmp/mysql.sockskip-lockingkey_buffer = 16Mmax_allowed_packet = 1Mtable_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Minnodb_file_per_tabledefault-character-set=utf8log=general-loglog-slow-queries=slow-loglog-bin=mysql-binserver-id       = 1innodb_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值为2port            = 3307###修改为新的端口socket          = /tmp/mysql2.sock###修改为新的socketdatadir=/home/anmh/mysql1/datadir/###修改为新的目录key_buffer = 16Mmax_allowed_packet = 1Mtable_cache = 64sort_buffer_size = 512Knet_buffer_length = 8Kread_buffer_size = 256Kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8Minnodb_file_per_tabledefault-character-set=utf8log=/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/mysql1mkdir /home/anmh/mysql1/logdir /home/anmh/mysql1/datadirmkdir /home/anmh/mysql1/datadir/idbmkdir /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] Aborting100919 11:02:07 [Note] /usr/local/libexec/mysqld: Shutdown complete查看具体的错误说明：[root@dbadb1 bin]# /usr/local/bin/perror 13OS error code  13:  Permission denied

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

2.文件的目录没有权限:[root@dbadb1 mysql1]# lldrwxr-xr-x  3 root root  4096 Sep 19 11:03 datadirdrwxr-xr-x  2 root root  4096 Sep 19 11:03 logdir[root@dbadb1 mysql1]# chmod -Rf 777 logdir datadir[root@dbadb1 mysql1]# lldrwxr-xr-x  3 root root  4096 Sep 19 11:03 datadirdrwxr-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个mysqlEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.0.40-log Source distributionType ‘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是它的新端口。如果不加的话，默认是3306Enter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.0.40-log Source distributionType ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.mysql>

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

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

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

2009-12-02 04:07一、安装单实例Mysql5.1服务器shell> groupadd mysqlshell> useradd -g mysql mysqlshell> gunzip < mysql-VERSION.tar.gz | tar -xvf -shell> cd mysql-VERSIONshell> ./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> makeshell> make installshell> cp support-files/my-medium.cnf /etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqldchmod 755 /etc/init.d/mysqldchkconfig --add mysqldchkconfig --level 2345 mysqld onPATH=/usr/local/mysql/bin:$PATHecho "PATH=/usr/local/mysql/bin:\$PATH" >> /etc/profileshell> cd /usr/local/mysqlshell> bin/mysql_install_db --user=mysqlshell> chown -R root  .shell> chown -R mysql varshell> chgrp -R mysql .shell> bin/mysqld_safe --user=mysql &二、多实例安装Mysql5.1服务器一、安装监听端口3306的Mysql服务器shell> groupadd mysqlshell> useradd -g mysql mysqlshell> gunzip < mysql-VERSION.tar.gz | tar -xvf -shell> cd mysql-VERSIONshell> ./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> makeshell> make installshell> mkdir /usr/local/mysql3306/etcshell> cp support-files/my-medium.cnf /usr/local/mysql3306/etc/my.cnfcp support-files/mysql.server /etc/init.d/mysqld3306chmod 755 /etc/init.d/mysqld3306chkconfig --add mysqld3306chkconfig --level 2345 mysqld3306 onPATH=/usr/local/mysql3306/bin:$PATHecho "PATH=/usr/local/mysql3306/bin:\$PATH" >> /etc/profileshell> cd /usr/local/mysql3306shell> bin/mysql_install_db --user=mysqlshell> chown -R root  .shell> chown -R mysql varshell> chgrp -R mysql .shell> bin/mysqld_safe --user=mysql &修改my.cnf设置监听端口为3306二、安装监听端口3307的Mysql服务器shell> groupadd mysqlshell> useradd -g mysql mysqlshell> gunzip < mysql-VERSION.tar.gz | tar -xvf -shell> cd mysql-VERSIONshell> ./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> makeshell> make installshell> mkdir /usr/local/mysql3307/etcshell> cp support-files/my-medium.cnf /usr/local/mysql3307/etc/my.cnfcp support-files/mysql.server /etc/init.d/mysql3307chmod 755 /etc/init.d/mysql3307chkconfig --add mysql3307chkconfig --level 2345 mysql3307 onPATH=/usr/local/mysql3306/bin:$PATHecho "PATH=/usr/local/mysql3306/bin:\$PATH" >> /etc/profileshell> cd /usr/local/mysql3307shell> bin/mysql_install_db --user=mysqlshell> chown -R root  .shell> chown -R mysql varshell> 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 =3306socket = /tmp/mysql.sock1pid-file=/usr/local/mysql/var1/db-app1.pidlog=/usr/local/mysql/var1/db-app.logdatadir = /usr/local/mysql/var1user = mysql

[mysqld2]port =3307socket = /tmp/mysql.sock2pid-file = /usr/local/mysql/var2/db-app2.piddatadir = /usr/local/mysql/var2log=/usr/local/local/var2/db-app.loguser = mysql

[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash[isamchk]key_buffer = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2M[myisamchk]key_buffer = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_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服务，这里我就不在啰嗦了。

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

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

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