MySQL多实例

  • MySQL多实例已关闭评论
  • 96 次浏览
  • A+
所属分类:linux技术
摘要

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


MySQL多实例

介绍

应用场景

资金紧张公司

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

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

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