Linux下MySQL多实例部署记录
什么是MySQL多实例
- 简单地说，Mysql多实例就是在一台服务器上同时开启多个不同的服务端口（3306、3307），同时运行多个Mysql服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。
- 这些Mysql多实例公用一套Mysql安装程序，使用不同的my.cnf（也可以相同）配置文件，启动程序（也可以相同）和数据文件。在提供服务时，多实例 Mysql在逻辑上看来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
- 打个比方，Mysql多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（cpu、mem、disk）、软件资源（centos操作系统）可以看作房子的卫生间、客厅，是房子的公用资源。
MySQL多实例优缺点：
- 1、有效利用服务器资源：当单个服务器资源有空剩余时，可以充分利用剩余的资源创建更多的MySQL实例提供更多的服务。
- 2、节约服务器资源：当公司资金紧张，但是数据库又需要多个并且需各自尽量独立提供服务或者需要主从同步等，MySQL多实例就再好不过了。
- 3、资源相互争抢问题：当某个服务实例并发很高或者有慢查询时，整个实例会消耗更多的内存、CPU、磁盘、IO资源，导致服务器上的其它实例提供服务的质量下降，这就相当于大家在一个房子的不同卧室（MySQL实例），需要上厕所（硬件的CPU、内存、磁盘的IO资源）时，一个占用了厕所，其他人都要等待。
Mysql多实例安装指南：
具体详细参考官网 （https://dev.mysql.com/doc/refman/5.7/en/installing.html）
- mysql的安装方法有多种，如二进制安装、源码编译安装、yum安装;
- yum安装都是默认路径，安装相对简单；
- 源码安装编译的过程比较长，若没有对源码进行修改且要求使用mysql较高版本；
准备环境。
[root@mysql-multi ~] # cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
[root@mysql-multi ~] # uname -r
3.10.0-862.el7.x86_64
[root@mysql-multi ~] # hostname -I
172.16.70.37
[root@mysql-multi ~] # getenforce
Permissive
[root@mysql-multi ~] # systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded ( /usr/lib/systemd/system/firewalld .service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man :firewalld(1)
Jul 23 14:36:11 mysql-multi systemd[1]: Starting firewalld - dynamic firewall daemon...
Jul 23 14:36:12 mysql-multi systemd[1]: Started firewalld - dynamic firewall daemon.
Jul 23 15:09:10 mysql-multi systemd[1]: Stopping firewalld - dynamic firewall daemon...
Jul 23 15:09:11 mysql-multi systemd[1]: Stopped firewalld - dynamic firewall daemon.
# CentOS 7 版本的系统默认自带安装了MariaDB，需要先清理
[root@mysql-multi ~] # rpm -qa |grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
或
[root@mysql-multi ~] # yum list installed | grep mariadb
mariadb-libs.x86_64 1:5.5.56-2.el7 @anaconda
# 卸载
[root@mysql-multi ~] # rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
或
[root@mysql-multi ~] # yum -y remove mariadb-libs.x86_64安装并配置MySQL多实例。
YUM源安装方式如下：
官网rpm包下载：https://downloads.mysql.com/archives/community，选择下载适合的版本。
# yum安装MySQL5.7（默认最新版本）
[root@Mysql-Master01 ~] # wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Master01 ~] # yum localinstall -y mysql57-community-release-el7-10.noarch.rpm
[root@Mysql-Master01 ~] # yum repolist enabled | grep "mysql.*-community.*"
[root@Mysql-Master01 ~] # yum install -y mysql-community-server
--------------------------------------------------------------------------------------------------------------------------
# 安装MySQL5.7.34（指定版本)
# 将rpm包上传至服务器
[root@mysql-multi ~] # yum install libaio lrzsz tree net-tools -y
[root@Mysql-Master01 ~] # ls
mysql-community-client-5.7.34-1.el7.x86_64.rpm mysql-community-libs-5.7.34-1.el7.x86_64.rpm
mysql-community-common-5.7.34-1.el7.x86_64.rpm mysql-community-server-5.7.34-1.el7.x86_64.rpm
# 必须按顺序安装（common-->libs-->client-->server）
[root@Mysql-Master01 ~] # rpm -ivh mysql-community-common-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~] # rpm -ivh mysql-community-libs-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~] # rpm -ivh mysql-community-client-5.7.34-1.el7.x86_64.rpm
[root@Mysql-Master01 ~] # rpm -ivh mysql-community-server-5.7.34-1.el7.x86_64.rpm
==========================================================================================================================
[root@mysql-multi ~] # yum list installed | grep mysql
mysql-community-client.x86_64 5.7.34-1.el7 installed
mysql-community-common.x86_64 5.7.34-1.el7 installed
mysql-community-libs.x86_64 5.7.34-1.el7 installed
mysql-community-server.x86_64 5.7.34-1.el7 installed
# 创建实例目录
[root@mysql-multi ~] # mkdir -p /data/app/mysql/{3306,3307}
[root@mysql-multi ~] # mkdir -p /data/app/mysql/3306/{data,binlog,logs}
[root@mysql-multi ~] # mkdir -p /data/app/mysql/3307/{data,binlog,logs}
[root@mysql-multi ~] # tree /data/app/mysql/
/data/app/mysql/
├── 3306
│ ├── binlog
│ ├── data
│ └── logs
└── 3307
├── binlog
├── data
└── logs
# 设置目录属主属组
[root@mysql-multi ~] # chown -R mysql:mysql /data/app/mysql
[root@mysql-multi ~] # ls -ld /data/app/mysql
drwxr-xr-x. 4 mysql mysql 30 Jul 29 18:39 /data/app/mysql
[root@mysql-multi ~] # ls -ld /data/app
drwxr-xr-x. 3 root root 19 Jul 29 18:39 /data/app
# 新增配置文件my3306.cnf
[root@mysql-multi ~] # mv /etc/my.cnf /etc/my.cnf_bak
[root@mysql-multi ~] # cat /etc/my3306.cnf
[mysqld]
user = mysql
port = 3306
server_id = 3306
datadir = /data/app/mysql/3306/data
socket = /var/lib/mysql/mysql3306 .sock
symbolic-links = 0
log-error = /data/app/mysql/3306/logs/mysqld3306 .log
pid- file = /var/run/mysqld/mysqld3306 .pid
# 新增配置文件my3307.cnf
[root@mysql-multi ~] # cp /etc/my3306.cnf /etc/my3307.cnf
[root@mysql-multi ~] # sed -i 's/3306/3307/g' /etc/my3307.cnf
[root@mysql-multi ~] # cat /etc/my3307.cnf
[mysqld]
user = mysql
port = 3307
server_id = 3307
datadir = /data/app/mysql/3307/data
socket = /var/lib/mysql/mysql3307 .sock
symbolic-links = 0
log-error = /data/app/mysql/3307/logs/mysqld3307 .log
pid- file = /var/run/mysqld/mysqld3307 .pid
# 备份mysql启动服务文件
[root@mysql-multi ~] # mv /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service_bak
# 新增mysqld3306.service启动文件
[root@mysql-multi ~] # cat /usr/lib/systemd/system/mysqld3306.service
[Unit]
Description=MySQL Server
Documentation= man :mysqld(8)
Documentation=http: //dev .mysql.com /doc/refman/en/using-systemd .html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile= /var/run/mysqld/mysqld3306 .pid
TimeoutSec=0
PermissionsStartOnly= true
#ExecStartPre=/usr/bin/mysqld_pre_systemd_3306 3306
ExecStart= /usr/sbin/mysqld --defaults- file = /etc/my3306 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3306 .pid $MYSQLD_OPTS
EnvironmentFile=- /etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp= false
# 新增mysqld3307.service启动文件
[root@mysql-multi ~] # cp /usr/lib/systemd/system/mysqld3306.service /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~] # sed -i 's/3306/3307/g' /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~] # cat /usr/lib/systemd/system/mysqld3307.service
[Unit]
Description=MySQL Server
Documentation= man :mysqld(8)
Documentation=http: //dev .mysql.com /doc/refman/en/using-systemd .html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile= /var/run/mysqld/mysqld3307 .pid
TimeoutSec=0
PermissionsStartOnly= true
#ExecStartPre=/usr/bin/mysqld_pre_systemd_3307 3307
ExecStart= /usr/sbin/mysqld --defaults- file = /etc/my3307 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3307 .pid $MYSQLD_OPTS
EnvironmentFile=- /etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp= false
# 初始化多实例3306，3307
[root@mysql-multi ~] # mysqld --defaults-file=/etc/my3306.cnf --initialize --user=mysql --datadir=/data/app/mysql/3306/data
[root@mysql-multi ~] # mysqld --defaults-file=/etc/my3307.cnf --initialize --user=mysql --datadir=/data/app/mysql/3307/data
# 启动多实例3306，3307
[root@mysql-multi ~] # systemctl start mysqld3306
[root@mysql-multi ~] # systemctl start mysqld3307
[root@mysql-multi ~] # netstat -nutpl | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 128270 /mysqld
tcp6 0 0 :::3307 :::* LISTEN 128328 /mysqld
[root@mysql-multi ~] # ps -ef | grep mysql
mysql 128270 1 1 11:43 ? 00:00:00 /usr/sbin/mysqld --defaults- file = /etc/my3306 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3306 .pid
mysql 128328 1 1 11:43 ? 00:00:00 /usr/sbin/mysqld --defaults- file = /etc/my3307 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3307 .pid
root 128373 949 0 11:43 pts /0 00:00:00 grep --color=auto mysql
======================================== 【 再新增一MySQL实例 】===================================================================
# 创建目录，设置属主属组
[root@mysql-multi ~] # mkdir -p /data/app/mysql/3308/{data,binlog,logs}
[root@mysql-multi ~] # chown -R mysql:mysql /data/app/mysql/3308
[root@mysql-multi ~] # ls -ld /data/app/mysql/3308
drwxr-xr-x. 5 mysql mysql 44 Aug 5 14:45 /data/app/mysql/3308
# 新增配置文件my3308.cnf
[root@mysql-multi ~] # cp /etc/my3306.cnf /etc/my3308.cnf
[root@mysql-multi ~] # sed -i 's/3306/3308/g' /etc/my3308.cnf
[root@mysql-multi ~] # cat /etc/my3308.cnf
[mysqld]
user = mysql
port = 3308
server_id = 3308
datadir = /data/app/mysql/3308/data
socket = /var/lib/mysql/mysql3308 .sock
symbolic-links = 0
log-error = /data/app/mysql/3308/logs/mysqld3308 .log
pid- file = /var/run/mysqld/mysqld3308 .pid
# 新增mysqld3308.service启动文件
[root@mysql-multi ~] # cp /usr/lib/systemd/system/mysqld3306.service /usr/lib/systemd/system/mysqld3308.service
[root@mysql-multi ~] # sed -i 's/3306/3308/g' /usr/lib/systemd/system/mysqld3308.service
[root@mysql-multi ~] # cat /usr/lib/systemd/system/mysqld3308.service
[Unit]
Description=MySQL Server 3308
Documentation= man :mysqld(8)
Documentation=http: //dev .mysql.com /doc/refman/en/using-systemd .html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile= /var/run/mysqld/mysqld3308 .pid
TimeoutSec=0
PermissionsStartOnly= true
#ExecStartPre=/usr/bin/mysqld_pre_systemd_3308 3308
ExecStart= /usr/sbin/mysqld --defaults- file = /etc/my3308 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3308 .pid $MYSQLD_OPTS
EnvironmentFile=- /etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp= false
# 初始化多实例3308
[root@mysql-multi ~] # mysqld --defaults-file=/etc/my3308.cnf --initialize --user=mysql --datadir=/data/app/mysql/3308/data
# 启动多实例3308
[root@mysql-multi ~] # systemctl start mysqld3308
[root@mysql-multi ~] # netstat -nutpl | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 5062 /mysqld
tcp6 0 0 :::3307 :::* LISTEN 5098 /mysqld
tcp6 0 0 :::3308 :::* LISTEN 5189 /mysqld
[root@mysql-multi ~] # ps -ef |grep mysql
mysql 5062 1 0 14:43 ? 00:00:01 /usr/sbin/mysqld --defaults- file = /etc/my3306 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3306 .pid
mysql 5098 1 0 14:44 ? 00:00:01 /usr/sbin/mysqld --defaults- file = /etc/my3307 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3307 .pid
mysql 5189 1 4 14:57 ? 00:00:01 /usr/sbin/mysqld --defaults- file = /etc/my3308 .cnf --daemonize --pid- file = /var/run/mysqld/mysqld3308 .pid
<span style= "font-size: 14px;" data-mce-style= "font-size: 14px;" >【使用systemd配置管理多个MySQL 实例】< /span >
[root@mysql-multi ~] # cat /etc/my.cnf
[mysqld@3306]
user = mysql
port = 3306
server_id = 3306
datadir = /data/app/mysql/3306/data
socket= /var/lib/mysql/mysql3306 .sock
symbolic-links = 0
log-error = /data/app/mysql/3306/logs/mysqld3306 .log
pid- file = /var/run/mysqld/mysqld3306 .pid
[mysqld@3307]
user = mysql
port = 3307
server_id = 3307
datadir = /data/app/mysql/3307/data
socket= /var/lib/mysql/mysql3307 .sock
symbolic-links = 0
log-error = /data/app/mysql/3307/logs/mysqld3307 .log
pid- file = /var/run/mysqld/mysqld3307 .pid
[mysqld@3308]
user = mysql
port = 3308
server_id = 3308
datadir = /data/app/mysql/3308/data
socket= /var/lib/mysql/mysql3308 .sock
symbolic-links = 0
log-error = /data/app/mysql/3308/logs/mysqld3308 .log
pid- file = /var/run/mysqld/mysqld3308 .pid
#默认mysqld@.service
[root@mysql-multi ~] # egrep -v "^$|^#" /usr/lib/systemd/system/mysqld@.service
[Unit]
Description=MySQL Server
Documentation= man :mysqld(8)
Documentation=http: //dev .mysql.com /doc/refman/en/using-systemd .html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile= /var/run/mysqld/mysqld- %i.pid
TimeoutSec=0
PermissionsStartOnly= true
ExecStartPre= /usr/bin/mysqld_pre_systemd %I
ExecStart= /usr/sbin/mysqld --defaults-group-suffix=@%I --daemonize --pid- file = /var/run/mysqld/mysqld- %i.pid $MYSQLD_OPTS
EnvironmentFile=- /etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=fals
# 测试启动MySQL多实例
[root@mysql-multi ~] # ps -ef | grep mysql
root 1901 1027 0 11:41 pts /0 00:00:00 grep --color=auto mysql
[root@mysql-multi ~] # netstat -ntpl | grep mysql
[root@mysql-multi ~] #
[root@mysql-multi ~] # systemctl start mysqld@3306
[root@mysql-multi ~] # systemctl start mysqld@3307
[root@mysql-multi ~] # systemctl start mysqld@3308
[root@mysql-multi ~] # netstat -ntpl | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 1924 /mysqld
tcp6 0 0 :::3307 :::* LISTEN 1965 /mysqld
tcp6 0 0 :::3308 :::* LISTEN 2004 /mysqld
[root@mysql-multi ~] # systemctl status "mysqld@330*"
● mysqld@3307.service - MySQL Server
Loaded: loaded ( /usr/lib/systemd/system/mysqld @.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2022-01-10 11:42:31 CST; 37s ago
Docs: man :mysqld(8)
http: //dev .mysql.com /doc/refman/en/using-systemd .html
Process: 1963 ExecStart= /usr/sbin/mysqld --defaults-group-suffix=@%I --daemonize --pid- file = /var/run/mysqld/mysqld- %i.pid $MYSQLD_OPTS (code=exited, status=0 /SUCCESS )
Process: 1960 ExecStartPre= /usr/bin/mysqld_pre_systemd %I (code=exited, status=0 /SUCCESS )
Main PID: 1965 (mysqld)
CGroup: /system .slice /system-mysqld .slice /mysqld @3307.service
└─1965 /usr/sbin/mysqld --defaults-group-suffix=@3307 --daemonize --pid- file = /var/run/mysqld/mysqld-3307 .pid
Jan 10 11:42:31 Server-01 systemd[1]: Starting MySQL Server...
Jan 10 11:42:31 Server-01 systemd[1]: Started MySQL Server.
● mysqld@3306.service - MySQL Server
Loaded: loaded ( /usr/lib/systemd/system/mysqld @.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2022-01-10 11:42:23 CST; 44s ago
Docs: man :mysqld(8)
http: //dev .mysql.com /doc/refman/en/using-systemd .html
Process: 1921 ExecStart= /usr/sbin/mysqld --defaults-group-suffix=@%I --daemonize --pid- file = /var/run/mysqld/mysqld- %i.pid $MYSQLD_OPTS (code=exited, status=0 /SUCCESS )
Process: 1918 ExecStartPre= /usr/bin/mysqld_pre_systemd %I (code=exited, status=0 /SUCCESS )
Main PID: 1924 (mysqld)
CGroup: /system .slice /system-mysqld .slice /mysqld @3306.service
└─1924 /usr/sbin/mysqld --defaults-group-suffix=@3306 --daemonize --pid- file = /var/run/mysqld/mysqld-3306 .pid
Jan 10 11:42:23 Server-01 systemd[1]: Starting MySQL Server...
Jan 10 11:42:23 Server-01 systemd[1]: Started MySQL Server.
● mysqld@3308.service - MySQL Server
Loaded: loaded ( /usr/lib/systemd/system/mysqld @.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2022-01-10 11:42:34 CST; 34s ago
Docs: man :mysqld(8)
http: //dev .mysql.com /doc/refman/en/using-systemd .html
Process: 2001 ExecStart= /usr/sbin/mysqld --defaults-group-suffix=@%I --daemonize --pid- file = /var/run/mysqld/mysqld- %i.pid $MYSQLD_OPTS (code=exited, status=0 /SUCCESS )
Process: 1999 ExecStartPre= /usr/bin/mysqld_pre_systemd %I (code=exited, status=0 /SUCCESS )
Main PID: 2004 (mysqld)
CGroup: /system .slice /system-mysqld .slice /mysqld @3308.service
└─2004 /usr/sbin/mysqld --defaults-group-suffix=@3308 --daemonize --pid- file = /var/run/mysqld/mysqld-3308 .pid
Jan 10 11:42:33 Server-01 systemd[1]: Starting MySQL Server...
Jan 10 11:42:34 Server-01 systemd[1]: Started MySQL Server
<span style= "font-size: 15px;" data-mce-style= "font-size: 15px;" >注意：此方法仅适用mysqld@.service的【start|stop|status】< /span >源码编译安装方式如下：
MySQL下载：https://downloads.mysql.com/archives/community
Boost下载：https://sourceforge.net/projects/boost/files/boost/1.59.0
MySQL多实例：https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html
# 依赖包和编译软件
[root@mysql-multi ~] # yum install -y cmake make gcc gcc-c++ openssl openssl-devel ncurses ncurses-devel libaio-devel wget lrzsz tree
[root@mysql-multi ~] # rpm -qa ncurses-devel libaio-devel
libaio-devel-0.3.109-13.el7.x86_64
ncurses-devel-5.9-14.20130511.el7_4.x86_64
# 下载上传源码包并解压编译安装（最好内存>8G）
ls [root@mysql-multi ~] # ls
boost_1_59_0. tar .gz mysql-boost-5.7.34. tar .gz
[root@mysql-multi ~] # tar xf boost_1_59_0.tar.gz
[root@mysql-multi ~] # tar xf mysql-boost-5.7.34.tar.gz
[root@mysql-multi ~] # ls
boost_1_59_0 boost_1_59_0. tar .gz mysql-5.7.34 mysql-boost-5.7.34. tar .gz
[root@mysql-multi ~] # cd mysql-5.7.34/
[root@mysql-multi mysql-5.7.34] # cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8mb4 \
-DENABLED_LOCAL_INFILE=1 -DWITH_SYSTEMD=1 -DWITH_BOOST= /root/boost_1_59_0 -DEXTRA_CHARSETS=all
......
......最末尾显示如下内容，则完成
-- CMAKE_SHARED_LINKER_FLAGS
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mysql-5 .7.34
选项说明：
-DCMAKE_INSTALL_PREFIX= /usr/local/mysql # mysql安装目录
-DDEFAULT_CHARSET=utf8mb4 # 数据库默认字符编码
-DENABLED_LOCAL_INFILE=1 # 允许从本文件导入数据
-DWITH_SYSTEMD=1 # 提供systemd脚本
-DWITH_BOOST= /root/boost_1_59_0 # boost源路径
-DEXTRA_CHARSETS=all # 安装所有字符集
[root@mysql-multi mysql-5.7.34] #make -j 4 && make install # make -j 4 表示用CPU4核心同时进行编译：cat /proc/cpuinfo|grep "processor"|wc -l
......
......最末尾显示如下内容，则完成
-- Up-to- date : /usr/local/app/mysql/mysql-test/mysql-test-run
-- Installing: /usr/local/app/mysql/mysql-test/lib/My/SafeProcess/my_safe_process
-- Up-to- date : /usr/local/app/mysql/mysql-test/lib/My/SafeProcess/my_safe_process
-- Installing: /usr/local/app/mysql/mysql-test/lib/My/SafeProcess/Base .pm
-- Installing: /usr/local/app/mysql/support-files/mysqld_multi .server
-- Installing: /usr/local/app/mysql/support-files/mysql-log-rotate
-- Installing: /usr/local/app/mysql/support-files/magic
-- Installing: /usr/local/app/mysql/share/aclocal/mysql .m4
-- Installing: /usr/local/app/mysql/support-files/mysql .server
# 创建实例目录
[root@mysql-multi ~] # mkdir -p /data/app/mysql/{3306,3307}/{data,binlog,logs}
[root@mysql-multi ~] # tree /data/app/mysql
/data/app/mysql
├── 3306
│ ├── binlog
│ ├── data
│ └── logs
└── 3307
├── binlog
├── data
└── logs
# 创建用户,设置目录属主属组
[root@mysql-multi ~] # useradd -M -r -s /sbin/nologin mysql
[root@mysql-multi ~] # chown -R mysql:mysql /data/app/mysql
[root@mysql-multi ~] # ls -ld /data/app/mysql
drwxr-xr-x. 4 mysql mysql 30 Aug 5 12:17 /data/app/mysql
# 新增配置文件my3306.cnf
[root@mysql-multi ~] # mv /etc/my.cnf /etc/my.cnf_bak
[root@mysql-multi ~] # cat /etc/my3306.cnf
[mysqld]
user = mysql
port = 3306
server_id = 3306
basedir = /usr/local/mysql
datadir = /data/app/mysql/3306/data
socket = /data/app/mysql/3306/mysql3306 .sock
symbolic-links = 0
log-error = /data/app/mysql/3306/logs/mysqld3306 .log
pid- file = /data/app/mysql/3306/mysqld3306 .pid
character_set_server = utf8
default-storage-engine = INNODB
# 新增配置文件my3307.cnf
[root@mysql-multi ~] # cp /etc/my3306.cnf /etc/my3307.cnf
[root@mysql-multi ~] # sed -i 's/3306/3307/g' /etc/my3307.cnf
[root@mysql-multi ~] # cat /etc/my3307.cnf
[mysqld]
user = mysql
port = 3307
server_id = 3307
basedir = /usr/local/mysql
datadir = /data/app/mysql/3307/data
socket = /data/app/mysql/3307/mysql3307 .sock
symbolic-links = 0
log-error = /data/app/mysql/3307/logs/mysqld3307 .log
pid- file = /data/app/mysql/3307/mysqld3307 .pid
character_set_server = utf8
default-storage-engine = INNODB
# 安装后规范化操作（设置环境变量、输出头文件和库文件、设置man路径）
[root@mysql-multi ~] # echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile.d/mysql.sh
[root@mysql-multi ~] # chmod +x /etc/profile.d/mysql.sh
[root@mysql-multi ~] # source /etc/profile.d/mysql.sh
[root@mysql-multi ~] # echo "MANPATH /usr/local/mysql/man" >>/etc/man.config
[root@mysql-multi ~] # echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
[root@mysql-multi ~] # ldconfig
[root@mysql-multi ~] # ln -s /usr/local/mysql/include /usr/include/mysql
# 新增实例3306，3307 systemd方式
[root@mysql-multi ~] # cat /usr/lib/systemd/system/mysqld3306.service
[Unit]
Description=MySQL Server
Documentation= man :mysqld(8)
Documentation=http: //dev .mysql.com /doc/refman/en/using-systemd .html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile= /data/app/mysql/3306/mysqld3306 .pid
TimeoutSec=0
PermissionsStartOnly= true
#ExecStartPre=/usr/bin/mysqld_pre_systemd_3306 3306
ExecStart= /usr/local/mysql/bin/mysqld --defaults- file = /etc/my3306 .cnf --daemonize --pid- file = /data/app/mysql/3306/mysqld3306 .pid $MYSQLD_OPTS
EnvironmentFile=- /etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp= false
[root@mysql-multi ~] # cp /usr/lib/systemd/system/mysqld3306.service /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~] # sed -i 's/3306/3307/g' /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~] # cat /usr/lib/systemd/system/mysqld3307.service
[Unit]
Description=MySQL Server
Documentation= man :mysqld(8)
Documentation=http: //dev .mysql.com /doc/refman/en/using-systemd .html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile= /data/app/mysql/3307/mysqld3307 .pid
TimeoutSec=0
PermissionsStartOnly= true
#ExecStartPre=/usr/bin/mysqld_pre_systemd_3307 3307
ExecStart= /usr/local/mysql/bin/mysqld --defaults- file = /etc/my3307 .cnf --daemonize --pid- file = /data/app/mysql/3307/mysqld3307 .pid $MYSQLD_OPTS
EnvironmentFile=- /etc/sysconfig/mysql
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp= false
# 初始化实例3306，3307
[root@mysql-multi ~] # /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --initialize --basedir=/usr/local/mysql --user=mysql --datadir=/data/app/mysql/3306/data
[root@mysql-multi ~] # /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --initialize --basedir=/usr/local/mysql --user=mysql --datadir=/data/app/mysql/3307/data
# 启动实例3306，3307服务 （start|stop|restart|status）
[root@mysql-multi ~] # systemctl start mysqld3306
[root@mysql-multi ~] # systemctl start mysqld3307
[root@mysql-multi ~] # netstat -nuptl | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 27165 /mysqld
tcp6 0 0 :::3307 :::* LISTEN 27201 /mysqld
[root@mysql-multi ~] # ps -ef | grep mysql
mysql 27165 1 2 17:03 ? 00:00:00 /usr/local/mysql/bin/mysqld --defaults- file = /etc/my3306 .cnf --daemonize --pid- file = /data/app/mysql/3306/mysqld3306 .pid
mysql 27201 1 2 17:03 ? 00:00:00 /usr/local/mysql/bin/mysqld --defaults- file = /etc/my3307 .cnf --daemonize --pid- file = /data/app/mysql/3307/mysqld3307 .pid修改MySQL实例密码并测试登录。
# 获取实例初始密码
[root@mysql-multi ~] # grep 'temporary password' /data/app/mysql/3306/logs/mysqld3306.log
2021-08-05T08:52:37.904630Z 1 [Note] A temporary password is generated for root@localhost: ,&YrsLryq3Ll
[root@mysql-multi ~] # grep 'temporary password' /data/app/mysql/3307/logs/mysqld3307.log
2021-08-05T08:52:48.082526Z 1 [Note] A temporary password is generated for root@localhost: OvxKu, su =4O1
# 修改实例密码
[root@mysql-multi ~] # mysqladmin -p -S /data/app/mysql/3306/mysql3306.sock password
Enter password: # 输入初始密码
New password: # 输入新密码 123456
Confirm new password: # 再次输入新密码 123456
--------------------------------------------------------------------------------------
# 或
shell> mysql -uroot -p /data/app/mysql/3306/mysql3306 .sock
mysql> ALTER USER 'root' @ 'localhost' IDENTIFIED BY '123456' ;
--------------------------------------------------------------------------------------
[root@mysql-multi ~] # mysqladmin -p -S /data/app/mysql/3306/mysql3307.sock password
Enter password: # 输入初始密码
New password: # 输入新密码 654321
Confirm new password: # 再次输入新密码 654321
# 测试登录
[root@mysql-multi ~] # mysql -uroot -p -S /data/app/mysql/3306/mysql3306.sock
Enter password: # 输入密码 123456
[root@mysql-multi ~] # mysql -uroot -p -S /data/app/mysql/3307/mysql3307.sock
Enter password: # 输入密码 654321
# 停止实例
[root@mysql-multi ~] # mysqladmin -uroot -p -S /data/app/mysql/3306/mysql3306.sock shutdown
Enter password: # 输入密码 123456
[root@mysql-multi ~] # mysqladmin -uroot -p -S /data/app/mysql/3307/mysql3307.sock shutdown
Enter password: # 输入密码 654321
至此，MySQL多实例已经实现！附：如何重置mysql root密码？
一、 在已知MYSQL数据库的ROOT用户密码的情况下，修改密码的方法：
在SHELL环境下，使用mysqladmin命令设置：
mysqladmin –u root –p password “新密码” 回车后要求输入旧密码
在mysql>环境中,使用update命令，直接更新mysql库user表的数据：
update mysql.user set password=password( '新密码' ) where user= 'root' ;
flush privileges;
注意：mysql语句要以分号”；”结束
在mysql>环境中，使用grant命令，修改root用户的授权权限。
grant all on *.* to root@ 'localhost' identified by '新密码' ；
二、 如忘记了mysql数据库的ROOT用户的密码，又如何做呢？方法如下：
1.在其配置文件 /etc/my .cnf中加入skip-grant-tables=1即可，然后重启mysql，使用mysql命令即可进入
cat /etc/my .cnf
[mysqld]
datadir= /var/lib/mysql
socket= /var/lib/mysql/mysql .sock
log-error= /var/log/mysqld .log
pid- file = /var/run/mysqld/mysqld .pid
skip-grant-tables=1
使用空密码的root用户登录数据库，重新设置ROOT用户的密码
＃mysql -u root
Mysql> update mysql.user set authentication_string=password( '新密码' ) where user= 'root' ;
Mysql> flush privileges;
然后将 /etc/my .cnf中的skip-grant-tables=1注释掉，重启mysql服务即可。
2.关闭当前运行的mysqld服务程序：service mysqld stop（要先将mysqld添加为系统服务） 使用mysqld_safe脚本以安全模式（不加载授权表）启动mysqld 服务
systemctl set -environment MYSQLD_OPTS= "--skip-grant-tables"
mysql> alter user 'root' @ 'localhost' identified by '123456' ;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决方法：
先刷新一下权限表。
mysql> flush privileges;
mysql> alter user 'root' @ 'localhost' identified by '123456' ;
Query OK, 0 rows affected (0.00 sec)
3.再次关闭mysqld服务程序
systemctl stop mysqld
systemctl unset -environment MYSQLD_OPTS
systemctl start mysqld
再次测试登录mysql
mysql -p -u root
********** 如果您认为这篇文章还不错或者有所收获，请点击右下角的【推荐】/【赞助】按钮，因为您的支持是我继续创作分享的最大动力！ **********
作者：讲文张字
出处：https://www.cnblogs.com/zhangwencheng
版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出 原文链接
出处：https://www.cnblogs.com/zhangwencheng
版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出 原文链接
