共計 8460 個字符，預計需要花費 22 分鐘才能閱讀完成。
今天就跟大家聊聊有關如何進行 MySQL 數據庫中的多實例配置，可能很多人都不太了解，為了讓大家更加了解，丸趣 TV 小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。
MySQL 數據庫基礎篇之入門基礎命令
所有的操作都是基于單實例的，mysql 多實例在實際生產環境也是非常實用的，因為必須要掌握。
1、什么是多實例
多實例就是一臺服務器上開啟多個不同的服務端口（默認 3306），運行多個 mysql 的服務進程，這此服務進程通過不同的 socket 監聽不同的服務端口來提供各在的服務，所有實例之間共同使用一套 MYSQL 的安裝程序，但各自使用不同的配置文件、啟動程序、數據文件，在邏輯上是相對獨立的。
多實例主要作用是：充分利用現有的服務器硬件資源，為不同的服務提供數據服務，但是如果某個實例并發比較高的，同樣是會影響到其它實例的性能
2、安裝多實例環境準備
安裝前需要先安裝 mysql，但是只需將安裝過程進行到 make install 即可（編譯安裝），如果使用免安裝程序，只需解壓軟件包即可，今天的環境是通過免安裝包來安裝 mysql 主程序（其它的安裝可以參考前面的安裝過程自行測試）
系統環境
[root@centos6 ~]# cat /etc/redhat-release CentOS release 6.5 (Final) [root@centos6 ~]# uname -r 2.6.32-431.el6.x86_64安裝程序
mysql-5.5.52-linux2.6-x86_64.tar.gz
首先將軟件下載到本地
wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.52-linux2.6-x86_64.tar.gz創建安裝用戶
[root@centos6 ~]#groupadd mysql [root@centos6 ~]#useradd mysql -s /sbin/nologin -g mysql -M [root@centos6 ~]#tail -1 /etc/passwd mysql:x:500:500::/home/mysql:/sbin/nologin創建多實例的數據目錄
[root@centos6 tools]# mkdir -p /data/{3306,3307} [root@centos6 tools]# tree /data/ /data/ +-- 3306 +-- 3307 2 directories, 0 files3、安裝 MYSQL 多實例
接下來進行安裝 mysql 的多實例操作
解壓軟件
[root@centos6 tools]# ll mysql-5.5.52-linux2.6-x86_64.tar.gz -rw-r--r--. 1 root root 185855000 Aug 26 21:38 mysql-5.5.52-linux2.6-x86_64.tar.gz [root@centos6 tools]# tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz拷貝配置文件
[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3306/my.cnf [root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3306/mysql [root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3307/my.cnf [root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3307/mysql為一規范安裝路徑，將免安裝包拷貝到應用程序目錄下
[root@centos6 tools]# mv mysql-5.5.52-linux2.6-x86_64 /application/mysql [root@centos6 tools]# ll /application/mysql total 72 drwxr-xr-x. 2 root root 4096 Dec 9 17:15 bin -rw-r--r--. 1 7161 31415 17987 Aug 26 19:24 COPYING drwxr-xr-x. 3 root root 4096 Dec 9 17:15 data drwxr-xr-x. 2 root root 4096 Dec 9 17:15 docs drwxr-xr-x. 3 root root 4096 Dec 9 17:15 include -rw-r--r--. 1 7161 31415 301 Aug 26 19:24 INSTALL-BINARY drwxr-xr-x. 3 root root 4096 Dec 9 17:15 lib drwxr-xr-x. 4 root root 4096 Dec 9 17:15 man drwxr-xr-x. 10 root root 4096 Dec 9 17:15 mysql-test -rw-r--r--. 1 7161 31415 2496 Aug 26 19:24 README drwxr-xr-x. 2 root root 4096 Dec 9 17:15 scripts drwxr-xr-x. 27 root root 4096 Dec 9 17:15 share drwxr-xr-x. 4 root root 4096 Dec 9 17:15 sql-bench drwxr-xr-x. 2 root root 4096 Dec 9 17:15 support-files修改配置文件與啟動文件
因為是多實例，其中參數需要修改，修改后的配置文件如下：配置文件 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 #log_long_format #log-error = /data/3307/error.log #log-slow-queries = /data/3307/slow.log pid-file = /data/3307/mysql.pid server-id = 3 [mysqld_safe] log-error=/data/3307/mysql3307.err pid-file=/data/3307/mysqld.pid啟動程序文件 mysql
[root@backup 3307]# cat mysql #!/bin/sh init port=3307 mysql_user= root mysql_pwd= migongge CmdPath= /application/mysql/bin mysql_sock= /data/${port}/mysql.sock #startup 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其它的配置可參考配置文件進行修改即可
多實例初始化操作
[root@centos6 3306]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql Installing MySQL system tables... 161209 18:02:17 [Warning] THREAD_CONCURRENCY is deprecated and will be removed in a future release. 161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3336 ... OK Filling help tables... 161209 18:02:17 [Warning] THREAD_CONCURRENCY is deprecated and will be removed in a future release. 161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3343 ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE 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 centos6 password new-password Alternatively you can run: /application/mysql/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly 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.pl cd /application/mysql/mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/初始化成功后，會在數據目錄下產生一個數據目錄 data 和一些文件
[root@centos6 3306]# ll /data/3306/data/ total 1136 drwx------. 2 mysql root 4096 Dec 9 18:02 mysql -rw-rw----. 1 mysql mysql 27693 Dec 9 18:02 mysql-bin.000001 -rw-rw----. 1 mysql mysql 1114546 Dec 9 18:02 mysql-bin.000002 -rw-rw----. 1 mysql mysql 38 Dec 9 18:02 mysql-bin.index drwx------. 2 mysql mysql 4096 Dec 9 18:02 performance_schema drwx------. 2 mysql root 4096 Dec 9 18:02 test另一個實例的初始化請參考上述操作進行，操作過程不再一一介紹
[root@centos6 3307]# ll /data/3307/data/ total 1136 drwx------. 2 mysql root 4096 Dec 9 18:40 mysql -rw-rw----. 1 mysql mysql 27693 Dec 9 18:40 mysql-bin.000001 -rw-rw----. 1 mysql mysql 1114546 Dec 9 18:40 mysql-bin.000002 -rw-rw----. 1 mysql mysql 38 Dec 9 18:40 mysql-bin.index drwx------. 2 mysql mysql 4096 Dec 9 18:40 performance_schema drwx------. 2 mysql root 4096 Dec 9 18:40 test4、啟動多實例并登錄
啟動服務
[root@backup 3307]# /data/3306/mysql start Starting MySQL... [root@backup 3307]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN) [root@backup 3307]# /data/3307/mysql start Starting MySQL... [root@backup 3307]# lsof -i :3307 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 21648 mysql 11u IPv4 92899 0t0 TCP *:opsession-prxy (LISTEN)檢查端口
[root@backup 3307]# netstat -lntup|grep mysql tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 21648/mysqld tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19986/mysqld登陸多實例數據庫
[root@backup ~]# 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.51-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 create database data3306; Query OK, 1 row affected (0.00 sec) mysql show databases; +--------------------+ | Database | +--------------------+ | information_schema | | data3306 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql quit Bye [root@backup ~]# mysql -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.51 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 show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.05 sec)成功登陸，并在 3306 實例中創建數據庫，但是 3307 實例上查看并沒有創建過的數據，說明兩個實例是獨立的
注：如果再需要新增一個實例，基本的配置步驟同上述一樣，只需要相應修改配置文件與啟動程序文件中的端口號與數據目錄的路徑即可，最后可以將多實例數據庫啟動命令加入開機自啟動。
看完上述內容，你們對如何進行 MySQL 數據庫中的多實例配置有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注丸趣 TV 行業資訊頻道，感謝大家的支持。
正文完
