MySQL數(shù)據(jù)庫(kù)入門(mén)多實(shí)例配置
前面介紹了相關(guān)的基礎(chǔ)命令操作：MySQL數(shù)據(jù)庫(kù)基礎(chǔ)篇之入門(mén)基礎(chǔ)命令
所有的操作都是基于單實(shí)例的，mysql多實(shí)例在實(shí)際生產(chǎn)環(huán)境也是非常實(shí)用的，因?yàn)楸仨氁莆铡?/p>
1、什么是多實(shí)例
多實(shí)例就是一臺(tái)服務(wù)器上開(kāi)啟多個(gè)不同的服務(wù)端口（默認(rèn)3306），運(yùn)行多個(gè)mysql的服務(wù)進(jìn)程，這此服務(wù)進(jìn)程通過(guò)不同的socket監(jiān)聽(tīng)不同的服務(wù)端口來(lái)提供各在的服務(wù)，所有實(shí)例之間共同使用一套MYSQL的安裝程序，但各自使用不同的配置文件、啟動(dòng)程序、數(shù)據(jù)文件，在邏輯上是相對(duì)獨(dú)立的。
多實(shí)例主要作用是：充分利用現(xiàn)有的服務(wù)器硬件資源，為不同的服務(wù)提供數(shù)據(jù)服務(wù)，但是如果某個(gè)實(shí)例并發(fā)比較高的，同樣是會(huì)影響到其它實(shí)例的性能
2、安裝多實(shí)例環(huán)境準(zhǔn)備
安裝前需要先安裝mysql，但是只需將安裝過(guò)程進(jìn)行到make install即可（編譯安裝），如果使用免安裝程序，只需解壓軟件包即可，今天的環(huán)境是通過(guò)免安裝包來(lái)安裝mysql主程序（其它的安裝可以參考前面的安裝過(guò)程自行測(cè)試）
系統(tǒng)環(huán)境
- [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
創(chuàng)建安裝用戶
- [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
創(chuàng)建多實(shí)例的數(shù)據(jù)目錄
- [root@centos6 tools]# mkdir -p /data/{3306,3307}
- [root@centos6 tools]# tree /data/
- /data/
- +-- 3306
- +-- 3307
- 2 directories, 0 files
3、安裝MYSQL多實(shí)例
接下來(lái)進(jìn)行安裝mysql的多實(shí)例操作
解壓軟件
- [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
為一規(guī)范安裝路徑，將免安裝包拷貝到應(yīng)用程序目錄下
- [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
修改配置文件與啟動(dòng)文件
因?yàn)槭嵌鄬?shí)例，其中參數(shù)需要修改，修改后的配置文件如下：配置文件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
啟動(dòng)程序文件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
其它的配置可參考配置文件進(jìn)行修改即可
多實(shí)例初始化操作
- [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/
初始化成功后，會(huì)在數(shù)據(jù)目錄下產(chǎn)生一個(gè)數(shù)據(jù)目錄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
另一個(gè)實(shí)例的初始化請(qǐng)參考上述操作進(jìn)行，操作過(guò)程不再一一介紹
- [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 test
4 、啟動(dòng)多實(shí)例并登錄
啟動(dòng)服務(wù)
- [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
登陸多實(shí)例數(shù)據(jù)庫(kù)
- [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實(shí)例中創(chuàng)建數(shù)據(jù)庫(kù)，但是3307實(shí)例上查看并沒(méi)有創(chuàng)建過(guò)的數(shù)據(jù)，說(shuō)明兩個(gè)實(shí)例是獨(dú)立的
注：如果再需要新增一個(gè)實(shí)例，基本的配置步驟同上述一樣，只需要相應(yīng)修改配置文件與啟動(dòng)程序文件中的端口號(hào)與數(shù)據(jù)目錄的路徑即可，最后可以將多實(shí)例數(shù)據(jù)庫(kù)啟動(dòng)命令加入開(kāi)機(jī)自啟動(dòng)。
更多關(guān)于Mysql相關(guān)技術(shù)文章，請(qǐng)持續(xù)關(guān)注民工哥技術(shù)之路。如需要關(guān)注更多其它技術(shù)方向的文章，也可以關(guān)注民工哥的個(gè)人微信公眾號(hào)：民工哥技術(shù)之路，關(guān)注 民工哥技術(shù)之路 微信公眾號(hào)對(duì)話框回復(fù)關(guān)鍵字：1024 可以獲取一份最新整理的技術(shù)干貨：包括系統(tǒng)運(yùn)維、數(shù)據(jù)庫(kù)、redis、MogoDB、電子書(shū)、Java基礎(chǔ)課程、Java實(shí)戰(zhàn)項(xiàng)目、架構(gòu)師綜合教程、架構(gòu)師實(shí)戰(zhàn)項(xiàng)目、大數(shù)據(jù)、Docker容器、ELK Stack、機(jī)器學(xué)習(xí)、BAT面試精講視頻等。
分享到微信 
分享到微博
