一、什么是多实例以及优缺点
1、什么是多实例
就是在一台服务器上开启多个不同的服务端口，比如3306、3307、3308...，运行多个不同的MySQL服务。
这些MySQL多实例共用一套安装程序，使用不同（也可以相同）的配置文件、启动程序、数据文件；多实例对硬件资源的获取通过配置文件来指定。
2、多实例的优缺点
- 优点
可以有效利用服务器资源。当单个服务器资源充足时，可以充分利用剩余资源；节约资源。需要独立的数据库服务，并且需要主从同步的情况下。
- 缺点
当某一个实例消耗大量的CPU、内存时会影响其它实例提供的服务质量。
3、多实例应用场景
- 需要数据库单独提供服务，并且需要进行主从同步
- 服务并发量不大，服务器的资源很充足
二、多实例的安装
（一）准备工作
1、关闭单实例服务
在进行多实例安装时，先看看服务器上是否还有MySQL服务，如果有停掉它：
# 查看mysql服务 [root@hadoop-slave1 support-files]# ps -ef | grep mysql root 44428 1 0 Aug08 pts/0 00:00:00 /bin/sh /application/mysql-5.5.32/bin/mysqld_safe
--datadir=/application/mysql-5.5.32/data
--pid-file=/application/mysql-5.5.32/data/hadoop-slave1.pid mysql 44671 44428 0 Aug08 pts/0 00:00:14 /application/mysql-5.5.32/bin/mysqld --basedir=/application/mysql-5.5.32
--datadir=/application/mysql-5.5.32/data
--plugin-dir=/application/mysql-5.5.32/lib/plugin --user=mysql
--log-error=/application/mysql-5.5.32/data/hadoop-slave1.err
--pid-file=/application/mysql-5.5.32/data/hadoop-slave1.pid
--socket=/application/mysql-5.5.32/tmp/mysql.sock --port=3306 root 51080 2397 0 04:00 pts/0 00:00:00 mysql -uroot -p root 54564 18054 0 10:01 pts/2 00:00:00 grep --color=auto mysql # 停掉服务 [root@hadoop-slave1 support-files]# pkill mysqld # 再次查看 [root@hadoop-slave1 support-files]# ps -ef | grep mysql root 51080 2397 0 04:00 pts/0 00:00:00 mysql -uroot -p root 54642 18054 0 10:05 pts/2 00:00:00 grep --color=auto mysql
2、删除启动的文件
# 后续无需此种启动方式 [root@hadoop-slave1 support-files]# rm -f /etc/init.d/mysqld
（二）多实例配置
1、建立对应的目录
[root@hadoop-slave1 /]# mkdir -p /data/{3306,3307}/data
可看到生成的目录结构：
[root@hadoop-slave1 /]# tree data data #总的实例目录 ├── 3306 #3306实例目录 │ └── data #3306实例的数据文件目录 └── 3307 # 3307实例目录 └── data #3307实例的数据文件目录 4 directories, 0 files
2、my.cnf文件配置
在3306和3307目录下分别建立my.cnf文件：
[root@hadoop-slave1 /]# tree /data /data ├── 3306 │ ├── data │ └── my.cnf #3306配置文件 └── 3307 ├── data └── my.cnf #3307配置文件 4 directories, 2 files
其中，3306端口下的my.cnf文件为：
[client] port = 3306 socket = /data/3306/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3306 socket = /data/3306/mysql.sock basedir = /application/mysql-5.5.32 #先进行单实例安装，此为单实例的安装目录 datadir = /data/3306/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_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/3306/error.log #log-slow-queries = /data/3306/slow.log pid-file = /data/3306/mysql.pid log-bin = /data/3306/mysql-bin relay-log = /data/3306/relay-bin relay-log-info-file = /data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M max_binlog_size = 2M expire_logs_days = 7 key_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 = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 1 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3306/mysql_3306.err pid-file=/data/3306/mysqld.pid
3307端口下的my.cnf文件为：
[client] port = 3307 socket = /data/3307/mysql.sock [mysql] no-auto-rehash [mysqld] user = mysql port = 3307 socket = /data/3307/mysql.sock basedir = /application/mysql-5.5.32 datadir = /data/3307/data open_files_limit = 1024 back_log = 600 max_connections = 800 max_connect_errors = 3000 table_cache = 614 external-locking = FALSE max_allowed_packet =8M sort_buffer_size = 1M join_buffer_size = 1M thread_cache_size = 100 thread_concurrency = 2 query_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 = 7 key_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 = 1 skip-name-resolve slave-skip-errors = 1032,1062 replicate-ignore-db=mysql server-id = 3 innodb_additional_mem_pool_size = 4M innodb_buffer_pool_size = 32M innodb_data_file_path = ibdata1:128M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 4M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 innodb_file_per_table = 0 [mysqldump] quick max_allowed_packet = 2M [mysqld_safe] log-error=/data/3307/mysql_3307.err pid-file=/data/3307/mysqld.pid
上面不同端口的配置文件不同的地方有以下几处：
- socket参数
- datadir参数
- long_query_time(3306有3307无)
- server_id参数
3、多实例启动脚本
在每一个端口目录下建立各自的启动脚本：
[root@hadoop-slave1 /]# tree /data/ /data/ ├── 3306 │ ├── data │ ├── my.cnf │ └── mysql # 启动脚本 └── 3307 ├── data ├── my.cnf └── mysql #启动脚本
其中，3306端口的mysql启动脚本为：
#!/bin/sh port=3306 mysql_user="root" mysql_pwd="" CmdPath="/application/mysql-5.5.32/bin" mysql_sock="/data/${port}/mysql.sock" #startup function function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL... " /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else printf "MySQL is running... " exit fi } #stop function function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped... " exit else printf "Stoping MySQL... " ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown fi } #restart function function_restart_mysql() { printf "Restarting MySQL... " 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} " esac
3307端口的启动脚本为：
#!/bin/sh port=3307 mysql_user="root" mysql_pwd="" CmdPath="/application/mysql-5.5.32/bin" mysql_sock="/data/${port}/mysql.sock" #startup function function_start_mysql() { if [ ! -e "$mysql_sock" ];then printf "Starting MySQL... " /bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null & else printf "MySQL is running... " exit fi } #stop function function_stop_mysql() { if [ ! -e "$mysql_sock" ];then printf "MySQL is stopped... " exit else printf "Stoping MySQL... " ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown fi } #restart function function_restart_mysql() { printf "Restarting MySQL... " 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} " esac
4、授权
授权mysql用户管理多实例的目录：
[root@hadoop-slave1 /]# chown -R mysql:mysql /data
接着授权各自端口中mysql为可执行文件：
# 授权为可执行文件 [root@hadoop-slave1 /]# find /data/ -type f -name "mysql"|xargs chmod +x # 查看授权情况 [root@hadoop-slave1 /]# find /data/ -type f -name "mysql"|xargs ls -l -rwxr-xr-x. 1 mysql mysql 1314 Aug 9 18:34 /data/3306/mysql -rwxr-xr-x. 1 mysql mysql 1314 Aug 9 18:40 /data/3307/mysql
5、全局变量配置
全局变量配置MySQL的命令，这在单实例中已经配置了，可以查看：
[root@hadoop-slave1 /]# tail -1 /etc/profile export PATH=/application/mysql-5.5.32/bin:$PATH
（三）启动多实例数据库
1、多实例数据库初始化
这与单实例数据库初始化差不多，只不过数据目录有变化：
# 初始化3306 [root@hadoop-slave1 scripts]# pwd #进入到安装的MySQL目录 /application/mysql-5.5.32/scripts [root@hadoop-slave1 scripts]# ./mysql_install_db --basedir=/application/mysql-5.5.32/ --datadir=/data/3306/data --user=mysql
# ./便是表示当前目录下寻找mysql_install_db，否则寻找全局的 # 初始化3307 [root@hadoop-slave1 scripts]# ./mysql_install_db --basedir=/application/mysql-5.5.32/ --datadir=/data/3307/data --user=mysql
初始化就是生成数据的基本数据表。如下：
[root@hadoop-slave1 /]# tree /data/3306/data /data/3306/data ├── mysql │ ├── columns_priv.frm │ ├── columns_priv.MYD │ ├── columns_priv.MYI │ ├── db.frm │ ├── db.MYD │ ├── db.MYI │ ├── event.frm │ ├── event.MYD │ ├── event.MYI │ ├── func.frm │ ├── func.MYD │ ├── func.MYI │ ├── general_log.CSM │ ├── general_log.CSV │ ├── general_log.frm │ ├── help_category.frm │ ├── help_category.MYD │ ├── help_category.MYI │ ├── help_keyword.frm │ ├── help_keyword.MYD │ ├── help_keyword.MYI │ ├── help_relation.frm │ ├── help_relation.MYD │ ├── help_relation.MYI │ ├── help_topic.frm │ ├── help_topic.MYD │ ├── help_topic.MYI │ ├── host.frm │ ├── host.MYD │ ├── host.MYI │ ├── ndb_binlog_index.frm │ ├── ndb_binlog_index.MYD │ ├── ndb_binlog_index.MYI │ ├── plugin.frm │ ├── plugin.MYD │ ├── plugin.MYI │ ├── proc.frm │ ├── proc.MYD │ ├── proc.MYI │ ├── procs_priv.frm │ ├── procs_priv.MYD │ ├── procs_priv.MYI │ ├── proxies_priv.frm │ ├── proxies_priv.MYD │ ├── proxies_priv.MYI │ ├── servers.frm │ ├── servers.MYD │ ├── servers.MYI │ ├── slow_log.CSM │ ├── slow_log.CSV │ ├── slow_log.frm │ ├── tables_priv.frm │ ├── tables_priv.MYD │ ├── tables_priv.MYI │ ├── time_zone.frm │ ├── time_zone_leap_second.frm │ ├── time_zone_leap_second.MYD │ ├── time_zone_leap_second.MYI │ ├── time_zone.MYD │ ├── time_zone.MYI │ ├── time_zone_name.frm │ ├── time_zone_name.MYD │ ├── time_zone_name.MYI │ ├── time_zone_transition.frm │ ├── time_zone_transition.MYD │ ├── time_zone_transition.MYI │ ├── time_zone_transition_type.frm │ ├── time_zone_transition_type.MYD │ ├── time_zone_transition_type.MYI │ ├── user.frm │ ├── user.MYD │ └── user.MYI ├── performance_schema │ ├── cond_instances.frm │ ├── db.opt │ ├── events_waits_current.frm │ ├── events_waits_history.frm │ ├── events_waits_history_long.frm │ ├── events_waits_summary_by_instance.frm │ ├── events_waits_summary_by_thread_by_event_name.frm │ ├── events_waits_summary_global_by_event_name.frm │ ├── file_instances.frm │ ├── file_summary_by_event_name.frm │ ├── file_summary_by_instance.frm │ ├── mutex_instances.frm │ ├── performance_timers.frm │ ├── rwlock_instances.frm │ ├── setup_consumers.frm │ ├── setup_instruments.frm │ ├── setup_timers.frm │ └── threads.frm └── test 3 directories, 90 files
2、启动多实例数据库
# 启动MySQL [root@hadoop-slave1 /]# /data/3306/mysql start Starting MySQL... [root@hadoop-slave1 /]# /data/3307/mysql start Starting MySQL... # 查看启动的情况 [root@hadoop-slave1 /]# netstat -lntup|grep 330[6-7] tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 63013/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 63737/mysqld
3、启动MySQL
多实例的启动需要使用下面的启动方式：
[root@hadoop-slave1 3306]# mysql -S mysql.sock # 连接MySQL服务器 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 2 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
也就是sock的方式，它的重启：
[root@hadoop-slave1 /]# /data/3306/mysql stop # 停止MySQL，如果在配置文件中指定了密码，需要指定密码来停止服务 [root@hadoop-slave1 /]# /data/3306/mysql start # 启动MySQL
4、为root用户增加密码
[root@hadoop-slave1 bin]# pwd /application/mysql-5.5.32/bin [root@hadoop-slave1 bin]# mysqladmin -uroot -S /data/3306/mysql.sock password '123456' #添加密码
此时，将配置文件中的密码添加上：
# vim /data/3306/my.cnf ... ysql_pwd="123456" ...
同理3307也是如此。那么我们为什么修改密码后再配置文件中也需要加上呢？原因就是启动脚本文件中启动MySQL不需要密码，但是停止服务时候是需要的。
现在配置文件中含有数据库密码，所以这也是比较危险的，所以需要做以下授权，目前该mysql的启动文件：
[root@hadoop-slave1 3306]# find /data -type f -name "mysql" -exec ls -l {} ; -rwxr-xr-x. 1 mysql mysql 1314 Aug 9 22:41 /data/3306/mysql -rwxr-xr-x. 1 mysql mysql 1314 Aug 9 18:40 /data/3307/mysql
用户组和用户是mysql，修改为root用户：
[root@hadoop-slave1 3306]# find /data -type f -name "mysql" -exec chmod 700 {} ; [root@hadoop-slave1 3306]# find /data -type f -name "mysql" -exec chown root.root {} ;
[root@hadoop-slave1 3306]# find /data -type f -name "mysql" -exec ls -l {} ; -rwx------. 1 root root 1314 Aug 9 22:41 /data/3306/mysql -rwx------. 1 root root 1314 Aug 9 18:40 /data/3307/mysql
5、重启MySQL服务
[root@hadoop-slave1 3306]# pkill mysqld [root@hadoop-slave1 3306]# /data/3306/mysql start Starting MySQL... [root@hadoop-slave1 3306]# /data/3307/mysql start Starting MySQL...
查看启动情况：
[root@hadoop-slave1 3306]# ss -lntup | grep 330 tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",pid=66078,fd=12)) tcp LISTEN 0 128 *:3307 *:* users:(("mysqld",pid=66801,fd=11))
可以通过stop和start命令来进行停止和启动。
此时登录就需要使用密码来进行登录了：
[root@hadoop-slave1 3306]# mysql -uroot -p -S /data/3306/mysql.sock #密码登录方式 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server version: 5.5.32-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
三、添加实例
上面是安装3306和3307实例的，如果再添加3308实例如何做呢？
1、创建数据目录
[root@hadoop-slave1 /]# mkdir /data/3308/data -p
2、创建my.cnf和mysql启动脚本
将3306中的my.cnf和mysql脚本拷贝到3308，进行相应的修改即可：
[root@hadoop-slave1 /]# cp /data/3306/my.cnf /data/3308 [root@hadoop-slave1 /]# cp /data/3306/mysql /data/3308
对于my.cnf将其中的3306替换为3308，将sever-id进行修改为其它值；对于mysql脚本只需要将port修改为3308即可。
3、进行授权
[root@hadoop-slave1 /]# chown -R mysql.mysql /data/3308/
4、初始化
[root@hadoop-slave1 3308]# /application/mysql-5.5.32/scripts/mysql_install_db
--basedir=/application/mysql-5.5.32/
--datadir=/data/3308/data --user=mysql
5、启动3308
[root@hadoop-slave1 3308]# /data/3308/mysql start Starting MySQL... [root@hadoop-slave1 3308]# netstat -lntup | grep 330 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 66078/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 66801/mysqld tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 68513/mysqld
6、登录
# 设置密码 [root@hadoop-slave1 3308]# mysqladmin -uroot -S /data/3308/mysql.sock password '123456' # 登录 [root@hadoop-slave1 3308]# mysql -uroot -p123456 -S /data/3308/mysql.sock