Linux

MySQL编译安装及多实例实现

MySQL编译安装及多实例实现这个安装方法仅用于CentOS

编译安装MySQL

安装编译所需要的包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boostdevel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssldevel libevent-devel libaio-devel -y

添加MySQL用户

[root@localhost ~]# useradd -r -s /sbin/nologin -d /data/mysql mysql

为MySQL用户添加家目录

[root@localhost ~]# mkdir /data/mysql
[root@localhost ~]# chmod 700 /data/mysql
[root@localhost ~]# chown mysql.mysql /data/mysql

下载mariadb源码包

root@localhost ~]# wget http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-10.2.23/source/mariadb-10.2.23.tar.gz

解压MySQL源码包

[root@localhost ~]# tar -xf mariadb-10.2.23.tar.gz

编译安装MySQL，此处需要注意如果编译时中间如果出现缺包的错误，需要将此目录下的CMakeCache.txt删除后重新进行编译。

[root@localhost ~]# cd mariadb-10.2.23
[root@localhost mariadb-10.2.23]# cmake . \
-DCMAKE_INSTALL_PREFIX=/apps/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-DSYSCONFDIR=/etc/mysql \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

[root@localhost mariadb-10.2.23]# make && make install

初始化数据库

[root@localhost mariadb-10.2.23]# cd /apps/mysql/
[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/data/mysql
Installing MariaDB/MySQL system tables in '/data/mysql' ...
OK

放置配置文件并修改

[root@localhost mysql]# mkdir /etc/mysql        #创建MySQL配置文件目录
[root@localhost mysql]# cp support-files/my-huge.cnf /etc/mysql/my.cnf
[root@localhost mysql]# sed -i '/\[mysqld\]/adatadir=/data/mysql' /etc/mysql/my.cnf

配置服务启动脚本

[root@localhost mysql]# cp support-files/mysql.service /etc/init.d/mysqld
[root@localhost mysql]# chmod +x /etc/init.d/mysqld

[root@localhost mysql]# chkconfig --add mysqld

启动服务

[root@localhost mysql]# service mysqld start
Starting mysqld (via systemctl):                     [  OK  ]

为MySQL添加环境变量

[root@localhost mysql]# echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost mysql]# . /etc/profile.d/mysql.sh

MySQL的多实例环境搭建

在测试环境中通常会需要在一台主机上搭建多个版本的MySQL，此处以编译安装完毕的MySQL为例，演示如何搭建多实例的MySQL。

为每个实例创建各自的目录并赋予权限（此处以创建2个实例为例）

[root@localhost /]# mkdir -pv /mysql/{3306,3307}/{data,etc,socket,bin,log,pid}
[root@localhost ~]# chown -R mysql.mysql /mysql
[root@localhost ~]# tree /mysql                 #查看下目录结构
/mysql
├── 3306
│   ├── bin
│   ├── data
│   ├── etc
│   ├── log
│   ├── pid
│   └── socket
└── 3307
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

为每个实例初始化数据库

#为3306数据库初始化数据库
[root@localhost /]# /app/mysql/scripts/mysql_install_db --user=mysql --datadir=/mysql/3306/data
#为3307数据初始化数据库
[root@localhost /]# /app/mysql/scripts/mysql_install_db --user=mysql --datadir=/mysql/3307/data

为每个实例创建配置文件，配置文件可以参考/etc/my.cnf进行修改

[root@localhost /]# cp /etc/my.cnf /mysql/3306/etc/my.cnf       #复制/etc/my.cnf到多实例的etc目录下
[root@localhost /]# vim /mysql/3306/etc/my.cnf                  #修改my.cnf，写入配置
[mysqld]                                                        #注意需要将#!includedir /etc/my.cnf.d注释
port=3306                           #此处实例1的端口为3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid

#将3306上配置完成的配置文件复制到3307上并做修改
[root@localhost /]# cp /mysql/3306/etc/my.cnf /mysql/3307/etc/
[root@localhost /]# sed -i 's/3306/3307/' /mysql/3307/etc/my.cnf    #将实例2配置文件中所有3306部分改为3307

为实例添加服务脚本，此为事先准备好的服务脚本

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}


function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}


function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

将服务启动脚本存放至各实例的bin目录下，并加以修改

[root@localhost /]# vim /mysql/3306/bin/mysqld      #对端口和cmd_path进行修改
port=3306
cmd_path="/app/mysql/bin"

[root@localhost /]# vim /mysql/3307/bin/mysqld      #对端口和cmd_path进行修改
port=3307
cmd_path="/app/mysql/bin"

[root@localhost /]# chmod +x /mysql/3306/bin/mysqld #为服务脚本添加执行权限
[root@localhost /]# chmod +x /mysql/3307/bin/mysqld

关闭编译安装的MySQL服务

[root@localhost /]# ss -tnl |grep 3306
LISTEN     0      80          :::3306                    :::*
[root@localhost /]# service mysqld stop
Stopping mysqld (via systemctl):                      [  OK  ]

启动实例1，2

[root@localhost /]# /mysql/3306/bin/mysqld start
Starting MySQL...
[root@localhost /]# /mysql/3307/bin/mysqld start
Starting MySQL...

[root@localhost /]# ss -tnl | grep 3306
LISTEN     0      80          :::3306                    :::*
[root@localhost /]# ss -tnl | grep 3307
LISTEN     0      80          :::3307                    :::*

连接数据库

#客户端在连接多实例的MySQL时，需要指定端口号和socket文件路径否则报错。
[root@localhost /]# mysql -uroot -p -P3306 -S /mysql/3306/socket/mysql.sock
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.23-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#已经连接上实例1

关闭多实例MySQL服务

#多实例的MySQL服务在关闭时需要输入MySQL管理员的密码，也可以将管理员密码存放在配置文件的变量mysql_pwd=""中
[root@localhost /]# /mysql/3306/bin/mysqld stop
Stoping MySQL...
Enter password:

以上为mariadb多实例的配置方法。

如果觉得文章对你有用，请随意赞赏

SQL