MySQL多实例

MySQL多实例

介绍

应用场景：

资金紧张公司

若公司资金紧张，公司业务访问量不太大，但又希望不同业务的数据库服务各自能够尽量独立地提供服务而互相不受影响，或者，还有需要主从复制等技术提供备份或读写分离服务的需求，那么，多实例就再好不过了。

用户并发访问量不大的业务
当公司业务访问量不太大的时候，服务器的资源基本上都是浪费的，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源以及搭配好服务，也不会有太大的问题。

大公司使用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
可以看到两个数据库启动了