Linux下MySQL多實(shí)例部署記錄
什么是MySQL多實(shí)例
- 簡(jiǎn)單地說(shuō)，Mysql多實(shí)例就是在一臺(tái)服務(wù)器上同時(shí)開(kāi)啟多個(gè)不同的服務(wù)端口（3306、3307），同時(shí)運(yùn)行多個(gè)Mysql服務(wù)進(jìn)程，這些服務(wù)進(jìn)程通過(guò)不同的socket監(jiān)聽(tīng)不同的服務(wù)端口來(lái)提供服務(wù)。
- 這些Mysql多實(shí)例公用一套Mysql安裝程序，使用不同的my.cnf（也可以相同）配置文件，啟動(dòng)程序（也可以相同）和數(shù)據(jù)文件。在提供服務(wù)時(shí)，多實(shí)例 Mysql在邏輯上看來(lái)是各自獨(dú)立的，它們根據(jù)配置文件的對(duì)應(yīng)設(shè)定值，獲得服務(wù)器相應(yīng)數(shù)量的硬件資源。
- 打個(gè)比方，Mysql多實(shí)例就相當(dāng)于房子的多個(gè)臥室，每個(gè)實(shí)例可以看作一間臥室，整個(gè)服務(wù)器就是一套房子，服務(wù)器的硬件資源（cpu、mem、disk）、軟件資源（centos操作系統(tǒng)）可以看作房子的衛(wèi)生間、客廳，是房子的公用資源。
MySQL多實(shí)例優(yōu)缺點(diǎn)：
- 1、有效利用服務(wù)器資源：當(dāng)單個(gè)服務(wù)器資源有空剩余時(shí)，可以充分利用剩余的資源創(chuàng)建更多的MySQL實(shí)例提供更多的服務(wù)。
- 2、節(jié)約服務(wù)器資源：當(dāng)公司資金緊張，但是數(shù)據(jù)庫(kù)又需要多個(gè)并且需各自盡量獨(dú)立提供服務(wù)或者需要主從同步等，MySQL多實(shí)例就再好不過(guò)了。
- 3、資源相互爭(zhēng)搶問(wèn)題：當(dāng)某個(gè)服務(wù)實(shí)例并發(fā)很高或者有慢查詢時(shí)，整個(gè)實(shí)例會(huì)消耗更多的內(nèi)存、CPU、磁盤(pán)、IO資源，導(dǎo)致服務(wù)器上的其它實(shí)例提供服務(wù)的質(zhì)量下降，這就相當(dāng)于大家在一個(gè)房子的不同臥室（MySQL實(shí)例），需要上廁所（硬件的CPU、內(nèi)存、磁盤(pán)的IO資源）時(shí)，一個(gè)占用了廁所，其他人都要等待。
Mysql多實(shí)例安裝指南：
具體詳細(xì)參考官網(wǎng) （https://dev.mysql.com/doc/refman/5.7/en/installing.html）
- mysql的安裝方法有多種，如二進(jìn)制安裝、源碼編譯安裝、yum安裝;
- yum安裝都是默認(rèn)路徑，安裝相對(duì)簡(jiǎn)單；
- 源碼安裝編譯的過(guò)程比較長(zhǎng)，若沒(méi)有對(duì)源碼進(jìn)行修改且要求使用mysql較高版本；
準(zhǔn)備環(huán)境。
[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 版本的系統(tǒng)默認(rèn)自帶安裝了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多實(shí)例。
YUM源安裝方式如下：
官網(wǎng)rpm包下載：https://downloads.mysql.com/archives/community，選擇下載適合的版本。
# yum安裝MySQL5.7（默認(rèn)最新版本）
[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包上傳至服務(wù)器
[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
# 創(chuàng)建實(shí)例目錄
[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
# 設(shè)置目錄屬主屬組
[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啟動(dòng)服務(wù)文件
[root@mysql-multi ~] # mv /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service_bak
# 新增mysqld3306.service啟動(dòng)文件
[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啟動(dòng)文件
[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
# 初始化多實(shí)例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
# 啟動(dòng)多實(shí)例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實(shí)例 】===================================================================
# 創(chuàng)建目錄，設(shè)置屬主屬組
[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啟動(dòng)文件
[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
# 初始化多實(shí)例3308
[root@mysql-multi ~] # mysqld --defaults-file=/etc/my3308.cnf --initialize --user=mysql --datadir=/data/app/mysql/3308/data
# 啟動(dòng)多實(shí)例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配置管理多個(gè)MySQL 實(shí)例】< /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
#默認(rèn)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
# 測(cè)試啟動(dòng)MySQL多實(shí)例
[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多實(shí)例：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
# 下載上傳源碼包并解壓編譯安裝（最好內(nèi)存>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
......
......最末尾顯示如下內(nèi)容，則完成
-- CMAKE_SHARED_LINKER_FLAGS
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mysql-5 .7.34
選項(xiàng)說(shuō)明：
-DCMAKE_INSTALL_PREFIX= /usr/local/mysql # mysql安裝目錄
-DDEFAULT_CHARSET=utf8mb4 # 數(shù)據(jù)庫(kù)默認(rèn)字符編碼
-DENABLED_LOCAL_INFILE=1 # 允許從本文件導(dǎo)入數(shù)據(jù)
-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核心同時(shí)進(jìn)行編譯：cat /proc/cpuinfo|grep "processor"|wc -l
......
......最末尾顯示如下內(nèi)容，則完成
-- 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
# 創(chuàng)建實(shí)例目錄
[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
# 創(chuàng)建用戶,設(shè)置目錄屬主屬組
[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
# 安裝后規(guī)范化操作（設(shè)置環(huán)境變量、輸出頭文件和庫(kù)文件、設(shè)置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
# 新增實(shí)例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
# 初始化實(shí)例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
# 啟動(dòng)實(shí)例3306，3307服務(wù) （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實(shí)例密碼并測(cè)試登錄。
# 獲取實(shí)例初始密碼
[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
# 修改實(shí)例密碼
[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
# 測(cè)試登錄
[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
# 停止實(shí)例
[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多實(shí)例已經(jīng)實(shí)現(xiàn)！附：如何重置mysql root密碼？
一、 在已知MYSQL數(shù)據(jù)庫(kù)的ROOT用戶密碼的情況下，修改密碼的方法：
在SHELL環(huán)境下，使用mysqladmin命令設(shè)置：
mysqladmin –u root –p password “新密碼” 回車(chē)后要求輸入舊密碼
在mysql>環(huán)境中,使用update命令，直接更新mysql庫(kù)user表的數(shù)據(jù)：
update mysql.user set password=password( '新密碼' ) where user= 'root' ;
flush privileges;
注意：mysql語(yǔ)句要以分號(hào)”；”結(jié)束
在mysql>環(huán)境中，使用grant命令，修改root用戶的授權(quán)權(quán)限。
grant all on *.* to root@ 'localhost' identified by '新密碼' ；
二、 如忘記了mysql數(shù)據(jù)庫(kù)的ROOT用戶的密碼，又如何做呢？方法如下：
1.在其配置文件 /etc/my .cnf中加入skip-grant-tables=1即可，然后重啟mysql，使用mysql命令即可進(jìn)入
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用戶登錄數(shù)據(jù)庫(kù)，重新設(shè)置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服務(wù)即可。
2.關(guān)閉當(dāng)前運(yùn)行的mysqld服務(wù)程序：service mysqld stop（要先將mysqld添加為系統(tǒng)服務(wù)） 使用mysqld_safe腳本以安全模式（不加載授權(quán)表）啟動(dòng)mysqld 服務(wù)
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
解決方法：
先刷新一下權(quán)限表。
mysql> flush privileges;
mysql> alter user 'root' @ 'localhost' identified by '123456' ;
Query OK, 0 rows affected (0.00 sec)
3.再次關(guān)閉mysqld服務(wù)程序
systemctl stop mysqld
systemctl unset -environment MYSQLD_OPTS
systemctl start mysqld
再次測(cè)試登錄mysql
mysql -p -u root
********** 如果您認(rèn)為這篇文章還不錯(cuò)或者有所收獲，請(qǐng)點(diǎn)擊右下角的【推薦】/【贊助】按鈕，因?yàn)槟闹С质俏依^續(xù)創(chuàng)作分享的最大動(dòng)力！ **********
作者：講文張字
出處：http://www.rzrgm.cn/zhangwencheng
版權(quán)：本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出 原文鏈接
出處：http://www.rzrgm.cn/zhangwencheng
版權(quán)：本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出 原文鏈接
浙公網(wǎng)安備 33010602011771號(hào)