• MySQL/mariadb知识点——安装篇(4)多实例配置


    MySQL的多实例配置

    有时候在一台物理机中需要多个测试环境,那么就有了搭建数据库的多个示例的需求;

    多个实例的意思即:运行多份程序,实例与实例之间没有影响。要注意监听的端口需要不同

    搭建环境:centos7.4,编译安装MariaDB-10.2.15版本,软件目录:/app/mysql/

    1、创建运行的目录环境

    [root@centos7 ~]# mkdir -p /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data,bin}
    [root@centos7 ~]# chown -R mysql:mysql /mysqldb/
    

    2、初始化数据库

    [root@centos7 ~]# cd /app/mysql/
    [root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3306/data/ --user=mysql --basedir=/app/mysql/ 
    [root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3307/data/ --user=mysql --basedir=/app/mysql/
    [root@centos7 mysql]# scripts/mysql_install_db --datadir=/mysqldb/3308/data/ --user=mysql --basedir=/app/mysql/
    

      以上为编译安装,安装目录为/app/mysql/ 需要先进入软件的安装目录然后执行初始化脚本,如果是yum安装的包,则直接运行mysql_install_db

    3、提供配置文件并按需要修改

    [root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3306/etc/my.cnf
    [root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3307/etc/my.cnf
    [root@centos7 mysql]# cp support-files/my-huge.cnf /mysqldb/3308/etc/my.cnf
    
    [root@centos7 mysqldb]# cd /mysqldb/
    [root@centos7 mysqldb]# vim 3306/etc/my.cnf
    [mysqld]
    port        = 3306
    datadir     = /mysqldb/3306/data
    socket      = /mysqldb/3306/socket/mysql.sock
    [root@centos7 mysqldb]# vim 3307/etc/my.cnf  #按以上配置示例更改
    [root@centos7 mysqldb]# vim 3308/etc/my.cnf
    

    4、提供服务启动脚本

    [root@centos7 ~]# cat mysqld  #脚本示例
    #!/bin/bash
    
    port=3306  #需要修改为当前实例的端口号
    mysql_user="root"
    mysql_pwd=""
    cmd_path="/app/mysql/bin"  #安装目录下的bin
    mysql_basedir="/mysqldb"  #实例数据库文件所在目录
    mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
    
    function_start_mysql()
    {
        if [ ! -e "$mysql_sock" ];then
          printf "Starting MySQL...
    "
          ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
        else
          printf "MySQL is running...
    "
          exit
        fi
    }
    
    
    function_stop_mysql()
    {
        if [ ! -e "$mysql_sock" ];then
           printf "MySQL is stopped...
    "
           exit
        else
           printf "Stoping MySQL...
    "
           ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
       fi
    }
    
    
    function_restart_mysql()
    {
        printf "Restarting MySQL...
    "
        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}
    "
    esac
    

      

    [root@centos7 ~]# cp mysqld /mysqldb/3306/bin/
    [root@centos7 ~]# cp mysqld /mysqldb/3307/bin/
    [root@centos7 ~]# cp mysqld /mysqldb/3308/bin/
    [root@centos7 ~]# vim /mysqldb/3306/bin/mysqld
    port=3306
    [root@centos7 ~]# vim /mysqldb/3307/bin/mysqld
    port=3307
    [root@centos7 ~]# vim /mysqldb/3308/bin/mysqld
    port=3308
    

    5、修改脚本文件权限,防止密码被别人看到

    [root@centos7 ~]# chmod 700 /mysqldb/3306/bin/mysqld 
    [root@centos7 ~]# chmod 700 /mysqldb/3307/bin/mysqld  
    [root@centos7 ~]# chmod 700 /mysqldb/3308/bin/mysqld 
    

    6、启动服务

    [root@centos7 ~]# service mysqld stop  #保证自己原来的服务停止,释放3306端口
    [root@centos7 ~]# /mysqldb/3306/bin/mysqld start  #启动服务
    [root@centos7 ~]# /mysqldb/3307/bin/mysqld start
    [root@centos7 ~]# /mysqldb/3308/bin/mysqld start
    [root@centos7 ~]# ss -tnl  #如果看到三个实例监听的端口都打开后说明服务启动正常
    LISTEN 0 80 :::3306 :::*
    LISTEN 0 80 :::3307 :::*
    LISTEN 0 80 :::3308 :::*
    

    7、连接测试

    [root@centos7 ~]# mysql -S /mysqldb/3306/socket/mysql.sock  #使用-S指定套接字文件
    Server version: 10.2.15-MariaDB-log Source distribution
    MariaDB [(none)]> show variables like '%port';  #查看端口是否是3306
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | extra_port          | 0     |
    | large_files_support | ON    |
    | port                | 3306  |
    | report_port         | 3306  |
    +---------------------+-------+
    4 rows in set (0.00 sec)
    
    [root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock  #再连接测试一下3307和3308
    Server version: 10.2.15-MariaDB-log Source distribution
    MariaDB [(none)]> show variables like '%port';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | extra_port          | 0     |
    | large_files_support | ON    |
    | port                | 3307  |
    | report_port         | 3307  |
    +---------------------+-------+
    4 rows in set (0.00 sec)
    
    [root@centos7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock
    Server version: 10.2.15-MariaDB-log Source distribution
    MariaDB [(none)]> show variables like '%port';
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | extra_port          | 0     |
    | large_files_support | ON    |
    | port                | 3308  |
    | report_port         | 3308  |
    +---------------------+-------+
    4 rows in set (0.00 sec)
    

    *8、停止实例

    [root@centos7 ~]# /mysqldb/3306/bin/mysqld stop
    
  • 相关阅读:
    gearman任务分发改进
    gearman实现任务分发
    BeanStalkd 做队列服务
    Tomcat各种日志的关系与catalina.out文件的分割
    数据库系统原理-关系数据库的规范化理论总结
    MySQL配置参数innodb_flush_log_at_trx_commit
    gRPC快速入门
    使用vagrant和kubeadm搭建k8s集群
    VS项目属性中的C/C++运行库:MT、MTd、MD、MDd
    消除C++中警告代码
  • 原文地址:https://www.cnblogs.com/Gmiaomiao/p/9173912.html
Copyright © 2020-2023  润新知