简单的说，mysql 多实例就是在一台服务器上同时开启多个不同的服务端口（如 3306、 3307），同时运行多个 mysql 服务进程，这些服务进程通过不同的 socket 监听不同的服务端 口来提供服务 这些 mysql 多实例公用一套 mysql 安装程序，使用不同的 my.cnf（也可以相同）配置 文件、启动程序（也可以相同）和数据文件，在提供服务时，多实例 mysql 在逻辑上看来是各自独立的，他们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
[root@mysql ~]# yum install ncurses-devel libaio-devel –y[root@mysql ~]# rpm -qa ncurses-devel libaio-devel libaio-devel-0.3.107-10.el6.x86_64 ncurses-devel-5.7-4.20090207.el6.x86_64?
[root@mysql tools]# pwd /home/tools[root@mysql tools]# ls cmake-2.8.8.tar.zip[root@mysql tools]# unzip cmake-2.8.8.tar.zip Archive: cmake-2.8.8.tar.zip inflating: cmake-2.8.8.tar.gz[root@mysql tools]# ls cmake-2.8.8.tar.gz cmake-2.8.8.tar.zip[root@mysql tools]# tar xfv cmake-2.8.8.tar.gz[root@mysql tools]# ls cmake-2.8.8 cmake-2.8.8.tar.gz cmake-2.8.8.tar.zip[root@mysql tools]# cd cmake-2.8.8[root@mysql cmake-2.8.8]# ./configure [root@mysql cmake-2.8.8]# gmake [root@mysql cmake-2.8.8]# gmake install
关于 cmake 软件也可以直接 yum 安装
yum install -y cmake
1、创建安装 mysql 软件的用户 mysql[root@mysql ~]# useradd mysql -s /sbin/nologin –M2、解压 mysql-5.5.27 的软件包[root@mysql tools]# lscmake-2.8.8 cmake-2.8.8.tar.gz cmake-2.8.8.tar.zip mysql-5.5.27 mysql-5.5.27.tar.gz3、编译安装 mysql（下面是编译参数）/usr/local/bin/cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.27 -DMYSQL_DATADIR=/application/mysql-5.5.27/data -DMYSQL_UNIX_ADDR=/application/mysql-5.5.27/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_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[root@mysql mysql-5.5.27]# make && make install[root@mysql mysql-5.5.27]# ln -s /application/mysql-5.5.27 /application/mysql[root@mysql ~]# echo "PATH=/application/mysql/bin:$PATH" >>/etc/profile 把 mysql 的命令放入至全局环境变量之中[root@mysql ~]# . /etc/profile[root@mysql mysql-5.5.27]# cd /application/mysql[root@mysql mysql]# lsbin data include lib mysql-test scripts sql-benchCOPYING docs INSTALL-BINARY man README share support-files
[root@mysql ~]# mkdir /mysqldata/{3306,3307}/data -p[root@mysql ~]# tree /mysqldata/mysqldata├── 3306 │ └── data└── 3307 └── data 4 directories, 0 files?
#实例1：3306配置文件[root@mysql 3306]# lsdata my.cnf（配置文件） mysql（程序启动文件） mysql-bin.000001 mysql-bin.indexmysqld.pid mysql_oldboy3306.err mysql.sock[root@mysql 3306]# cat my.cnf[client]port = 3306socket = /mysqldata/3306/mysql.sock[mysql]no-auto-rehash[mysqld]user = mysqlport = 3306socket = /mysqldata/3306/mysql.sockbasedir = /application/mysqldatadir = /mysqldata/3306/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1Mjoin_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2M?long_query_time = 1#log_long_format#log-error = /data/3306/error.log#log-slow-queries = /data/3306/slow.logpid-file = /mysqldata/3306/mysql.pidlog-bin = /mysqldata/3306/mysql-binrelay-log = /mysqldata/3306/relay-binrelay-log-info-file = /mysqldata/3306/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_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_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 1innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe]log-error=/mysqldata/3306/mysql_oldboy3306.errpid-file=/mysqldata/3306/mysqld.pid 实例 1、3306 的启动文件，并授权执行的权限[root@mysql 3306]# chmod +x /mysqldata/3306/mysql[root@mysql 3306]# cat mysql#!/bin/sh#################################################this scripts is created by oldboy at 2007-06-09#################################################initport=3306mysql_user="root"mysql_pwd="oldboy"CmdPath="/application/mysql/bin"mysql_sock="/mysqldata/${port}/mysql.sock"#startup functionfunction_start_mysql(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/mysqldata/${port}/my.cnf 2>&1 >/dev/null & else printf "MySQL is running...\n" exit fi}#stop functionfunction_stop_mysql(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S/mysqldata/${port}/mysql.sock shutdown fi}#restart functionfunction_restart_mysql(){ printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql}case $1 instart) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: /mysqldata/${port}/mysql {start|stop|restart}\n"esac 更改 3306 实例目录的属主和属组权限，初始化数据库[root@mysql 3306]# chown -R mysql.mysql /mysqldata/3306[root@mysql mysql]# ./scripts/mysql_install_db --datadir=/mysqldata/3306/data --basedir=/application/mysql --user=mysqlInstalling MySQL system tables...OKFilling help tables...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/application/mysql/bin/mysqladmin -u root password ‘new-password‘/application/mysql/bin/mysqladmin -u root -h mysql password ‘new-password‘Alternatively you can run:/application/mysql/bin/mysql_secure_installationwhich will also give you the option of removing the test databases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd /application/mysql ; /application/mysql/bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd /application/mysql/mysql-test ; perl mysql-test-run.plPlease report any problems with the /application/mysql/scripts/mysqlbug script! 启动 3306 实例[root@mysql 3306]# /mysqldata/3306/mysql start[root@mysql 3306]# lsof -i :3306COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 31375 mysql 12u IPv4 98252 0t0 TCP *:mysql (LISTEN)
3307 实例配置文件[root@mysql 3307]# lsdata my.cnf（配置文件） mysql（程序启动文件） mysqld.pid mysql_oldboy3307.errmysql.sock[root@mysql 3307]# cat my.cnf[client]port = 3307socket = /mysqldata/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user = mysqlport = 3307socket = /mysqldata/3307/mysql.sockbasedir = /application/mysqldatadir = /mysqldata/3307/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_packet =8Msort_buffer_size = 1M join_buffer_size = 1Mthread_cache_size = 100thread_concurrency = 2query_cache_size = 2Mquery_cache_limit = 1Mquery_cache_min_res_unit = 2k#default_table_type = InnoDBthread_stack = 192K#transaction_isolation = READ-COMMITTEDtmp_table_size = 2Mmax_heap_table_size = 2M#long_query_time = 1#log_long_format#log-error = /data/3307/error.log#log-slow-queries = /data/3307/slow.logpid-file = /mysqldata/3307/mysql.pid#log-bin = /data/3307/mysql-binrelay-log = /mysqldata/3307/relay-binrelay-log-info-file = /mysqldata/3307/relay-log.infobinlog_cache_size = 1Mmax_binlog_cache_size = 1Mmax_binlog_size = 2Mexpire_logs_days = 7key_buffer_size = 16Mread_buffer_size = 1Mread_rnd_buffer_size = 1Mbulk_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_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 3innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32Minnodb_data_file_path = ibdata1:128M:autoextendinnodb_file_io_threads = 4 innodb_thread_concurrency = 8innodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 2Minnodb_log_file_size = 4Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M`[mysqld_safe]log-error=/mysqldata/3307/mysql_oldboy3307.errpid-file=/mysqldata/3307/mysqld.pid 3307 实例的启动配置文件[root@mysql 3307]# cat /mysqldata/3307/mysql#!/bin/sh#################################################this scripts is created by oldboy at 2007-06-09#oldboy QQ:31333741#site:http://www.etiantian.org#blog:http://oldboy.blog.51cto.com#oldboy trainning QQ group: 208160987 226199307 44246017#################################################initport=3307mysql_user="root"mysql_pwd="oldboy"CmdPath="/application/mysql/bin"mysql_sock="/mysqldata/${port}/mysql.sock"#startup functionfunction_start_mysql(){ if [ ! -e "$mysql_sock" ];then printf "Starting MySQL...\n" /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/mysqldata/${port}/my.cnf 2>&1 >/dev/null & else printf "MySQL is running...\n" exit fi}#stop functionfunction_stop_mysql(){ if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped...\n" exit else printf "Stoping MySQL...\n" ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S/mysqldata/${port}/mysql.sock shutdown fi}#restart functionfunction_restart_mysql(){ printf "Restarting MySQL...\n" function_stop_mysql sleep 2 function_start_mysql}case $1 instart) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf "Usage: /mysqldata/${port}/mysql {start|stop|restart}\n"esac 更改 3307 实例的目录权限，初始化数据库[root@mysql 3307]# chown -R mysql.root /mysqldata/3307[root@mysql mysql]# ./scripts/mysql_install_db --datadir=/mysqldata/3307/data --basedir=/application/mysql --user=mysqlInstalling MySQL system tables...OKFilling help tables...OK To start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/application/mysql/bin/mysqladmin -u root password ‘new-password‘/application/mysql/bin/mysqladmin -u root -h mysql password ‘new-password‘Alternatively you can run:/application/mysql/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd /application/mysql ; /application/mysql/bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd /application/mysql/mysql-test ; perl mysql-test-run.plPlease report any problems with the /application/mysql/scripts/mysqlbug script!启动 3307 实例[root@mysql mysql]# /mysqldata/3307/mysql startStarting MySQL...[root@mysql mysql]# lsof -i :3307COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEmysqld 32195 mysql 11u IPv4 99276 0t0 TCP *:opsession-prxy (LISTEN)
多实例本地登录一般是通过 socket 文件来指定具体到那个实例的，此文件的具体位置是在 mysql 编译过程或者 my.cnf 文件里指定的，在本地登录数据库时，登录程序会通过socket 文件来判断登录的是哪个数据库实例例如：通过 mysql –uroot –ppcwangjixuan –S /mysqldata/3307/mysql.sock 可知，登录的是 3307 这个实例，mysql.sock 文件是 mysql 服务端与本地 mysql 客户端进行通信的 unix 套接字文件。
远程登录 mysql 多实例中的一个实例中，通过 TCP 端口（port）来指定所要登录的mysql 实例，此端口的配置是在 mysql 配置文件 my.cnf 中指定的例如：在 mysql –oldboy –ppcwangjixuan –h 10.0.0.171 –P 3307 中，-P 为端口参数，后面接具体的实例端口，端口是一种逻辑连接位置，是客户端程序被分派到计算机上特殊服务程序的一种方式，强调提前在 10.0.0.171 上对 oldboy 用户做了授权。
