MySQL多实例
简单的说就是在一台机器上开启多个不同的服务端口(如3306,3307等)运行多个MySQL服务进程，这些服务进程通过不同个socket监听不同的服务端口来提供各自的服务
多实例的作用与问题
1、有效利用服务器
2、节约服务器资源
3、资源互相抢占问题
多实例应用场景
1、资金紧张型公司
2、并发访问不是特别大的业务
3、门户网站应用MySQL多实例场景
1、创建MySQL多实例的数据文件目录
先关掉我们之前启动的单实例
[root@centos03 ~]# /etc/init.d/mysqld stop
[root@centos03 ~]# mv /etc/init.d/mysqld /etc/init.d/dan.mysqld
[root@centos03 ~]# mkdir -p /data/{3306,3307}/data
[root@centos03 ~]# tree /data
/data
|-- 3306
| `-- data
`-- 3307
`-- data
4 directories, 0 files
多实例的是在我们之前上面文章操作单实例的基础上进行的，只是在单实例进行到
ln -s /application/mysql-5.5.32/ /application/mysql 这一步时候就不再往下面操作了，这个不清楚的话可以参考上篇文章安装，这里就不再重复操作了
2、创建MySQL多实例的配置文件
[root@centos03 ~]# vim /data/3306/my.cnf[client]port = 3306socket = /data/3306/mysql.sock[mysql]no-auto-rehash[mysqld]user = mysqlport = 3306socket = /data/3306/mysql.sockbasedir = /application/mysqldatadir = /data/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 = 2Mlong_query_time = 1#log_long_format#log-error = /data/3306/error.log#log-slow-queries = /data/3306/slow.logpid-file = /data/3306/mysql.pidlog-bin = /data/3306/mysql-binrelay-log = /data/3306/relay-binrelay-log-info-file = /data/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 = 1Mlower_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=/data/3306/mysql_martin3306.errpid-file=/data/3306/mysqld.pidvim /data/3307/my.cnf[client]port = 3307socket = /data/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user = mysqlport = 3307socket = /data/3307/mysql.sockbasedir = /application/mysqldatadir = /data/3307/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/3307/error.log#log-slow-queries = /data/3307/slow.logpid-file = /data/3307/mysql.pid#log-bin = /data/3307/mysql-binrelay-log = /data/3307/relay-binrelay-log-info-file = /data/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 = 1Mlower_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 = 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=/data/3307/mysql_martin3307.errpid-file=/data/3307/mysqld.pid
3、创建多实例的启动脚本
[root@centos03 ~]# vim /data/3306/mysql#!/bin/sh#initport=3306mysql_user="root"mysql_pwd="123456"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startup functionfunction_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 & 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 /data/${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: /data/${port}/mysql {start|stop|restart}\n"esac[root@centos03 ~]# vim /data/3307/mysql#!/bin/sh#initport=3307mysql_user="root"mysql_pwd="123456"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startup functionfunction_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 & 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 /data/${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: /data/${port}/mysql {start|stop|restart}\n"esac[root@centos03 ~]# tree /data/data|-- 3306| |-- data| |-- my.cnf| `-- mysql`-- 3307 |-- data |-- my.cnf `-- mysql4 directories, 4 files[root@centos03 ~]# chown -R mysql.mysql /data[root@centos03 ~]# find /data -type f -name "mysql"|xargs ls -l-rw-r--r--. 1 mysql mysql 1018 Jun 14 01:04 /data/3306/mysql-rw-r--r--. 1 mysql mysql 1017 Jun 14 01:01 /data/3307/mysql[root@centos03 ~]# find /data -type f -name "mysql"|xargs chmod 700 [root@centos03 ~]# find /data -type f -name "mysql"|xargs ls -l -rwx------. 1 mysql mysql 1018 Jun 14 01:04 /data/3306/mysql-rwx------. 1 mysql mysql 1017 Jun 14 01:01 /data/3307/mysql 4、初始化数据库
[root@example mysql-5.5.32]# cd /application/mysql/scripts/[root@example scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data/ --user=mysql[root@example scripts]# ./mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data/ --user=mysql
5、启动数据库
[root@centos03 scripts]# /data/3306/mysql startStarting MySQL...[root@centos03 scripts]# /data/3307/mysql start Starting MySQL...[root@centos03 scripts]# ss -lantup|grep 330tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",20906,12))tcp LISTEN 0 128 *:3307 *:* users:(("mysqld",21624,11)) 6、登录数据库测试
[root@centos03 scripts]# mysql -S /data/3306/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.32-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> [root@centos03 scripts]# mysql -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
7、两个数据库都成功了，大家可以创建一个实例3308，这里就不再操作了，简单看下最后的效果