linux mysql 实例_linux下Mysql多实例实现

什么是MySQL多实例

MySQL多实例就是在一台机器上开启多个不同的服务端口(如：3306,3307)，运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务:；

Mysql多实例实现的3种方式

mysql 多实例常规来讲，有三种方案可以实现，这三种方案各有利弊，如下：

1、基于多配置文件

通过使用多个配置文件来启动不同的进程，以此来实现多实例。

配置文件：

[client]

port=3307socket=/data/3307/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user=mysql

port=3307socket=/data/3307/mysql.sock

basedir=/application/mysql

datadir=/data/3307/data

open_files_limit=1024back_log=600max_connections=800max_connect_errors=3000table_cache=614external-locking =FALSE

max_allowed_packet=8M

sort_buffer_size=1M

join_buffer_size=1M

thread_cache_size=100thread_concurrency=2query_cache_size=2M

[root@web0013307]# cat my.cnf

[client]

port=3307socket=/data/3307/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user=mysql

port=3307socket=/data/3307/mysql.sock

basedir=/application/mysql

datadir=/data/3307/data

open_files_limit=1024back_log=600max_connections=800max_connect_errors=3000table_cache=614external-locking =FALSE

max_allowed_packet=8M

sort_buffer_size=1M

join_buffer_size=1M

thread_cache_size=100thread_concurrency=2query_cache_size=2M

query_cache_limit=1M

query_cache_min_res_unit=2k

#default_table_type=InnoDB

thread_stack=192K

#transaction_isolation= READ-COMMITTED

tmp_table_size=2M

max_heap_table_size=2M

#long_query_time= 1#log_long_format

#log-error = /data/3307/error.log

#log-slow-queries = /data/3307/slow.log

pid-file =/data/3307/mysql.pid

#log-bin = /data/3307/mysql-bin

relay-log =/data/3307/relay-bin

relay-log-info-file =/data/3307/relay-log.info

binlog_cache_size=1M

max_binlog_cache_size=1M

max_binlog_size=2M

expire_logs_days=7key_buffer_size=16M

read_buffer_size=1M

read_rnd_buffer_size=1M

bulk_insert_buffer_size=1M

#myisam_sort_buffer_size=1M

#myisam_max_sort_file_size=10G

#myisam_max_extra_sort_file_size=10G

#myisam_repair_threads= 1#myisam_recover

lower_case_table_names=1skip-name-resolve

slave-skip-errors =1032,1062replicate-ignore-db=mysql

server-id = 7innodb_additional_mem_pool_size=4M

innodb_buffer_pool_size=32M

innodb_data_file_path=ibdata1:128M:autoextend

innodb_file_io_threads=4innodb_thread_concurrency=8innodb_flush_log_at_trx_commit=2innodb_log_buffer_size=2M

innodb_log_file_size=4M

innodb_log_files_in_group=3innodb_max_dirty_pages_pct=90innodb_lock_wait_timeout=120innodb_file_per_table=0[mysqldump]

quick

max_allowed_packet=2M

[mysqld_safe]

log-error=/data/3307/mysql_oldboy3307.err

pid-file=/data/3307/mysqld.pid

View

优点：逻辑简单，配置简单

缺点：管理起来不方便

mysql启动停止脚本。

#！/bin/sh

#init

port=3307mysql_user="root"mysql_pwd="111111"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"function_start_mysql()

{if [ ! -e "$mysql_sock"];then

printf"Starting MySql...\n"

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

elseprintf"Mysql is running...\n"exit

fi

}

#stop function

function_stop_mysql()

{if [ ! -e "$mysql_sock"];then

printf"MySql is stopped...\n"exitelseprintf"Stoping MySQL...\n"${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

fi

}

#restart function

function_restart_mysql()

{

printf"Restarting Mysql...\n"function_stop_mysql

sleep2function_start_mysql

}case $1 instart)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;*)

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

esac

View Code

2、基于mysqld_multi

通过官方自带的 mysqld_multi 工具，使用单独配置文件来实现多实例

优点： 便于集中管理管理

缺点： 不方便针对每个实例配置进行定制

3、基于IM

使用 MySQL 实例管理器(MYSQLMANAGER)，这个方法好像比较好不过也有点复杂

优点：便于集中管理

缺点：耦合度高。IM一挂，实例全挂

不方便针对每个实例配置进行定制