MySQL主从复制与Keepalive结合方案

当前目录下：
chown -R mysql.mysql /data/myshare/
ln -s /data/myshare/mysql-8.0.32-linux-glibc2.17-x86_64-minimal  /home/mysql/mysql
#1、编辑数据库初始化文件my.cnf
#MySQL8 my.cnf
cat >> /data/mysql/run/my.cnf <<EOF
[client]
port    = 3306
socket    = /data/mysql/tmp/mysql.sock
mysqlx_socket = /data/mysql/tmp/mysqlx.sock
[mysql]
prompt="\u@8B05-IVDB01 \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]
user    = mysql
port    = 3306
basedir    = /home/mysql/mysql
datadir    = /data/mysql/data
socket    = /data/mysql/tmp/mysql.sock
mysqlx_socket = /data/mysql/tmp/mysqlx.sock
pid-file = /data/mysql/tmp/8B05-IVDB01.pid
character-set-server = utf8mb4
skip_name_resolve = 1

#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"

open_files_limit    = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysql/logs/mysqlslow.log
log-error = /data/mysql/logs/mysqlerr.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 3306
log-bin = /data/mysql/logs/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

#注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项
expire_logs_days = 7

master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

transaction_isolation = READ-COMMITTED
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 40960M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysql/data/undolog
innodb_undo_tablespaces = 95


[mysqldump]
quick
max_allowed_packet = 32M
#临时文件目录
slave_load_tmpdir=/data/mysql/tmp/
tmpdir =/data/mysql/tmp/
EOF


##MySQL5.7 profile
[client]
port    = 3306
socket    = /data/mysql/tmp/mysql.sock
[mysql]
prompt = "\u@db \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
user    = mysql
port    = 3306
basedir    = /home/mysql/mysql
datadir    = /data/mysql/data
socket    = /data/mysql/tmp/mysql.sock
character-set-server    = utf8mb4
skip_name_resolve        = 1
open_files_limit        = 65535
back_log    = 1024
max_connections    = 500
table_open_cache    = 1024
table_definition_cache    = 1024
table_open_cache_instances    = 64
thread_stack    = 512K
external-locking    = FALSE
max_allowed_packet    = 32M
sort_buffer_size    = 4M
join_buffer_size    = 4M
thread_cache_size    = 768
query_cache_size    = 0
query_cache_type    = 0
interactive_timeout    = 600
wait_timeout        = 600
tmp_table_size        = 32M
max_heap_table_size    = 32M
slow_query_log        = 1
slow_query_log_file = /data/mysql/logs/mysqlslow.log
log-error            = /data/mysql/logs/mysqlerr.log
long_query_time        = 0.1
server-id    = 33061
log_timestamps=SYSTEM

log-bin    = /data/mysql/logs/mysql-bin
sync_binlog    = 1
binlog_cache_size    = 4M
max_binlog_cache_size  = 1G
max_binlog_size    = 1G
expire_logs_days    = 7
gtid-mode    = on
enforce-gtid-consistency    = on
log-slave-updates
binlog_format    = row
relay_log_recovery    = 1
relay-log-purge    = 1
key_buffer_size    = 32M
read_buffer_size    = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
lock_wait_timeout    = 3600
explicit_defaults_for_timestamp    = 1
innodb_thread_concurrency        = 0
innodb_sync_spin_loops        = 100
innodb_spin_wait_delay        = 30
transaction_isolation        = READ-COMMITTED
innodb_buffer_pool_size        = 40960M
innodb_buffer_pool_instances    = 4
innodb_buffer_pool_load_at_startup    = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path    = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size    = 32M
innodb_log_file_size    = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size  = 4G
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads  = 8
innodb_purge_threads    = 4

innodb_page_cleaners    = 4
innodb_open_files        = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method        = O_DIRECT
innodb_lru_scan_depth    = 4000
#innodb_cheacksum_algorithm    = crc32
innodb_lock_wait_timeout    = 10
innodb_rollback_on_timeout    = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table    = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 0
performance_schema    = 1
performance_schema_instrument = '%=on'
[mysqldump]
quick
max_allowed_packet = 32M
#2、创建数据库实例
chown -R mysql.mysql /data/mysql/

cd /home/mysql/mysql/bin/
/home/mysql/mysql/bin/mysqld --defaults-file=/data/mysql/run/my.cnf --initialize --user=mysql --basedir=/home/mysql/mysql --datadir=/data/mysql/data &
#启动实例：
/home/mysql/mysql/bin/mysqld_safe  --defaults-file=/data/mysql/run/my.cnf --user=mysql &
#加密连接
/home/mysql/mysql/bin/mysql_ssl_rsa_setup -d /data/mysql/data

cat >> /data/mysql/run/shell.sh <<EOF
mysql -uroot -p -S /data/mysql/tmp/mysql.sock
EOF

cat >> /data/mysql/run/start.sh <<EOF
mysqld_safe  --defaults-file=/data/mysql/run/my.cnf --user=mysql &
EOF
cat >> /data/mysql/run/stop.sh <<EOF
/home/mysql/mysql/bin/mysqladmin shutdown -uroot -p -S /data/mysql/tmp/mysql.sock
EOF

#3、配置从库
从库创建：MySQL8
create user 'dbsync'@'10.74.147.%' identified by 'xxxxxx';
alter user 'dbsync'@'10.74.147.%' identified with mysql_native_password by 'xxxxxx';
grant replication slave on *.* to 'dbsync'@'10.74.147.%' identified by 'xxxxxx';


change master to 
master_host='10.74.147.197', 
master_user='dbsync',
master_password='xxxxxx',
master_auto_position=1;

# cat keepalived.conf
! Configuration File for keepalived
global_defs {
   router_id LVS_MASTER
}
vrrp_script chk_mysql_port {
    script "/etc/keepalived/chk_mysql.sh" #这里通过脚本检测
    interval 2      #脚本执行间隔, 每2s检测一次
    weight -5       #脚本结果导致的优先级变更, 检测失败(脚本返回非0)则优先级-5
    fall 2  #检测连续两次失败才算真的失败
    rise 1  #检测1次成功就算成功
}
vrrp_instance VI_1 {
    state MASTER
    interface ens192
    virtual_router_id 52
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.81.139.41/24 dev ens192 label ens192:1
    }
    unicast_src_ip 10.80.139.39
    unicast_peer {
        10.81.139.40
    }
    track_script {
      chk_mysql_port
    }
}

# cat /etc/keepalived/chk_mysql.sh
#!/bin/bash
h1=`ps -C mysqld_safe --no-header | wc -l`
#h2=`ps -C ntpd --no-header | wc -l`
if [  $h1 -eq 0 ] ;
then
systemctl stop keepalived
fi

# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived
global_defs {
   router_id LVS_BACKUP
   script_user root
   enable_script_security
}
vrrp_script chk_mysql_port {
    script "/etc/keepalived/chk_mysql.sh" #这里通过脚本检测
    interval 2      #脚本执行间隔, 每2s检测一次
    weight -5       #脚本结果导致的优先级变更, 检测失败(脚本返回非0)则优先级-5
    fall 2  #检测连续两次失败才算真的失败
    rise 1  #检测1次成功就算成功
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens192
    virtual_router_id 52
    priority 80 
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.81.139.41 dev ens192 label ens192:1
    }
    unicast_src_ip 10.80.139.40
    unicast_peer {
        10.81.139.39
    }

    track_script {
      chk_mysql_port
    }
}

# cat chk_mysql.sh
#!/bin/bash
h1=`ps -C mysqld_safe --no-header | wc -l`
#h2=`ps -C ntpd --no-header | wc -l`
if [  $h1 -eq 0 ] ;
then
systemctl stop keepalived
fi