MySQL多實例簡單的說就是在一臺伺服器上安裝一套MySQL程式，通過不同的埠對外提供訪問，多實例不僅節省物理主機成本，還有效提升了單台物理主機的CPU、磁碟I/O使用效率，而且還可以在多實例之間做部署資料庫HA方案，根據不同的配置文件和啟動文件配置多實例，這種方法邏輯和配置簡單，但是不方便管理。 ...
一、什麼是MySQL多實例？
MySQL多實例簡單的說就是在一臺伺服器上安裝一套MySQL程式，通過不同的埠對外提供訪問，多實例不僅節省物理主機成本，還有效提升了單台物理主機的CPU、磁碟I/O使用效率，而且還可以在多實例之間做部署資料庫HA方案。
二、如何配置MySQL多實例？
配置mysql多實例有兩種方式
1、根據官方提供的是通過mysqld_multi使用單獨的配置文件來實現多實例，這種方式定製每個實例的配置不太方面，優點是管理起來很方便，集中管理。
2、使用多個配置文件和啟動文件，配置文件之間的區別：server-id、socket文件的位置、配置路徑和數據存放位置不同。初始化的時候只用不同的配置文件進行初始化資料庫，啟動時使用不同的啟動文件來啟動，這種方法邏輯和配置簡單，但是不方便管理。
下麵我們以第二種多實例的方法進行配置
三、多實例配置
MySQL安裝的是mysql5.5.52版本，安裝方法請看MySQL5.5.52編譯安裝
1、停止單實例mysql資料庫
[root@db01 ~]# /etc/init.d/mysqld stop Shutting down MySQL. SUCCESS!
2、禁止開機自啟動
[root@db01 ~]# chkconfig mysqld off [root@db01 ~]# chkconfig --list mysqld mysqld 0:關閉 1:關閉 2:關閉 3:關閉 4:關閉 5:關閉6:關閉
3、創建多實例根目錄/data/目錄
[root@db01 ~]# mkdir -p /data/{3306,3307}/data
需要特別說明一下，在多實例啟動文件中，啟動MySQL不同勢力服務所需要執行的命令實質是有區別的，例如，啟動3306實例命令如下
mysql_safe --defaults-file=/data/3306/mysql &>/dev/null
啟動3307實例的命令如下：
mysql_safe --defaults-file=/data/3307/mysql &>/dev/null
下麵看看多實例啟動文件中，停止MySQL不同實例服務的實質命令
停止3306實例的命令如下：
mysqladmin -uroot -p123456 -S /data/3306/mysql.sock shutdown
停止3307實例的命令如下：
mysqladmin -uroot -p123456 -S /data/3307/mysql.sock shutdown
4、創建MySQL多實例的配置文件和啟動文件
1）3306mysql實例配置文件
[root@db01 ~]# vim /data/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 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
2）3307mysql實例配置文件
[root@db01 ~]# cp /data/3306/my.cnf /data/3307/my.cnf [root@db01 ~]# sed -i 's#3306#3307#g' /data/3307/my.cnf [root@db01 ~]# sed -n /server-id/p /data/3307/my.cnf server-id = 1 [root@db01 ~]# sed -i 's#server-id = 1#server-id = 2#g' /data/3307/my.cnf [root@db01 ~]# cat /data/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 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 = 2 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
5、MySQL多實例啟動文件的創建和配置文件創建幾乎一樣，也可以通過vim命令來添加如下：
1）3306mysql實例啟動文件
[root@db01 ~]# vim /data/3306/mysql #!/bin/bash ################################################ # Filename:mysql # Description:Start MySQL multi instance script # Version:1.0 # Date:2016/12/10 # Author:xuanwiei # Email:[email protected] ################################################ #init port=3306 mysql_user="root" mysql_pwd="123456" #這裡將來是要修改為和資料庫密碼一致 CmdPath="/application/mysql/bin" mysql_sock="/data/${port}/mysql.sock" #startup function 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 & else printf "MySQL is running...\n" exit fi } #stop function function_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 function function_restart_mysql() { printf "Restarting MySQL...\n" 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}\n" esac
2）3307mysql實例啟動文件
[root@db01 ~]# cp /data/3306/mysql /data/3307/mysql [root@db01 ~]# sed -i 's#3306#3307#g' /data/3307/mysql [root@db01 ~]# cat /data/3307/mysql #!/bin/bash ################################################ # Filename: mysql # Description: Start MySQL multi instance script # Version: 1.0 # Date: 2016/12/10 # Author: xuanwiei # Email: [email protected] ################################################ #init port=3307 mysql_user="root" mysql_pwd="123456" CmdPath="/application/mysql/bin" mysql_sock="/data/${port}/mysql.sock" #startup function 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 & else printf "MySQL is running...\n" exit fi } #stop function function_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 function function_restart_mysql() { printf "Restarting MySQL...\n" 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}\n" esac
6、配置MySQL多實例的文件許可權
（1）通過下麵的命令授權mysql用戶和用戶組管理整個多實例的根目錄/data
[root@db01 ~]# chown -R mysql.mysql /data
（2）通過下麵的mysql多實例所有啟動文件的mysql可執行，設置700許可權最佳，註意不要用755許可權，因為文件里有資料庫管理員密碼，會被讀取到。
[root@db01 scripts]# find /data/ -type f -name "mysql" /data/3306/mysql /data/3307/mysql [root@db01 scripts]# find /data/ -type f -name "mysql"|xargs chmod 700 [root@db01 scripts]# find /data/ -type f -name "mysql"|xargs ls -l -rwx------ 1 root root 1359 12月 10 16:20 /data/3306/mysql -rwx------ 1 root root 1359 12月 10 16:22 /data/3307/mysql
7、初始化MySQL多實例的資料庫文件
（1）初始化MySQL資料庫
cd /application/mysql/scripts/ <==註意和MySQL5.1的路徑不同，MySQL5.1不在MySQL bin路徑下了 3306實例 /application/mysql/scripts/mysql_install_db \ --basedir=/application/mysql \ --datadir=/data/3306/data \ --user=mysql 3307實例 /application/mysql/scripts/mysql_install_db \ --basedir=/application/mysql \ --datadir=/data/3307/data \ --user=mysql
提示：--basedir=/application/mysql為MySQL的安裝路徑，--datadir為不同的實例數據目錄
操作過程：
[root@db01 ~]# cd /application/mysql/scripts/ [root@db01 scripts]# /application/mysql/scripts/mysql_install_db \ > --basedir=/application/mysql \ > --datadir=/data/3306/data \ > --user=mysql WARNING: The host 'db01' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables... 161117 14:14:14 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46676 ... OK Filling help tables... 161117 14:14:15 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46683 ... OK
如果有兩個ok，就表示初始化成功
其中WARNING: The host 'db01' could not be looked up with resolveip.
原因是因為db01沒有在hosts文件中解析
解決：echo "172.16.1.52 db01" >>/etc/hosts
[root@db01 scripts]# /application/mysql/scripts/mysql_install_db \ > --basedir=/application/mysql \ > --datadir=/data/3307/data \ > --user=mysql Installing MySQL system tables... 161117 14:18:20 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46733 ... OK Filling help tables... 161117 14:18:21 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52) starting as process 46740 ... OK
如果有兩個ok，就表示初始化成功
這次沒用出現WARNING: The host 'db01' could not be looked up with resolveip.
（2）初始化資料庫的原理及結果
[root@db01 scripts]# tree /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 …………………省略部分………………………………
（3）初始化故障
示例1：給出了警告信息“WARNING: The host 'db01' could not be looked up with resolveip.”
這個警告信息可以忽略，如果非要解決則需修改主機名解析
echo "172.16.1.52 db01" >>/etc/hosts
8、啟動MySQL多實例資料庫
第一個實例3306的啟動命令
/data/3306/mysql start
第二個實例3307的啟動命令
/data/3307/mysql start
現在檢查MySQL多實例資料庫是否成功啟動
netstat -lntup|grep 330
操作過程：
[root@db01 scripts]# /data/3306/mysql
Usage: /data/3306/mysql {start|stop|restart}
[root@db01 scripts]# /data/3306/mysql start
Starting MySQL...
[root@db01 scripts]# /data/3307/mysql start
Starting MySQL...
查看埠
[root@db01 scripts]# ss -nlutp|grep 330
tcp LISTEN 0 600 *:3306 *:* users:(("mysqld",48766,12))
tcp LISTEN 0 600 *:3307 *:* users:(("mysqld",49510,12))
9、配置及管理MySQL多實例資料庫
（1）配置MySQL多實例資料庫開機自啟動
服務的開機自啟動和關鍵，MySQL多實例的啟動也不例外，把MySQL多實例的啟動命令加入/etc/rc.local，實現開機自啟動：
cat >>/etc/rc.local<<EOF #mysql multi instances /data/3306/mysql start /data/3307/mysql start EOF tail -3 /etc/rc.local
提示：要確保MySQL腳本有執行許可權
（2）登陸mysql測試
登錄時要指定sock文件
測試命令如下：
mysql -S /data/3306/mysql.sock <==直接敲進來了，而且身份還是root，但是多了-S /data/3306/mysql.sock，用戶區別登錄不同的實例
操作演示
[root@db01 scripts]# mysql -S /data/3306/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.52-log Source distribution Copyright (c) 2000, 2016, 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> [root@db01 3306]# mysql -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.52-log Source distribution Copyright (c) 2000, 2016, 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>
到這裡MySQL多實例就配置完成啦O(∩_∩)O~~！！！
