基于二进制安装的mariadb实现多实例

 

1、mariadb版本:10.2.23

 

2、二进制安装mariadb的shell脚本

    #!/bin/bash
    id mysql &>/dev/null
    if [ `echo $?` -ne 0 ];then
        userdel -r mysql &>/dev/null
        useradd -r -u 336 -s /sbin/nologin -d /data/mysql mysql &>/dev/null
    else
        useradd -r -u 336 -s /sbin/nologin -d /data/mysql mysql &>/dev/null
    fi 
    rpm -q libaio &>/dev/null
    [ `echo $?` -ne 0 ] && yum -y install libaio
    rpm -q expect &>/dev/null
    [ `echo $?` -ne 0 ] && yum -y install expect
    if [ -e mariadb-10.2.23-linux-x86_64.tar.gz ];then
        #此文件可能会造成影响,所以先清空 
        > /etc/my.cnf
        tar xf mariadb-10.2.23-linux-x86_64.tar.gz -C /usr/local/
        cd  /usr/local/
        ln -s mariadb-10.2.23-linux-x86_64/ mysql
        chown -R root.root /usr/local/mysql/
        echo 'PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh 
        mkdir /data/mysql -p
        chown mysql.mysql /data/mysql/
        cd /usr/local/mysql
        ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql
        mkdir -p /etc/mysql
        cp /usr/local/mysql/support-files/my-huge.cnf /etc/mysql/my.cnf
        #禁止主机名解析,建议使用
        sed -ri '/^\[mysqld\]/askip_name_resolve = on' /etc/mysql/my.cnf
        sed -ri '/^\[mysqld\]/adatadir=\/data\/mysql' /etc/mysql/my.cnf
        cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
        chkconfig --add mysqld
        service mysqld start
        expect <<EOF
            spawn mysql_secure_installation
            expect {
            "Enter current password for root" { send "\n";exp_continue }
            "Set root password" { send "\n";exp_continue }
            "New password" { send "123456\n";exp_continue }
            "Re-enter new password" { send "123456\n";exp_continue }
            "Remove anonymous users" { send "y\n";exp_continue }
            "Disallow root login remotely" { send "y\n";exp_continue }
            "Remove test database and access to it" { send "y\n";exp_continue }
            "Reload privilege tables now" { send "y\n" }
            }
    #expect "]#" { send "exit\n" }
    expect eof
    EOF
    mysqladmin -uroot -p123456 ping &>/dev/null
    [ `echo $?` -eq 0 ] && echo 'mysql is running !' || echo 'mysql is stopped' 
else
    echo -e "mariadb-10.2.23-linux-x86_64.tar.gz is not exist"
    exit
fi

 

3、实现多示例

 

1、创建多实例对应的目录结构

    mkdir -p /mysql/{3306,3307,3308}/{data,etc,socket,log,bin,pid}
    chown -R mysql.mysql /mysql/

 

2、创建多实例的数据库文件

    /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3306/data/ --user=mysql
    /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3307/data/ --user=mysql
    /usr/local/mysql/scripts/mysql_install_db --datadir=/mysql/3308/data/ --user=mysql

 

3、创建对应配置文件

    cp /etc/mysql/my.cnf /mysql/3306/etc/
    vim /mysql/3306/etc/my.cnf
        [mysqld]
        port=3306 加一行                                                                                        
        datadir=/mysql/3306/data
        socket=/mysql/3306/socket/mysql.sock
        [mysqld_safe]
        log-error=/mysql/3306/log/mariadb.log
        pid-file=/mysql/3306/pid/mariadb.pid
    #3307和3308同步修改
        cp /mysql/3306/etc/my.cnf  /mysql/3307/etc/my.cnf   
        /mysql/3307/etc/my.cnf 修改
        cp /mysql/3306/etc/my.cnf  /mysql/3308/etc/my.cnf   
        /mysql/3308/etc/my.cnf 修改   

 

4、准备各实例的启动脚本并添加执行权限

    vi /mysql/{3306,3307,3308}/bin/mysqld   
    cat /mysql/3306/bin/mysqld              
        #!/bin/bash                                                                                                                                                                      
        port=3306                                                                                         
        mysql_user="root"                                                                                 
        mysql_pwd="123456"                                                                                
        #cmd_path="/mysql/3306/bin"                                                                       
        cmd_path="/usr/local/mariadb-10.2.23-linux-x86_64/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                                                                                              
    #添加执行权限
    chmod +x /mysql/{3306,3307,3308}/bin/mysqld

 

5、启动服务并做安全加固(如果不做安全加固,则stop时的脚本有问题,密码不正确)

    /mysql/{3306,3307,3308}/bin/mysqld start
    #安全加固
    /usr/local/mysql/bin/mysql_secure_installation -S /mysql/{3306,3307,3308}/socket/mysql.sock (输入的密码与启动脚本一致)

 

6、测试连接

    mysql -uroot -p123456 -S /mysql/{3306,3307,3308}/socket/mysql.sock