mysql5.7 标准化源码编译并做keepalived 高可用架构

规划

两台服务器分别安装mysql，并做成主从
安装keepalived，将主库连接地址改为虚ip，并用域名完成解析
从库允许写，以防止主库服务器宕机后虚ip 飘至从库服务器，从库不允许写的问题，所以从库要保证是可以写入的

keepalived 安装

两台服务器均要安装依赖包,两台服务器已配置bond4,10.147.1.1 配置

shell>yum install keepalived -y
shell> cat /etc/keepalived/keepalived.conf
global_defs {
     notification_email {
     xinliang_li@moviebook.cn
     }
     notification_email_from root@kubernetes1.yp14.cn
                 smtp_server exmail.qq.com
                 smtp_connect_timeout 30
                 router_id master01_11
}

vrrp_script check_svr {
    script "/moviebook/scripts/chk_server.sh"
    interval 20
    weight 5
 }

vrrp_instance VI_1 {
    state MASTER
    interface bond4
    virtual_router_id 98
    priority 100
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 10.147.1.1 label bond4:0
    unicast_peer {
        10.147.1.3
    }

    virtual_ipaddress {         ##主节点上的vip
        10.147.1.100 dev bond4 label bond4:0
        #vip2 dev eth0 label eth0:1    ##如果每个节点上有多个vip，一个一行填上，只填单个节点上的vip
    }
    
    track_script {
        check_svr
    }
}

vrrp_instance VI_2 {
    state BACKUP
    interface bond4
    virtual_router_id 99
    priority 80
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 10.147.1.1 label bond4:0
    unicast_peer {
        10.147.1.3
    }
    virtual_ipaddress {         ##备节点上的vip
        10.147.1.101 dev bond4 label bond4:1
        #vip2 dev eth0 label eth0:3   ##如果每个节点上有多个vip，一个一行填上，只填单个节点上的vip
    }

    track_script {
        check_svr
    }
}

shell>cat /moviebook/scripts/chk_server.sh
#!/bin/bash
MSTATUS=$(netstat -anltp|grep -w "LISTEN"|grep -w mysqld|wc -l)
IPADD=$(ip a|grep -i "inet .* brd"|awk '{print $2}'|awk -F'/' '{print $1}')
HOSTNAME=$(hostname)
DATET=$(date +%Y-%m-%d\ %H:%M:%S)
LOGDIR=/export/logs/keepalived
LOGFILE=$LOGDIR/keepalived.log
ERRLOG="IP: ${IPADD}\nHOSTNAME: ${HOSTNAME}\nFatal error: Mysql servers maybe down, please check."
if [ $MSTATUS -eq 0 ];then
        pkill keepalived
        echo -e "${DATET}\n${ERRLOG}" >> $LOGFILE        
fi


10.147.1.3 配置
shell>cat /etc/keepalived/keepalived.conf
global_defs {
     notification_email {
     xinliang_li@moviebook.cn
     }
     notification_email_from root@kubernetes1.yp14.cn
                 smtp_server exmail.qq.com
                 smtp_connect_timeout 30
                 router_id master01_12
}

vrrp_script check_svr {
    script "/moviebook/scripts/chk_server.sh"
    interval 20
    weight 5
 }

vrrp_instance VI_1 {
    state BACKUP
    interface bond4
    virtual_router_id 98
    priority 80
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 10.147.1.3 label bond4:0
    unicast_peer {
        10.147.1.1
    }

    virtual_ipaddress {         ##主节点上的vip
        10.147.1.100 dev bond4 label bond4:1
        #vip2 dev eth0 label eth0:1    ##如果每个节点上有多个vip，一个一行填上，只填单个节点上的vip
    }
    
    track_script {
        check_svr
    }
}
vrrp_instance VI_2 {
    state MASTER
    interface bond4
    virtual_router_id 99
    priority 100
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 10.147.1.3 label bond4:0
    unicast_peer {
        10.147.1.1
    }
    virtual_ipaddress {         ##备节点上的vip
        10.147.1.101 dev bond4 label bond4:0
        #vip2 dev eth0 label eth0:3   ##如果每个节点上有多个vip，一个一行填上，只填单个节点上的vip
    }
    
    track_script {
        check_svr
    }
}

shell>cat /moviebook/scripts/chk_server.sh
#!/bin/bash
MSTATUS=$(netstat -anltp|grep -w "LISTEN"|grep -w mysqld|wc -l)
IPADD=$(ip a|grep -i "inet .* brd"|awk '{print $2}'|awk -F'/' '{print $1}')
HOSTNAME=$(hostname)
DATET=$(date +%Y-%m-%d\ %H:%M:%S)
LOGDIR=/export/logs/keepalived
LOGFILE=$LOGDIR/keepalived.log
ERRLOG="IP: ${IPADD}\nHOSTNAME: ${HOSTNAME}\nFatal error: Mysql servers maybe down, please check."
if [ $MSTATUS -eq 0 ];then
        pkill keepalived
        echo -e "${DATET}\n${ERRLOG}" >> $LOGFILE        
fi

shell> chmod 777 /moviebook/scripts/chk_server.sh
启动keepalived

以10.147.1.1 安装数据库为例

安装依赖包

shell>yum -y install gcc-* bison* libaio* ncurses-* cmake
shell>yum install -y openssl openssl-devel ncurses ncurses-devel autoconf

下载源码包

http://ftp.ntu.edu.tw/MySQL/Downloads/
下载最近的带boost的版本
或者
https://dev.mysql.com/downloads/

#下载地址
wget  https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.34.tar.gz
如：下载mysql-boost-5.7.34.tar.gz上传到/usr/local/src/下
解压
shell>cd /usr/local/src && tar zxf mysql-boost-5.7.34.tar.gz

#创建mysql 用户
useradd  mysql -s /sbin/nologin

安装

shell>cd /usr/local/src/mysql-boost-5.7.34
shell>cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_general_ci -DMYSQL_USER=mysql -DWITH_BOOST=boost
shell>gmake -j 4 && make install

配置环境变量

shell>echo "export PATH=\$PATH:/opt/mysql/bin" >>/etc/profile
shell>source /etc/profile

初始化数据库，请根据实际情况修改目录

准备数据库配置文件
shell> cat my3350.cnf 
[client]
port=3350

max_allowed_packet=48M

[mysql]
port=3350

max_allowed_packet=64M

[mysqld]
port=3350
user=mysql
server_id=13350
character-set-server=utf8mb4

#####dir#####
basedir=/opt/mysql
datadir=/export/mysql3350
socket=/export/mysql3350/mysql.sock
pid_file=/export/mysql3350/mysql.pid
log_error=/export/mysql3350/mysql-error.log
tmpdir=/export/mysql3350
log_bin=/export/mysql3350/mysql-bin
slow_query_log_file=/export/mysql3350/mysql-slow.log
relay_log=/export/mysql3350/relay-bin
slave_load_tmpdir=/export/mysql3350

#####undo#####
innodb_undo_log_truncate=1
innodb_undo_tablespaces=3
innodb_max_undo_log_size=256M
innodb_purge_rseg_truncate_frequency=32
innodb_undo_directory=/export/mysql3350


#####innodb setting######
#innodb = force
innodb_data_home_dir = /export/mysql3350
innodb_data_file_path = ibdata1:128M;ibdata2:128M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
innodb_autoinc_lock_mode = 1
innodb_log_group_home_dir = /export/mysql3350
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 8G  #可动态修改，实例分配的总内存
innodb_log_file_size = 1024M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_lock_wait_timeout = 10
innodb_buffer_pool_instances = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_max_dirty_pages_pct = 70
innodb_sync_spin_loops = 10
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_stats_on_metadata = OFF

#####log setting#####
slow_query_log=1
long_query_time=1
log_timestamps=SYSTEM
log_error_verbosity=2
log_queries_not_using_indexes=OFF
binlog_rows_query_log_events=ON
binlog_format=row
binlog_cache_size=4M
binlog_checksum=none
expire_logs_days=31
relay_log_purge=1
log_slave_updates=1
sync_binlog=1
back_log=500

#####common settings#####
sysdate_is_now=1

lower_case_table_names=1
thread_handling=one-thread-per-connection
max_connections=1024
max_user_connections=1024
max_connect_errors=999999999
thread_cache_size=50
table_open_cache=2048
open_files_limit=65535
#table_definition_cache=5120
interactive_timeout=3600
wait_timeout=3600
query_cache_type=0
query_cache_size=0
sort_buffer_size=16M
read_buffer_size=8M
join_buffer_size=16M
read_rnd_buffer_size=4M
group_concat_max_len=64K
max_tmp_tables=128
tmp_table_size=1024M
max_heap_table_size=512M
max_allowed_packet=128M
skip_external_locking
skip_name_resolve=1

#symbolic_links=0

skip_symbolic_links=1

sql_mode = 'NO_ENGINE_SUBSTITUTION'
default_storage_engine=INNODB

default_time_zone = '+8:00'

show_compatibility_56=ON

#####replication#####
gtid_mode=on
enforce_gtid_consistency=1
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
slave_skip_errors=1032,1062
skip_slave_start=1
replicate_same_server_id=0
#slave_pending_jobs_size_max=128M

report_host='10.147.1.1'      ##如果有主从关系的，主从节点均配置，配置为本节点的实IP

[myisamchk]
key_buffer = 64M
sort_buffer_size = 32M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]

interactive-timeout=1800

[mysqld_safe]

open-files-limit = 65535

创建对应目录
shell> mkdir /export/mysql3350  &&  mkdir /export/etc && mkdir /export/logs


进行初始化操作
shell>mysqld --defaults-file=/export/etc/my3350.cnf --initialize-insecure

注: --initialize-insecure表示初始化的时候root密码为空，可以后面再修改成自己的密码

启动数据库

shell>mysqld_safe --defaults-file=/export/etc/my3350.cnf &

设置admindba 权限

shell>mysql -u root -S  /export/mysql3350/mysql.sock  -p 
mysql>use mysql;
mysql>update user set authentication_string=password('新密码') where user='root' and Host='localhost';
mysql>create user 'admindba'@'%' IDENTIFIED by 'xxxxxxxxxx';
mysql>grant all privileges on *.* to 'admindba'@'%';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'admindba'@'%' WITH GRANT OPTION;
mysql>flush privileges;