首页 资讯频道 互联频道 智能频道 网络 数据频道 安全频道 服务器频道 存储频道

非常实用!MySQL数据库入门多实例配置

2020-02-14 10:52:56 来源 : segmentfault

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

1、什么是多实例

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

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

2、安装多实例环境准备

安装前需要先安装mysql,但是只需将安装过程进行到make install即可(编译安装),如果使用免安装程序,只需解压软件包即可,今天的环境是通过免安装包来安装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相关技术文章,请持续关注民工哥技术之路。如需要关注更多其它技术方向的文章,也可以关注民工哥的个人微信公众号:民工哥技术之路,关注 民工哥技术之路 微信公众号对话框回复关键字:1024 可以获取一份最新整理的技术干货:包括系统运维、数据库、redis、MogoDB、电子书、Java基础课程、Java实战项目、架构师综合教程、架构师实战项目、大数据、Docker容器、ELK Stack、机器学习、BAT面试精讲视频等。

相关文章

最近更新