mysql的多实例有两种方式可以实现，两种方式各有利弊。

第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。

环境介绍：
mysql 版本：5.1.59
操作系统：Centos 5.5~5.6
mysql实例数：3个
实例占用端口分别为：3306、3307、3308

必要软件包

yum -y install ncurses-devel gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* libmcrypt* libtool-ltdl-devel* libtool make

创建mysql用户

/usr/sbin/groupadd mysql/usr/sbin/useradd -g mysql mysql -s /sbin/nologin

编译安装MySQL

cd /usr/local/src/mysql-5.1.59./configure /'--prefix=/usr/local/mysql' /'--with-charset=utf8' /'--with-extra-charsets=complex' /'--with-pthread' /'--enable-thread-safe-client' /'--with-ssl' /'--with-client-ldflags=-all-static' /'--with-mysqld-ldflags=-all-static' /'--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap,archive' /'--enable-shared' '--enable-assembler'# 接着执行编译make &&　make install

初始化数据库

/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3306/data --user=mysql/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3307/data --user=mysql/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3308/data --user=mysql

*为什么要初始化数据库? *
答:初始化的主要目的就是创建基础的数据库文件,例如生成MySQL库,表等.

授权数据库权限给mysql用户

setfacl -m u:mysql:rwx -R /usr/local/mysql setfacl -m d:u:mysql:rwx -R /usr/local/mysql

第一种使用多个配置文件启动多个不同进程的情况：

创建3个实例的配置文件

3306端口的配置文件

vim /usr/local/mysql/data/3306/my.cnf

[client]port = 3306socket = /usr/local/mysql/data/3306/mysql.sock[mysqld]datadir = /usr/local/mysql/data/3306/data/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3306socket = /usr/local/mysql/data/3306/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 3306key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=//u@//d //R://m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]log-error=/usr/local/mysql/data/3306/mysql_error3306.errpid-file=/usr/local/mysql/data/3306/mysqld.pidopen-files-limit = 8192

3307端口的配置文件

vim /usr/local/mysql/data/3307/my.cnf

[client]port = 3307socket = /usr/local/mysql/data/3307/mysql.sock[mysqld]datadir=/usr/local/mysql/data/3307/data/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3307socket = /usr/local/mysql/data/3307/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 3307key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=//u@//d //R://m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]log-error=/usr/local/mysql/data/3307/mysql_error3307.errpid-file=/usr/local/mysql/data/3307/mysqld.pidopen-files-limit = 8192

3308端口的配置文件

vim /usr/local/mysql/data/3308/my.cnf

[client]port = 3308socket = /usr/local/mysql/data/3308/mysql.sock[mysqld]datadir=/usr/local/mysql/data/3308/dataskip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3308socket = /usr/local/mysql/data/3308/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 3308key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=//u@//d //R://m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]log-error=/usr/local/mysql/data/3308/mysql_error3308.errpid-file=/usr/local/mysql/data/3308/mysqld.pidopen-files-limit = 8192

创建自启动文件

vim /usr/local/mysql/data/3306/mysqld , 3306的启动文件

#!/bin/bashmysql_port=3306mysql_username="admin"mysql_password="password"function_start_mysql(){printf "Starting MySQL.../n"/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null &}function_stop_mysql(){printf "Stoping MySQL.../n"/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S  /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown}function_restart_mysql(){printf "Restarting MySQL.../n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage:  /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;esac

vim /usr/local/mysql/data/3307/mysqld , 3307的启动文件

#!/bin/bashmysql_port=3307mysql_username="admin"mysql_password="password"function_start_mysql(){printf "Starting MySQL.../n"/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null &}function_stop_mysql(){printf "Stoping MySQL.../n"/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown}function_restart_mysql(){printf "Restarting MySQL.../n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;esac

vim /usr/local/mysql/data/3308/mysqld , 3308的启动文件

#!/bin/bashmysql_port=3308mysql_username="admin"mysql_password="password"function_start_mysql(){printf "Starting MySQL.../n"/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 /dev/null &}function_stop_mysql(){printf "Stoping MySQL.../n"/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown}function_restart_mysql(){printf "Restarting MySQL.../n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;esac

上面的三个启动文件中都包含了数据库密码等敏感信息,所以为了安全考虑,将权限改为只有root可以操作，执行下面的命令

find /usr/local/mysql/data -name 'mysqld'|xargs chmod 700 && find /usr/local/mysql/data -name 'mysqld'|xargs setfacl -b && find /usr/local/mysql/data -name 'mysqld'|xargs chown root.root

启动3306 3307 3308的MySQL

cd /usr/local/mysql/data && ./3306/mysqld startcd /usr/local/mysql/data && ./3307/mysqld startcd /usr/local/mysql/data && ./3308/mysqld start

启动报错
151016 06:36:48 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data/3307/data/
/usr/local/mysql/bin/mysqld: File './mysql-bin.~rec~' not found (Errcode: 28)
151016  6:36:48 [ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register  file.
151016  6:36:48 [ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
151016  6:36:48 [ERROR] Aborting
151016  6:36:48 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
151016 06:36:48 mysqld_safe mysqld from pid file /usr/local/mysql/data/3307/mysqld.pid ended

错误代码 (Errcode: 28)

产生错误原因(磁盘空间已满)

# df -Th文件系统      类型    容量  已用 可用 已用% 挂载点/dev/mapper/VolGroup00-LogVol00              ext3    8.9G  8.4G     0 100% //dev/sda1     ext3     99M   12M   82M  13% /boottmpfs        tmpfs   1014M     0 1014M   0% /dev/shm

解决方案
释放硬盘空间，重新启动即可。

将mysql的bin加入到path中

ln -s /usr/local/mysql/bin/mysql /usr/local/sbin/mysqlcd ~#或者，把path添加到当前用户目录的bashrc中，如果需要全局设定，请修改`/etc/profile` vi .bashrc #加入以下内容 PATH=/usr/local/mysql/bin:$PATH source /etc/profile

修改默认root密码(指定sock文件)

mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3306/mysql.sock mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3307/mysql.sock mysqladmin -uroot password 'aaaaaa' -S /usr/local/mysql/data/3308/mysql.sock

##登录数据库增加进程关闭admin(password)账号

/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3306/mysql.sockGRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password';flush privileges;

本机登录指定sock登录

mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock mysql -uroot -paaaaaa -S /usr/local/mysql/data/3307/mysql.sock mysql -uroot -paaaaaa -S /usr/local/mysql/data/3308/mysql.sock

后期增加一个实例的情况

初始化数据库

/usr/local/mysql/bin/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/3309/data --user=mysql

新建一个默认配置文件

vim /usr/local/mysql/data/3309/my.cnf

[client]port = 3309socket = /usr/local/mysql/data/3309/mysql.sock[mysqld]datadir = /usr/local/mysql/data/3309/data/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3309socket = /usr/local/mysql/data/3309/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 3309key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=//u@//d //R://m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]log-error=/usr/local/mysql/data/3309/mysql_error3309.errpid-file=/usr/local/mysql/data/3309/mysqld.pidopen-files-limit = 8192

创建自启动文件

#!/bin/bashmysql_port=3309mysql_username="admin"mysql_password="password"function_start_mysql(){printf "Starting MySQL.../n"/bin/bash /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/data/${mysql_port}/my.cnf 2>&1 > /dev/null &}function_stop_mysql(){printf "Stoping MySQL.../n"/usr/local/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /usr/local/mysql/data/${mysql_port}/mysql.sock shutdown}function_restart_mysql(){printf "Restarting MySQL.../n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /usr/local/mysql/data/${mysql_port}/mysqld {start|stop|restart|kill}";;esac

授权启动文件可执行

chmod a+x /usr/local/mysql/data/3309/mysqld

相关博文参考： 
mysql多实例的配置和管理