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
版权:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出 原文链接
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库