MySQL多实例管理（mysqld_multi）

最新推荐文章于 2024-02-09 20:02:04 发布

jnrjian

最新推荐文章于 2024-02-09 20:02:04 发布

阅读量1.3k

点赞数

文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/jnrjian/article/details/128573768

版权

定义：就是在一台mysql机器上开启多个不同的服务端口（如：3306,3307），运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务

1.MySQL多实例介绍

1.1.什么是MySQL多实例

MySQL多实例就是在一台机器上开启多个不同的服务端口（如：3306,3307），运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务:；

1.2.MySQL多实例的特点有以下几点

1：有效利用服务器资源，当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务。

2：节约服务器资源

3：资源互相抢占问题，当某个服务实例服务并发很高时或者开启慢查询时，会消耗更多的内存、CPU、磁盘IO资源，导致服务器上的其他实例提供服务的质量下降；

1.3.部署mysql多实例的两种方式

第一种是使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便；

第二种是通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理；

1.4.同一开发环境下安装两个数据库，必须处理以下问题

配置文件安装路径不能相同
数据库目录不能相同
启动脚本不能同名
端口不能相同
socket文件的生成路径不能相同
2.Mysql多实例安装部署

2.1.部署环境

Red Hat Enterprise Linux Server release 6.4

2.2.安装mysql软件版本

2.2.1.免编译二进制包

mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

2.3.解压和迁移

tar -xvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

mv mysql-5.6.21-linux-glibc2.5-x86_64 /usr/local/mysql

2.4.关闭iptables

临时关闭：service iptables stop

永久关闭：chkconfig iptables off

2.5.关闭selinux

vi /etc/sysconfig/selinux

将SELINUX修改为DISABLED，即SELINUX=DISABLED

2.6.创建mysql用户

groupadd -g 27 mysql

useradd -u 27 -g mysql mysql

id mysql

uid=501(mysql) gid=501(mysql) groups=501(mysql)

2.7.创建相关目录

mkdir -p /data/mysql/ {mysql_3306,mysql_3307}

mkdir /data/mysql/mysql_3306/ {data,log,tmp}

mkdir /data/mysql/mysql_3307/ {data,log,tmp}

2.8.更改目录权限

chown -R mysql:mysql /data/mysql/

chown -R mysql:mysql /usr/local/mysql/

2.9. 添加环境变量

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile

source /etc/profile

2.10.复制my.cnf文件到etc目录

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

2.11.修改my.cnf（在一个文件中修改即可）

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld_multi]

mysqld = /usr/local/mysql /bin/mysqld_safe

mysqladmin = /usr/local/mysql /bin/mysqladmin

log = /data/mysql/mysqld_multi.log

[mysqld]

user=mysql

basedir = /usr/local/mysql

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3306/data

port=3306

server_id=3306

socket=/tmp/mysql_3306.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3306/log/slow.log

log-error = /data/mysql/mysql_3306/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3306/log/mysql3306_bin

[mysqld3307]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql/mysql_3307/data

port=3307

server_id=3307

socket=/tmp/mysql_3307.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql/mysql_3307/log/slow.log

log-error = /data/mysql/mysql_3307/log/error.log

binlog_format = mixed

log-bin = /data/mysql/mysql_3307/log/mysql3307_bin

2.12. 初始化数据库

2.12.1. 初始化3306数据库

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf

2.12.2. 初始化3307数据库

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf

2.12.3. 检查数据库是否初始化成功

出现两个”OK”

2.12.4. 查看数据库是否初始化成功（2）

查看3306数据库

[root@mysql ~]# cd /data/mysql/mysql_3306/data

[root@mysql data]# ls

auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mysql.pid performance_schema test

查看3307数据库

[root@mysql ~]# cd /data/mysql/mysql_3307/data

[root@mysql data]# ls

auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mysql.pid performance_schema test

2.13.设置启动文件

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

2.14.mysqld_multi进行多实例管理

启动全部实例：/usr/local/mysql/bin/mysqld_multi start

查看全部实例状态：/usr/local/mysql/bin/mysqld_multi report

启动单个实例：/usr/local/mysql/bin/mysqld_multi start 3306

停止单个实例：/usr/local/mysql/bin/mysqld_multi stop 3306

查看单个实例状态：/usr/local/mysql/bin/mysqld_multi report 3306

2.14.1.启动全部实例

[root@mysql ~]# /usr/local/mysql/bin/mysqld_multi start

[root@mysql ~]# /usr/local/mysql/bin/mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3307 is running

2.15.查看启动进程

2.16.修改密码

mysql的root用户初始密码是空，所以需要登录mysql进行修改密码，下面以3306为例：

mysql -S /tmp/mysql_3306.sock

set password for root@'localhost'=password('123456');

flush privileges;

下次登录：

[root@mysql ~]# mysql -S /tmp/mysql_3306.sock -p

Enter password:

2.17.新建用户及授权

一般新建数据库都需要新增一个用户，用于程序连接，这类用户只需要insert、update、delete、select权限。

新增一个用户，并授权如下：

grant select,delete,update,insert on *.* to admin@'192.168.0.%' identified by '123456';

flush privileges

2.18.外部软件登录数据库

2.19.测试成功

3.源码安装常见报错信息

1：安装mysql报错

checking for tgetent in -lncurses... no

checking for tgetent in -lcurses... no

checking for tgetent in -ltermcap... no

checking for tgetent in -ltinfo... no

checking for termcap functions library... configure: error: No curses/termcap library found

原因：

缺少ncurses安装包

解决方法：

yum list|grep ncurses

yum -y install ncurses-devel

yum install ncurses-devel

2：.../depcomp: line 571: exec: g++: not found

make[1]: *** [my_new.o] 错误 127

make[1]: Leaving directory `/home/justme/software/mysql-5.1.30/mysys'

make: *** [all-recursive] 错误 1

解决方法：

yum install gcc-c++

3：.../include/my_global.h:909: error: redeclaration of C++ built-in type `bool'

make[2]: *** [my_new.o] Error 1

make[2]: Leaving directory `/home/tools/mysql-5.0.22/mysys'

make[1]: *** [all-recursive] Error 1

make[1]: Leaving directory `/home/tools/mysql-5.0.22'

make: *** [all] Error 2

是因为gcc-c++是在configure之后安装的，此时只需重新configure后再编译make即可。

4：初始化数据库报错

报错现象：

root@mysql mysql-6.0.11-alpha]# scripts/mysql_install_db --basedir=/usr/local/mysql/ --user=mysql

Installing MySQL system tables...

ERROR: 1136 Column count doesn't match value count at row 1

150414 7:15:56 [ERROR] Aborting

150414 7:15:56 [Warning] Forcing shutdown of 1 plugins

150414 7:15:56 [Note] /usr/local/mysql//libexec/mysqld: Shutdown complete

Installation of system tables failed! Examine the logs in

/var/lib/mysql for more information.

You can try to start the mysqld daemon with:

shell> /usr/local/mysql//libexec/mysqld --skip-grant &

and use the command line tool /usr/local/mysql//bin/mysql

to connect to the mysql database and look at the grant tables:

shell> /usr/local/mysql//bin/mysql -u root mysql

mysql> show tables

Try 'mysqld --help' if you have problems with paths. Using --log

gives you a log in /var/lib/mysql that may be helpful.

The latest information about MySQL is available on the web at

http://www.mysql.com/. Please consult the MySQL manual section

'Problems running mysql_install_db', and the manual section that

describes problems on your OS. Another information source are the

MySQL email archives available at http://lists.mysql.com/.

Please check all of the above before mailing us! And remember, if

you do mail us, you MUST use the /usr/local/mysql//scripts/mysqlbug script!

原因：

原有安装的mysql信息没有删除干净

解决方法：

删除/var/lib/mysql目录

本文链接：https://blog.csdn.net/robinson_0612/article/details/40339167

MySQL数据库的集中化运维，可以通过在一台MySQL数据库服务器上，部署多个MySQL实例。该功能是通过mysqld_multi来实现。mysqld_multi用于管理多个mysqld的服务进程，这些mysqld服务进程程序可以用不同的socket或是监听于不同的端口，同时将数据文件分布到不同的磁盘以分散IO。mysqld_multi提供简单的命令用于启动，关闭和报告所管理的服务器的状态。从而减少生产环境的维护成本，方便后续的迁移和清理等工作，借助多实例绑定的方式提高服务器的整体资源利用率。对于多实例的配置有2种方式，一种是在my.cnf为所有实例提供配置，一种是使用每一个实例一个配置文件。本文主要描述第一种方式。

第二种多实例配置方式请参考：MySQL多实例配置(二)

有关MySQL单实例的安装请参考：Linux 下MySQL源码安装完整版

1、各数据库多实例的差异

MSSQL

MSSQL中的实例指的是一个SQL server服务器上仅有一个缺省实例。缺省实例名即为机器名ServerName(或IP)。

如果在同一台机器上再安装SQL server，我们可以对实例命名如ServerName/InstanceName。

即一台SQL server服务器上可以存在多个不同的实例。一个实例下可以存在多个不同的数据库。

对于不同实例下的数据库的访问，使用ServerName/InstanceName：PortNo即可实现访问，缺省实例为ServerName：PortNo。

对不同的实例配置IP地址，相关的访问协议，端口等等。

实例的可访问性需要启动该实例对应的相关服务。此处需要注意的是实例名和实例的服务名并不是相同的。

缺省的实例的服务名为MSSQLSERVER，而命名实例的服务名为MSSQL$INSTANCE_NAME。

Oracle

一个Oracle Server由一个Oracle实例和一个Oracle数据库组成。即：Oracle Server = Oracle Instance + Oracle Database

在Oracle的实例主要是由SGA，PGA以及一堆的后台进程来组成，此称之为实例。

一系列物理文件的集合包括控制文件、数据文件、联机日志文件、参数文件、密码文件等称之为数据库。

一个实例只能访问一个数据库，一个数据库可以被多个实例访问。

MySQL

MySQL实例的概念与MSSQL差不多，一个MySQL实例下可以存在或访问N个数据库。

不同的实例间可以用不同的端口号来区分，各个实例的数据可以使用不同的磁盘目录。

MySQL多实例通过mysqld_multi工具来进行管理。

2、现有的环境

mysql安装路径: /u01/app/mysql

mysql数据路径: /u01/app/mysqldata/data3306

mysql端口号: 3306

mysql 版本：5.6.12 Source distribution

OS 环境：SUSE Linux Enterprise Server 11 SP3 (x86_64)

3、初始化实例

#为新实例创建数据目录并赋权

suse11:~ # mkdir -p /u01/app/mysqldata/data3406

suse11:~ # mkdir -p /u01/app/mysqldata/data3506

suse11:~ # chown mysql:mysql -R /u01/app/mysqldata/data3406

suse11:~ # chown mysql:mysql -R /u01/app/mysqldata/data3506

#初始化实例

suse11:~ # cd /u01/app/mysql

suse11:/u01/app/mysql # ./scripts/mysql_install_db --user=mysql --ldata=/u01/app/mysqldata/data3406/

suse11:/u01/app/mysql # ./scripts/mysql_install_db --user=mysql --ldata=/u01/app/mysqldata/data3506/

# Author : Leshami

# Blog : http://blog.csdn.net/leshami

4、修改配置文件

suse11:~ # more /etc/my.cnf #本配置文件中仅提供了多实例的基本参数，生产环境根据情形自行添加

[mysqld_multi]

mysqld = /u01/app/mysql/bin/mysqld_safe

mysqladmin = /u01/app/mysql/bin/mysqladmin

user = admin #此帐户用于多实例关闭时使用，需要在每个实例上创建并授权

password = xxx #使用统一的密码便于管理

[mysqld3306]

socket = /tmp/mysql3306.sock

port = 3306

pid-file = /u01/app/mysqldata/data3306/mysql3306.pid

datadir = /u01/app/mysqldata/data3306

basedir = /u01/app/mysql

user = mysql

server-id=3306

[mysqld3406]

socket = /tmp/mysql3406.sock

port = 3406

pid-file = /u01/app/mysqldata/data3406/mysql3406.pid

datadir = /u01/app/mysqldata/data3406

basedir = /u01/app/mysql

user = mysql

server-id=3406

[mysqld3506]

socket = /tmp/mysql3506.sock

port = 3506

pid-file = /u01/app/mysqldata/data3506/mysql3506.pid

datadir = /u01/app/mysqldata/data3506

basedir = /u01/app/mysql

user = mysql

server-id=3506

5、启动关闭多实例

suse11:~ # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is not running

MySQL server from group: mysqld3406 is not running

MySQL server from group: mysqld3506 is not running

suse11:~ # mysqld_multi start 3306

suse11:~ # mysqld_multi start 3406,3506 #可以同时启动多个实例

suse11:~ # netstat -ntlp | grep mysql

tcp 0 0 :::3306 :::* LISTEN 14786/mysqld

tcp 0 0 :::3406 :::* LISTEN 15103/mysqld

tcp 0 0 :::3506 :::* LISTEN 15371/mysqld

suse11:/tmp # ls *.sock

mysql3306.sock mysql3406.sock mysql3506.sock

#为新实例修改密码及创建账户

suse11:/tmp # mysql -uroot -pxxx -S ./mysql3306.sock #3306已经有初始密码

root@localhost[(none)]> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;

suse11:/tmp # mysql -uroot -p -S ./mysql3406.sock

Enter password: #此时密码为空

root@localhost[(none)]> set password for 'root'@'localhost'=password('xxx');

root@localhost[(none)]> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;

suse11:/tmp # mysql -uroot -p -S ./mysql3506.sock

Enter password: #此时密码为空

root@localhost[(none)]> set password for 'root'@'localhost'=password('xxx');

root@localhost[(none)]> grant shutdown on *.* to 'admin'@'localhost' identified by 'xxx' with grant option;

#使用TCP方式登录测试

suse11:/tmp # mysql -uroot -pxxx -P3506

root@localhost[(none)]>

#检查多实例的状态

suse11:/tmp # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3406 is running

MySQL server from group: mysqld3506 is running

#停止多实例服务器

suse11:~ # mysqld_multi stop 3306

suse11:~ # mysqld_multi report 3306

Reporting MySQL servers

MySQL server from group: mysqld3306 is not running

suse11:~ # mysqld_multi stop 3406

suse11:~ # mysqld_multi stop 3506

#多实例服务器的日志

suse11:~ # tail /u01/app/mysql/share/mysqld_multi.log

Stopping MySQL servers

Warning: Using a password on the command line interface can be insecure.

141017 23:40:09 mysqld_safe mysqld from pid file /u01/app/mysqldata/data3406/mysql3406.pid ended

mysqld_multi log file version 2.16; run: Fri Oct 17 23:40:09 2014

Stopping MySQL servers

Warning: Using a password on the command line interface can be insecure.

141017 23:40:11 mysqld_safe mysqld from pid file /u01/app/mysqldata/data3506/mysql3506.pid ended

#一次启动多个实例

suse11:~ # mysqld_multi start 3306-3506

suse11:~ # mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3306 is running

MySQL server from group: mysqld3406 is running

MySQL server from group: mysqld3506 is running

6、配置多实例的自启动

# echo "/u01/app/mysql/bin/mysqld_multi start 3306-3506" >>/etc/init.d/boot.local

2、现有的环境

mysql安装路径: /u01/app/mysql

mysql数据路径: /u01/app/mysqldata/data3306

mysql端口号: 3306

mysql 版本：5.6.12 Source distribution

OS 环境：SUSE Linux Enterprise Server 11 SP3 (x86_64)

3、初始化实例

#为新实例创建数据目录并赋权

suse11:~ # mkdir -p /u01/app/mysqldata/data3606

suse11:~ # mkdir -p /u01/app/mysqldata/data3706

suse11:~ # chown mysql:mysql -R /u01/app/mysqldata/data3606

suse11:~ # chown mysql:mysql -R /u01/app/mysqldata/data3706

#初始化实例

suse11:~ # cd /u01/app/mysql

suse11:/u01/app/mysql # ./scripts/mysql_install_db --user=mysql --ldata=/u01/app/mysqldata/data3606/

suse11:/u01/app/mysql # ./scripts/mysql_install_db --user=mysql --ldata=/u01/app/mysqldata/data3706/

# Author : Leshami

# Blog : http://blog.csdn.net/leshami

4、配置各实例的my.cnf文件

# vi /u01/app/mysqldata/data3606/my3606.cnf

[mysqld]

socket = /tmp/mysql3606.sock

port = 3606

pid-file = /u01/app/mysqldata/data3606/mysql3606.pid

datadir = /u01/app/mysqldata/data3606

basedir = /u01/app/mysql

user = mysql

server-id=3606

[client]

port = 3606

socket = /tmp/mysql3606.sock

[mysql]

no-auto-rehash

socket = /tmp/mysql3606.sock

prompt=\\u@\\h[\\d]> \\

# vi /u01/app/mysqldata/data3706/my3706.cnf

[mysqld]

socket = /tmp/mysql3706.sock

port = 3706

pid-file = /u01/app/mysqldata/data3706/mysql3706.pid

datadir = /u01/app/mysqldata/data3706

basedir = /u01/app/mysql

user = mysql

server-id=3706

[client]

port = 3706

socket = /tmp/mysql3706.sock

[mysql]

no-auto-rehash

socket = /tmp/mysql3706.sock

prompt=\\u@\\h[\\d]> \\

suse11:~ # chown -R mysql:mysql /u01/app/mysqldata/data3606/my3606.cnf

suse11:~ # chown -R mysql:mysql /u01/app/mysqldata/data3706/my3706.cnf

5、启动关闭多实例

suse11:~ # mysqld_safe --defaults-file=/u01/app/mysqldata/data3606/my3606.cnf &

suse11:~ # mysqld_safe --defaults-file=/u01/app/mysqldata/data3706/my3706.cnf &

suse11:~ # netstat -nltp|grep mysql

tcp 0 0 :::3606 :::* LISTEN 64277/mysqld

tcp 0 0 :::3706 :::* LISTEN 64597/mysqld

suse11:~ # mysqladmin -uroot password 'xxx' -S /tmp/mysql3606.sock

suse11:~ # mysqladmin -uroot password 'xxx' -S /tmp/mysql3706.sock

#下面使用套接字方式连接到实例

suse11:~ # mysql -uroot -pxxx -S /tmp/mysql3606.sock

root@localhost[(none)]>

suse11:~ # mysql -uroot -pxxx -S /tmp/mysql3706.sock

root@localhost[(none)]>

#下面使用TCP方式连接到实例

suse11:~ # mysql -uroot -pxxx -P3606 --protocol=tcp

root@localhost[(none)]> exit

suse11:~ # mysql -uroot -pxxx -P3706 --protocol=tcp

root@localhost[(none)]>

#关闭mysql实例

suse11:~ # mysqladmin -uroot -pxxx -S /tmp/mysql3606.sock shutdown

suse11:~ # mysqladmin -uroot -pxxx -S /tmp/mysql3706.sock shutdown

MySQL 5.7 多实例安装部署实例

1. 背景
MySQL数据库的集中化运维，可以通过在一台服务器上，部署运行多个MySQL服务进程，通过不同的socket监听不同的服务端口来提供各自的服务。各个实例之间是相互独立的，每个实例的datadir, port, socket, pid都是不同的。

2. 多实例特点
* 有效利用服务器资源，当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务。
* 资源互相抢占问题，当某个服务实例服务并发很高时或者开启慢查询时，会消耗更多的内存、CPU、磁盘IO资源，导致服务器上的其他实例提供服务的质量下降。

3. 环境 [ 关闭SeLinux ]
[root@MySQL ~]# cat /etc/ RedHat -release
CentOS release 6.9 (Final)

[root@MySQL ~]# uname -r
2.6.32-696.3.2.el6.x86_64

[root@MySQL ~]# getenforce
Disabled

4. 部署 [ 4个实例 ]
* 下载 MySQL 5.7 二制包 [ 推荐官方下载 ]　此下载版本大于5.7.5
[root@MySQL ~]# wget wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

* 解压 MySQL 5.7 二进制包到指定目录
[root@MySQL ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

* 创建 MySQL 软链接
[root@MySQL ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

* 创建 MySQL 用户
[root@MySQL ~]# useradd -r -s /sbin/nologin mysql

* 在 MySQL 二进制包目录中创建 mysql-files 目录 [MySQL 数据导入/导出数据专放目录]
[root@MySQL ~]# mkdir -v /usr/local/mysql/mysql-files
mkdir: created directory `/usr/local/mysql/mysql-files'

* 创建多实例数据目录
[root@MySQL ~]# mkdir -vp /data/mysql_data{1..4}
mkdir: created directory `/data'
mkdir: created directory `/data/mysql_data1'
mkdir: created directory `/data/mysql_data2'
mkdir: created directory `/data/mysql_data3'
mkdir: created directory `/data/mysql_data4'

* 修改 MySQL 二进制包目录的所属用户与所属组
1 [root@MySQL ~]# chown root.mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

* 修改 MySQL 多实例数据目录与数据导入/导出专放目录的所属用户与所属组
[root@MySQL ~]# chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}

* 配置 MySQL 配置文件 /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /tmp/mysql_multi.log

[mysqld1]
# 设置数据目录　[多实例中一定要不同]
datadir = /data/mysql_data1
# 设置sock存放文件名　[多实例中一定要不同]
socket = /tmp/mysql.sock1
# 设置监听开放端口　[多实例中一定要不同]
port = 3306
# 设置运行用户
user = mysql
# 关闭监控
performance_schema = off
# 设置innodb 缓存大小
innodb_buffer_pool_size = 32M
# 设置监听IP地址
bind_address = 0.0.0.0
# 关闭DNS 反向解析
skip-name-resolve = 0

[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2
port = 3307
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[mysqld3]
datadir = /data/mysql_data3
socket = /tmp/mysql.sock3
port = 3308
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[mysqld4]
datadir = /data/mysql_data4
socket = /tmp/mysql.sock4
port = 3309
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

* 初始化各个实例 [ 初始化完成后会自带随机密码在输出日志中 ]
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3
[root@MySQL ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

* 各实例开启 SSL 连接
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data3
[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data4

* 复制多实例脚本到服务管理目录下 [ /etc/init.d/ ]
[root@MySQL ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

* 添加脚本执行权限
[root@MySQL ~]# chmod +x /etc/init.d/mysqld_multi

* 添加进service服务管理
[root@MySQL ~]# chkconfig --add mysqld_multi

5. 启动测试
* 查个多实例状态
[root@MySQL ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

* 启动多实例
[root@MySQL ~]# /etc/init.d/mysqld_multi start

* 查看多实例状态
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running

* 查看实例监听端口
[root@MySQL ~]# netstat -lntp | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2673/mysqld
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 2676/mysqld
tcp 0 0 0.0.0.0:3308 0.0.0.0:* LISTEN 2679/mysqld
tcp 0 0 0.0.0.0:3309 0.0.0.0:* LISTEN 2682/mysqld

6. 连接测试
　 * 实例1
[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock1 -p'z+Ilo*>s:3kw'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18

Copyright (c) 2000, 2017, 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> set password = '123456';
Query OK, 0 rows affected (0.00 sec)

　 * 实例2
[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock2 -p'b*AHUrTgu1rl'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18

Copyright (c) 2000, 2017, 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> set password = '123456';
Query OK, 0 rows affected (0.00 sec)

mysqld_multi安装多个mysql实例

一个机器上安装多个mysql实例，除了将每个实例在不同的目录下编译安装，为每个实例指定不同的端口，socket，配置文件，安装目录等。还可以使用mysqld_multi方式。

mysqld_multi是mysql设计的专门用来管理不同端口，不同socket的mysql实例的工具。它可以同时启动，停止多个mysql实例，也可以查看这些实例的状态。

mysqld_multi会去配置文件my.cnf(或者是—defaults-file参数指定的配置文件)中检查每一个以[mysqldN]为开头的一组配置(N这里是数字)。这个N是mysqld_multi用来区分每一个mysql实例用的。用法如下：

mysqld_multi start|stop|reload|report N1, N2, N3,… //reload等价于stop和start。

mysqld_multi需要的信息记录在配置文件my.cnf中的[mysqld_multi]组下。

注意，为了能用mysqld_multi统计管理所有的mysql实例，该管理账号必须存在于所有的mysql实例上，而且密码也一致。

以下是我做的测试以及安装步骤：

1、编写配置文件my.cnf，如下：

[root@CentOSMysql1 etc]# cat my.cnf

[mysqld_multi]

mysqld=mysqld_safe

#表示用mysqld_safe来启动mysql

mysqladmin=/usr/local/mysql/bin/mysqladmin

#指定mysqladmin工具的路径

log=/usr/local/mysql/mysqld_multi.log

#指定mysqld_multi的日志文件

user=root

pass=123456

#指定使用mysqld_multi工具的用户和密码

[mysqld3307]

#指定实例编号为3307的一些配置参数

datadir=/opt/data3307

port=3307

socket=/tmp/mysql.sock3307

[mysqld3308]

#指定实例编号为3308的一些配置参数

datadir=/opt/data3308

port=3308

socket=/tmp/mysql.sock3308

[mysqld56]

#指定实例编号为56的一些配置参数

basedir=/usr/local/mysql56/

datadir=/opt/data56

socket=/tmp/mysql.sock56

port=3310

2、准备安装之前工作

(1)查看是否有mysql用户和mysql组

[root@CentOSMysql1 etc]# cat group |grep mysql

[root@CentOSMysql1 etc]# groupadd mysql

[root@CentOSMysql1 mysql]# cat /etc/passwd|grep mysql

[root@CentOSMysql1 etc]# useradd -g mysql mysql

(2)下载mysql的安装文件。这里我下载的都是linux generic类型的

[root@CentOStest1 opt]# wget http://downloads.mysql.com/archives/get/file/mysql-5.6.19-linux-glibc2.5-x86_64.tar.gz

[root@CentOStest1 opt]# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

(3)解压这些安装包

[root@CentOSMysql1 opt]# tar -zxf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz

[root@CentOSMysql1 opt]# tar -zxf mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz

3、安装编号为3307的mysql实例，这里我计算用mysql5.7的版本。

[root@CentOSMysql1 opt]#mv mysql-5.7.16-linux-glibc2.5-x86_64 /usr/local

[root@CentOSMysql1 opt]#cd /usr/local/

[root@CentOSMysql1 local]#ln –s mysql-5.7.16-linux-glibc2.5-x86_64 mysql

[root@CentOSMysql1 local]# cd mysql

[root@CentOSMysql1 mysql]# chown -R mysql:mysql .

[root@CentOSMysql1 mysql]# bin/mysqld --initialize --user=mysql --datadir=/opt/data3307

//这条命令执行成功之后，会在结尾打印出临时密码，如下：

2017-12-21T02:08:32.598340Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-12-21T02:08:33.002195Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-12-21T02:08:33.090315Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-12-21T02:08:33.179548Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d898cae9-e5f3-11e7-9e66-000c299323ae.

2017-12-21T02:08:33.181477Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-12-21T02:08:33.184759Z 1 [Note] A temporary password is generated for root@localhost: L;0/g:XaweYi

[root@CentOSMysql1 mysql]# bin/mysql_ssl_rsa_setup --datadir=/opt/data3307

到这里，编号为3307的实例初步安装完毕，还剩下一个步骤就是修改临时密码。改密码之前需要把其所在mysql服务启动起来，这里我们用mysqld_multi启动，先查看下状态，如下：

[root@CentOSMysql1 bin]# mysqld_multi report

-bash: mysqld_multi: command not found

解决方法为将mysqld_multi工具所在的路径添加到环境变量中去：

[root@CentOSMysql1 bin]# vi /etc/profile

//在文件尾部添加以下语句

PATH=/usr/local/mysql/bin:$PATH

"/etc/profile" 79L, 1828C written

[root@CentOSMysql1 bin]# source /etc/profile

环境变量生效之后重试

[root@CentOSMysql1 mysql]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is not running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

[root@CentOSMysql1 mysql]# mysqld_multi start 3307

[root@CentOSMysql1 mysql]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

可以看到编号3307的mysql实例已经启动，然后修改root密码。因为my.cnf里面配置了root用户的密码是123456，所以现在我们将root的密码改为“123456”:

[root@CentOSMysql1 mysql]# mysql -uroot -p -S /tmp/mysql.sock3307

Enter password:

//这里输出刚才安装时出现的临时密码。

mysql> set password=password("123456");

到这里，编号为3307的实例安装完毕。

4、安装编号为3308的mysql实例，这里仍用mysql5.7的版本，但是端口号以及datadir要根据my.cnf中的来。

[root@CentOSMysql1 mysql]# bin/mysqld --initialize --user=mysql --datadir=/opt/data3308

2017-12-21T02:36:31.294011Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2017-12-21T02:36:31.779696Z 0 [Warning] InnoDB: New log files created, LSN=45790

2017-12-21T02:36:31.881428Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2017-12-21T02:36:31.948774Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c138a63c-e5f7-11e7-8f7a-000c299323ae.

2017-12-21T02:36:31.950062Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2017-12-21T02:36:31.953758Z 1 [Note] A temporary password is generated for root@localhost: whKyXCtxz9;g

//临时密码同样在尾处生成。

[root@CentOSMysql1 mysql]# bin/mysql_ssl_rsa_setup --datadir=/opt/data3308

拉起编号3308的mysql实例服务

[root@CentOSMysql1 mysql]# mysqld_multi start 3308

修改编号3308的mysql实例中root用户的密码为“123456”。

[root@CentOSMysql1 mysql]# mysql -uroot -p -S /tmp/mysql.sock3308

Enter password:

//这里输出刚才安装时出现的临时密码。

mysql> set password=password("123456");

到这里，编号为3308的实例安装完毕。

5、安装编号为56的mysql实例，这里使用mysql5.6的版本，由安装目录，端口号以及datadir根据my.cnf中的来。

[root@CentOSMysql1 opt]#mv mysql-5.6.34-linux-glibc2.5-x86_64 /usr/local

[root@CentOSMysql1 opt]#cd /usr/local/

[root@CentOSMysql1 local]#ln –s mysql-5.6.34-linux-glibc2.5-x86_64 mysql56

[root@CentOSMysql1 local]# cd mysql56

[root@CentOSMysql1 mysql56]# chown -R mysql:mysql .

[root@CentOSMysql1 mysql56]# scripts/mysql_install_db --user=mysql --datadir=/opt/data56

这一步和mysql5.7有些不一样，不会生成临时密码。Mysql5.6的临时密码默认是空。

[root@CentOSMysql1 local]# mysqld_multi start 56

拉起编号56的mysql实例服务

[root@CentOSMysql1 local]# mysql -uroot -p -S /tmp/mysql.sock56

修改编号56的mysql实例中root用户的密码为“123456”。

mysql> set password="123456";

ERROR 1372 (HY000): Password hash should be a 41-digit hexadecimal number

mysql> set password=password("123456");

到这里，编号为56的实例安装完毕。

6、最后，试试用mysqld_multi统一起停所有的mysql实例

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

[root@CentOSMysql1 ~]# mysqld_multi stop

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is not running

MySQL server from group: mysqld3308 is not running

MySQL server from group: mysqld56 is not running

[root@CentOSMysql1 ~]# mysqld_multi start

[root@CentOSMysql1 ~]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

MySQL server from group: mysqld56 is running

Mysql多实例安装部署

1.修改配置文件

vim /etc/my.cnf

#M-M-S
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
log=/root/mysql_log
#格式[mysql标签号]
[mysqld13]
#进程号
pid-file=/usr/local/mysql/data/a/mysql.pid
port=3306
#工作目录
datadir=/usr/local/mysql/data/a/
#socket号
socket=/usr/local/mysql/data/a/mysql.sock
user=mysql
server-id=13
#日志格式
log_bin=mysql-bin
binlog_format=row
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#自动清洗日志
expire_logs_days=4
#下面的需要 >5.6版本，若不是则下面配置略过，跳到下一个标签
#开启gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

[mysqld31]
pid-file=/usr/local/mysql/data/b/mysql.pid
port=3307
datadir=/usr/local/mysql/data/b/
socket=/usr/local/mysql/data/b/mysql.sock
user=mysql
server-id=31
log_bin=mysql-bin
binlog_format=row
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
expire_logs_days=4
#下面的需要 >5.6版本，若不是则下面配置略过
#开启gtid
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

2.创建工作目录

cd  /usr/local/mysql/data/
mkdir {a,b}
#修改属主属组mysql（防止出现权限问题）
chown -R mysql:mysql a
chown -R mysql:mysql b

3.初始化MySQL

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/a/

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/b/

若成功初始化在工作目录会出现test库和mysql库

检查数据库是否初始化成功：

test_ok.png

4.mysqld_multi进行多实例管理

启动全部实例：/usr/local/mysql/bin/mysqld_multi start
#需要等待1分钟让MySQL启动才能看到report  is running
查看全部实例状态：/usr/local/mysql/bin/mysqld_multi report 
启动单个实例：/usr/local/mysql/bin/mysqld_multi start 标签号
启动单个实例：/usr/local/mysql/bin/mysqld_multi start 31 
停止单个实例：/usr/local/mysql/bin/mysqld_multi stop 31 
查看单个实例状态：/usr/local/mysql/bin/mysqld_multi report 31

5.查看启动进程

ps -ef | grep mysql
或者查看端口号
netstat -tulnp | grep mysql

jnrjian

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL多实例管理（mysqld_multi）

就是在。
复制链接

扫一扫

MySQL多实例管理（mysqld_multi）

“相关推荐”对你有帮助么？