[TOC]

0. MySQL多实例介绍

Q:什么是MySQL多实例?
答:简单的说,就是在一台机器上开启多个不同的服务端口(如: 3306、 3307 ) ,运行多个 MysQL 服务进程,服务进程通过不同的 socket 监听不同的服务端口来提供各自的服务。

这些 MySQL 多实例共用一套 MySQL 安装程序,使用不同(也可以相同)的 my . cnf 配里文件、启动程序,数据文件。在提供服务时,多实例 MySQL 在逻辑上看来是各自独立的,多个实例的自身是根据配里文件对应的设定值,来取得服务器的相关硬件资源多少。
比如: MysQL 多实例相当于房子的多个卧室一样,每个实例可以看作一个卧室,整个服务器就是一套房子,服务器的硬件资源( cPu , meln . disk )可以看作房子的卫生间、厨房、厅一样,感房子的公用资源,北漂蜗居在一起,肯定要共用上述资源。

WeiyiGeek.多示例图示

多实例的优势和问题介绍:

  • 优势:
    • 有效利用服务器资源,充分利用资源提供更多的服务;(一般服务器利用率到达60~70%)
    • 节约服务器成本,数据库需要独立的提供服务,需要主从同步的技术的,多实例就比较好了;
  • 问题:
    • 资源互相抢占问题,当服务器某个实例进行并发高或者有慢查询时候,会消耗更多的内存,CPU,磁盘IO资源,导致服务器的其他的实例提供服务的质量下降。

多实例的门户企业应用场景:

  • 资金紧张型公司的选择,需要交叉主从同步进行等技术提供备份或读写分离服务时,基本台服务器部署3-4个实例,这样的服务是相对独立的;
  • 并发访问部署特别大的业务,MySQL多实例一个很值得使用的技术,即使并发很大,合理分配好系统资源,也不会有太大问题;
  • 门户网站应用多实例场景,门户网站使用多实例的目的是配硬件好的服务器,节省IDC机柜空间,同时跑多实例让硬件资源不浪费;

1. MySQL多实例常见配置方案

常见的两种方法:

  • 1.多配置文件部署方案,通过配置多个配置文件即多个启动程序;(优势逻辑简单,配置简单,缺点是管理起来不太方便;)
  • 2.单一配置文件部署方案,在my.cnf配置文件样例,mysql_mutil 进行配置并且启动; (定制每个实例的配置不太方面,优点是管理起来很方便,集中管理;)
    mysql_mutil --config-file=/data/mysql/my_multi.cnf start 1,2,3,4,5 #启动的实例号s

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

  • 1.配置文件安装路径不能相同
  • 2.数据库目录不能相同
  • 3.启动脚本不能同名
  • 4.端口不能相同
  • 5.socket文件的生成路径不能相同
  • 6.主从设置不同server-id

2. MySQL多实例配置实战

(1) 5.5.23的源码编译安装多实例方法(多配置文件)
//安装依赖
yum install libaio-devel -y

//创建数据存储目录
[[email protected] ~]$ mkdir -p /data/{3306,3307}/data //递归创建多个文件
[[email protected] data]# ls /application/mysql/support-files/my-* //数据库默认提供多个配置模板,根据硬件修改
/application/mysql/support-files/my-huge.cnf
/application/mysql/support-files/my-innodb-heavy-4G.cnf
/application/mysql/support-files/my-large.cnf
/application/mysql/support-files/my-medium.cnf
/application/mysql/support-files/my-small.cnf //复制到 3306 3307 中

//我们为每一个实例建立一个配置文件,一个启动文件,对应数据文件;
cp /application/mysql/support-files/my-small.cnf /data/{3306,3307}/data/my.cnf
tree /data

//在vim编辑器下:%s/3306/3307/g,并修改my.cnf下server-id与其他实例不同id(server-id = 1或2总之不一样就行)
[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /application/mysql #需要根据实际情况更改: 比如 yum 默认安装的mysql 8.0 /var/bin/mysql
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#default_table_type = InnoDB
thread_stack = 192K
#transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
#log_long_format
#log-error = /data/3306/error.log
#log-slow-queries = /data/3306/slow.log
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
#myisam_sort_buffer_size = 1M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql

#下面这里是不一样的
server-id = 1
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0

[mysqldump]
quick
max_allowed_packet = 2M

[mysqld_safe]
log-error = /data/3306/mysql_3306.err
pid-file = /data/3306/mysqld.pid

在3306和3307中建立一个mmysqld.sh脚本文件:

#init
port=3306 #必须
mysql_user="root" #需求更改
mysql_pwd="[email protected]" #需求更改
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}

#stop function
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}

#restart function
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}

case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mmysqld.sh {start|stop|restart}\n"
esac

授权及增加执行权限并且初始化数据库(两个实例分别初始化):

#授权
chown -R mysql.mysql /data
find /data/ -type f -name "mysql"|xargs chmod +x

#初始化脚本
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql

//单实例初始化
./mysql_install_db --user=mysql

#多实例数据库停止与启动:(在mmysqld.sh文件中)
[启动]
/application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &
/application/mysql/bin/mysqld_safe --defaults-file = /data/3307/my.cnf 2>&1 > /dev/null &

[停止]
mysqladmin -usystem -psystem -S /data/3306/mysql.sock shutdwon
mysqladmin -usystem -psystem -S /data/3307/mysql.sock shutdwon

#启动脚本
/data/3306/mmysqld.sh restart

#可以利用一个shell添加一个实例3308端口Mysql:
#!/usr/bin
mkdir /data/3308/data -p
cp /data/3307/my.cnf /data/3308/
cp /data/3307/mmysqld.sh /data/3308/
sed -i 's#3307#3308#g' /data/3308/my.cnf
sed -i 's#3307#3308#g' /data/3308/mmysqld.sh
chmod +x /data/3308/mysql
chown -R mysql.mysql /data/3308/
/application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3308/data/ --user=mysql
/data/3308/mysql start

WeiyiGeek.5.5.x源码安装多实例


(2) 5.7.24的二进制安装多实例方法(多配置文件)

补充 MySQL 5.7.X 多实例化:

#1. 创建一个mysql用户和组并设置/sbin/nologin
#2. 实例化文件目录以及权限
mkdir -vp /data/{3306,3307}/{data,binlog}
chown -R mysql:mysql /usr/local/mysql/ /data

#3. 创建多实例配置文件
# /data/3306/my.cnf
#----------------------------------
[client]
port = 3306
socket = /data/3306/mysql.sock
default-character-set=utf8 # 设置mysql客户端默认字符集

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql #需要根据实际情况路径更改
datadir = /data/3306/data
log-error= /data/3306/mysql-error.log
log-bin = /data/3306/binlog
server-id = 3306
character-set-server=utf8 #服务端默认字符集
explicit_defaults_for_timestamp #时区


# /data/3307/my.cnf
#------------------------------------
[client]
port = 3307
socket = /data/3307/mysql.sock
default-character-set=utf8 # 设置mysql客户端默认字符集

[mysqld]
user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /usr/local/mysql #需要根据实际情况路径更改
datadir = /data/3307/data
log-error= /data/3307/mysql-error.log
log-bin = /data/3307/binlog
server-id = 3307
character-set-server=utf8 #服务端默认字符集
explicit_defaults_for_timestamp #时区



#4. 初始化数据库以及启动
mysqld --defaults-file=/data/3306/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --explicit_defaults_for_timestamp
mysqld --defaults-file=/data/3307/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --explicit_defaults_for_timestamp

mysqld_safe --defaults-file=/data/3306/my.cnf --user=mysql &
mysqld_safe --defaults-file=/data/3307/my.cnf --user=mysql &

#5. 查看登录密码 + 登录数据库
cat mysql-error.log | grep "passowrd" #在设置的log文件下
[email protected]: <S!Ka6+8f6MJ
[email protected]: i?+WDyVav8L?

#登陆数据库
mysql -uroot -p'yxi9CG<9D5go' -S /data/3306/mysql.sock
mysql -uroot -p'i?+WDyVav8L?' -S /data/3307/mysql.sock
ALTER USER 'root'@'localhost' IDENTIFIED BY '[email protected]';

WeiyiGeek.5.7.24多实例


(3) 8.x的二进制安装多实例方法(多配置文件)

实验环境版本: MySQL 8.0.13
系统环境:cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core) 3.10.0-957.1.3.el7.x86_64

执行步骤:

## 1. 创建一个mysql用户和组并设置/sbin/nologin
groupadd -g 500 mysql
useradd -r -g mysql -s /sbin/nologin mysql

## 2. 创建多实例化目录
mkdir -vp /data/mysqldata/{{3306,3307}/{data,tmp,binlog,innodb_ts,innodb_log,logs},backup,scripts}
touch /data/mysqldata/3306/tmp/mysql-error.log /data/mysqldata/3307/tmp/mysql-error.log

## 3. 授权建立得目录(非常重要 常常会使mysql报错)
chown -R mysql:mysql /data/

## 4. 在实例化文件中写一个mysql.cnf配置
# my.cnf 我这里以3307为例
[client]
default-character-set=utf8 # 设置mysql客户端默认字符集
port = 3307
socket = /data/mysqldata/3307/mysql.sock

# The MySQL server
[mysqld]
port = 3307
mysqlx_port = 33070
user = mysql
server-id = 3307
socket = /data/mysqldata/3307/mysql.sock
mysqlx_socket=/data/mysqldata/3307/mysqlx.sock
pid-file = /data/mysqldata/3307/mysql.pid
basedir = /usr/local/mysql8
datadir = /data/mysqldata/3307/data
tmpdir = /data/mysqldata/3307/tmp #非必须
log-bin = /data/mysqldata/3307/binlog/mysql-bin
log-error = /data/mysqldata/3307/mysql-error.log
explicit_defaults_for_timestamp
character-set-server=utf8 #服务端默认字符集

#------------------------- 配置文档分割线 ------------------------------------#

[[email protected] ]$ cp /3307/my.cnf ../3306/my.cnf
[[email protected] ]$ sed -i 's/3307/3306/g' ../3306/my.cnf

#初始化实例与启动数据库:
/usr/local/mysql8/bin/mysqld --defaults-file=/data/mysqldata/3307/3307.cnf --initialize --user=mysql --basedir=/usr/local/mysql8
/usr/local/mysql8/bin/mysqld --defaults-file=/data/mysqldata/3306/3306.cnf --initialize --user=mysql --basedir=/usr/local/mysql8

#实例化后在我们配置的mysql-error.log查看密码;
[[email protected] 3307]# grep "password" /data/mysqldata/3306/mysql-error.log
2019-01-18T02:58:28.529902Z 5 [Note] [MY-010454] [Server] A temporary password is generated for [email protected]: l>(J9/P8dt*f

#启动数据库
/usr/local/mysql8/bin/mysqld_safe --defaults-file=/data/mysqldata/3307/3307.cnf --user=mysql &
/usr/local/mysql8/bin/mysqld_safe --defaults-file=/data/mysqldata/3306/3306.cnf --user=mysql &

#登录数据库以及密码更改:
mysql -u root -p';!u>iE9hf(ah' -S /data/mysqldata/3307/mysql.sock
mysql -u root -p'fllyBv!ud9gu' -S /data/mysqldata/3306/mysql.sock

mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '[email protected]'; #密码强度是有要求的,这也是MYSQL8的特性
Query OK, 0 rows affected (0.14 sec)
mysql>ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '[email protected]!'; #认证方式

WeiyiGeek.8.0多实例化

WeiyiGeek.8.0多实例化登陆成功


(4). MySQL 5.7.X多实例化(单配置文件未实现)

系统环境:CentOS release 6.10 (Final) 2.6.32-754.10.1.el6.i686

###1. 创建相关目录与实例化目录权限:###
[[email protected] ~]# mkdir -vp /usr/local/mysql
mkdir: created directory `/usr/local/mysql`

* 创建 MySQL 软链接 (不起作用)
ln -s /usr/local/mysql-5.7.24-linux-glibc2.12-i686/ /usr/local/mysql

* 创建 用户建立 软链接
groupadd -g 500 mysql
useradd -r -g mysql -u 500 -s /sbin/nologin mysql

* 在 MySQL 二进制包目录中创建 mysql-files 目录 [MySQL 数据导入/导出数据专放目录]
mkdir -v /usr/local/mysql/mysql-files
mkdir -vp /data/mysql_data{1..4}/{data,logs}
chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}


###2.编辑my.cnf文档:####
mysqld_multi --example > my.cnf

#单一配置文件部署方案
* 配置 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/3306mysql.sock
# 设置监听开放端口 [多实例中一定要不同]
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
server-id = 1
basedir = /usr/local/mysql

[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/3307mysql.sock
port = 3307
user = mysql
server-id = 2
basedir = /usr/local/mysql


####3. 初始化数据库:#####
#mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql_3306/data
#或者采用 --defaults-file=/etc/my.cnf --initialize-insecure #建立的密码为空 --user=mysql 指定用户
mysqld --defaults-file=/data/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1 --explicit_defaults_for_timestamp
mysqld --initialize --basedir=/usr/local/mysql8/ --datadir=/app/mysql/mysqldata/3306/data/ --explicit_defaults_for_timestamp

#各实例开启 SSL 连接
/usr/local/mysql/bin/mysql_ssl_rsa_setup --user=mysql --basedir=/usr/local/mysql --datadir=/app/mysql/mysqldata/3306/data/

#查看数据库是否初始化成功
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

#登录以及修改密码:
mysql的root用户初始密码是空,所以需要登录mysql进行修改密码,下面以3306为例:
mysql -S /tmp/mysql_3306.sock
set password for [email protected]'localhost'=password('123456'); #<=5.7.x
flush privileges;


(5). MySQL 8.x多实例化(单配置文件未实现)

环境与上面是一致得:

# 1. 与 MySQL 8.X 多实例化 (多配置文件)建立
mkdir -vp /app/mysql/mysqldata/{{3307,3308}/{data,tmp,binlog,innodb_ts,innodb_log,undo},backup,scripts}
touch /app/mysql/mysqldata/3307/mysql-error.log /app/mysql/mysqldata/3308/mysql-error.log
chown -R mysql:mysql /app/

#2. 使用mysqld_multi,需要3个配置文件,其中两个是用于初始化,另一个是用于mysqld_multi使用:
[mysqld_multi]
mysqld = /usr/local/mysql8/bin/mysqld_safe
mysqladmin = /usr/local/mysql8/bin/mysqladmin
log = /app/mysql/mysqldata/mysqld_multi.log
user = root

[mysqld3307]
port=3307
user=mysql
basedir=/usr/local/mysql8
socket=/tmp/mysql3307.sock
pid-file=/app/mysql/mysqldata/3307/mysql3307.pid
datadir=/app/mysql/mysqldata/3307/data
log-error=/app/mysql/mysqldata/3307/mysql-error.log
explicit_defaults_for_timestamp

#3. 初始化:
mysqld --initialize --defaults-file=/app/my.cnf --user=mysql --datadir=/app/mysql/mysqldata/3307/data --basedir=/usr/local/mysql8

$ mysqld --initialize --user=root --datadir=/app/mysql/mysqldata/3307/data --basedir=/usr/local/mysql8 --defaults-file=/etc/mysql.cnf
[email protected]: I0pFT4aNqh#r
$ mysqld --initialize --user=root --datadir=/app/mysql/mysqldata/3308/data --basedir=/usr/local/mysql8
[email protected]: F,3Dv%mRLY*f


#4. 开启及其登录
/usr/local/mysql8/bin/mysqld_safe --port=3308 --user=root --basedir=/usr/local/mysql8 --socket=/app/mysql/mysqldata/3308/mysql3308.sock --pid-file=/app/mysql/mysqldata/3308/mysql3308.pid --log-error=/app/mysql/mysqldata/3308/mysql-error.log --explicit_defaults_for_timestamp &
/usr/local/mysql8/bin/mysqld --basedir=/usr/local/mysql8 --datadir=/app/mysql/mysqldata/3308/data --plugin-dir=/usr/local/mysql8/lib/plugin --user=root --explicit-defaults-for-timestamp --log-error=/app/mysql/mysqldata/3308/mysql-error.log --pid-file=/app/mysql/mysqldata/3308/mysql3308.pid --socket=/app/mysql/mysqldata/3308/mysql3308.sock --port=3308 &

/usr/local/mysql8/bin/mysqld_safe --port=3307 --user=root --basedir=/usr/local/mysql8 --socket=/app/mysql/mysqldata/3307/mysql3307.sock --pid-file=/app/mysql/mysqldata/3307/mysql3307.pid --log-error=/app/mysql/mysqldata/3307/mysql-error.log --explicit_defaults_for_timestamp &
/usr/local/mysql8/bin/mysqld --basedir=/usr/local/mysql8 --datadir=/app/mysql/mysqldata/3307/data --plugin-dir=/usr/local/mysql8/lib/plugin --user=root --explicit-defaults-for-timestamp --log-error=/app/mysql/mysqldata/3307/mysql-error.log --pid-file=/app/mysql/mysqldata/3307/mysql3307.pid --socket=/app/mysql/mysqldata/3307/mysql3307.sock --port=3307 &

mysql -uroot -p'F,3Dv%mRLY*f' -S /app/mysql/mysqldata/3308/mysql3308.sock
mysql -uroot -p'-KdgrGN1pkR:' -S /app/mysql/mysqldata/3307/mysql3307.sock


3. MySQL多实例登陆与安全配置

多实例数据库的登陆:(比较特殊)

#本地登陆:
mysql -S /data/3306/mysql.sock #需要指定socket

#远程登录(非本机LINUX):端口号是实例中的端口(逻辑连接位置)
mysql –h主机名 -u用户名 –p密码 –P3306
mysql> system mysql -S /data/3307/mysql.sock #(直接登录到3306的数据库)

#为用户增加密码,修改密码
mysqladmin -S /data/3306/mysql.sock -uroot password '123.asd'
mysqladmin -S /data/3307/mysql.sock –uroot –p123.asd password '123456'

MySQL多实例配置数据库进行安全配置
1)设置mysql的shell脚本中password为空,分别进行设置;

#即这样得形式mysql -uroot -p  #使之不在从命令行中输入账号密码
username = $1
password = $2
printf "Usage: /data/${port}/mysql username password {start|stop|restart}\n"

2)设置mysql的权限与所属用户和组

[[email protected] ~]$ find /data/ -type f -name "mysql" -exec chown root:root {} \;
[[email protected] ~]$ find /data/ -type f -name "mysql" -exec chmod 700 {} \;
[[email protected] ~]$ find /data/ -type f -name "mysql" -exec ls -lsh {} \;
4.0K -rwx------. 1 root root 1013 6月 26 22:38 /data/3306/mysql
4.0K -rwx------. 1 root root 1013 6月 26 22:36 /data/3307/mysql

3)再次启动开启查看然后关闭测试

[[email protected] ~]$ /data/3306/mysql root 123.asd start #实际不建议这种形式容易泄露密码
Starting MySQL...
[[email protected] ~]$ /data/3307/mysql root 123.asd start
Starting MySQL...

[[email protected] ~]$ ss -tlnp
*:3307


__

4. MySQL多实例配置数据库-故障排除

1)查看端口服务是不是启用;
2)查看MySQL的错误日志;
3)细看执行启动命令后的屏幕输出;
4)查看系统的/var/log/messages/;
5)如果是关联服务则需要看相关服务的LOG;