今天就跟大家聊聊有关如何进行MySQL数据库中的多实例配置，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

MySQL数据库基础篇之入门基础命令

所有的操作都是基于单实例的，mysql多实例在实际生产环境也是非常实用的，因为必须要掌握。

1、什么是多实例

多实例就是一台服务器上开启多个不同的服务端口（默认3306），运行多个mysql的服务进程，这此服务进程通过不同的socket监听不同的服务端口来提供各在的服务，所有实例之间共同使用一套MYSQL的安装程序，但各自使用不同的配置文件、启动程序、数据文件，在逻辑上是相对独立的。

多实例主要作用是：充分利用现有的服务器硬件资源，为不同的服务提供数据服务，但是如果某个实例并发比较高的，同样是会影响到其它实例的性能

2、安装多实例环境准备

安装前需要先安装mysql，但是只需将安装过程进行到make install即可（编译安装），如果使用免安装程序，只需解压软件包即可，今天的环境是通过免安装包来安装mysql主程序（其它的安装可以参考前面的安装过程自行测试）

系统环境

[root@centos6~]#cat/etc/redhat-releaseCentOSrelease6.5(Final)[root@centos6~]#uname-r2.6.32-431.el6.x86_64

安装程序

mysql-5.5.52-linux2.6-x86_64.tar.gz

首先将软件下载到本地

wgethttp://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.52-linux2.6-x86_64.tar.gz

创建安装用户

[root@centos6~]#groupaddmysql[root@centos6~]#useraddmysql-s/sbin/nologin-gmysql-M[root@centos6~]#tail-1/etc/passwdmysql:x:500:500::/home/mysql:/sbin/nologin

创建多实例的数据目录

[root@centos6tools]#mkdir-p/data/{3306,3307}[root@centos6tools]#tree/data//data/+--3306+--33072directories,0files

3、安装MYSQL多实例

接下来进行安装mysql的多实例操作

解压软件

[root@centos6tools]#llmysql-5.5.52-linux2.6-x86_64.tar.gz-rw-r--r--.1rootroot185855000Aug2621:38mysql-5.5.52-linux2.6-x86_64.tar.gz[root@centos6tools]#tarzxfmysql-5.5.52-linux2.6-x86_64.tar.gz

拷贝配置文件

[root@centos6mysql-5.5.52-linux2.6-x86_64]#cpsupport-files/my-small.cnf/data/3306/my.cnf[root@centos6mysql-5.5.52-linux2.6-x86_64]#cpsupport-files/mysql.server/data/3306/mysql[root@centos6mysql-5.5.52-linux2.6-x86_64]#cpsupport-files/my-small.cnf/data/3307/my.cnf[root@centos6mysql-5.5.52-linux2.6-x86_64]#cpsupport-files/mysql.server/data/3307/mysql

为一规范安装路径，将免安装包拷贝到应用程序目录下

[root@centos6tools]#mvmysql-5.5.52-linux2.6-x86_64/application/mysql[root@centos6tools]#ll/application/mysqltotal72drwxr-xr-x.2rootroot4096Dec917:15bin-rw-r--r--.171613141517987Aug2619:24COPYINGdrwxr-xr-x.3rootroot4096Dec917:15datadrwxr-xr-x.2rootroot4096Dec917:15docsdrwxr-xr-x.3rootroot4096Dec917:15include-rw-r--r--.1716131415301Aug2619:24INSTALL-BINARYdrwxr-xr-x.3rootroot4096Dec917:15libdrwxr-xr-x.4rootroot4096Dec917:15mandrwxr-xr-x.10rootroot4096Dec917:15mysql-test-rw-r--r--.17161314152496Aug2619:24READMEdrwxr-xr-x.2rootroot4096Dec917:15scriptsdrwxr-xr-x.27rootroot4096Dec917:15sharedrwxr-xr-x.4rootroot4096Dec917:15sql-benchdrwxr-xr-x.2rootroot4096Dec917:15support-files

修改配置文件与启动文件

因为是多实例，其中参数需要修改，修改后的配置文件如下：配置文件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/data#log_long_format#log-error=/data/3307/error.log#log-slow-queries=/data/3307/slow.logpid-file=/data/3307/mysql.pidserver-id=3[mysqld_safe]log-error=/data/3307/mysql3307.errpid-file=/data/3307/mysqld.pid

启动程序文件mysql

[root@backup3307]#catmysql#!/bin/shinitport=3307mysql_user="root"mysql_pwd="migongge"CmdPath="/application/mysql/bin"mysql_sock="/data/${port}/mysql.sock"#startupfunction_start_mysql(){if[!-e"$mysql_sock"];thenprintf"StartingMySQL...\n"/bin/sh${CmdPath}/mysqld_safe--defaults-file=/data/${port}/my.cnf2>&1>/dev/null&elseprintf"MySQLisrunning...\n"exitfi}#stopfunctionfunction_stop_mysql(){if[!-e"$mysql_sock"];thenprintf"MySQLisstopped...\n"exitelseprintf"StopingMySQL...\n"${CmdPath}/mysqladmin-u${mysql_user}-p${mysql_pwd}-S/data/${port}/mysql.sockshutdownfi}#restartfunctionfunction_restart_mysql(){printf"RestartingMySQL...\n"function_stop_mysqlsleep2function_start_mysql}case$1instart)function_start_mysql;;stop)function_stop_mysql;;restart)function_restart_mysql;;*)printf"Usage:/data/${port}/mysql{start|stop|restart}\n"esac

其它的配置可参考配置文件进行修改即可

多实例初始化操作

[root@centos63306]#/application/mysql/scripts/mysql_install_db--basedir=/application/mysql--datadir=/data/3306/data--user=mysqlInstallingMySQLsystemtables...16120918:02:17[Warning]'THREAD_CONCURRENCY'isdeprecatedandwillberemovedinafuturerelease.16120918:02:17[Note]/application/mysql/bin/mysqld(mysqld5.5.52-log)startingasprocess3336...OKFillinghelptables...16120918:02:17[Warning]'THREAD_CONCURRENCY'isdeprecatedandwillberemovedinafuturerelease.16120918:02:17[Note]/application/mysql/bin/mysqld(mysqld5.5.52-log)startingasprocess3343...OKTostartmysqldatboottimeyouhavetocopysupport-files/mysql.servertotherightplaceforyoursystemPLEASEREMEMBERTOSETAPASSWORDFORTHEMySQLrootUSER!Todoso,starttheserver,thenissuethefollowingcommands:/application/mysql/bin/mysqladmin-urootpassword'new-password'/application/mysql/bin/mysqladmin-uroot-hcentos6password'new-password'Alternativelyyoucanrun:/application/mysql/bin/mysql_secure_installationwhichwillalsogiveyoutheoptionofremovingthetestdatabasesandanonymoususercreatedbydefault.Thisisstronglyrecommendedforproductionservers.Seethemanualformoreinstructions.YoucanstarttheMySQLdaemonwith:cd/application/mysql;/application/mysql/bin/mysqld_safe&YoucantesttheMySQLdaemonwithmysql-test-run.plcd/application/mysql/mysql-test;perlmysql-test-run.plPleasereportanyproblemsathttp://bugs.mysql.com/

初始化成功后，会在数据目录下产生一个数据目录data和一些文件

[root@centos63306]#ll/data/3306/data/total1136drwx------.2mysqlroot4096Dec918:02mysql-rw-rw----.1mysqlmysql27693Dec918:02mysql-bin.000001-rw-rw----.1mysqlmysql1114546Dec918:02mysql-bin.000002-rw-rw----.1mysqlmysql38Dec918:02mysql-bin.indexdrwx------.2mysqlmysql4096Dec918:02performance_schemadrwx------.2mysqlroot4096Dec918:02test

另一个实例的初始化请参考上述操作进行，操作过程不再一一介绍

[root@centos63307]#ll/data/3307/data/total1136drwx------.2mysqlroot4096Dec918:40mysql-rw-rw----.1mysqlmysql27693Dec918:40mysql-bin.000001-rw-rw----.1mysqlmysql1114546Dec918:40mysql-bin.000002-rw-rw----.1mysqlmysql38Dec918:40mysql-bin.indexdrwx------.2mysqlmysql4096Dec918:40performance_schemadrwx------.2mysqlroot4096Dec918:40test

4 、启动多实例并登录

启动服务

[root@backup3307]#/data/3306/mysqlstartStartingMySQL...[root@backup3307]#lsof-i:3306COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAMEmysqld19986mysql10uIPv4909670t0TCP*:mysql(LISTEN)[root@backup3307]#/data/3307/mysqlstartStartingMySQL...[root@backup3307]#lsof-i:3307COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAMEmysqld21648mysql11uIPv4928990t0TCP*:opsession-prxy(LISTEN)

检查端口

[root@backup3307]#netstat-lntup|grepmysqltcp000.0.0.0:33070.0.0.0:*LISTEN21648/mysqldtcp000.0.0.0:33060.0.0.0:*LISTEN19986/mysqld

登陆多实例数据库

[root@backup~]#mysql-S/data/3306/mysql.sockWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.5.51-logSourcedistributionCopyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>createdatabasedata3306;QueryOK,1rowaffected(0.00sec)mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||data3306||mysql||performance_schema||test|+--------------------+5rowsinset(0.00sec)mysql>quitBye[root@backup~]#mysql-S/data/3307/mysql.sockWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1Serverversion:5.5.51SourcedistributionCopyright(c)2000,2016,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||test|+--------------------+4rowsinset(0.05sec)

成功登陆，并在3306实例中创建数据库，但是3307实例上查看并没有创建过的数据，说明两个实例是独立的

注：如果再需要新增一个实例，基本的配置步骤同上述一样，只需要相应修改配置文件与启动程序文件中的端口号与数据目录的路径即可，最后可以将多实例数据库启动命令加入开机自启动。

看完上述内容，你们对如何进行MySQL数据库中的多实例配置有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。