Mariadb多实例安装

1.数据库多实例一般在测试环境中使用，生成环境不建议使用多实例

为何要做多实例呢

说白了就是缺机器呗

基于Mariadb源码编译来进行多实例安装

规划多实例目录文件，这里目录用端口号来代替了，容易区分

[root@node6 ~]#mkdir /mysql/{3307,3308,3309} -p[root@node6 ~]#ls /mysql/3303307/ 3308/ 3309/[root@node6 /mysql]#mkdir {3307,3308,3309}/{data,etc,socket,log,pid} -pvmkdir: created directory ‘3307/data’mkdir: created directory ‘3307/etc’mkdir: created directory ‘3307/socket’mkdir: created directory ‘3307/log’mkdir: created directory ‘3307/pid’mkdir: created directory ‘3308/data’mkdir: created directory ‘3308/etc’mkdir: created directory ‘3308/socket’mkdir: created directory ‘3308/log’mkdir: created directory ‘3308/pid’mkdir: created directory ‘3309/data’mkdir: created directory ‘3309/etc’mkdir: created directory ‘3309/socket’mkdir: created directory ‘3309/log’mkdir: created directory ‘3309/pid’#　查看目录结构[root@node6 /mysql]#tree ..├── 3307│   ├── data│   ├── etc│   ├── log│   ├── pid│   └── socket├── 3308│   ├── data│   ├── etc│   ├── log│   ├── pid│   └── socket└── 3309    ├── data    ├── etc    ├── log    ├── pid    └── socket

2.修改目录的所有者为mysql用户

[root@node6 /mysql]#ll total 0drwxr-xr-x 6 root root 53 May  3 09:49 3307drwxr-xr-x 6 root root 53 May  3 09:49 3308drwxr-xr-x 6 root root 53 May  3 09:49 3309[root@node6 /mysql]#chown -R mysql.mysql *[root@node6 /mysql]#ll total 0drwxr-xr-x 6 mysql mysql 53 May  3 09:49 3307drwxr-xr-x 6 mysql mysql 53 May  3 09:49 3308drwxr-xr-x 6 mysql mysql 53 May  3 09:49 3309

3.生成各自的数据库文件

# 先进入到源码或者二进制安装的程序目录下[root@node6 /mysql]#cd /data/mysql/[root@node6 /data/mysql]#./scripts/mysql_install_db --user=mysql --datadir=/mysql/3307/data[root@node6 /data/mysql]#./scripts/mysql_install_db --user=mysql --datadir=/mysql/3308/data[root@node6 /data/mysql]#./scripts/mysql_install_db --user=mysql --datadir=/mysql/3309/data# 查看生成好的数据库文件目录，这里就查看其中一个3307[root@node6 /data/mysqlll /mysql/3307/datatotal 110660-rw-rw---- 1 mysql mysql    16384 May  3 09:56 aria_log.00000001-rw-rw---- 1 mysql mysql       52 May  3 09:56 aria_log_control-rw-rw---- 1 mysql mysql      938 May  3 09:56 ib_buffer_pool-rw-rw---- 1 mysql mysql 12582912 May  3 09:56 ibdata1-rw-rw---- 1 mysql mysql 50331648 May  3 09:56 ib_logfile0-rw-rw---- 1 mysql mysql 50331648 May  3 09:56 ib_logfile1drwx------ 2 mysql root      4096 May  3 09:56 mysql-rw-rw---- 1 mysql mysql    28999 May  3 09:56 mysql-bin.000001-rw-rw---- 1 mysql mysql       19 May  3 09:56 mysql-bin.index-rw-rw---- 1 mysql mysql        7 May  3 09:56 mysql-bin.statedrwx------ 2 mysql mysql       20 May  3 09:56 performance_schemadrwx------ 2 mysql root         6 May  3 09:56 test

4.创建各个数据库的配置文件，以及修改各个配置文件的端口号以及目录

[root@node6 /data/mysql]#cp /etc/my.cnf /mysql/3307/etc/[root@node6 /mysql/3307/etc]#vim my.cnf[mysqld]port=3307datadir=/mysql/3307/socket=/mysql/3307/socket/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/mysql/3307/log/mariadb.logpid-file=/mysql/3307/pid/mariadb.pid## include all files from the config directory##!includedir /etc/my.cnf.d# 把3307上的配置文件复制到后面两个实例上去[root@node6 /mysql/3307/etc]#cp my.cnf /mysql/3308/etc/[root@node6 /mysql/3307/etc]#cp my.cnf /mysql/3309/etc/# 修改其它两个实例的配置文件改成相对应的端口和目录[root@node6 /mysql/3307/etc]#sed -i 's/3307/3308/' /mysql/3308/etc/my.cnf [root@node6 /mysql/3307/etc]#sed -i 's/3307/3309/' /mysql/3309/etc/my.cnf

5.创建服务的启动脚本

[root@node6 /mysql/3307]#vim mysqld#!/bin/bash#chkconfig: 345 80 2port=3307mysql_user="root"mysql_pwd=""cmd_path="/data/mysql/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 instart)    function_start_mysql;;stop)    function_stop_mysql;;restart)    function_restart_mysql;;*)    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"esac#　把3307上的启动脚本复制到3308、3309上去，并把启动端口修改即可，其它不用修改[root@node6 /mysql/3307]#cp mysqld ../3308/[root@node6 /mysql/3307]#cp mysqld ../3309/[root@node6 /mysql/3307]#sed -i 's/3307/3308/' ../3308/mysqld[root@node6 /mysql/3307]#sed -i 's/3307/3309/' ../3309/mysqld

6.启动多实例，并查看相对应的端口是否处于监听状态

# 添加执行权限给mysqld[root@node6 /mysql/3307]#chmod +x mysqld [root@node6 /mysql/3307]#chmod +x ../3308/mysqld [root@node6 /mysql/3307]#chmod +x ../3309/mysqld [root@node6 /mysql/3307]#./mysqld startStarting MySQL...[root@node6 /mysql/3307]#../3308/mysqld start Starting MySQL...[root@node6 /mysql/3307]#../3309/mysqld start Starting MySQL...[root@node6 /mysql/3307]#ss -tnl State      Recv-Q Send-Q                         Local Address:Port                                        Peer Address:Port              LISTEN     0      128                                        *:111                                                    *:*                  LISTEN     0      128                                        *:22                                                     *:*                  LISTEN     0      100                                127.0.0.1:25                                                     *:*                  LISTEN     0      80                                        :::3306                                                  :::*                  LISTEN     0      80                                        :::3307                                                  :::*                  LISTEN     0      80                                        :::3308                                                  :::*                  LISTEN     0      80                                        :::3309                                                  :::*                  LISTEN     0      128                                       :::111                                                   :::*                  LISTEN     0      128                                       :::22                                                    :::*                  LISTEN     0      100                                      ::1:25                                                    :::*

7.连接到多实例上去

[root@node6 /mysql/3307]#mysql -uroot -p -S /mysql/3307/socket/mysql.sock Enter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 8Server version: 10.2.23-MariaDB Source distributionCopyright (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)]> select @@port;+--------+| @@port |+--------+|   3307 |+--------+1 row in set (0.00 sec)# 修改3307实例密码和加固安全，修改之后的密码需要在启动脚本中添加进去[root@node6 /mysql/3307]#mysql_secure_installation -S /mysql/3307/socket/mysql.sock[root@node6 /mysql/3307]#vim mysqld#!/bin/bash#chkconfig: 345 80 2port=3307mysql_user="root"mysql_pwd="123456"

8.把启动脚本拷贝到/etc/init.d/改名为mysqld3307以示区分是哪个实例的启动脚本，并添加到开机启动项里

[root@node6 /mysql/3307]#cp mysqld /etc/init.d/mysqld3307[root@node6 /mysql/3307]#chkconfig --add mysqld3307[root@node6 /mysql/3307]#chkconfig --listmysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:offmysqld3307      0:off   1:off   2:off   3:on    4:on    5:on    6:offnetconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:offnetwork         0:off   1:off   2:on    3:on    4:on    5:on    6:off

后续就可以登录到数据库里进行操作了