MySQL的二进制、编译方式安装方法及多实例实现

2022-03-24 05:54:36

1 MySQL

1.1 MySQL 介绍

1.1.1 MySQL 历史

1979年：TcX公司 Monty Widenius，Unireg
1996年：发布MySQL1.0，Solaris版本，Linux版本
1999年：MySQL AB公司，瑞典
2003年：MySQL 5.0版本，提供视图、存储过程等功能
2008年：Sun 10亿美元收购MySQL
2009年：Oracle 75亿美元收购sun
2009年：Monty成立MariaDB

1.1.2 MySQL系列

1.1.2.1 MySQL 的三大主要分支

mysql
mariadb
percona Server

1.1.2.2 官方网址

https://www.mysql.com/
http://mariadb.org/
https://www.percona.com

1.1.2.3 官方文档

https://dev.mysql.com/doc/
https://mariadb.com/kb/en/
https://www.percona.com/software/mysql-database/percona-server

1.1.2.4 版本演变

MySQL：5.1 --> 5.5 --> 5.6 --> 5.7 -->8.0
MariaDB：5.5 -->10.0--> 10.1 --> 10.2 --> 10.3 --> 10.4 --> 10.5

1.1.3 MYSQL的特性

插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择，Mysql5.5.5开始innoDB引擎是MYSQL默认引擎

MyISAM ==> Aria

InnoDB ==> XtraDB
单进程，多线程
诸多扩展和新特性
提供了较多测试组件
开源

1.2 MySQL 安装方式介绍和快速安装

1.2.1 安装方式介绍

源代码：编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
程序包管理器管理的程序包

1.2.2 RPM包安装MySQL

CentOS 安装光盘
项目官方：https://downloads.mariadb.org/mariadb/repositories/
国内镜像：https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/

? https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/
CentOS 8：安装光盘直接提供

mysql-server：8.0

mariadb-server : 10.3.17
CentOS 7：安装光盘直接提供

mariadb-server：5.5 服务器包

mariadb 客户端工具包
CentOS 6：

mysql-server：5.1 服务器包

mysql 客户端工具包

1.3 初始化脚本提高安全性

运行脚本：mysql_secure_installation

设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库

1.4 MySQL 组成

1.4.1 客户端程序

mysql: 交互式的CLI工具
mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中
mysqladmin：基于mysql协议管理mysqld
mysqlimport：数据导入工具

MyISAM存储引擎的管理工具：
myisamchk：检查MyISAM库
myisampack：打包MyISAM表，只读

1.4.2 服务器端程序

mysqld_safe
mysqld
mysqld_multi 多实例，示例：mysqld_multi --example

1.4.3 用户账号

mysql用户账号由两部分组成：

? ‘USERNAME‘@‘HOST‘

说明：

? HOST限制此用户可通过哪些远程主机连接mysql服务器

支持使用通配符：

? % 匹配任意长度的任意字符

? 172.16.0.0/255.255.0.0 或 172.16.%.%

? _ 匹配任意单个字符

1.4.4 mysql 客户端命令

1.4.4.1 mysql 运行命令类型

客户端命令：本地执行，每个命令都完整形式和简写格式

mysql> \h, help
mysql> \u, use
mysql> \s, status
mysql> \!, system

MariaDB [hellodb]> status;
MariaDB [hellodb]> \s;
--------------
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          10
Current database:       hellodb
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ‘‘
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.17-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /mysql/3306/socket/mysql.sock
Uptime:                 3 min 57 sec

服务端命令：通过mysql协议发往服务器执行并取回结果，命令末尾都必须使用命令结束符号，默认为分号

mysql> SELECT VERSION();
MariaDB [none]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.3.17-MariaDB |
+-----------------+
1 row in set (0.001 sec)

1.4.4.2 mysql 使用模式

交互模式：

脚本模式：

mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
cat /path/somefile.sql | mysql –uUSERNAME -pPASSWORD

mysql> source /path/from/somefile.sql

1.4.4.3 mysql命令使用格式

mysql [OPTIONS] [database]

mysql客户端常用选项：

-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e “SQL“ 执行SQL命令
-V, --version 显示版本
-v --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置

登录系统：

#默认空密码登录
mysql –uroot –p
mysql
#密码登录
mysql -uroot -pPASSWORD
#socket登录
mysql -uroot -pPASSWORD -S /mysql/3306/socket/mysql.socket
#远程登录
mysql -hIP -uroot -pPASSWORD

运行mysql命令：

mysql> use mysql;
mysql> select user();   #查看当前用户
mysql> SELECT User,Host,Password FROM user;

范例：mysql的配置文件，修改提示符

#查看mysql版本
[root@c8-mysql ~]# mysql -V
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1

#临时修改mysql提示符
[root@c8-mysql ~]# mysql -uroot  -pmagedu --prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_" 
10:49:59(root@localhost) [(none)]>

#临时修改mysql提示符
[root@c8-mysql ~]# export MYSQL_PS1="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"
[root@c8-mysql ~]# mysql -uroot -pmagedu
10:51:01(root@localhost) [(none)]>

#持久修改mysql提示符
[root@c8-mysql ~]# cat /etc/my.cnf.d/mysql-clients.cnf
[mysql]
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"
[root@c8-mysql ~]# mysql -uroot -pmagedu
10:54:14(root@localhost) [(none)]>

范例：配置客户端mysql的自动登录

[root@c8-mysql ~]# vim/etc/my.cnf.d/client.conf
[client]
user=wang
password=magedu

[root@c8-mysql ~]# mysql
10:55:59(root@localhost) [(none)]> exit

1.4.4.4 mysqladmin命令

mysqladmin 命令格式

mysqladmin [OPTIONS] command command....

范例：

#查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -uroot -pcentos ping
[root@c8-mysql ~]# mysqladmin -uroot -pmagedu ping      
mysqld is alive

#关闭mysql服务，但mysqladmin命令无法开启
mysqladmin –uroot –pcentos shutdown
[root@c8-mysql ~]# mysqladmin –uroot –pmagedu shutdown

#创建数据库testdb
mysqladmin -uroot –pcentos create testdb
[root@c8-mysql ~]# mysqladmin -uroot –pmagedu create testdb

#删除数据库testdb
mysqladmin -uroot -pcentos drop testdb
[root@c8-mysql ~]# mysqladmin -uroot –pmagedu drop testdb

Do you really want to drop the ‘testdb‘ database [y/N] y
Database "testdb" dropped

#修改root密码
mysqladmin –uroot –pcentos password ‘magedu‘
[root@c8-mysql ~]# mysqladmin -uroot –pmagedu password ‘centos‘
[root@c8-mysql ~]# mysql -uroot -pmagedu
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)

#日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs

1.4.4.5 服务器端配置

服务器端(mysqld)：工作特性有多种配置方式

1、命令行选项：

2、配置文件：类ini格式,集中式的配置，能够为mysql的各应用程序提供配置信息

服务器端配置文件：

/etc/my.cnf.d/mariadb-server.cnf

/etc/my.cnf         #Global选项

/etc/mysql/my.cnf   #Global选项

~/.my.cnf           #User-specific 选项

配置文件格式：

[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]

[server]
[client]
#格式：parameter = value
#说明：_和- 相同
#1，ON，TRUE意义相同， 0，OFF，FALSE意义相同

1.4.4.6 socket地址

服务器监听的两种socket地址：

ip socket: 监听在tcp的3306端口，支持远程通信，侦听3306/tcp端口可以在绑定有一个或全部接口IP上
unix sock: 监听在sock文件上，仅支持本机通信, 如：/var/lib/mysql/mysql.sock)

说明：host为localhost 时自动使用unix sock

1.4.4.7 关闭mysqld网络连接

只侦听本地客户端，所有客户端和服务器的交互都通过一个socket文件实现，socket的配置存放在/var/lib/mysql/mysql.sock）可在/etc/my.cnf修改

范例：

vim /etc/my.cnf
[mysqld]
skip-networking=1

2 二进制格式安装mysql

2.1 二进制安装5.6

#1 创建用户和组，创建mysql目录，并设置mysql权限
[root@c7-mysql soft]# groupadd -r -g 306 mysql
[root@c7-mysql soft]# useradd -r -g 306 -u 306 -d /data/mysql mysql
[root@c7-mysql soft]# mkdir /data/mysql -p
[root@c7-mysql soft]# chown mysql.mysql /data/mysql
#解压缩到/usr/local目录中，二进制安装必须放在此目录下
[root@c7-mysql soft]# tar -xf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@c7-mysql soft]# cd /usr/local
#设置软链接，并设置权限
[root@c7-mysql local]# ln -s mysql-5.6.51-linux-glibc2.12-x86_64 mysql
[root@c7-mysql local]# ll
lrwxrwxrwx  1 root root  35 May 23 20:28 mysql -> mysql-5.6.51-linux-glibc2.12-x86_64
[root@c7-mysql local]# ll mysql/        #这个目录下有些文件不是root权限管理

[root@c7-mysql local]# chown -R root.root mysql/

#2准备配置文件
[root@c7-mysql local]# cat >/etc/my.cnf <<EOF
[mysqld]
datadir=/data/mysql
skip_name_resolve = on #禁止主机名解析，建议使用
EOF
#3 创建数据库文件
[root@c7-mysql local]# /usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql --user=mysql
#提示没找到/usr/bin/perl文件，需要安装perl-Data-Dumper
-bash: /usr/local/mysql/scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory
[root@c7-mysql local]# cd mysql/scripts/
[root@c7-mysql scripts]# cd ..
[root@c7-mysql mysql]# ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
-bash: ./scripts/mysql_install_db: /usr/bin/perl: bad interpreter: No such file or directory

[root@c7-mysql yum.repos.d]# yum install perl-Data-Dumper -y

#使用绝对路径创建数据库文件，会提示无bin/my_print_defaults文件，需要进入/usr/local/mysql目录执行
[root@c7-mysql yum.repos.d]# /usr/local/mysql/scripts/mysql_install_db --datadir=/data/mysql --user=mysql
FATAL ERROR: Could not find ./bin/my_print_defaults

[root@c7-mysql yum.repos.d]# cd /usr/local/mysql
[root@c7-mysql mysql]# ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
Installing MySQL system tables...

#准备服务脚本并启动服务
[root@c7-mysql mysql]# cp ./support-files/mysql.server /etc/init.d/mysqld
[root@c7-mysql mysql]# chkconfig --add mysqld
[root@c7-mysql mysql]# service mysqld start
Starting MySQL.Logging to ‘/data/mysql/c7-client.err‘.
 SUCCESS!
#设置PATH路径
[root@c7-mysql mysql]# echo ‘PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
[root@c7-mysql mysql]# . /etc/profile.d/mysql.sh
#安全初始化
[root@c7-mysql mysql]# mysql_secure_installation
...省略...
Thanks for using MySQL!
Cleaning up...
[root@c7-mysql mysql]# mysql -uroot -pmagedu
mysql> exit
Bye
#已加入到开机启动
[root@c7-mysql mysql]# chkconfig --list|grep mysqld
mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

2.2 centos7和centos8一键安装5.6

#1 创建用户和组，创建mysql目录，并设置mysql权限
groupadd -r -g 306 mysql
useradd -r -g 306 -u 306 -d /data/mysql mysql
mkdir /data/mysql -p
chown mysql.mysql /data/mysql
#解压缩到/usr/local目录中，二进制安装必须放在此目录下
tar -xf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz -C /usr/local
cd /usr/local
#设置软链接，并设置权限
ln -s mysql-5.6.51-linux-glibc2.12-x86_64 mysql
chown -R root.root mysql/

#2准备配置文件
cat >/etc/my.cnf <<EOF
[mysqld]
datadir=/data/mysql
skip_name_resolve = on
EOF
#3 创建数据库文件
yum install perl-Data-Dumper -y

#使用绝对路径创建数据库文件，会提示无bin/my_print_defaults文件，需要进入/usr/local/mysql目录执行
cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
#准备服务脚本并启动服务
cp ./support-files/mysql.server /etc/init.d/mysqld
service mysqld start

#设置PATH路径
echo ‘PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh
#安全初始化
mysql_secure_installation

[root@c7-mysql mysql]# mysql -uroot -pmagedu
mysql> exit
Bye

#centos7加入到开机启动
chkconfig --add mysqld

#centos8设置开机启动，添加mysqld到rc.local，并添加rc.local的执行权限
cat >>/etc/rc.local<<EOF
/etc/init.d/mysqld start
EOF
chmod +x /etc/rc.local

2.3 实战案例：centos7和centos8一键安装mysql-5.6二进制包的脚本

#!/bin/bash
#mysql-install.sh
DIR=`pwd`
NAME="mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz"
FULL_NAME=${DIR}/${NAME}
DATA_DIR="/data/mysql"
yum install libaio perl-Data-Dumper vim gcc gcc-c++ wget autoconf net-tools lrzsz -y
yum install curl libncurses* policycoreutils openssh-server openssh-clients postfix -y
if [ -f ${FULL_NAME} ];then
        echo "安装文件存在"
else
        echo "安装文件不存在"
exit 3
fi

if [ -h /usr/local/mysql ];then
        echo "Mysql 已经安装"
exit 3
else
        tar xf ${FULL_NAME} -C /usr/local
        MYSQL_DIR=`echo $NAME| sed -nr ‘s/^(.*[0-9]).*/\1/p‘`
        ln -s /usr/local/$MYSQL_DIR/ /usr/local/mysql
        if id mysql;then
                echo "mysql 用户已经存在，跳过创建用户过程"
        else
                useradd -r -s /sbin/nologin mysql
        fi
        if id mysql;then
                chown -R root.root /usr/local/mysql/
        if [ ! -d /data/mysql ];then
                mkdir -pv /data/mysql && chown -R mysql.mysql /data/mysql/
                cd /usr/local/mysql
                ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql
                cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
                chmod a+x /etc/init.d/mysqld
                cp ${DIR}/my.cnf /etc/my.cnf
                echo ‘PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
                [ -f /etc/profile.d/mysql.sh ] && source /etc/profile.d/mysql.sh
                service mysqld start
#centos8或7开机启动，可以统一设置成
    echo "/etc/init.d/mysqld start" >>/etc/rc.local
    chmod +x /etc/rc.local
#centos7开机启动也可使用如下命令
#   chkconfig --add mysqld
        else
                echo "MySQL数据目录已经存在"
        exit 3
        fi
        fi
fi

#my.cnf
[mysqld]
socket=/data/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/data/mysql/mysql.pid

2.4 centos8下二进制安装5.7

[root@c8-mysql soft]# tar -xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@c8-mysql soft]# yum -y install libaio numactl-libs
[root@c8-mysql local]# ln -s mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
[root@c8-mysql local]# chown -R root.root /usr/local/mysql

[root@c8-mysql local]# echo ‘PATH=/usr/local/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
[root@c8-mysql local]# . /etc/profile.d/mysql.sh
[root@c8-mysql local]# cp /etc/my.cnf{,.bak}
[root@c8-mysql local]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@c8-mysql local]# mysqld --initialize --user=mysql --datadir=/data/mysql
[root@c8-mysql local]# cat /data/mysql/mysql.log
2021-05-23T13:29:36.951263Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-23T13:29:37.149993Z 1 [Note] A temporary password is generated for root@localhost: FCj%;67giVOb
[root@c8-mysql local]# ls /usr/local/mysql/support-files/
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@c8-mysql local]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@c8-mysql local]# ll /etc/init.d/mysqld
-rwxr-xr-x 1 root root 10576 May 23 21:30 /etc/init.d/mysqld
[root@c8-mysql local]# service mysqld start
Starting MySQL. SUCCESS!
[root@c8-mysql local]# ss -nlt
State            Recv-Q      Send-Q      Local Address:Port     Peer Address:Port
LISTEN           0           128               0.0.0.0:22            0.0.0.0:*
LISTEN           0           128                  [::]:22               [::]:*
LISTEN           0           80                      *:3306                *:*

[root@c8-mysql local]# mysql -uroot -p‘FCj%;67giVOb‘
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@c8-mysql ~]# yum install -y libncurses*

[root@c8-mysql ~]# mysqladmin -uroot -p‘FCj%;67giVOb‘ password magedu
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@c8-mysql ~]# mysql -uroot -pmagedu
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.33 MySQL Community Server (GPL)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> exit
Bye

#设置开机启动，添加mysqld到rc.local，并添加rc.local的执行权限
[root@c8-mysql ~]# cat /etc/rc.local
touch /var/lock/subsys/local
/etc/init.d/mysqld start
[root@c8-mysql etc]# chmod +x /etc/rc.local
[root@c8-mysql ~]# ll /etc/rc.local     #可执行权限才能开机执行里面的命令
lrwxrwxrwx. 1 root root 13 Apr 24  2020 /etc/rc.local -> rc.d/rc.local

2.5 centos7、8一键安装mysql5.7

#!/bin/bash
#
#********************************************************************
#Author:        wangxiaochun
#QQ:            29308620
#Date:          2020-02-12
#FileName：      install_mysql5.7_for_centos.sh
#URL:           http://www.magedu.com
#Description：       The test script
#Copyright (C):     2020 All rights reserved
#********************************************************************

. /etc/init.d/functions 
SRC_DIR=`pwd`
MYSQL=‘mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz‘
COLOR="echo -e \\033[01;31m"
END=‘\033[0m‘
MYSQL_ROOT_PASSWORD=magedu

check (){
cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
        $COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
    return
fi
} 

install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
     yum  -y -q install libaio libncurses* numactl-libs &> /dev/null
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr ‘s/^(.*[0-9]).*/\1/p‘`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R  root.root /usr/local/mysql/
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }

    echo ‘PATH=/usr/local/mysql/bin/:$PATH‘ > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=1
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
    mysqld --initialize --user=mysql --datadir=/data/mysql 
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
#centos7启用chkconfig，centos8取消
#    chkconfig --add mysqld
#    chkconfig mysqld on
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    MYSQL_OLDPASSWORD=`awk ‘/A temporary password/{print $NF}‘ /data/mysql/mysql.log`
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成" 
}

check

install_mysql
#centos8需要添加启动到rc.local中
echo "/etc/init.d mysqld start" >>/etc/rc.local
chmod +x /etc/rc.local

2.6 centos7和8源码编译安装mariadb

建议：内存4G以上

2.6.1 安装相关依赖包

#centos7只需要安装这些就可以
[root@c7-mysql soft]# yum -y install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel

#centos8系统还需要安装make，否则报错
CMake Error: CMake was unable to find a build program corresponding to "Unix Makefiles".  CMAKE_MAKE_PROGRAM is not set.  You probably need to select a different build tool.
CMake Error: CMAKE_C_COMPILER not set, after EnableLanguage
CMake Error: CMAKE_CXX_COMPILER not set, after EnableLanguage
-- Configuring incomplete, errors occurred!
See also "/soft/mariadb-10.5.9/CMakeFiles/CMakeOutput.log".

[root@c8-mysql soft]# yum -y install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel make

2.6.2 做准备用户和数据目录

useradd -r -s /sbin/nologin -d /data/mysql mysql

2.6.3 准备数据库目录

mkdir /data/mysql -p
chown mysql.mysql /data/mysql

2.6.4 源码编译安装

编译安装说明
利用cmake编译，而利用传统方法，cmake的重要特性之一是其独立于源码(out-of-source)的编译功能，即编译工作可以在另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影响，因此在同一个源码树上可以进行多次不同的编译，如针对于不同平台编译。
编译选项:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

2.6.4.1 下载并解压缩源码包

#测试两个版本
tar xvf mariadb-10.2.38.tar.gz
tar xvf mariadb-10.5.9.tar.gz

2.6.4.2 源码编译安装mariadb（以10.5.9为例）

cd mariadb-10.5.9/
cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql -DMYSQL_DATADIR=/data/mysql/ -DSYSCONFDIR=/etc/ -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
#多颗cpu同时运行
make -j 8 && make install; date

提示：如果出错，执行rm -f CMakeCache.txt

2.6.5 准备环境变量

echo ‘PATH=/app/mysql/bin:$PATH‘ > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

2.6.6 生成数据库文件

cd /app/mysql/
scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql

[root@c8-mysql mysql]# scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
Installing MariaDB/MySQL system tables in ‘/data/mysql/‘ ...
OK

2.6.7 准备配置文件

# mariadb-10.2.38，有my-huge.cnf文件
cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf
# mariadb-10.5.9，没有这个文件，使用wsrep.cnf文件
cp /app/mysql/support-files/wsrep.cnf /etc/my.cnf

2.6.8 准备启动脚本,并启动服务

cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld
service mysqld start

#centos7可以使用chkconfig添加自启动
chkconfig --add mysqld
#centos8需要把mysqld启动添加的rc.local中
echo "/etc/init.d/mysqld start" >>/etc/rc.local
chmod +x /etc/rc.local

2.6.9 安全初始化

mysql_secure_installation

#以centos8为例
[root@c8-mysql mysql]# mysql_secure_installation
...省略...
Thanks for using MariaDB!

[root@c8-mysql mysql]# mysql -uroot -pmagedu
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.2.38-MariaDB-log 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.

MariaDB [(none)]> exit
Bye

[root@c8-mysql ~]# ss -ntl
State        Recv-Q       Send-Q           Local Address:Port     Peer Address:Port
LISTEN            0       128                    0.0.0.0:22            0.0.0.0:*
LISTEN            0       80                     0.0.0.0:3306          0.0.0.0:*
LISTEN            0       128                       [::]:22               [::]:*

#mariadb10.5.9默认设置root和mysql本地socket登录不需要密码，修改密码可以通过mysql_secure_installation命令，也可以登录到mysql后，使用set命令修改
[root@c8-mysql ~]# mysql    #本地socket登录不需要密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.5.9-MariaDB Source distribution

MariaDB [(none)]> set password for ‘root‘@‘localhost‘ = password(‘magedu‘);
Query OK, 0 rows affected (0.021 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

[root@c8-mysql mysql]# mysql -h127.0.0.1 -uroot -pcentos
ERROR 1698 (28000): Access denied for user ‘root‘@‘localhost‘
[root@c8-mysql mysql]# mysql -h127.0.0.1 -uroot -pmagedu
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 10.5.9-MariaDB Source distribution

MariaDB [(none)]> exit
Bye
[root@c8-mysql mysql]#

3 CentOS 7和8 实现MySQL的多实例

3.1 实验目的

#centos7和8系统实现mysql多实例基本相同，以centos8为例
CentOS 8 yum安装mariadb-10.3.17并实现三个实例

3.2 环境要求

一台系统CentOS 8.X主机

3.3 前提准备

关闭SElinux
关闭防火墙
时间同步

3.4 实现步骤

3.4.1 安装mariadb(以mariadb10.3为例)

#centos7yum安装的是mariadb5.5版本
#centos8yum安装的是mariadb10.3版本
[root@c8-mysql soft]# yum install mariadb-server

3.4.2 准备三个实例的目录

[root@c8-mysql soft]# mkdir -pv /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
[root@c8-mysql soft]# chown -R mysql.mysql /mysql
[root@c8-mysql soft]# tree -d /mysql
/mysql
├── 3306
│?? ├── bin
│?? ├── data
│?? ├── etc
│?? ├── log
│?? ├── pid
│?? └── socket
├── 3307
│?? ├── bin
│?? ├── data
│?? ├── etc
│?? ├── log
│?? ├── pid
│?? └── socket
└── 3308
    ├── bin
    ├── data
    ├── etc
    ├── log
    ├── pid
    └── socket

21 directories

3.4.3 生成数据库文件

[root@c8-mysql soft]# mysql_install_db --datadir=/mysql/3306/data --user=mysql
[root@c8-mysql soft]# mysql_install_db --datadir=/mysql/3307/data --user=mysql
[root@c8-mysql soft]# mysql_install_db --datadir=/mysql/3308/data --user=mysql

3.4.4 准备配置文件

[root@c8-mysql soft]# cat /mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock
log-error=/mysql/3306/log/mysql.log
pid-file=/mysql/3306/pid/mysql.pid

[root@c8-mysql soft]# cp /mysql/3306/etc/my.cnf /mysql/3307/etc/
[root@c8-mysql soft]# cp /mysql/3306/etc/my.cnf /mysql/3308/etc/
[root@c8-mysql soft]# sed -i ‘s/3306/3307/‘ /mysql/3307/etc/my.cnf
[root@c8-mysql soft]# sed -i ‘s/3306/3308/‘ /mysql/3308/etc/my.cnf

3.4.5 准备启动脚本

[root@c8-mysql soft]# cat /mysql/3306/bin/mysqld
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="magedu"
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

[root@c8-mysql soft]# chmod +x /mysql/3306/bin/mysqld
[root@c8-mysql soft]# cp /mysql/3306/bin/mysqld /mysql/3307/bin/
[root@c8-mysql soft]# cp /mysql/3306/bin/mysqld /mysql/3308/bin/
[root@c8-mysql soft]# sed -i ‘s/3306/3307/‘ /mysql/3307/bin/mysqld
[root@c8-mysql soft]# sed -i ‘s/3306/3308/‘ /mysql/3308/bin/mysqld

3.4.6 启动服务

[root@c8-mysql ~]# /mysql/3306/bin/mysqld start
Starting MySQL...
[root@c8-mysql ~]# /mysql/3307/bin/mysqld start
Starting MySQL...
[root@c8-mysql ~]# /mysql/3308/bin/mysqld start
Starting MySQL...
[root@c8-mysql ~]# ss -nlt
State             Recv-Q    Send-Q      Local Address:Port          Peer Address:Port
LISTEN            0         128               0.0.0.0:22                 0.0.0.0:*
LISTEN            0         128                  [::]:22                    [::]:*
LISTEN            0         80                      *:3306                     *:*
LISTEN            0         80                      *:3307                     *:*
LISTEN            0         80                      *:3308                     *:*

#启动后，会在相应的目录中生成mysql.sock和mysql.pid文件，服务停止后，会自动删除

3.4.7 登录实例

#两种登录方式，一是使用socket登录，首次登录没有密码
[root@c8-mysql ~]# mysql -uroot -S /mysql/3306/socket/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server

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

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

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ‘‘
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.17-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /mysql/3306/socket/mysql.sock       #查看socket项
Uptime:                 20 sec

Threads: 7  Questions: 4  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.200
--------------

MariaDB [(none)]> show variables like ‘port‘;   #查看端口号
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.006 sec)

MariaDB [(none)]>exit

#二是使用IP+端口号登录
[root@c8-mysql ~]# mysql -h127.0.0.1 -P3307
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server

MariaDB [(none)]> show variables like ‘port‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3307  |
+---------------+-------+
1 row in set (0.006 sec)

MariaDB [(none)]>exit
Bye

3.4.8 修改root密码

#直接修改密码
[root@c8-mysql ~]# mysqladmin -uroot -S /mysql/3306/socket/mysql.sock password ‘magedu‘
[root@c8-mysql ~]# mysqladmin -uroot -S /mysql/3307/socket/mysql.sock password ‘magedu‘
#使用密码登录
[root@c8-mysql ~]# mysql -uroot -pmagedu -S /mysql/3306/socket/mysql.sock
[root@c8-mysql ~]# mysql -uroot -p -S /mysql/3307/socket/mysql.sock
Enter password:

#首次登录后修改密码
[root@c8-mysql ~]# mysql -h127.0.0.1 -P3308
MariaDB [(none)]> update mysql.user set password=password(‘magedu‘) where user=‘root‘;
Query OK, 4 rows affected (0.001 sec)
Rows matched: 4  Changed: 4  Warnings: 0
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> exit
Bye
[root@c8-mysql ~]# mysql -h127.0.0.1 -P3308
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
[root@c8-mysql ~]# mysql -h127.0.0.1 -P3308 -uroot -pmagedu
MariaDB [(none)]> exit
Bye