什么是多实例
简单来说,Mysql多实例就是在一台服务器上同时开启多个不同的服务器端口,同时运行多个mysql服务进程,这些服务进程通过不同的socket监听不同的服务端口来提供服务。
两种不同的多实例配置
- 使用不同的my.conf配置文件,启动程序,数据文件
[client]
port=3306
socket=/tmp/mysql.sock
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = mysql
[mysqld3306]
datadir=/data/mysql/mysql_3306/data
port=3306
server_id=3306
socket=/tmp/mysql_3306.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3306/log/slow.log
log-error = /data/mysql/mysql_3306/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3306/log/mysql3306_bin
[mysqld3307]
datadir=/data/mysql/mysql_3307/data
port=3307
server_id=3307
socket=/tmp/mysql_3307.sock
log-output=file
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql_3307/log/slow.log
log-error = /data/mysql/mysql_3307/log/error.log
binlog_format = mixed
log-bin = /data/mysql/mysql_3307/log/mysql3307_bin
启动程序
mysqld_multi --config-file=/data/mysql/my_multi.cnf start 1,2
小结:上述配置耦合度太高,一个配置文件,不方便管理。
- 单一配置文件,单一启动程序的多实例部署方案
1、my01.conf
[client]
port = 3307
socket = /data/dbdata_3307/mysql.sock
[mysqld]
datadir=/data/dbdata_3307/
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
port = 3307
socket = /data/dbdata_3307/mysql.sock
...........
2、my02.conf
[client]
port = 3308
socket = /data/dbdata_3308/mysql.sock
[mysqld]
datadir=/data/dbdata_3308/
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
port = 3308
socket = /data/dbdata_3308/mysql.sock
流程:
1、创建数据库存储目录
2、复制配置文件并修改【如上】
3、对数据库文件目录授权
4、./mysql_install_db对数据库初始化
5、通过指定不同的socket启动mysql -uroot -pxxx -S /data/dbdata_3308/mysql.sock
多实例启动脚本
#!/bin/sh
#init
port=3306
mysql_user="root"
mysql_pwd="123456"
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
缺陷
1、当某个数据库实力并发很高或者有sql慢查询时,整个实例会消耗大量的系统CPU、磁盘I/O等资源,会造成服务器上的其他数据库实例质量一起下降。
应用场景
1、资金紧张型公司的选择
2、并发访问不是很大的业务
3、门户网站应用MYSQL多实例场景