MySQL多实例
介绍
应用场景：
资金紧张公司
若公司资金紧张，公司业务访问量不太大，但又希望不同业务的数据库服务各自能够尽量独立地提供服务而互相不受影响，或者，还有需要主从复制等技术提供备份或读写分离服务的需求，那么，多实例就再好不过了。
用户并发访问量不大的业务
当公司业务访问量不太大的时候，服务器的资源基本上都是浪费的，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源以及搭配好服务，也不会有太大的问题。
大公司使用mysql读写分离
采用形式：
每个实例都有单独的配置文件、启动脚本、数据目录
部署MySQL多实例
二进制安装
获取二进制代码包
wget https://cdn.mysql.com/archives/mysql-5.6/mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
安装mysql运行所需的基础依赖
yum install ncurses-devel libaio-devel gcc make cmake -y
停止mysql服务
/etc/init.d/mysqld stop
环境清理 清空PATH有关的mysql 注释掉之前的$PATH
#export PATH=/application/mysql/bin:$PATH
退出登录
[root@localhost ~]# logout
[root@localhost ~]# mysql
-bash: mysql: 未找到命令
创建用户
useradd -s /sbin/nologin -M mysql
准备好多实例的目录
mkdir -p /my_mysql/{3306,3307}
二进制安装比源代码编译安装省去了很多步骤 解压好之后bin目录中文件自动生成
缺点就是编译安装包30m 二进制包300m
进入二进制包存在的目录 解压文件 -C 指定目录解压缩
tar -zxvf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz -C /application/
准备二进制mysql运行所需要的环境
准备3306实例的my.cnf
cd /my_mysql/3306
vim my.cnf
[client]
port=3306
socket=/my_mysql/3306/mysql.sock
[mysqld]
port=3306
socket=/my_mysql/3306/mysql.sock
basedir=/application/mysql-5.6.40-linux-glibc2.12-x86_64
datadir=/my_mysql/3306/data
log-bin=/my_mysql/3306/mysql-bin
server-id=1
[mysqld_safe]
log-error=/my_mysql/3306/mysql_3306_error.log
pid-file=/my_mysql/3306/mysqld_3306.pid
准备3307实例的my.cnf
[client]
port=3307
socket=/my_mysql/3307/mysql.sock
[mysqld]
port=3307
socket=/my_mysql/3307/mysql.sock
basedir=/application/mysql-5.6.40-linux-glibc2.12-x86_64
datadir=/my_mysql/3307/data
log-bin=/my_mysql/3307/mysql-bin
server-id=2
[mysqld_safe]
log-error=/my_mysql/3307/mysql_3307_error.log
pid-file=/my_mysql/3307/mysqld_3307.pid
mysql启停脚本
注意 3306和3307 这两个实例，配置文件也得区分开来
3306/mysqld_3306
port=3306
mysql_user="mysql"
Cmdpath="/application/mysql-5.6.40-linux-glibc2.12-x86_64/bin/"
mysql_sock="/my_mysql/${port}/mysql.sock"
mysqld_pid_file_path=/my_mysql/${port}/mysqld_${port}.pid
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/my_mysql/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
sleep 3
else
printf "MySQL is running...\n"
exit 1
fi
}
stop(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stoping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_path"`
if(kill -0 $mysqld_pid 2 > /dev/null)
then
kill $mysqld_pid
sleep 2
fi
fi
}
restart(){
printf "Restarting MySQL...\n"
stop
sleep 2
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
printf"Usage:/my_mysql/${port}/mysql{start|stop|restart}\n"
esac
chmod +x mysql_3306
3307/mysqld_3307
port=3307
mysql_user="mysql"
Cmdpath="/application/mysql-5.6.40-linux-glibc2.12-x86_64/bin/"
mysql_sock="/my_mysql/${port}/mysql.sock"
mysqld_pid_file_path=/my_mysql/${port}/mysqld_${port}.pid
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/my_mysql/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
sleep 3
else
printf "MySQL is running...\n"
exit 1
fi
}
stop(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit 1
else
printf "Stoping MySQL...\n"
mysqld_pid=`cat "$mysqld_pid_file_path"`
if(kill -0 $mysqld_pid 2 > /dev/null)
then
kill $mysqld_pid
sleep 2
fi
fi
}
restart(){
printf "Restarting MySQL...\n"
stop
sleep 2
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*)
printf"Usage:/my_mysql/${port}/mysql{start|stop|restart}\n"
esac
chmod +x mysql_3307
用户、组授权
降低权限，全部赋予给mysql
chown -R mysql.mysql /my_mysql/
path配置
vim /etc/profile
export PATH=/application/mysql-5.6.40-linux-glibc2.12-x86_64/bin:$PATH
保存退出
source /etc/profile
创建多个实例对应的数据目录
mkdir -p /my_mysql/3306/data
mkdir -p /my_mysql/3307/data
见证mysql的多实例初始化
先初始化3306的数据 (出现两个ok)
/application/mysql-5.6.40-linux-glibc2.12-x86_64/scripts/mysql_install_db --defaults-file=/my_mysql/3306/my.cnf --basedir=/application/mysql-5.6.40-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3306/data --user=mysql
初始化3307的数据 (出现两个ok)
/application/mysql-5.6.40-linux-glibc2.12-x86_64/scripts/mysql_install_db --defaults-file=/my_mysql/3307/my.cnf --basedir=/application/mysql-5.6.40-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3307/data --user=mysql
创建错误日志
touch /my_mysql/3306/mysql_3306_error.log
touch /my_mysql/3306/mysql_3307_error.log
启动3306mysql 套接字登录
/my_mysql/3306/mysqld_3306 start
套接字登录mysql
mysql -S /my_mysql/3306/mysql.sock
启动3307mysql 套接字登录
/my_mysql/3307/mysqld_3306 start
套接字登录mysql
mysql -S /my_mysql/3307/mysql.sock
netstat -tunlp | grep mysql
可以看到两个数据库启动了