Linux下MySQL多实例部署及安装指南
程序员文章站
2022-07-07 23:27:44
什么是mysql多实例 简单地说,mysql多实例就是在一台服务器上同时开启多个不同的服务端口(3306、3307),同时运行多个mysql服务进程,这些服务进程通过不同的socket监听不同的...
什么是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多实例安装指南:
具体详细参考官网 ()
- 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包下载:,选择下载适合的版本。
# 将rpm包上传至服务器 [root@mysql-multi ~]# yum install libaio lrzsz tree net-tools -y [root@mysql-multi ~]# 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 # 必须按照顺序执行安装操作 [root@mysql-multi ~]# rpm -ivh mysql-community-common-5.7.34-1.el7.x86_64.rpm [root@mysql-multi ~]# rpm -ivh mysql-community-libs-5.7.34-1.el7.x86_64.rpm [root@mysql-multi ~]# rpm -ivh mysql-community-client-5.7.34-1.el7.x86_64.rpm [root@mysql-multi ~]# 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 = /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 # 新增配置文件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 = /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 # 备份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=/data/app/mysql/3306/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=/data/app/mysql/3306/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=/data/app/mysql/3307/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=/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 ~]# 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=/data/app/mysql/3306/mysqld3306.pid mysql 128328 1 1 11:43 ? 00:00:00 /usr/sbin/mysqld --defaults-file=/etc/my3307.cnf --daemonize --pid-file=/data/app/mysql/3307/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 = /data/app/mysql/3308/mysql3308.sock symbolic-links = 0 log-error = /data/app/mysql/3308/logs/mysqld3308.log pid-file = /data/app/mysql/3308/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=/data/app/mysql/3308/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=/data/app/mysql/3308/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=/data/app/mysql/3306/mysqld3306.pid mysql 5098 1 0 14:44 ? 00:00:01 /usr/sbin/mysqld --defaults-file=/etc/my3307.cnf --daemonize --pid-file=/data/app/mysql/3307/mysqld3307.pid mysql 5189 1 4 14:57 ? 00:00:01 /usr/sbin/mysqld --defaults-file=/etc/my3308.cnf --daemonize --pid-file=/data/app/mysql/3308/mysqld3308.pid
源码编译安装方式如下:
mysql下载:
boost下载:
mysql多实例:
# 依赖包和编译软件 [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多实例已经实现!
到此这篇关于linux下mysql多实例部署记录的文章就介绍到这了,更多相关mysql多实例部署内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!