记录mysql多实例安装过程以及管理

junjie77649

已于 2023-09-26 11:13:03 修改

阅读量104

点赞数

文章标签： mysql adb 数据库

于 2023-09-26 09:04:55 首次发布

本文链接：https://blog.csdn.net/junjie77649/article/details/133280746

版权

什么是多实例？

多实例即在一台主机上，安装运行多个mysql，满足需要使用不同数据库的场景，实现数据相互独立，不受影响。相当于安卓手机的应用分身。

好处：

当单个服务器资源有剩余的时候，可以充分利用剩余资源提供更多服务。且可以实现资源的逻辑隔离。

适合场景：

1，资金紧张的公司
2，用户并发访问量不大的业务
3，大型网站也有用多实例

部署多实例

原理：

基于一套mysql应用，初始化三次，生成3个数据目录，实现多实例。
每个实例都有单独的：

配置文件
启动脚本
数据目录

如何准备mysql应用

采用二进制方式安装mysql

[root@mysql_master1 ~]# wget https://mirrors.aliyun.com/mysql/MySQL-5.6/mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz
[root@mysql_master1 ~]# ls -l
total 366796
-rw-------.  1 root root       1538 Sep 24 04:58 anaconda-ks.cfg
drwxr-xr-x  34 7161 31415      4096 Sep 25 06:51 mysql-5.6.49
-rw-r--r--   1 root root  343184888 Jun  2  2020 mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz
-rw-r--r--   1 root root   32402099 Jun  1  2020 mysql-5.6.49.tar.gz

安装所需依赖

[root@mysql_master1 ~]# yum -y install ncurses-devel libaio-devel openssl openssl-devel gcc make cmake net-tools vim tree

环境的清理：

注释掉之前的mysql的环境变量。
停止当前linux的mysql（如果存在mysql进程监听）

#注释变量
[root@mysql_master1 bin]# vim /etc/profile
#export PATH=/application/mysql/bin:$PATH
#停止之前服务
[root@mysql_master1 ~]# /etc/init.d/mysqld stop

创建用户：

#创建用户以及用户组
[root@mysql_master1 ~]# groupadd mysql
[root@mysql_master1 ~]# useradd -s /sbin/nologin -g mysql mysql
[root@mysql_master1 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

#已创建好
[root@mysql_master1 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

准备多实例的目录：

[root@mysql_master1 ~]# mkdir /my_mysql/{3306,3307} -p
[root@mysql_master1 ~]# mkdir /my_mysql/3306/{data,tmp,log} -p
[root@mysql_master1 ~]# mkdir /my_mysql/3307/{data,tmp,log} -p
[root@mysql_master1 ~]# tree /my_mysql/
/my_mysql/
├── 3306
│   ├── data
│   ├── log
│   └── tmp
└── 3307
    ├── data
    ├── log
    └── tmp

8 directories, 0 files

解压缩二进制的mysql的软件包：

[root@mysql_master1 tools]# tar -zxvf mysql-5.6.49-linux-glibc2.12-x86_64.tar.gz -C /application/
[root@mysql_master1 tools]# ls /application/ -la
total 0
drwxr-xr-x   4 root  root   82 Sep 25 10:02 .
dr-xr-xr-x. 19 root  root  279 Sep 25 09:57 ..
lrwxrwxrwx   1 mysql mysql  26 Sep 25 07:20 mysql -> /application/mysql-5.6.49/
drwxr-xr-x  14 mysql mysql 234 Sep 25 08:31 mysql-5.6.49
drwxr-xr-x  13 root  root  191 Sep 25 10:02 mysql-5.6.49-linux-glibc2.12-x86_64
[root@mysql_master1 mysql-5.6.49-linux-glibc2.12-x86_64]# ls
bin  data  docs  include  lib  LICENSE  man  mysql-test  README  scripts  share  sql-bench  support-files

修改目录权限：

[root@mysql_master1 ~]# chown -R mysql:mysql /application/mysql-5.6.49-linux-glibc2.12-x86_64/
[root@mysql_master1 ~]# chown -R mysql:mysql /my_mysql/
[root@mysql_master1 ~]# ls -la /my_mysql/
total 0
drwxr-xr-x   4 mysql mysql  30 Sep 25 20:11 .
dr-xr-xr-x. 19 root  root  279 Sep 25 20:11 ..
drwxr-xr-x   5 mysql mysql  40 Sep 25 20:11 3306
drwxr-xr-x   5 mysql mysql  40 Sep 25 20:11 3307
[root@mysql_master1 ~]# ls -la /application/mysql-5.6.49-linux-glibc2.12-x86_64/
total 236
drwxr-xr-x 13 mysql mysql    191 Sep 25 20:15 .
drwxr-xr-x  3 root  root      49 Sep 25 20:15 ..
drwxr-xr-x  2 mysql mysql   4096 Sep 25 20:15 bin
drwxr-xr-x  3 mysql mysql     18 Sep 25 20:15 data
drwxr-xr-x  2 mysql mysql     55 Sep 25 20:15 docs
drwxr-xr-x  3 mysql mysql   4096 Sep 25 20:15 include
drwxr-xr-x  3 mysql mysql    316 Sep 25 20:15 lib
-rw-r--r--  1 mysql mysql 219891 Jun  2  2020 LICENSE
drwxr-xr-x  4 mysql mysql     30 Sep 25 20:15 man
drwxr-xr-x 10 mysql mysql    291 Sep 25 20:15 mysql-test
-rw-r--r--  1 mysql mysql    587 Jun  2  2020 README
drwxr-xr-x  2 mysql mysql     30 Sep 25 20:15 scripts
drwxr-xr-x 28 mysql mysql   4096 Sep 25 20:15 share
drwxr-xr-x  4 mysql mysql   4096 Sep 25 20:15 sql-bench
drwxr-xr-x  2 mysql mysql    136 Sep 25 20:15 support-files

添加环境变量：

#做一个软链接，方便后面好处理。
[root@mysql_master1 ~]# ln -s /application/mysql-5.6.49-linux-glibc2.12-x86_64/ /application/mysql
[root@mysql_master1 ~]# echo 'export PATH=$PATH:/application/mysql/bin' >> /etc/profile
[root@mysql_master1 ~]# source /etc/profile
[root@mysql_master1 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/application/mysql/bin:/root/bin

准备二进制mysql运行所需的环境：

准备mysql多实例的的配置文件
数据初始化，生成mysql的初始化data数据
配置启动文件

#实例的配置文件
[root@mysql_master1 ~]# cp /application/mysql/support-files/my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
[root@mysql_master1 ~]# vim /etc/my.cnf
[root@mysql_master1 ~]# cat /etc/my.cnf

[client]

port=3306

socket=/my_mysql/3306/mysql.sock

[mysqld_multi]

mysqld = /application/mysql-5.6.49-linux-glibc2.12-x86_64/bin/mysqld_safe

mysqladmin = /application/mysql-5.6.49-linux-glibc2.12-x86_64/bin/mysqladmin

log = /my_mysql/mysqld_multi.log

[mysqld]

user=mysql

basedir = /application/mysql-5.6.49-linux-glibc2.12-x86_64/

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld3306]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/my_mysql/3306/data

port=3306

server_id=3306

socket=/my_mysql/3306/mysql.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /my_mysql/3306/log/slow.log

log-error = /my_mysql/3306/log/error.log

binlog_format = mixed

log-bin = /my_mysql/3306/log/mysql3306.bin

[mysqld3307]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/my_mysql/3307/data

port=3307

server_id=3307

socket=/my_mysql/3307/mysql.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /my_mysql/3307/log/slow.log

log-error = /my_mysql/3307/log/error.log

binlog_format = mixed

log-bin = /my_mysql/3307/log/mysql3307_bin

初始化实例：

[root@mysql_master1 ~]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql-5.6.49-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3306/data --defaults-file=/etc/my.cnf --user=mysql
[root@mysql_master1 ~]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql-5.6.49-linux-glibc2.12-x86_64/ --datadir=/my_mysql/3307/data --defaults-file=/etc/my.cnf --user=mysql
#检查是否正常生成初始化文件
[root@mysql_master1 ~]# ls /my_mysql/3306/
data  ibdata1  ib_logfile0  ib_logfile1  log  mysql  performance_schema  test  tmp
[root@mysql_master1 ~]# ls /my_mysql/3307/
data  ibdata1  ib_logfile0  ib_logfile1  log  mysql  performance_schema  test  tmp

配置启动文件：

[root@mysql_master1 data]# cp /application/mysql/support-files/mysql.server /etc/init.d/mysql

管理多实例服务：

#启动所有实例
[root@mysql_master1 data]# mysqld_multi start

#查看实例运行状态
[root@mysql_master1 data]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running

#停止所有实例
[root@mysql_master1 data]# mysqld_multi stop

#启动单个实例
[root@mysql_master1 data]# mysqld_multi start 3306

[root@mysql_master1 data]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is not running

#停止单个实例
[root@mysql_master1 data]# mysqld_multi stop 3306

分别登录两个实例：

[root@mysql_master1 ~]# mysql -S /my_mysql/3306/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

[root@mysql_master1 ~]# mysql -S /my_mysql/3307/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.49-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>

开机自启：
将启动命令添加到/etc/rc.local,实现开机启动：

#将启动命令写入rc.local
[root@mysql_master1 ~]# echo "/application/mysql/bin/mysqld_multi start" >>/etc/rc.local
#添加执行权限
[root@mysql_master1 ~]# chmod +x /etc/rc.d/rc.local
#添加环境变量，并重新加载
[root@mysql_master1 ~]# echo "/etc/rc.local" >> ~/.bash_profile && source /etc/profile
#重启验证
[root@mysql_master1 ~]# reboot
#状态正常
[root@mysql_master1 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running