MySQL 多實例

[TOC]

多實例概述

什么是多實例:

在一臺物理主機上運行多個數(shù)據(jù)庫服務(wù)

優(yōu)點:

• 節(jié)約運維成本
• 提高硬件利用率

配置多實例

配置步驟

1. 安裝支持多實例服務(wù)的軟件包
2. 修改主配置文件
3. 初始化授權(quán)庫
4. 啟動服務(wù)
5. 客戶端訪問測試

安裝配置

#下載 二進制 mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz 包,解壓
tar -xf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz  -C /usr/local

#方便使用 創(chuàng)建軟鏈
ln -s /usr/local/mysql-5.7.25-linux-glibc2.12-x86_64 /usr/local/mysql

#創(chuàng)建 mysql 用戶
groupadd mysql -g 27
useradd -r -u 27 -g mysql -s /bin/false mysql

添加 my.cnf 配置

#啟用多實例
[mysqld_multi]
#指定進程文件路徑
mysqld=/usr/local/mysql/bin/mysqld_safe
#指定管理命令路徑
mysqladmin=/usr/local/mysql/bin/mysqladmin
#指定進程用戶
user=root

#實例進程名稱,1表示實例編號.
[mysqld1]
port=3307
datadir=/data/mysql3307
pid-file=/data/mysql3307/my3307.pid
socket=/data/mysql3307/my3307.socket
log-error=/data/mysql3307/my3307.err

[mysqld2]
port=3308
datadir=/data/mysql3308
pid=/data/mysql3308/my3308.pid
socket=/data/mysql3308/my3308.socket
log-error=/data/mysql3308/my3308.err

創(chuàng)建數(shù)據(jù)目錄

mkdir -p /data/msyql_{3307,3308}
chown mysql. -R /data

初始化授權(quán)庫

記錄初始化最后的初始密碼

mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_3307 --initialize
mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_3308 --initialize

導(dǎo)入環(huán)境變量

export PATH=/usr/local/mysql/bin/:$PATH
source /etc/profile

啟動 停止

mysqld_multi start 1
mysqld_multi start 2
mysqld_multi --user=root --password="password" stop 1
mysqld_multi --user=root --password="password" stop 2

連接測試

1. 使用初始密碼連接
2. 修改本機登錄密碼
3. 連接實例

mysql -uroot -p -S /path/socket
mysql> alter user root@'localhost' identified by '123123';

排錯

[root@localhost mysql]# mysqld_multi start 1
-bash: ./bin/mysqld_multi: /usr/bin/perl: bad interpreter: No such file or directory
#缺少perl依賴
yum -y install perl perl-devel

[root@localhost mysql]# ./bin/mysqld_multi start 1
WARNING: my_print_defaults command not found.
Please make sure you have this command available and
in your path. The command is available from the latest
MySQL distribution.
ABORT: Can't find command 'my_print_defaults'.
This command is available from the latest MySQL
distribution. Please make sure you have the command
in your PATH.
#未配置環(huán)境變量,需要添加 /usr/local/mysql/bin至環(huán)境變量
export PATH=/usr/local/mysql/bin/:$PATH
source /etc/profile