MySQL单机多实例的配置笔记

网上的大部分都是mysqld_multi方式启动的多实例。

以前在老男孩老师的视频里看到的另一种方法，如下。

参考：http://www.ilanni.com/?p=8020

环境

    CentOS6.7x86_64【2.6.32-573.el6.x86_64】

    mysql-5.6.30通用二进制安装包

创建mysql的独立配置文件目录

mkdir /data/{3306,3307} -pv

建立账户

groupadd mysql

useradd -s /sbin/nologin -g mysql -Mmysql

创建mysql的数据目录

mkdir -p /data/{3306,3307}/data

安装Mysql及准备配置文件

tar xf mysql-5.6.30-linux-glibc2.5-x86_64.tar.gz-C /usr/local

cd /usr/local

ln -s mysql-5.6.30-linux-glibc2.5-x86_64mysql

chown mysql.mysql mysql/ -R

cp /usr/local/mysql/support-files/my-default.cnf /data/3306/my.cnf 

修改/data/3306/my.cnf

[client]

user = root

socket =/data/3306/mysql.sock

port = 3306

[mysql]

no-auto-rehash

default_character_set = utf8

prompt = [\d] >

[mysqld]

# Basic

port = 3306

socket =/data/3306/mysql.sock

basedir =/usr/local/mysql

datadir =/data/3306/data

user = mysql

skip-external-locking

skip_name_resolve = ON

character-set-server = utf8

key_buffer_size = 16M

table_open_cache = 64

sort_buffer_size = 512K

net_buffer_length = 8K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

max_allowed_packet = 50M

max_connections = 1000

thread_cache_size = 80

connect_timeout = 200

wait_timeout = 100

interactive_timeout = 100

server_id = 1

# query cache

query_cache_type=OFF

query_cache_size=0

# logs

slow_query_log=ON

slow_query_log_file =/data/3306/data/localhost-slow.log

long_query_time = 5

log_error = /data/3306/data/mysql.err

log_warnings = 2

# binlogs

log-bin=mysql-bin

binlog_format=mixed

expire_logs_days = 10

binlog_cache_size=2M

# InnoDB Optimize

innodb_file_per_table = ON

innodb_write_io_threads = 8

innodb_read_io_threads = 8

innodb_buffer_pool_dump_at_shutdown= ON

innodb_buffer_pool_load_at_startup= ON

innodb_buffer_pool_instances = 8

innodb_buffer_pool_size = 2G

innodb_additional_mem_pool_size =128M

innodb_data_home_dir = /data/3306/data

innodb_data_file_path =ibdata1:10M:autoextend

innodb_log_group_home_dir= /data/3306/data

innodb_log_file_size = 512M

innodb_log_buffer_size = 1G

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqld_safe]

log-error=/data/3306/3306.err

pid-file=/data/3306/3306.pid

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

修改/data/3307/my.cnf

cp /data/3306/my.cnf  /data/3307/my.cnf

sed -i 's#3306#3307#g'  /data/3307/my.cnf

初始化mysql多实例

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql

chown mysql.mysql/data/{3306,3307} -R

启动mysql多实例

/usr/local/mysql/bin/mysqld_safe--defaults-file=/data/3306/my.cnf &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf&

ps aux |grep mysqld

ss -lnt|egrep "3306|3307"

配置启动脚本

vim /etc/init.d/mysqld_3306

#!/bin/sh

# chkconfig: 345 80 15

# description: A very fast andreliable SQL database engine.

#init

port=3306

mysql_user="root"

mysql_pwd=""

CmdPath="/usr/local/mysql/bin"

#startup function

function_start_mysql()

{

   printf "Starting MySQL...\n"

   /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf2>&1 > /dev/null &

}

#stop function

function_stop_mysql()

{

   printf "Stoping MySQL...\n"

   ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S/data/${port}/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

   printf "Restarting MySQL...\n"

   function_stop_mysql

   sleep 2

   function_start_mysql

}

case $1 in

start)

   function_start_mysql

;;

stop)

   function_stop_mysql

;;

restart)

   function_restart_mysql

;;

*)

   printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

vim /etc/init.d/mysqld_3307

#!/bin/sh

# chkconfig: 345 81 16

# description: A very fast andreliable SQL database engine.

#init

port=3307

mysql_user="root"

mysql_pwd=""

CmdPath="/usr/local/mysql/bin"

#startup function

function_start_mysql()

{

   printf "Starting MySQL...\n"

   /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf2>&1 > /dev/null &

}

#stop function

function_stop_mysql()

{

   printf "Stoping MySQL...\n"

   ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S/data/${port}/mysql.sock shutdown

}

#restart function

function_restart_mysql()

{

   printf "Restarting MySQL...\n"

   function_stop_mysql

   sleep 2

   function_start_mysql

}

case $1 in

start)

   function_start_mysql

;;

stop)

   function_stop_mysql

;;

restart)

   function_restart_mysql

;;

*)

   printf "Usage: /data/${port}/mysql {start|stop|restart}\n"

esac

设置开机自启动

chmod +x /etc/init.d/mysqld_3306

chmod +x /etc/init.d/mysqld_3307

chkconfig --add mysqld_3306

chkconfig --add mysqld_3307

登录测试

mysql -uroot -S /data/3306/mysql.sock

mysql -uroot -S /data/3307/mysql.sock

都是可以登录的，后面和普通的数据库一样操作就行了。