MySQL单机改造成HA方案的配置

Vim etc/sysctl.confvm.swappiness = 0net.ipv6.conf.all.disable_ipv6 = 1net.core.somaxconn = 65535net.core.netdev_max_backlog = 65535net.ipv4.tcp_max_syn_backlog = 65535net.ipv4.tcp_fin_timeout = 10net.ipv4.tcp_tw_reuse = 1net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_keepalive_intvl = 30net.ipv4.tcp_keepalive_probes = 3net.ipv4.tcp_keepalive_time = 600net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576net.ipv4.ip_local_port_range = 40000 65500kernel.sem = 250 32000 100 128vm.min_free_kbytes = 524288kernel.shmmni = 4096kernel.shmall = 2097152kernel.shmmax = 2542751744vm.min_free_kbytes=524288kernel.shmall = free/4kernel.shmmax = free*1024*0.8
复制

▼▼▼
[mysql]port=3306socket=/data/mysql/tmp/mysql.sockdefault-character-set=utf8[mysqld_safe]log-error=/data/mysql/log/mysql.error[mysqld]port=3306user=shsncbasedir=/home/shsnc/mysqldatadir=/data/mysql/datasocket=/data/mysql/tmp/mysql.sockpid-file=/data/mysql/log/mysql.pidcharacter-set-server=utf8skip-name-resolvelower_case_table_names=1transaction_isolation=READ-COMMITTEDserver_id=1autocommit=1tmp_table_size=128Mmax_heap_table_size=96Mmax_connections=1000max_connect_errors=6000long_query_time=1innodb_buffer_pool_size=16Ginnodb_flush_log_at_trx_commit=0innodb_log_buffer_size=8Minnodb_log_file_size=128Minnodb_log_files_in_group=2innodb_file_per_table=1innodb_flush_method=O_DIRECTinnodb_write_io_threads=8innodb_read_io_threads=4innodb_doublewrite=0innodb_purge_threads=1innodb_stats_on_metadata=OFFinnodb_io_capacity=1000log-bin-trust-function-creators=1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USERevent_scheduler=onmax_allowed_packet=2Gslow_query_log=1auto_increment_offset=1auto_increment_increment=2#replicationlog_bin=/data/mysql/log/bin.loglog_bin_index=/data/mysql/log/bin.indexsync_binlog=1master_info_repository=TABLErelay_log_info_repository=TABLEsync_binlog=1log_slave_updatesbinlog_format=ROWbinlog_rows_query_log_events=1relay_log=/data/mysql/log/relay.logrelay_log_recovery=1slave_skip_errors=ddl_exist_errorsslave-rows-search-algorithms='INDEX_SCAN,HASH_SCAN'#gtidenforce_gtid_consistency=1gtid_mode=ONmaster_info_repository=tablerelay_log_info_repository=tableslave_parallel_type=logical_clockslave_parallel_workers=4slave_pending_jobs_size_max=1Gslave_preserve_commit_order=1sync_master_info=1sync_relay_log_info=1
复制

▼▼▼
mysqld_safe --defaults-file=/home/mysql/my_cnf/my.cnf &
复制

▼▼▼
create userrepl@'192.168.1.%' identified with mysql_native_password by 'xxxxxx';grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@'192.168.1.%';flush privileges;
复制

▼▼▼
CHANGE MASTER TO   MASTER_HOST='192.168.1.113',   MASTER_USER='repl',   MASTER_PASSWORD='xxxxxx',   MASTER_PORT=3306,   MASTER_AUTO_POSITION=1;Start slave;
复制

▼▼▼
CHANGE MASTER TO   MASTER_HOST='192.168.1.115',   MASTER_USER='repl',   MASTER_PASSWORD='xxxxxx',   MASTER_PORT=3306,   MASTER_AUTO_POSITION=1;Start slave;
复制

▼▼▼
./configure --prefix=/opt/keepalived && make && make install
复制

▼▼▼
! Configuration File for keepalivedglobal_defs {    notification_email {       shsnc@shsnc.com   }    notification_email_from smtp.163.com    smtp_server 127.0.0.1    smtp_connect_timeout 30    router_id LVS_DEVEL}vrrp_script checkk_mysql {    script "/opt/keepalived/script/check_mysql.sh"    interval 5    fall 3    rise 2}vrrp_instance VI_1 {    state BACKUP    interface ens160    virtual_router_id 51    priority 100    nopreempt    advert_int 1    authentication {        auth_type PASS        auth_pass 110120    }    virtual_ipaddress {        135.10.51.8    }    track_script {        checkk_mysql    }    notify_master "/opt/keepalived/script/keepalived_notify.sh master"    notify_backup "/opt/keepalived/script/keepalived_notify.sh backup"}
复制

▼▼▼
A=`ps -C mysqld --no-header |wc -l`if [ $A -eq 0 ]then    /bin/systemctl stop syskeepalivedfi
复制

▼▼▼
#!/bin/bash. ~/.bash_profile#mysql userDB_USER="root"#mysql user passwordDB_PASSWORD='shsnc!@#'MYSQL_SOCK=""#mysql_binMYSQL_BIN="/home/shsnc/mysql/bin/mysql"#mysql client commandMYSQL_CMD="${MYSQL_BIN} -u${DB_USER} -p${DB_PASSWORD}"#query the killed seesion's id sqlMYSQL_SQL="select concat('kill ',id,';') from information_schema.processlist where user not in ('system user','repl','replic','backup','bkpuser','bomcjk','root','myrobot')"#关闭事件任务AMP_EVENT_PARTITION_DISABLE="ALTER event domp.event_add_partitions ON COMPLETION PRESERVE DISABLE"DOMP_EVENT_PARTITION_DISABLE="ALTER event amp.event_add_partitions ON COMPLETION PRESERVE DISABLE"#开启事件任务AMP_EVENT_PARTITION_ENABLE="ALTER event domp.event_add_partitions ON COMPLETION PRESERVE ENABLE"DOMP_EVENT_PARTITION_ENABLE="ALTER event amp.event_add_partitions ON COMPLETION PRESERVE ENABLE"#define function: get mysql service informationfunction get_mysql_infor() {    for sock in `ps -ef | grep mysqld | grep '\-\-socket=' | awk -F'--socket=' '{print $2}' | awk '{print $1}'`    do        MYSQL_SOCK="${MYSQL_SOCK}$(echo $sock)"    done}#define function: mysql kill sessionsfunction kill_sessions(){    #receive a mysql socket file parameter    #my_sock=$(get_mysql_infor)  unset MYSQL_SOCK  get_mysql_infor      # kill session     #..................    #begin to kill mysql sessions    ${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${MYSQL_SQL}" 2>/dev/null | ${MYSQL_CMD} > /dev/null 2>&1}#define function: set mysql read_only modefunction set_readonly(){    #receive mysql socket file && read_only sign parameter    #my_sock=$(get_mysql_infor)  unset MYSQL_SOCK  get_mysql_infor      my_sign=$1    #begin to set mysql read_only mode    ${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "set global read_only=${my_sign}" 2>/dev/null}#define function: keepalived state changed to masterfunction Keepalived_changed_to_master(){  #my_sock=$(get_mysql_infor)  unset MYSQL_SOCK  get_mysql_infor#开始事件任务${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${AMP_EVENT_PARTITION_ENABLE} 2>/dev/null "${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${DOMP_EVENT_PARTITION_ENABLE} 2>/dev/null "  Seconds_Behind_Master=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave status\G" | grep -w Seconds_Behind_Master | awk -F": " '{print $2}')  Slave_IO_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave status\G" | grep -w Slave_IO_Running | awk -F": " '{print $2}')  Slave_SQL_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave status\G" | grep -w Slave_SQL_Running | awk -F": " '{print $2}')  Master_Log_File=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')  Relay_Master_Log_File=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')  Read_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')  Exec_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}')  echo  #    if [ "${Slave_IO_Running}" = "Yes" -a "${Slave_SQL_Running}" = "Yes" ]    then        if [ $Seconds_Behind_Master -eq 0 ]        then            echo '判断复制无延迟,即将切换'            #.........set mysql no read_only mode.........            set_readonly 0            exit 0        fi    fi    if [ "${Slave_IO_Running}" = "Connecting" -a "${Slave_SQL_Running}" = "Yes" ]    then        if [ "${Master_Log_File}" = "${Relay_Master_Log_File}" -a "${Read_Master_Log_Pos}" = "${Exec_Master_Log_Pos}" ]        then            echo "主库宕机,即将切换"            #.........set mysql no read_only mode.........            set_readonly 0            exit 0    fi    fi}#define function: keepalived state changed to backupfunction Keepalived_changed_to_backup(){  #my_sock=$(get_mysql_infor)  unset MYSQL_SOCK  get_mysql_infor${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${AMP_EVENT_PARTITION_DISABLE} 2>/dev/null "${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${DOMP_EVENT_PARTITION_DISABLE} 2>/dev/null "    #set mysql read_only mode  set_readonly 1  #kill mysql sessions  kill_sessions}#start this shellcase $1 in    master)      Keepalived_changed_to_master        ;;     backup)      Keepalived_changed_to_backup      ;;  *)      ;;esac

复制

▼▼▼
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'shsnc'@'135.10.66.125' identified by 'xxxxxx';
复制