Software

Mysql


Mysql Config Type1

Sngle(Master)

  • 1) server.cnf

    • [server]

    [mysqld]
    datadir=/home/db/mariadb
    log-error=/var/log/mysql/mysqld.log
    long_query_time = 1
    slow-query-log-file = /var/log/mysql/mysqld_slow_query.log
    character-set-server=utf8mb4
    character-set-client-handshake=false
    performance_schema=1

    ## Master Start
    server-id = 1
    #report_host = master1
    auto_increment_increment = 1
    auto_increment_offset = 1
    log_bin = master-bin
    log_bin_index = master-bin.index
    binlog_format = mix
    # not fab for performance, but safer #sync_binlog = 1
    expire_logs_days = 2
    max_binlog_size = 1000M
    ## Master End

    !include /etc/my.cnf.d/optionxx.cnf
    [galera]
    [embedded]
    [mariadb]


  • 2) option

    • # *** INNODB Specific options ***
    innodb_buffer_pool_size = 1G
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_write_io_threads = 8
    innodb_read_io_threads = 8
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 8M
    innodb_log_file_size = 256M
    innodb_max_dirty_pages_pct = 90
    innodb_lock_wait_timeout = 120

    max_connections = 1024
    max_connect_errors = 100
    table_open_cache = 1024
    max_allowed_packet = 16M
    binlog_cache_size = 1M
    max_heap_table_size = 512M
    read_buffer_size = 16M
    read_rnd_buffer_size = 64M
    sort_buffer_size = 128M
    join_buffer_size = 8M
    thread_cache_size = 8
    query_cache_size = 64M
    query_cache_limit = 2M
    ft_min_word_len = 2

    default-storage-engine = Innodb
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 64M

    event_scheduler = Off


  • 3) mysql_multi


    • [mysqld]
    기본 정보
    ..
    ..

    ..
    [mysqld_multi]
    mysqld = safe_mysqld
    mysqladmin = mysqladmin

    [mysql3306]
    socket = /var/run/mysqld/mysql3306.sock
    port = 3306;
    datadir = /home/db/mariadb3306
    pid-file=
    log =

    [mysql3307]
    socket = /var/run/mysqld/mysql3307.sock
    port = 3307;
    datadir = /home/db/mariadb3307
    pid-file=
    log =

    [다중서버 관리자 추가 하기]
    #mysql -u root -S /tmp/mysql.sock -proot_password -e "GRANT SHUTDOWN ON *.* TO multi_admin@localhost IDENTIFIED BY 'multipass'"



  • 3) Dual Master


    • 서버 1 [server.conf]
    기본 정보 ex) 192.168.100.111
    ..

    ..
    #####################
    # Master Start
    #####################
    server-id = 11
    log_bin = master-bin
    log_bin_index = master-bin.index
    binlog_format = mix
    auto_increment_increment = 2
    auto_increment_offset = 1
    log-slave-updates
    #####################


    mariadb -e "create user 'repli'@'192.168.%' identified by 'replipwd';"
    mariadb -e "grant replication slave on *.* to 'repli'@'192.168.%' identified by 'replipwd';"
    mariadb -e "flush privileges;"
    mariadb -e "show master status;"


    서버 2 [server.conf]
    기본 정보 ex) 192.168.100.112
    ..

    ..
    #####################
    # Master Start
    #####################
    server-id = 12
    log_bin = master-bin
    log_bin_index = master-bin.index
    binlog_format = mix
    auto_increment_increment = 2
    auto_increment_offset = 2
    log-slave-updates
    #####################


    mariadb -e "create user 'repli'@'192.168.%' identified by 'replipwd';"
    mariadb -e "grant replication slave on *.* to 'repli'@'192.168.%' identified by 'replipwd';"
    mariadb -e "flush privileges;"
    mariadb -e "show master status;"


    서버1
    [root@server 1 ~]# cat dualmaster1.sh #####################
    #!/bin/bash
    MASTER_ID_2="192.168.100.112" ## 서버2번 아이피 주소
    MYSQL_MASTER=`mariadb -h ${MASTER_ID_2} -u root -p비밀번호 -e "show master status"`
    MASTER_LOG_FILE=`echo $MYSQL_MASTER | awk -F' ' '{print $5}'`
    MASTER_LOG_POS=`echo $MYSQL_MASTER | awk -F' ' '{print $6}'`
    mariadb -e "STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; CHANGE MASTER TO MASTER_HOST='${MASTER_ID_2}', MASTER_USER='repli',MASTER_PASSWORD='replipwd', MASTER_LOG_FILE='${MASTER_LOG_FILE}', MASTER_LOG_POS=${MASTER_LOG_POS};START SLAVE;"
    echo "OK"
    #####################
    [root@server 1 ~]# ./dualmaster1.sh
    ok


    서버2
    [root@server 1 ~]# cat dualmaster2.sh #####################
    #!/bin/bash
    MASTER_ID_1="192.168.100.111" ## 서버1번 아이피 주소
    MYSQL_MASTER=`mariadb -h ${MASTER_ID_1} -u root -p비밀번호 -e "show master status"`
    MASTER_LOG_FILE=`echo $MYSQL_MASTER | awk -F' ' '{print $5}'`
    MASTER_LOG_POS=`echo $MYSQL_MASTER | awk -F' ' '{print $6}'`
    mariadb -e "STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; CHANGE MASTER TO MASTER_HOST='${MASTER_ID_1}', MASTER_USER='repli',MASTER_PASSWORD='replipwd', MASTER_LOG_FILE='${MASTER_LOG_FILE}', MASTER_LOG_POS=${MASTER_LOG_POS};START SLAVE;"
    echo "OK"
    #####################
    [root@server 1 ~]# ./dualmaster2.sh
    ok


    https://myinfrabox.tistory.com/265