mysql多实例介绍及配置
1、mysql多实例介绍
1.1 什么是mysql多实例
mysql多实例就是在一台机器上开启多个不同的服务端口（如：3306,3307），运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务:；
1.2 mysql多实例的特点有以下几点
1：有效利用服务器资源，当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务。
2：节约服务器资源
3：资源互相抢占问题，当某个服务实例服务并发很高时或者开启慢查询时，会消耗更多的内存、CPU、磁盘IO资源，导致服务器上的其他实例提供服务的质量下降；
1.3 部署mysql多实例的两种方式
第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便；
第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理；
1.4 同一开发环境下安装两个数据库，必须处理以下问题
配置文件安装路径不能相同
数据库目录不能相同
启动脚本不能同名
端口不能相同
socket文件的生成路径不能相同
2、mysqld_multi配置mysql多实例
在进行此操作前已经编译安装好了mysql，安装位置在/application/mysql/下
2.1 初始化数据目录
mkdir /usr/local/var -p
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql1 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql2 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql3 --user=mysql
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/usr/local/var/mysql4 --user=mysql
修改授权
chown -R mysql.mysql /usr/local/var/mysql*
2.2 配置多实例启动脚本
cp /application/mysql/support-files/mysqld_multi.server /etc/init.d/
#修改basedir和bindir为安装路径
basedir=/application/mysql
bindir=/application/mysql/bin
2.3 配置多实例数据库配置文件
在/etc/目录下创建创建文件/etc/mysqld_multi.cnf，内容如下
[mysqld_multi]
mysqld = /application/mysql/bin/mysqld_safe
mysqladmin = /application/mysql/bin/mysqladmin
#user = mysql
#password = my_password
[mysqld1]
socket = /usr/local/var/mysql1/mysql1.sock
port = 3306
pid-file = /usr/local/var/mysql1/mysql1.pid
datadir = /usr/local/var/mysql1
#language = /usr/local/mysql/share/mysql/english
user = mysql
[mysqld2]
socket = /usr/local/var/mysql2/mysql2.sock
port = 3307
pid-file = /usr/local/var/mysql2/mysql2.pid
datadir = /usr/local/var/mysql2
#language = /usr/local/mysql/share/mysql/english
user = mysql
[mysqld3]
socket = /usr/local/var/mysql3/mysql3.sock
port = 3308
pid-file = /usr/local/var/mysql3/mysql3.pid
datadir = /usr/local/var/mysql3
#language = /usr/local/mysql/share/mysql/english
user = mysql
[mysqld4]
socket = /usr/local/var/mysql4/mysql4.sock
port = 3309
pid-file = /usr/local/var/mysql4/mysql4.pid
datadir = /usr/local/var/mysql4
#language = /usr/local/mysql/share/mysql/english
user = mysql
2.4 启动多实例数据库
mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start
#启动或停止具体某一个实例可在start、stop后面加上具体数据1,2,3等
mysqld_multi进行多实例管理
启动全部实例：/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start
查看全部实例状态：/usr/local/mysql/bin/mysqld_multi report
启动单个实例：/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf start 3306
停止单个实例：/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf stop 3306
查看单个实例状态：/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report 3306
检查
[root@db02 ~]# netstat -lntup
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5016/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 5026/mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 5036/mysqld
tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 5042/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1317/sshd
tcp 0 0 :::22 :::* LISTEN 1317/sshd
2.5 登录相应数据库
#进入端口为3306的数据库
mysql -uroot -p -h127.0.0.1 -P3306 ####密码为空
或者
mysql -S /usr/local/var/mysql1/mysql1.sock
3、多配置文件实现MySQL多实例
在进行此操作前已经编译安装好了mysql，安装位置在/application/mysql/下
3.1 创建目录和配置文件
mkdir -p /data/{3306,3307}/data
vim /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
server-id = 1
[mysqld_safe]
log-error=/data/3306/mysql_3306.err
pid-file=/data/3306/mysqld.pid
3.2 创建启动脚本
vim /data/3306/mysql
#!/bin/sh
#init
port=3306
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
3.4 增加3307实例
sed 's#3306#3307#g;s#server-id = 1#server-id = 3#g' /data/3306/my.cnf >/data/3307/my.cnf
sed 's#3306#3307#g' /data/3306/mysql >/data/3307/mysql
3.5 修改权限
chown -R mysql.mysql /data
chmod +x /data/{3306,3307}/mysql
3.6 初始化
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3306/data
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/data/3307/data
3.7 启动
/data/3306/mysql start
/data/3307/mysql start
启动的时候报错
[root@db01 ~]# 180707 16:30:54 mysqld_safe error: log-error set to '/data/3306/mysql_3306.err', however file don't exists. Create writable for user 'mysql'.
手动创建文件并授权，再次启动成功
touch /data/3306/mysql_3306.err
chown mysql:mysql /data/3306/mysql_3306.err
检查
[root@db01 ~]# netstat -lntup|grep mysql
tcp6 0 0 :::3306 :::* LISTEN 2023/mysqld
tcp6 0 0 :::3307 :::* LISTEN 2505/mysqld
3.8 初始化密码登录
mysqladmin -uroot -S /data/3306/mysql.sock password '123456'
mysqladmin -uroot -S /data/3307/mysql.sock password '123456'