- A+
所属分类：linux技术
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 可以看到两个数据库启动了 
