system service►Atlassian►mariadb install

Atlassian mariadb install

系统初始化

1
2
3
4
5
6
7
8
9
10
本次使用mariadb作为MySQL数据库软件安装使用:
首先简绍一下mairadb优点,当然这些MySQL也在开发出来
1.并行复制,在从库中距日志回放,可以配置多个sql线程并行执行
2.多源复制,可以将多个mysql实例中中数据汇集到一个实例中,报表库非常实用
3.连接线程池,高并发下使用线程池维护连接线程能很好的降低资源负载
4.使用GTID,故障恢复更加快捷
5.MySQL是只适应哈希索引.Mariadb支持更好的hash join

上面好多特征MySQL5.6,MySQL5.7,MySQL7.0等也在逐渐提供,且有一些也在开源社区可以下载插件.但是就目前来说mariadb是一个不错的选择.
版本 mariadb-10.0.14不是最新版本,是一个很稳定且上述特征都已经提供

  1. 服务器信息

    1
    2
    10.10.10.13 mariadb.master
    10.10.10.14 mariadb.slave

  2. 查看系统版本

    1
    2
    [root@localhost ~]# cat /etc/redhat-release
    CentOS Linux release 7.3.1611 (Core)

  3. 设置主机名

    1
    2
    3
    4
    5
    6
    7
    8
    9
    //mariadb13
    [root@localhost ~]# hostname mariadb13 && echo mariadb13 | tee /etc/hostname              
    [root@localhost ~]# echo '10.10.10.13 mariadb13' |tee -a /etc/hosts
    [root@localhost ~]# $SHELL

    //mariadb14
    [root@localhost ~]# hostname mariadb14 && echo mariadb14 | tee /etc/hostname  
    [root@localhost ~]# echo '10.10.10.14 mariadb14' |tee -a /etc/hosts  
    [root@localhost ~]# $SHELL

  4. 硬盘格式化挂载

    1
    2
    3
    [root@[x] ~]# mkfs.xfs /dev/vdb
    [root@[x] ~]# echo '/dev/vdb /mnt xfs defaults 0 0' | tee -a /etc/fstab
    [root@[x] ~]# mount -a

  5. 设置打开最大文件数

    1
    2
    3
    [root@[x] ~]# echo '* - nproc  65535' | tee -a /etc/security/limits.conf
    [root@[x] ~]# echo '* - nofile 65535' | tee -a /etc/security/limits.conf
    [root@[x] ~]# ls /etc/security/limits.d/*|xargs rm -f

  6. 设置yum源

    1
    2
    3
    4
    5
    6
    [root@[x] ~]# mkdir /etc/yum.repos.d/backup && mv /etc/yum.repos.d/{*,backup}
    [root@[x] ~]# rpm --import http://yum.ops.cn/epel/RPM-GPG-KEY-EPEL-7
    [root@[x] ~]# curl -o /etc/yum.repos.d/epel.repo http://yum.ops.cn/epel-7.repo
    [root@[x] ~]# rpm --import http://yum.ops.cn/centos/RPM-GPG-KEY-CentOS-7
    [root@[x] ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo http://yum.ops.cn/centos-7.repo
    [root@[x] ~]# yum clean all && yum makecache

  7. 安装基础依赖库和常用工具包

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    [root@[x] ~]# yum -y groupinstall "Development Tools"
    [root@[x] ~]# yum -y install \
    make cmake \
    bison-devel \
    bzip2-devel \
    zlib zlib-devel \
    openssl openssl-devel openssl-libs openssl-static \
    pcre pcre-devel pcre-static \
    ncurses ncurses-devel ncurses-libs \
    curl-devel \
    expat-devel \
    gettext-devel \
    openldap openldap-devel \
    readline readline-devel readline-static \
    libssh2 libssh2-devel \
    unixODBC unixODBC-devel \
    sqlite sqlite-devel \
    tcl tcl-devel \
    perl-Digest-SHA1 \
    python-libs python-devel python2-pip python-crypto \
    perl-libs \
    perl-ExtUtils-MakeMaker \
    GeoIP GeoIP-devel \
    gperftools gperftools-devel gperftools-libs \
    libatomic_ops-devel \
    gtest gtest-devel \
    gdk-pixbuf2 gdk-pixbuf2-deve \
    libffi libffi-devel \
    libcurl libcurl-devel \
    http-parser http-parser-devel \
    libxml2* \
    libmcrypt* \
    libtool-ltdl-devel*
    [root@[x] ~]# yum -y install bash-completion fop lftp ntp ntpdate vim wget telnet dstat tree lrzsz net-tools nmap-ncat nmap sysstat dmidecode bc

  8. 关闭selinux

    1
    2
    [root@[x] ~]# setenforce 0
    [root@[x] ~]# sed -i s/'SELINUX=enforcing'/'SELINUX=disabled'/g /etc/selinux/config

  9. 关闭防火墙

    1
    [root@[x] ~]# systemctl stop firewalld && systemctl disable firewalld

  10. 设置系统时区

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    [root@[x] ~]# ntpdate cn.pool.ntp.org

    [root@[x] ~]# [ -f /etc/localtime ] && cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
    [root@[x] ~]# [ -f /etc/sysconfig/clock ] && echo 'ZONE="Asia/Shanghai"' | tee /etc/sysconfig/clock
    [root@[x] ~]# [ -f /etc/timezone ] && echo 'Asia/Shanghai' | tee /etc/timezone
    [root@[x] ~]# [ -f /etc/sysconfig/ntpd ] && echo 'SYNC_HWCLOCK=yes' | tee -a /etc/sysconfig/ntpd

    [root@[x] ~]# cp -f /etc/{ntp.conf,ntp.conf.bak}
    [root@[x] ~]# cat > /etc/ntp.conf <<EOF
    driftfile /var/lib/ntp/drift
    restrict default nomodify notrap nopeer noquery
    restrict 127.0.0.1
    restrict ::1
    server cn.pool.ntp.org prefer
    server 0.centos.pool.ntp.org iburst
    server 1.centos.pool.ntp.org iburst
    server 2.centos.pool.ntp.org iburst
    server 3.centos.pool.ntp.org iburst
    includefile /etc/ntp/crypto/pw
    keys /etc/ntp/keys
    disable monitor
    EOF

    [root@[x] ~]# cp -f /etc/ntp/{step-tickers,step-tickers.bak}
    [root@[x] ~]# cat > /etc/ntp/step-tickers <<EOF
    cn.pool.ntp.org
    0.centos.pool.ntp.org
    1.centos.pool.ntp.org
    2.centos.pool.ntp.org
    3.centos.pool.ntp.org
    EOF

    [root@[x] ~]# systemctl start ntpd && systemctl enable ntpd

  11. 安装python,并设置python源

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    [root@[x] ~]# git clone https://github.com/yyuu/pyenv.git /usr/local/pyenv

    [root@[x] ~]# echo 'export PYENV_ROOT="/usr/local/pyenv"' | tee /etc/profile.d/pyenv.sh
    [root@[x] ~]# echo 'export PATH="$PYENV_ROOT/bin:$PATH"' | tee -a /etc/profile.d/pyenv.sh
    [root@[x] ~]# echo 'eval "$(pyenv init -)"' | tee -a /etc/profile.d/pyenv.sh
    [root@[x] ~]# source /etc/profile

    [root@[x] ~]# mkdir -p ${PYENV_ROOT}/cache
    [root@[x] ~]# ls ${PYENV_ROOT}/cache/Python-2.7.13.tar.xz  将源码包放到这里
    [root@[x] ~]# pyenv install 2.7.13
    [root@[x] ~]# pyenv local 2.7.13

    [root@[x] ~]# mkdir ~/.pip
    [root@[x] ~]# cat > ~/.pip/pip.conf <<EOF
    [global]
    trusted-host=mirrors.aliyun.com
    index-url=http://mirrors.aliyun.com/pypi/simple/
    [list]
    format=columns
    EOF

  12. 设置开机启动文件权限

    1
    [root@[x] ~]# chmod +x /etc/rc.d/rc.local

  13. 创建常见目录

    1
    [root@[x] ~]# mkdir -p /mnt/{app,data,log,web,ops/{app,data,cron}}

  14. 部署mariadb

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    [root@mariadb[X] ~]# yum -y install wget make cmake gcc gcc-c++ autoconf automake zlib* libxml2* ncurses-devel libmcrypt* libtool-ltdl-devel*
    [root@mariadb[X] ~]# yum -y install keepalived

    [root@mariadb[X] ~]# useradd -s /sbin/nologin mysql

    [root@mariadb[X] ~]# cd /mnt/ops/app
    [root@mariadb[X] app]# tar xzf mariadb-10.0.14.tar.gz
    [root@mariadb[X] app]# cd mariadb-10.0.14
    [root@mariadb[X] mariadb-10.0.14]# cmake -DCMAKE_INSTALL_PREFIX=/mnt/app/mariadb \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_FEDERATED_STORAGE_ENGINE=1 \
    -DWITH_PARTITION_STORAGE_ENGINE=1 \
    -DWITH_READLINE=1 \
    -DENABLED_LOCAL_INFILE=1 \
    -DEXTRA_CHARSETS=all \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DMYSQL_USER=mysql
    [root@mariadb[X] mariadb-10.0.14]# make -j 8
    [root@mariadb[X] mariadb-10.0.14]# make -j 8 install

    [root@mariadb[X] mariadb-10.0.14]# echo 'export MYSQL_BASE=/mnt/app/mariadb' |tee /etc/profile.d/mysql.sh
    [root@mariadb[X] mariadb-10.0.14]# echo 'export MYSQL_BIN=$MYSQL_BASE/bin' |tee -a /etc/profile.d/mysql.sh
    [root@mariadb[X] mariadb-10.0.14]# echo 'export PATH=$MYSQL_BIN:$PATH'     |tee -a /etc/profile.d/mysql.sh
    [root@mariadb[X] mariadb-10.0.14]# source /etc/profile

    [root@mariadb[X] mariadb-10.0.14]# mkdir -p /mnt/{data,log}/mysql/3306
    [root@mariadb[X] mariadb-10.0.14]# chown -R mysql.mysql /mnt/{data,log}/mysql/3306
    [root@mariadb[X] mariadb-10.0.14]# mkdir -p /mnt/app/mariadb/conf

    [root@mariadb[X] mariadb-10.0.14]# chmod +x scripts/*
    [root@mariadb[X] mariadb-10.0.14]# ./scripts/mysql_install_db --basedir=/mnt/app/mysql/ --datadir=/mnt/data/mysql/3306/ --user=mysql

  15. mariadb master

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    [root@mariadb13 mariadb-10.0.14]# cat > /mnt/app/mariadb/conf/3306.cnf <<EOF
    [client]
    port            = 3306
    socket          = /mnt/data/mysql/3306/mysqld.sock

    [mysqld]
    port            = 3306
    socket          = /mnt/data/mysql/3306/mysqld.sock

    datadir         = /mnt/data/mysql/3306/
    basedir         = /mnt/app/mariadb

    default-storage-engine=InnoDB
    expire-logs-days  = 7

    skip-name-resolve
    skip-external-locking
    key_buffer_size         = 128M
    max_allowed_packet      = 64M
    table_open_cache        = 512
    sort_buffer_size        = 2M
    read_buffer_size        = 2M
    read_rnd_buffer_size    = 8M
    myisam_sort_buffer_size = 64M
    thread_concurrency      = 8
    query_cache_type        = 0
    thread_cache_size       = 128

    max_binlog_size         = 512M
    max_connections         = 2000
    max_user_connections    = 1600
    max_connect_errors      = 99999999
    connect_timeout         = 30
    tmp_table_size          = 128M
    max_heap_table_size     = 128M

    log-bin                 = 3306-bin
    binlog_cache_size       = 4M
    slow_query_log          = 1
    slow-query-log-file     = /mnt/log/mysql/3306/ms.slow
    pid_file                = /mnt/data/mysql/3306/mysqld.pid
    log_error               = /mnt/log/mysql/3306/err.log
    general_log_file        = /mnt/log/mysql/3306/gene.log
    long_query_time         = 1
    log-slave-updates
    #binlog_format          = MIXED
    binlog_format           = ROW

    sync-master-info=1
    slave-parallel-threads=20
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    #report-host=IP
    #report-port=3306

    server-id               = 1313
    #read_only              = 1
    #auto_increment_increment=2
    #auto_increment_offset=1

    innodb_file_per_table     = 1
    innodb_data_home_dir      = /mnt/data/mysql/3306
    innodb_log_group_home_dir = /mnt/data/mysql/3306
    innodb_buffer_pool_size   = 6G
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size      = 512M
    innodb_log_buffer_size    = 32M
    innodb_flush_log_at_trx_commit = 2
    #innodb_io_capacity = 500
    #innodb_support_xa = false
    innodb_lock_wait_timeout  = 30
    #transaction-isolation = READ-COMMITTED

    #thread pool
    thread_handling=pool-of-threads

    init-connect = 'set names utf8'
    character_set_server = utf8

    [mysqldump]
    quick
    max_allowed_packet = 64M

    [mysql]
    no-auto-rehash

    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout
    EOF

  16. mariadb slave

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    [root@mariadb14 ~]# cat > /mnt/app/mariadb/conf/3306.cnf <<EOF
    [client]
    port            = 3306
    socket          = /mnt/data/mysql/3306/mysqld.sock

    [mysqld]
    port            = 3306
    socket          = /mnt/data/mysql/3306/mysqld.sock

    datadir         = /mnt/data/mysql/3306/
    basedir         = /mnt/app/mariadb

    default-storage-engine=InnoDB
    expire-logs-days  = 7

    skip-name-resolve
    skip-external-locking
    key_buffer_size         = 128M
    max_allowed_packet      = 64M
    table_open_cache        = 512
    sort_buffer_size        = 2M
    read_buffer_size        = 2M
    read_rnd_buffer_size    = 8M
    myisam_sort_buffer_size = 64M
    thread_concurrency      = 8
    query_cache_type        = 0
    thread_cache_size       = 128

    max_binlog_size         = 512M
    max_connections         = 2000
    max_user_connections    = 1600
    max_connect_errors      = 99999999
    connect_timeout         = 30
    tmp_table_size          = 128M
    max_heap_table_size     = 128M

    log-bin                 = 3306-bin
    binlog_cache_size       = 4M
    slow_query_log          = 1
    slow-query-log-file     = /mnt/log/mysql/3306/ms.slow
    pid_file                = /mnt/data/mysql/3306/mysqld.pid
    log_error               = /mnt/log/mysql/3306/err.log
    general_log_file        = /mnt/log/mysql/3306/gene.log
    long_query_time         = 1
    log-slave-updates
    #binlog_format          = MIXED
    binlog_format           = ROW

    sync-master-info=1
    slave-parallel-threads=20
    binlog-checksum=CRC32
    master-verify-checksum=1
    slave-sql-verify-checksum=1
    #report-host=IP
    #report-port=3306

    server-id               = 1314
    read_only               = 1
    #auto_increment_increment=2
    #auto_increment_offset=1

    innodb_file_per_table     = 1
    innodb_data_home_dir      = /mnt/data/mysql/3306
    innodb_log_group_home_dir = /mnt/data/mysql/3306
    innodb_buffer_pool_size   = 6G
    innodb_additional_mem_pool_size = 20M
    innodb_log_file_size      = 512M
    innodb_log_buffer_size    = 32M
    innodb_flush_log_at_trx_commit = 2
    #innodb_io_capacity = 500
    #innodb_support_xa = false
    innodb_lock_wait_timeout  = 30
    #transaction-isolation = READ-COMMITTED

    #thread pool
    thread_handling=pool-of-threads

    init-connect = 'set names utf8'
    character_set_server = utf8

    [mysqldump]
    quick
    max_allowed_packet = 64M

    [mysql]
    no-auto-rehash

    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout
    EOF

  17. mariadb 启动服务

    1
    [root@mariadb[X] ~]# /mnt/app/mariadb/bin/mysqld_safe --defaults-file=/mnt/app/mariadb/conf/${port}.cnf &

  18. mariadb 连接脚本

    1
    2
    3
    4
    5
    6
    [root@mariadb[X] ~]# cat > /mnt/app/mariadb/bin/mylin <<EOF
    #!/bin/bash
    p=\$1
    shift
    mysql -h"127.0.0.1" -P"\$p" --default-character-set=utf8 --show-warnings -uroot -p'{password}' -A --prompt="(\u@\p)[\d]> " "\$@"
    EOF

  19. keepalived设置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    [root@mariadb13 ~]# cat /etc/keepalived/keepalived.conf
     Configuration File for keepalived

    global_defs {
       router_id HA_10.10.10.13
    }

    vrrp_instance VI_10.10.10.13 {
            state BACKUP
            smtp_alter
            dont_track_primary
            interface eth0
            virtual_router_id 92
            priority 101
            nopreempt
            advert_int 1
            authentication {
               auth_type PASS
               auth_pass 92
            }
            virtual_ipaddress {
                10.10.10.92
            }
    }
    [root@mariadb13 ~]# systemctl start keepalived
    [root@mariadb13 ~]# systemctl enable keepalived

    [root@mariadb14 ~]# cat /etc/keepalived/keepalived.conf
    Configuration File for keepalived

    global_defs {
       router_id HA_10.10.10.14
    }

    vrrp_instance VI_10.10.10.14 {
            state BACKUP
            smtp_alter
            dont_track_primary
            interface eth0
            virtual_router_id 92
            priority 100
            advert_int 1
            authentication {
               auth_type PASS
               auth_pass 92
            }
            virtual_ipaddress {
                10.10.10.92
            }

    }

    [root@mariadb14 ~]# systemctl start keepalived
    [root@mariadb14 ~]# systemctl enable keepalived