MySQL 多实例部署

该文章讲解下单台机器部署多实例MySQL。

本环境为自己在aws ec2机器上测试的结果，可正常运行，如有疑问可以留言后期一起学习。

强烈建议：再部署多实例msyql之前，卸载该机器上之前部署的mysql，一面出现问题。

 

一、安装依赖

首先安装cmake及其他依赖包：

yum install -y libaio-devel gcc gcc-c++ ncurses ncurses-devel cmake bzip*

安装boost：

wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
tar xzf boost_1_59_0.tar.gz
cd boost_1_59_0
./bootstrap.sh
./b2 install 

备注：boost 不一定需要安装，在mysql 5.7 之后可以选择性安装这个东西。也可以自行百度下

二、MySQL安装：

创建用户：

useradd  -s /sbin/nologin -M mysql

下载MySQL，本次说源码包安装，下载大家可以百度：

tar xf mysql-5.5.32.tar.gz
cd mysql-5.5.32/

编译：

cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data/ \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0 

安装：

make && make install

后续配置，，：

#制作软连接
ln -s /application/mysql-5.5.32 /application/mysql
#创建多实例数据存放目录
mkdir -p /data/mysql{3306,3307}/{data.logs}/
#授权
chown mysql.mysql -R /data/*
#添加环境变量
echo 'export PATH=/application/mysql/bin:$PATH' >> /etc/profile
source /etc/profile
#注意：环境变量一定添加到$PATH之前，否则运行mysql命令的时候可能出现问题

#初始化数据库 mysql3307
/application/mysql/scripts/mysql_install_db --collation-server=utf8_general_ci --basedir=/application/mysql --datadir=/data/mysql3306/data --user=mysql

#初始化数据库 mysql3307
/application/mysql/scripts/mysql_install_db --collation-server=utf8_general_ci --basedir=/application/mysql --datadir=/data/mysql3307/data --user=mysql

配置 mysql3306  配置文件：

# vim /data/mysql3306/my.cnf
[client] 
port = 3306
default-character-set = utf8
 
[mysql]
no-auto-rehash

[mysqld]
port=3306
socket = /data/mysql3306/data/mysql.sock
pid-file = /data/mysql3306/data/mysql.pid
basedir = /application/mysql
datadir = /data/mysql3306/data
relay-log = /data/mysql3306/relay-bin
relay-log-info-file = /data/mysql3306/relay-info
server-id=1
character_set_server = utf8
default_storage_engine = InnoDB
innodb_buffer_pool_size =1G
innodb_log_file_size =100MB
innodb_file_per_table= 1
key_buffer_size = 32M
tmp_table_size = 64M
max_heap_table = 64M
query_cache_type = 0
query_cache_size = 0
max_connections = 5000
thread_cache = 256
open_files_limit = 65535
log-bin=mysql-bin
log-bin-index= mysql-bin.index
 
[mysqld_safe]
# LOGGING
log_error=/data/mysql3306/logs/mysql-error.log
slow_query_log_file=/data/mysql3306/logs/mysql-slow.log
slow_query_log=1
##########################

参考mysql3306 的配置文件 配置mysql3307 配置文件：

省略。。。。

注意，如果多个实例，需要修改端口号，和data路径

编写 mysql3306 启动脚本：

# vim /data/mysql3306/mysql
#!/bin/sh  
# Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB  
# This file is public domain and comes with NO WARRANTY of any kind  
  
# MySQL daemon start/stop script.  
  
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based  
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.  
# When this is done the mysql server will be started when the machine is  
# started and shut down when the systems goes down.  
  
# Comments to support chkconfig on RedHat Linux  
# chkconfig: 2345 64 36  
# description: A very fast and reliable SQL database engine.  
  
# Comments to support LSB init script conventions  
### BEGIN INIT INFO  
# Provides: mysql  
# Required-Start: $local_fs $network $remote_fs  
# Should-Start: ypbind nscd ldap ntpd xntpd  
# Required-Stop: $local_fs $network $remote_fs  
# Default-Start:  2 3 4 5  
# Default-Stop: 0 1 6  
# Short-Description: start and stop MySQL  
# Description: MySQL is a very fast and reliable SQL database engine.  
### END INIT INFO  
   
# If you install MySQL on some other places than /usr/local/mysql, then you  
# have to do one of the following things for this script to work:  
#  
# - Run this script from within the MySQL installation directory  
# - Create a /etc/my.cnf file with the following information:  
#   [mysqld]  
#   basedir=<path-to-mysql-installation-directory>  
# - Add the above to any other configuration file (for example ~/.my.ini)  
#   and copy my_print_defaults to /usr/bin  
# - Add the path to the mysql-installation-directory to the basedir variable  
#   below.  
#  
# If you want to affect other MySQL variables, you should make your changes  
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.  
  
# If you change base dir, you must also change datadir. These may get  
# overwritten by settings in the MySQL configuration files.  
# 如果你的basedir不是默认的/usr/local/mysql目录，则需要设置这个参数。  
# 如果你的datadir不是在$basedir/data目录下，则需要设置datadir参数。  
basedir=/application/mysql
datadir=/data/mysql3306/data
my_cnf_dir=/data/mysql3306
# Default value, in seconds, afterwhich the script should timeout waiting  
# for server start.   
# Value here is overriden by value in my.cnf.   
# 0 means don't wait at all  
# Negative numbers mean to wait indefinitely  
service_startup_timeout=900  
  
# Lock directory for RedHat / SuSE.  
lockdir='/var/lock/subsys'  
lock_file_path="$lockdir/mysql"  
  
# The following variables are only set for letting mysql.server find things.  
#如果basedir或者datadir为空，则给与相应的默认值。同时设置对应的sbindir和libexecdir变量值。  
# Set some defaults  
mysqld_pid_file_path=  
if test -z "$basedir"  
then  
  basedir=/usr/local/mysql  
  bindir=/usr/local/mysql/bin  
  if test -z "$datadir"  
  then  
    datadir=/usr/local/mysql/data  
  fi  
  sbindir=/usr/local/mysql/bin  
  libexecdir=/usr/local/mysql/bin  
else  
  bindir="$basedir/bin"  
  if test -z "$datadir"  
  then  
    datadir="$basedir/data"  
  fi  
  sbindir="$basedir/sbin"  
  libexecdir="$basedir/libexec"  
fi  
  
# datadir_set is used to determine if datadir was set (and so should be  
# *not* set inside of the --basedir= handler.)  
datadir_set=  
  
#  
# Use LSB init script functions for printing messages, if possible  
# 如果得到系统自带日志函数，则使用，否则用自己的日志函数。  
lsb_functions="/lib/lsb/init-functions"  
if test -f $lsb_functions ; then  
  . $lsb_functions  
else  
  log_success_msg()  
  {  
    echo " SUCCESS! $@"  
  }  
  log_failure_msg()  
  {  
    echo " ERROR! $@"  
  }  
fi  
#设置PATH环境变量  
PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"  
export PATH  
  
mode=$1    # start or stop  
  
[ $# -ge 1 ] && shift  
  
#将第一个参数后的其他参数赋予other_args变量。  
other_args="$*"   # uncommon, but needed when called from an RPM upgrade action  
           # Expected: "--skip-networking --skip-grant-tables"  
           # They are not checked here, intentionally, as it is the resposibility  
           # of the "spec" file author to give correct arguments only.  
  
case `echo "testing\c"`,`echo -n testing` in  
    *c*,-n*) echo_n=   echo_c=     ;;  
    *c*,*)   echo_n=-n echo_c=     ;;  
    *)       echo_n=   echo_c='\c' ;;  
esac  
#参数解析函数（将参数值赋予对于变量）  
parse_server_arguments() {  
  for arg do  
    case "$arg" in  
      --basedir=*)  basedir=`echo "$arg" | sed -e 's/^[^=]*=//'`  
                    bindir="$basedir/bin"  
            if test -z "$datadir_set"; then  
              datadir="$basedir/data"  
            fi  
            sbindir="$basedir/sbin"  
            libexecdir="$basedir/libexec"  
        ;;  
      --datadir=*)  datadir=`echo "$arg" | sed -e 's/^[^=]*=//'`  
            datadir_set=1  
    ;;  
      --pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;  
      --service-startup-timeout=*) service_startup_timeout=`echo "$arg" | sed -e 's/^[^=]*=//'` ;;  
    esac  
  done  
}  
  
wait_for_pid () {  
  verb="$1"           # created | removed  
  pid="$2"            # process ID of the program operating on the pid-file  
  pid_file_path="$3" # path to the PID file.  
  
  i=0  
  avoid_race_condition="by checking again"  
  
  while test $i -ne $service_startup_timeout ; do  
  
    case "$verb" in  
      'created')  
        # wait for a PID-file to pop into existence.  
        test -s "$pid_file_path" && i='' && break  
        ;;  
      'removed')  
        # wait for this PID-file to disappear  
        test ! -s "$pid_file_path" && i='' && break  
        ;;  
      *)  
        echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path"  
        exit 1  
        ;;  
    esac  
  
    # if server isn't running, then pid-file will never be updated  
    if test -n "$pid"; then  
      if kill -0 "$pid" 2>/dev/null; then  
        :  # the server still runs  
      else  
        # The server may have exited between the last pid-file check and now.    
        if test -n "$avoid_race_condition"; then  
          avoid_race_condition=""  
          continue  # Check again.  
        fi  
  
        # there's nothing that will affect the file.  
        log_failure_msg "The server quit without updating PID file ($pid_file_path)."  
        return 1  # not waiting any more.  
      fi  
    fi  
  
    echo $echo_n ".$echo_c"  
    i=`expr $i + 1`  
    sleep 3
  
  done  
  
  if test -z "$i" ; then  
    log_success_msg  
    return 0  
  else  
    log_failure_msg  
    return 1  
  fi  
}  
  
# Get arguments from the my.cnf file,  
# the only group, which is read from now on is [mysqld]  
#得到有执行权限的my_print_defaults程序（这个程序是将my.cnf中的参数打印出来，  
#其读取my.cnf的顺序是/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf，后面的参数会覆盖掉前面的参数）  
if test -x ./bin/my_print_defaults  
then  
  print_defaults="./bin/my_print_defaults"  
elif test -x $bindir/my_print_defaults  
then  
  print_defaults="$bindir/my_print_defaults"  
elif test -x $bindir/mysql_print_defaults  
then  
  print_defaults="$bindir/mysql_print_defaults"  
else  
  # Try to find basedir in /etc/my.cnf  
  conf=/etc/my.cnf  
  print_defaults=  
  if test -r $conf  
  then  
    subpat='^[^=]*basedir[^=]*=.∗$'  
    dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf`  
    for d in $dirs  
    do  
      d=`echo $d | sed -e 's/[  ]//g'`  
      if test -x "$d/bin/my_print_defaults"  
      then  
        print_defaults="$d/bin/my_print_defaults"  
        break  
      fi  
      if test -x "$d/bin/mysql_print_defaults"  
      then  
        print_defaults="$d/bin/mysql_print_defaults"  
        break  
      fi  
    done  
  fi  
  
  # Hope it's in the PATH ... but I doubt it  
  test -z "$print_defaults" && print_defaults="my_print_defaults"  
fi  
  
#  
# Read defaults file from 'basedir'.   If there is no defaults file there  
# check if it's in the old (depricated) place (datadir) and read it from there  
#  
#获取附加的my.cnf。这个里的参数值会覆盖其他的参数设置。  
#注意，如果basedir下有my.cnf就不会再去读取datadir下的my.cnf。如果basedir下面没有my.cnf则会继续读取datadir下的my.cnf。  
extra_args="$my_cnf_dir/my.cnf"  
if test -r "$basedir/my.cnf"  
then  
  extra_args="-e $basedir/my.cnf"  
else  
  if test -r "$datadir/my.cnf"  
  then  
    extra_args="-e $datadir/my.cnf"  
  fi  
fi  
#将my_print_defaults程序打印出来的Mysql参数用parse_server_arguments进行解析，解析后的值会是这个脚本环境中的变量值。  
parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server`  
  
#  
# Set pid file if not given  
#设置mysqld_pid_file_path变量（其代表pid 文件的位置），如果没有设置这个值或者不是绝对路径，则默认会在$datadir下面  
if test -z "$mysqld_pid_file_path"  
then  
  #mysqld_pid_file_path=$datadir/`hostname`.pid  
  mysqld_pid_file_path=$datadir/mysql.pid  
else  
  case "$mysqld_pid_file_path" in  
    /* ) ;;  
    * )  mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;;  
  esac  
fi  
  
case "$mode" in  
  'start')  
    # Start daemon  
  
    # Safeguard (relative paths, core dumps..)  
    cd $basedir  
  
    echo $echo_n "Starting MySQL"  
    if test -x $bindir/mysqld_safe  
    then  
      # Give extra arguments to mysqld with the my.cnf file. This script  
      # may be overwritten at next upgrade.  
      #真正启动数据库就是下面这条命令。  
      #$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &  
      $bindir/mysqld_safe --defaults-file=$extra_args $other_args >/dev/null 2>&1 &  
      wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?  
  
      # Make lock for RedHat / SuSE  
      if test -w "$lockdir"  
      then  
        touch "$lock_file_path"  
      fi  
  
      exit $return_value  
    else  
      log_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)"  
    fi  
    ;;  
  
  'stop')  
    # Stop daemon. We use a signal here to avoid having to know the  
    # root password.  
  
    if test -s "$mysqld_pid_file_path"  
    then  
      mysqld_pid=`cat "$mysqld_pid_file_path"`  
  
      if (kill -0 $mysqld_pid 2>/dev/null)  
      then  
        echo $echo_n "Shutting down MySQL"  
        kill $mysqld_pid  
        # mysqld should remove the pid file when it exits, so wait for it.  
        wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$?  
      else  
        log_failure_msg "MySQL server process #$mysqld_pid is not running!"  
        rm "$mysqld_pid_file_path"  
      fi  
  
      # Delete lock for RedHat / SuSE  
      if test -f "$lock_file_path"  
      then  
        rm -f "$lock_file_path"  
      fi  
      exit $return_value  
    else  
      log_failure_msg "MySQL server PID file could not be found!"  
    fi  
    ;;  
  
  'restart')  
    # Stop the service and regardless of whether it was  
    # running or not, start it again.  
    if $0 stop  $other_args; then  
      $0 start $other_args  
    else  
      log_failure_msg "Failed to stop running server, so refusing to try to start."  
      exit 1  
    fi  
    ;;  
  
  'reload'|'force-reload')  
    if test -s "$mysqld_pid_file_path" ; then  
      read mysqld_pid <  "$mysqld_pid_file_path"  
      kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL"  
      touch "$mysqld_pid_file_path"  
    else  
      log_failure_msg "MySQL PID file could not be found!"  
      exit 1  
    fi  
    ;;  
  'status')  
    # First, check to see if pid file exists  
    if test -s "$mysqld_pid_file_path" ; then   
      read mysqld_pid < "$mysqld_pid_file_path"  
      if kill -0 $mysqld_pid 2>/dev/null ; then   
        log_success_msg "MySQL running ($mysqld_pid)"  
        exit 0  
      else  
        log_failure_msg "MySQL is not running, but PID file exists"  
        exit 1  
      fi  
    else  
      # Try to find appropriate mysqld process  
      mysqld_pid=`pidof $libexecdir/mysqld`  
  
      # test if multiple pids exist  
      pid_count=`echo $mysqld_pid | wc -w`  
      if test $pid_count -gt 1 ; then  
        log_failure_msg "Multiple MySQL running but PID file could not be found ($mysqld_pid)"  
        exit 5  
      elif test -z $mysqld_pid ; then   
        if test -f "$lock_file_path" ; then   
          log_failure_msg "MySQL is not running, but lock file ($lock_file_path) exists"  
          exit 2  
        fi   
        log_failure_msg "MySQL is not running"  
        exit 3  
      else  
        log_failure_msg "MySQL is running but PID file could not be found"  
        exit 4  
      fi  
    fi  
    ;;  
    *)  
      # usage  
      basename=`basename "$0"`  
      echo "Usage: $basename  {start|stop|restart|reload|force-reload|status}  [ MySQL server options ]"  
      exit 1  
    ;;  
esac  
  
exit 0 

参考mysql3306 启动脚本，编写 mysql3307 启动脚本。

备注：

启动文件我已做说明并修改，只需修改三个变量就可以，vim /data/mysql3306/mysql

basedir=/application/mysql

datadir=/data/mysql3306/data

my_cnf_dir=/data/mysql3306

 

启动、停止mysql：

/data/mysql3306/mysql start 

/data/mysql3306/mysql stop

登陆mysql：

mysql -S /data/mysql3306/data/mysql.sock

设置密码：

mysqladmin -S /data/mysql3306/data/mysql.sock password 'password'

登陆：

mysql -uroot -p -S /data/mysql3306/data/mysql.sock 

 

 

@@@@@@@@@

多实例的配置，到这里就结束了。如有问题请留言大家一起学习。