mysql多人管理_mysql的多实例管理

mysqld_multi 可以管理多个帧听不同Unix套接字文件和TCP/IP端口的连接的mysqld  进程。它可以启动或停止服务器，或报告它们的当前状态。

第一步：手动编写这个多启动配置文件

# vim /usr/local/mysql/etc/mysqld_multi.cnf

[mysqld_multi]  --多启动的参数组，不要写错

mysqld=/usr/local/mysql/bin/mysqld_safe  --启动时使用的程序

mysqladmin=/usr/local/mysql/bin/mysqladmin --管理程序

user=multi_admin   --定义管理用户名

password=multipass   --管理用户的密码

[mysqld00]

port=3300

datadir=/data00

pid-file=/var/run/mysqld/mysql00.pid

socket=/var/run/mysqld/mysql00.socket

general_log

general_log_file=/var/log/mysqld/mysql00.log

log-error=/var/log/mysqld/mysql00-err.log

[mysqld01]

port=3301

datadir=/data01

pid-file=/var/run/mysqld/mysql01.pid

socket=/var/run/mysqld/mysql01.socket

general_log

general_log_file=/var/log/mysqld/mysql01.log

log-error=/var/log/mysqld/mysql01-err.log

[mysqld02]

port=3302

datadir=/data02

pid-file=/var/run/mysqld/mysql02.pid

socket=/var/run/mysqld/mysql02.socket

general_log

general_log_file=/var/log/mysqld/mysql02.log

log-error=/var/log/mysqld/mysql02-err.log

第二步：

创建目录，并修改其权限

# mkdir /data00 /data01  /data02 /var/run/mysqld  /var/log/mysqld

# chown mysql.mysql /data00 /data01 /data02 /var/run/mysqld /var/log/mysqld /usr/local/mysql/etc/mysqld_multi.cnf

第三步：

初始化要管理的多个数据库

# /usr/local/mysql/bin/mysql_install_db --datadir=/data00/  --user=mysql

# /usr/local/mysql/bin/mysql_install_db --datadir=/data01/  --user=mysql

# /usr/local/mysql/bin/mysql_install_db --datadir=/data02/  --user=mysql

第四步，用mysqld_multi启动多个数据库

# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf start 0,1,2

也可以使用下面的命令来启动；--config-file参数将会被--defaults-extra-file参数替代

[root@li ~]# /usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/usr/local/mysql/etc/mysqld_multi.cnf start 0,1,2

# netstat -ntlup |grep 33

tcp        0      0 0.0.0.0:3300                0.0.0.0:*                   LISTEN      3855/mysqld

tcp        0      0 0.0.0.0:3301                0.0.0.0:*                   LISTEN      3494/mysqld

tcp        0      0 0.0.0.0:3302                0.0.0.0:*                   LISTEN      3507/mysqld

报告数据库状态用report

# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf report 0,1,2

Reporting MySQL servers

MySQL server from group: mysqld00 is running

MySQL server from group: mysqld01 is running

MySQL server from group: mysqld02 is running

分别使用这多个数据的方法：

就是使用mysql连接时，指定不同的socket

# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql00.socket

# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql01.socket

# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql02.socket

第五步：

授权关闭数据的权限

# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop 0,1,2

--使用上面命令stop后发现并不能关闭这多个数据库，因为每个数据库还需要对multi_admin用户进行授权

连接第一个数据库授权

# /usr/local/mysql/bin/mysql --socket=/var/run/mysqld/mysql00.socket

mysql> grant shutdown on *.* to 'multi_admin'@'localhost' identified by 'multipass';

mysql> flush privileges;

--每个数据库都要重复做一次上面的操作，都要授权

都授权后，可以很灵活的关闭和开启指定的数据库

# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop 0,2

# /usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf report 0,1,2

Reporting MySQL servers

MySQL server from group: mysqld00 is not running

MySQL server from group: mysqld01 is running

MySQL server from group: mysqld02 is not running

-------------------------------------------------------------

上面的程序是没有服务脚本的，要支持chkconfig，就得手动写

vim /etc/init.d/mysqld_multi

#!/bin/bash

# chkconfig: - 86 16

# description: start and stop the mysqld_multi service.

start () {

/usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf start 0,1,2

}

stop () {

/usr/local/mysql/bin/mysqld_multi --config-file=/usr/local/mysql/etc/mysqld_multi.cnf stop 0,1,2

}

case "$1" in

start )

start

sleep 2

echo " ...........................[OK]"

;;

stop )

stop

sleep 2

echo " ...........................[OK]"

;;

restart )

stop

sleep 5

start

echo ".........................[OK]"

;;

*  )

echo "USAGE:start|stop|restart"

;;

esac

# chmod  755   /etc/init.d/mysqld_multi

==========================================================

=============================================================

--------------------------------------------------------------

MySQL实例管理器(IM)是通过TCP/IP端口运行的后台程序，用来监视和管理MySQL数据库服务器实例。MySQL实例管理器 适合Unix-类操作系统和Windows。

rpm 版默认也支持，源码版需要在编译时加上--withmysqlmanager

# /usr/local/mysql/libexec/mysqlmanager  --启动程序

第一步：

手动编写配置文件

# vim /usr/local/mysql/etc/mysqlmanager.cnf

[manager]

default-mysqld-path=/usr/local/mysql/libexec/mysqld

socket=/var/run/mysqld/manager.socket

pid-file=/var/run/mysqld/manager.pid

password-file=/usr/local/mysql/etc/my.passwd --定义密码文件

monitoring-interval=2  --监控时间间隔

port=1999   --管理端口

bind-address=2.2.2.10 --管理IP

[mysqld03]

port=3303

datadir=/data03

socket=/var/run/mysqld/mysql03.socket

pid-file=/var/run/mysqld/mysql03.pid

general_log

general_log_file=/var/log/mysqld/mysql03.log

log-error=/var/log/mysqld/mysql03-err.log

user=mysql  --这一句不加启动会报错

[mysqld04]

port=3304

datadir=/data04

socket=/var/run/mysqld/mysql04.socket

pid-file=/var/run/mysqld/mysql04.pid

general_log

general_log_file=/var/log/mysqld/mysql04.log

log-error=/var/log/mysqld/mysql04-err.log

user=mysql

[mysqld05]

port=3305

datadir=/data05

socket=/var/run/mysqld/mysql05.socket

pid-file=/var/run/mysqld/mysql05.pid

general_log

general_log_file=/var/log/mysqld/mysql05.log

log-error=/var/log/mysqld/mysql05-err.log

user=mysql

第二步：

创建相关目录，修改权限

# mkdir /data03 /data04 /data05 /var/log/mysqld /var/run/mysqld/

# chown mysql.mysql /data03 /data04 /data05 /var/log/mysqld /var/run/mysqld/ /usr/local/mysql/etc/mysqlmanager.cnf

第三步：

初始化数据库

# /usr/local/mysql/bin/mysql_install_db --datadir=/data03 --user=mysql

# /usr/local/mysql/bin/mysql_install_db --datadir=/data04 --user=mysql

# /usr/local/mysql/bin/mysql_install_db --datadir=/data05 --user=mysql

第四步：创建密码文件

# /usr/local/mysql/libexec/mysqlmanager --passwd >> /usr/local/mysql/etc/my.passwd

# cat /usr/local/mysql/etc/my.passwd

li:*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

--因为mysql5.1.x版本上面的mysqlmanager程序有BUG，不能生成，所以就用rpm版mysqlmanager程序来替代生成

# /usr/libexec/mysqlmanager --passwd > /usr/local/mysql/etc/my.passwd

Creating record for new user.

Enter user name: li

Enter password:

Re-type password:

第五步：启动数据库

# /usr/local/mysql/libexec/mysqlmanager --defaults-file=/usr/local/mysql/etc/mysqlmanager.cnf &

第六步：通过管理IP地址登录

# /usr/local/mysql/bin/mysql -h 2.2.2.10 -P 1999 -u li -p123

mysql> show instances;

mysql> show instance status mysqld03;

mysql> stop instance  mysqld03;

mysql> start instance  mysqld03;

mysql> show instance options mysqld03;

mysql> show mysqld1 log files;