MySQL主从复制与Keepalive结合方案

• 一、准备工作

# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.

根据GNU，下载对应的MySQL数据库

操作系统版本：

# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)

地址划分：

主节点：10.81.139.39

从节点：10.81.139.40

VIP：10.81.139.41

• 二、MySQL初始化工作

运行initMySQL.sh

#/bin/bash
cat >> /etc/sysctl.conf <<EOF
#
#FOR MySQL 
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
#
EOF

/sbin/sysctl -p

#2、
cat >> /etc/security/limits.conf <<EOF
#
#FOR MySQL
#
mysql   soft   nofile    1024
mysql   hard   nofile    65536
mysql   soft   nproc    16384
mysql   hard   nproc    16384
mysql   soft   stack    10240
mysql   hard   stack    32768
mysql   hard   memlock    134217728
mysql   soft   memlock    134217728
#
EOF
groupadd -g 3306 mysql
useradd -u 3306 -g mysql mysql 
echo "mysql" | passwd mysql --stdin

sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config
#Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.
#SELINUX=permissive
#Once the change is complete, restart the server or run the following command.

systemctl stop firewalld
systemctl disable firewalld

#6、If you are not using Oracle Linux and UEK, you will need to manually disable transparent huge pages.#
#Create the directories in which the Oracle software will be installed.
mkdir -p /data/mysql/{data,logs,run,tmp}
chown -R mysql:mysql /data
chmod -R 775 /data/mysql
#7、编辑用户mysql profile
cat >> /home/mysql/.bash_profile <<EOF
# MySQL
export MySQL_HOSTNAME=`hostname`
export MySQL_BASE=/home/mysql/mysql
export PATH=/usr/sbin:/usr/local/bin:\$PATH
export PATH=\$MySQL_BASE/bin:\$PATH
#
EOF
#8、关闭大页
#1、首先检查THP的启用状态：
#[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
#[always] madvise never
#[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
#[always] madvise never
#如果输出结果为[always]表示透明大页启用了。[never]表示透明大页禁用、[madvise]表示（只在MADV_HUGEPAGE标志的VMA中使用THP
#这个状态就说明都是启用的。
#2、在运行时禁用THP(Transparent HugePages)
#运行以下命令即时禁用THP，该命令适用于其它Linux系统：
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
#3、永久禁用THP(Transparent HugePages )
#编辑rc.local文件：
cat >> /etc/rc.d/rc.local <<EOF
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
EOF
#4、保存退出，然后赋予rc.local文件执行权限：
chmod +x /etc/rc.d/rc.local
#5、最后重启系统，以后再检查THP应该就是被禁用了
#[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
#always madvise [never]
#[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
#always madvise [never]
cat /sys/kernel/mm/transparent_hugepage/enabled
cat /sys/kernel/mm/transparent_hugepage/defrag

 

• 三、MySQL创建过程createMySQL.sh

当前目录下：
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;

• 四、Keepalived配置

版本：keepalived-2.0.10.tar.gz

主节点：1、keepalived配置

# 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
    }
}

2、MySQL探活脚本

# 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

从节点：1、keepalived配置

# 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
    }
}

2、MySQL探活脚本

# 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

安装keepalived-2.0.10.tar.gz

# tar -zxvf keepalived-2.0.10.tar.gz

# cd keepalived-2.0.10/

使用 configure 命令配置安装目录与核心配置文件所在位置：

#  mkdir -p /etc/keepalived

# yum install -y openssl openssl-devel  libnl libnl-devel   ipvsadm

# ./configure --prefix=/etc/keepalived

# make && make install

# chmod u+x chk_mysql.sh

 

# ll
total 8
drwx------ 2 root root 21 Oct 17 00:13 bin
-rwx------ 1 root root 146 Oct 17 00:15 chk_mysql.sh
drwx------ 4 root root 41 Oct 17 00:13 etc
-rw------- 1 root root 835 Oct 17 00:17 keepalived.conf
drwx------ 2 root root 24 Oct 17 00:13 sbin
drwx------ 5 root root 40 Oct 17 00:13 share

# systemctl start keepalived

# systemctl status keepalived