MySQL 5.6 Binary Package Installation Configuration multi-instance method

MySQL supports launching multiple instances on a single machine, if your machine is performing well, launching multiple instances will maximize your server hardware resources.

MySQL multiple instances generally have two configuration methods:

1, the official recommended Mysqld_multi mode, multiple instances share a copy of the configuration file

2. Each instance uses a separate configuration file (this is how this article describes it)

Software Environment

1. SUSE Linux Enterprise Server SP1

2, Mysql-5.6.26-1.sles11.x86_64.rpm-bundle

Installation steps

1. Install MySQL

#rpm-IVH mysql-client-5.6.26-1.sles11.x86_64.rpm mysql-server-5.6.26-1.sles11.x86_64.rpm

2. Stop the existing MySQL service

#service MySQL Stop

#chkconfig MySQL off

3. Create multi-instance data save directory

#mkdir-P/data/mysql/{3306,3307}/data
#mkdir-P/data/mysql/{3306,3307}/log

4, the new my.cnf in/data/mysql/3306, the content is as follows

[Client]port=3306socket=/data/mysql/3306/mysql.sock[mysqld]port=3306socket=/data/mysql/3306/mysql.sockpid-file =/data/mysql/3306/data/mysql.pidbasedir=/usrdatadir=/data/mysql/3306/dataserver-id=1log-bin= mysql-binlog-bin-index=mysql-bin.index# Logginglog_error=/data/mysql/3306/log/mysql-error.logslow_query_log_ File=/data/mysql/3306/log/mysql-slow.logslow_query_log=1

5. Copy/etc/rc.d/mysql startup script to/data/mysql/3306

#cp/etc/rc.d/mysql/data/mysql/3306/

Edit the MySQL file under the/data/mysql/3306 directory and modify it as follows (the red part is the modified part)

#!/bin/sh# Copyright abandoned 1996 TCX datakonsult AB & Monty program KB & Detron hb# This file was public domain and comes with NO WARRANTY of any kind# MySQL daemon start/stop script.# Usually this was put in/etc/init.d (at least on M Achines SYSV R4 based# systems) and linked To/etc/rc3.d/s99mysql and/etc/rc0.d/k01mysql.# when the-is-done the MySQL SE  RVer'll be started if the machine is# started and shut, the systems goes down.# Comments to support Chkconfig On RedHat linux# chkconfig:2345 36# description:a very fast and reliable SQL database engine.# Comments to support L SB init script conventions### BEGIN init info# provides:mysql# required-start: $local _fs $network $remote _fs# Should-star T:ypbind nscd LDAP ntpd xntpd# required-stop: $local _fs $network $remote _fs# default-start:2 3 4 AA 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, then you# has to does one of the following things for this script to work:##-Run thi s script from within the MySQL installation directory#-Create a/etc/my.cnf file with the following information:# [Mys  qld]# 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 V ariable# below.## If you want to affect other MySQL variables, you should make your changes# in the/etc/my.cnf, ~/.my.c NF or other MySQL configuration files.# If Your change base dir, you must also change datadir. These may get# overwritten by settings in the MySQL configuration files.port=3306Basedir=datadir=/data/mysql/$portmycnf= $datadir/my.cnf# Default value, in seconds, afterwhich the script should-timeout waiting# for server start. # Value Here are overriden by Value in MY.CNF. # 0 means don ' t wait at all# negative numbers mean to wait indefinitelyservice_startup_timeout=900# Lock directory for Red Hat/suse.lockdir= '/var/lock/subsys 'lock_file_path= "$lockdir/mysql_$port"# The following variables is only set for letting Mysql.server find things.# set some defaultsmysqld_pid_file_path=if tes T-z "$basedir" then basedir=/usr bindir=/usr/bin if Test-z "$datadir" then Datadir=/var/lib/mysql fi sbindir=/us R/sbin libexecdir=/usr/sbinelse bindir= "$basedir/bin" if Test-z "$datadir" then datadir= "$basedir/data" fi sbind Ir= "$basedir/sbin" libexecdir= "$basedir/libexec" fi# Datadir_set is used to determine if DataDir were 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 _functionselse log_success_msg () {echo "success! [email protected] "} log_failure_msg () {echo" error!  [email protected] "}fipath="/sbin:/usr/sbin:/bin:/usr/bin: $basedir/bin "Export pathmode=$1 # Start or stop[$#-ge 1] && shiftother_args= "$*" # Uncommon, but needed WHen called from an RPM upgrade action # expected: "--skip-networking--skip-grant-tables" # they is n  OT checked here, intentionally, as it's 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 ';; Esacparse_server_arguments () {for ARG does 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= "$" # Created | removed pid= "$" # Process ID of the program operating on the Pid-file p  Id_file_path= "$" # 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-a pid-file to pops 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'll never be updated if test-n "$pid"; Then if kill-0 "$pid" 2>/dev/null; Then: # The server still runs else # The server is exited between the last pid-file check and n          ow. If Test-n "$avoid _race_condition";         Then Avoid_race_condition= "" Continue # Check again.        Fi # There's nothing that would 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 1 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 On Ly group, which is read from now on IS [Mysqld]if test-x./bin/my_print_defaultsthen print_defaults= "./bin/my_print_defa Ults "Elif test-x $bindir/my_print_defaultsthen print_defaults=" $bindir/my_print_defaults "elif test-x $bindir/mysql_ Print_defaultsthen 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_defa Ults "Break fi do fi # Hope It's in the PATH ... but I doubt it test-z" $print _defaults "&& p   rint_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#extra_arg S= ""#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#fiParse_server_arguments ' $print _defaults $extra _args mysqld server Mysql_server mysql.server ' # # Set pid file if not given#i F test-z "$mysqld _pid_file_path" then mysqld_pid_file_path= $datadir/' hostname '. Pidelse case "$mysqld _pid_file_path"    In/*);;  *) mysqld_pid_file_path= "$datadir/$mysqld _pid_file_path";;    Esacficase "$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 MYSQ LD with the my.cnf file.      This script # is overwritten at next upgrade. $bindir/mysqld_safe--defaults-file= "$MYCNF" --datadir= "$datadir"--pid-file= "$mysqld _pid_file_path" $other _args >/dev/null 2>&1 & wait_for_pid CRE Ated "$!" "$mysqld _pid_file_path";      Return_value=$? # make lock for Redhat/suse if Test-w "$lockdir" then Touch "$lock _file_path" fi exit $retur  N_value else log_failure_msg "couldn ' t find MySQL server ($bindir/mysqld_safe)" FI;; ' Stop ') # Stop daemon.    We use a signal here to avoid have 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&gt ;/dev/null) then echo $echo _n ' shutting down MySQL ' kill $mysqld _pid # mysqld should remove the        PID file when it is 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 being found!"  fi;;    ' Restart ') # Stop the service and regardless of whether it is # Running or not, start it again. If $ stop $other _args;       Then $ start $other _args else log_failure_msg "Failed to stop running server, so refusing-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 s      Ervice MySQL "touch" $mysqld _pid_file_path "Else log_failure_msg" MySQL pid file could not being 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 was not ru nning, but PID file exists "Exit 1 fi Else # Try to find appropriate mysqld process mysqld_pid= ' p Idof $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 being found ($mysqld _pid)" Exit 5 elif Test-z $mysqld _pid; Then if Test-f "$lock _file_path";         Then Log_failure_msg "MySQL isn't 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 was running but PID file    Could not being found "Exit 4 fi fi;; *) # Usage basename= ' basename ' + ' echo ' usage: $basename {start|stop|restart|reload|force-Reload|status} [MySQL server Options] "Exit 1;; Esacexit 0

6. Initialize Data

#mysql_install_db--defaults-file=/data/mysql/3306/my.cnf--user=mysql--datadir=/data/mysql/3306/data--force

7. Start MySQL

#/data/mysql/3306/mysql start

#ps-ef | grep MySQL

8, 3307 of the configuration such as 3306

Configuration file Download Http://files.cnblogs.com/files/txk1452/mysql%E5%A4%9A%E5%AE%9E%E4%BE%8B%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6.rar

MySQL 5.6 Binary Package Installation Configuration multi-instance method