MySQL数据库入门之多实例配置方法详解
本文实例讲述了MySQL数据库入门之多实例配置方法。分享给大家供大家参考，具体如下：
前面介绍了相关的基础命令操作：MySQL数据库基础篇之入门基础命令
所有的操作都是基于单实例的，mysql多实例在实际生产环境也是非常实用的，因为必须要掌握。
1、什么是多实例
多实例就是一台服务器上开启多个不同的服务端口（默认3306），运行多个mysql的服务进程，这此服务进程通过不同的socket监听不同的服务端口来提供各在的服务，所有实例之间共同使用一套MYSQL的安装程序，但各自使用不同的配置文件、启动程序、数据文件，在逻辑上是相对独立的。
多实例主要作用是：充分利用现有的服务器硬件资源，为不同的服务提供数据服务，但是如果某个实例并发比较高的，同样是会影响到其它实例的性能
2、安装多实例环境准备
安装前需要先安装mysql，但是只需将安装过程进行到makeinstall即可（编译安装），如果使用免安装程序，只需解压软件包即可，今天的环境是通过免安装包来安装mysql主程序（其它的安装可以参考前面的安装过程自行测试）
系统环境
[root@centos6~]#cat/etc/redhat-release CentOSrelease6.5(Final) [root@centos6~]#uname-r 2.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/passwd mysql:x:500:500::/home/mysql:/sbin/nologin
创建多实例的数据目录
[root@centos6tools]#mkdir-p/data/{3306,3307}
[root@centos6tools]#tree/data/
/data/
+--3306
+--3307
2directories,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/mysql total72 drwxr-xr-x.2rootroot4096Dec917:15bin -rw-r--r--.171613141517987Aug2619:24COPYING drwxr-xr-x.3rootroot4096Dec917:15data drwxr-xr-x.2rootroot4096Dec917:15docs drwxr-xr-x.3rootroot4096Dec917:15include -rw-r--r--.1716131415301Aug2619:24INSTALL-BINARY drwxr-xr-x.3rootroot4096Dec917:15lib drwxr-xr-x.4rootroot4096Dec917:15man drwxr-xr-x.10rootroot4096Dec917:15mysql-test -rw-r--r--.17161314152496Aug2619:24README drwxr-xr-x.2rootroot4096Dec917:15scripts drwxr-xr-x.27rootroot4096Dec917:15share drwxr-xr-x.4rootroot4096Dec917:15sql-bench drwxr-xr-x.2rootroot4096Dec917:15support-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@backup3307]#catmysql
#!/bin/sh
initport=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"StartingMySQL...\n"
/bin/sh${CmdPath}/mysqld_safe--defaults-file=/data/${port}/my.cnf2>&1>/dev/null&
else
printf"MySQLisrunning...\n"
exit
fi
}
#stopfunction
function_stop_mysql(){
if[!-e"$mysql_sock"];then
printf"MySQLisstopped...\n"
exit
else
printf"StopingMySQL...\n"
${CmdPath}/mysqladmin-u${mysql_user}-p${mysql_pwd}-S/data/${port}/mysql.sockshutdown
fi
}
#restartfunction
function_restart_mysql(){
printf"RestartingMySQL...\n"
function_stop_mysql
sleep2
function_start_mysql
}
case$1in
start)
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=mysql InstallingMySQLsystemtables... 16120918:02:17[Warning]'THREAD_CONCURRENCY'isdeprecatedandwillberemovedinafuturerelease. 16120918:02:17[Note]/application/mysql/bin/mysqld(mysqld5.5.52-log)startingasprocess3336... OK Fillinghelptables... 16120918:02:17[Warning]'THREAD_CONCURRENCY'isdeprecatedandwillberemovedinafuturerelease. 16120918:02:17[Note]/application/mysql/bin/mysqld(mysqld5.5.52-log)startingasprocess3343... OK Tostartmysqldatboottimeyouhavetocopy support-files/mysql.servertotherightplaceforyoursystem PLEASEREMEMBERTOSETAPASSWORDFORTHEMySQLrootUSER! Todoso,starttheserver,thenissuethefollowingcommands: /application/mysql/bin/mysqladmin-urootpassword'new-password' /application/mysql/bin/mysqladmin-uroot-hcentos6password'new-password' Alternativelyyoucanrun: /application/mysql/bin/mysql_secure_installation whichwillalsogiveyoutheoptionofremovingthetest databasesandanonymoususercreatedbydefault.Thisis stronglyrecommendedforproductionservers. Seethemanualformoreinstructions. YoucanstarttheMySQLdaemonwith: cd/application/mysql;/application/mysql/bin/mysqld_safe& YoucantesttheMySQLdaemonwithmysql-test-run.pl cd/application/mysql/mysql-test;perlmysql-test-run.pl Pleasereportanyproblemsathttp://bugs.mysql.com/
初始化成功后，会在数据目录下产生一个数据目录data和一些文件
[root@centos63306]#ll/data/3306/data/ total1136 drwx------.2mysqlroot4096Dec918:02mysql -rw-rw----.1mysqlmysql27693Dec918:02mysql-bin.000001 -rw-rw----.1mysqlmysql1114546Dec918:02mysql-bin.000002 -rw-rw----.1mysqlmysql38Dec918:02mysql-bin.index drwx------.2mysqlmysql4096Dec918:02performance_schema drwx------.2mysqlroot4096Dec918:02test
另一个实例的初始化请参考上述操作进行，操作过程不再一一介绍
[root@centos63307]#ll/data/3307/data/ total1136 drwx------.2mysqlroot4096Dec918:40mysql -rw-rw----.1mysqlmysql27693Dec918:40mysql-bin.000001 -rw-rw----.1mysqlmysql1114546Dec918:40mysql-bin.000002 -rw-rw----.1mysqlmysql38Dec918:40mysql-bin.index drwx------.2mysqlmysql4096Dec918:40performance_schema drwx------.2mysqlroot4096Dec918:40test
4、启动多实例并登录
启动服务
[root@backup3307]#/data/3306/mysqlstart StartingMySQL... [root@backup3307]#lsof-i:3306 COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME mysqld19986mysql10uIPv4909670t0TCP*:mysql(LISTEN) [root@backup3307]#/data/3307/mysql startStartingMySQL... [root@backup3307]#lsof-i:3307 COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODENAME mysqld21648mysql11uIPv4928990t0TCP*:opsession-prxy(LISTEN)
检查端口
[root@backup3307]#netstat-lntup|grepmysql tcp000.0.0.0:33070.0.0.0:*LISTEN21648/mysqld tcp000.0.0.0:33060.0.0.0:*LISTEN19986/mysqld
登陆多实例数据库
[root@backup~]#mysql-S/data/3306/mysql.sock WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis1 Serverversion:5.5.51-logSourcedistribution Copyright(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>quit Bye [root@backup~]#mysql-S/data/3307/mysql.sock WelcometotheMySQLmonitor. Commandsendwith;or\g. YourMySQLconnectionidis1 Serverversion:5.5.51Sourcedistribution Copyright(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相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。