MySQL 部署文档

作者：涂祎豪

安装前准备

创建 toptea 用户

useradd -b /home -m -s /bin/bash toptea
passwd toptea
id toptea

配置 yum 源 [如果需要]

安装 Oracle JDK 1.8

安装主机依赖包

yum -y install bison* gcc-c++* glibc* perl* ncurses* libnuma* ncurses-devel* libaio* perl-DBI* perl-DBD-MySQL*

修改主机参数

echo -e "\n##auto insert by mysql_auto_install##\ntoptea soft nproc 16384\ntoptea hard nproc 16384\ntoptea soft nofile 65536\ntoptea hard nofile 65536\ntoptea hard stack 1024000\ntoptea soft stack 1024000" >> /etc/security/limits.conf
cat /etc/security/limits.conf

添加主机 crontab 定时任务限制

echo -e "root\ntoptea" > /etc/cron.allow

关闭主机防火墙

systemctl stop firewalld
systemctl disable firewalld

关闭 selinux

setenforce 0
vi /etc/selinux/config
SELINUX=disabled

修改主机 swap

默认为 60，内存在使用到 100-60=40% 的时候，就开始出现有交换分区的使用。大家知道，内存的速度会比磁盘快很多，这样子会加大系统 io，同时造的成大量页的换进换出，严重影响系统的性能，所以我们在操作系统层面，要尽可能使用内存，对该参数进行调整。

vi /etc/sysctl.conf
vm.swappiness = 10
sysctl -p

新建 apps 目录

cd /home/toptea
mkdir apps
# 新建安装包目录
mkdir package

MySQL 单节点安装

MySQL 主从集群安装

上传并解压 MySQL

进入 /home/toptea/apps/package，将 mysql 安装包 mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz 上传至此，解压

cd /apps/svr/package/
tar -xvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /home/toptea/apps/
cd /home/toptea/apps/mysql
mkdir -p etc log data
​
mkdir sharedstorage
mkdir sharedstorage/logs
mkdir sharedstorage/logs/mysql_3306
mkdir sharedstorage/logs/mysql_3306/log
mkdir sharedstorage/logs/mysql_3306/relaylog
mkdir sharedstorage/logs/mysql_3306/binlog
mkdir sharedstorage/logs/mysql_3306/error
mkdir sharedstorage/logs/mysql_3306/slow
touch sharedstorage/logs/mysql_3306/slow/slow.log
mkdir sharedstorage/logs/mysql_3306/audit
mkdir sharedstorage/logs/mysql_3306/tmpdir

将 MySQL 的 bin 目录加入 PATH 环境变量

# 普通用户
PATH=$HOME/apps/mysql/bin:$PATH
source ~/.bash_profile
# root 用户
PATH=/home/toptea/apps/mysql/bin:$PATH
source /etc/profile

配置主库 MySQL 基础脚本（配置，初始化，启动，停止）

在 mysql 目录下创建 etc/my.cnf 文件，内容如下

[client]
port = 3306
socket = /tmp/mysql.sock
​
[mysql]
prompt=\u@\d \r:\m:\s>
​
[mysqld]
#####################system settings#######################
basedir = /home/toptea/apps/mysql
datadir = /home/toptea/apps/mysql/data
user = toptea
log-error = /home/toptea/apps/mysql/log/mysql.log
pid-file = /home/toptea/apps/mysql/mysql.pid
socket = /tmp/mysql.sock
tmpdir = /home/toptea/apps/mysql/sharedstorage/logs/mysql_3306/tmpdir
port = 3306
transaction_isolation='READ-COMMITTED'
server_id = 1     #主从ID不能相同
character-set-server = utf8mb4
init_connect='SET NAMES utf8mb4'
read_only=on
super_read_only=on
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log_bin_trust_function_creators = ON
federated
max_allowed_packet = 1024M
group_concat_max_len = 102400
thread_cache_size = 320
table_definition_cache = 4236
table_open_cache = 7672
innodb_open_files = 7672
innodb_page_cleaners = 8
replicate-wild-ignore-table=aiosp.%
skip_slave_start = ON
secure_file_priv =""     
open_files_limit = 65535
query_cache_size = 0
query_cache_type = 0
lower_case_table_names = 1
skip_name_resolve = on
default_time_zone = '+8:00'
explicit_defaults_for_timestamp = ON
log_timestamps = SYSTEM
tmp_table_size = 32M
​
#####################buffer settings#######################
innodb_buffer_pool_size = 56G # 看情况修改
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 32M
sort_buffer_size = 256k
read_buffer_size = 8M
join_buffer_size = 1M
​
#####################log settings#######################
innodb_log_file_size = 2G # 看情况修改
innodb_log_files_in_group = 6
innodb_log_group_home_dir = /home/toptea/apps/mysql/sharedstorage/logs/mysql_3306/log
relay-log = /home/toptea/apps/mysql/sharedstorage/logs/mysql_3306/relaylog
expire_logs_days = 10       
max_binlog_size = 1024M
log_bin = /home/toptea/apps/mysql/sharedstorage/logs/mysql_3306/binlog
slow_query_log = ON
long_query_time = 5
slow_query_log_file =/home/toptea/apps/mysql/sharedstorage/logs/mysql_3306/slow/slow.log
​
#####################connection settings########################
max_connections = 3000
max_connect_errors = 100
interactive_timeout = 1800
wait_timeout = 1800
​
#####################myisam engine#######################
# key_buffer_size = 1024M 
​
#####################innodb engine########################
innodb_flush_log_at_trx_commit = 1
innodb_undo_tablespaces=3
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 10
innodb_print_all_deadlocks = ON
innodb_file_per_table = ON
innodb_data_file_path = ibdata1:256M:autoextend
innodb_undo_log_truncate = ON
​
###################replication settings###################
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay-log-recovery = ON
binlog_rows_query_log_events = 1
binlog_format = row
sync_binlog = 1
log_slave_updates = ON
gtid_mode = ON
enforce-gtid-consistency = ON
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
slave_preserve_commit_order = ON
​
################### loose_rpl settings###################
plugin_dir = /home/toptea/apps/mysql/lib/plugin
plugin_load ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_wait_for_slave_count=1
​

创建文件 initdata.sh，内容如下：

#!/bin/sh
​
shelldir=`dirname $0`
cd $shelldir
shelldir=`pwd`
​
mkdir -p data log
​
bin/mysqld \
       --defaults-file=$shelldir/etc/my.cnf \
       --initialize \
       --user=toptea \
       --basedir=$shelldir \
       --datadir=$shelldir/data

创建文件 start.sh，内容如下：

#!/bin/sh
​
shelldir=`dirname $0`
cd $shelldir
shelldir=`pwd`
​
bin/mysqld_safe \
       --defaults-file=$shelldir/etc/my.cnf \
       --user=toptea &

创建文件 stop.sh，内容如下：

#!/bin/sh
​
shelldir=`dirname $0`
cd $shelldir
shelldir=`pwd`
​
bin/mysqladmin --defaults-file=$shelldir/etc/my.cnf -u root -p shutdown

赋予脚本可执行权限

chmod +x *.sh

复制一份到从库机器（记得改配置文件里的 server_id）

scp -r mysql/ toptea@192.168.121.129:/

修改 /home/toptea/apps/mysql/etc/ 中的 my.cnf

server_id = 2

其余操作同主库

操作 MySQL

初始化 MySQL

执行以下命令初始化数据库：

./initdata.sh

log/mysql.log 内容如下：

2021-12-09T09:25:39.413428Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-12-09T09:25:39.506684Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-12-09T09:25:39.568672Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 13bc30a2-7a44-11ea-9274-0050569ce1c6.
2021-12-09T09:25:39.571131Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-12-09T09:25:40.151535Z 0 [Warning] CA certificate ca.pem is self signed.
2021-12-09T09:25:40.455423Z 1 [Note] A temporary password is generated forroot@localhost: 7Y9p:dRsQbmN

记下最后一行系统自动生成的 root 密码

grep "temporary password" log/mysql.log

启动 MySQL

./start.sh

修改 root 密码

执行命令

bin/mysql --defaults-file=`pwd`/etc/my.cnf -u root -p

本次输入的密码是初始化生成的随机密码。登陆后输入命令：

set global super_read_only=off;
set password for 'root'@'localhost' = password('root667');
flush privileges;
​
# alter user 'root'@'localhost' identified by 'root667';

创建 toptea 数据库和用户

create database toptea;
grant all privileges on toptea.* to toptea@'%' identified by 'toptea667';
flush privileges;
​
# create user toptea@'%' IDENTIFIED BY 'toptea667';

停止 MySQL

./stop.sh

执行时，需要输入 MySQL root 用户的密码。

MySQL 主从配置

1. 创建同步用户

所有节点创建同步用户 repl，ip% 表示范围（1 对多），密码为 repl

create user repl@'192.168.121.%' IDENTIFIED BY 'Repl_2021';

赋予该用户同步所有数据库的表的权限

GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.121.%';
flush privileges;

2. 进行配置同步

从库（192.168.121.129）执行，master_host 指定为主库 ip

change master to master_user='repl',master_host='192.168.121.128',master_password='Repl_2021',master_port=3306,MASTER_AUTO_POSITION=1;

启动从库同步slave服务

start slave;

3.检查主从状态

在从库上输入

show slave status\G

若如下所示

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.128
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 194
               Relay_Log_File: relaylog.000004
                Relay_Log_Pos: 361
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: aiosp.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194
              Relay_Log_Space: 561
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 953a8c63-5d76-11ec-9bb7-000c29084734
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 953a8c63-5d76-11ec-9bb7-000c29084734:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

查看信息：

Slave_IO_Running: Yes # (网络正常)；
Slave_SQL_Running: Yes # (表结构正常)

如上所述主从配置成功

测试主从同步

在主库上执行创建数据库及表，并插入数据

set global read_only=off;
set global super_read_only=off;

create database test;
create table test.test(id int,name varchar(10));
insert into test.test values(1,'test1');
insert into test.test values(2,'test2');
select * from test.test;

从库查看数据

select * from test.test;

查询跟主库数据一致即可

至此，主从数据库安装完成

注：若出现问题，可以删掉重做，具体如下

第一步在从库执行：

stop slave;
reset slave all;
delete from mysql.user where user='repl';
flush privileges;
reset master;

第二步在主库执行：

delete from mysql.user where user='repl';
flush privileges;
reset master;
create user repl@'192.168.121.%' identified by 'Repl_2021';
GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.121.%';
flush privileges;

第三步在从库搭建复制链路：

change master to master_user= 'repl',master_host='192.168.121.128',master_password='Repl_2021',master_port=3306,MASTER_AUTO_POSITION=1;
start slave;
show slave status\G

change master to master_user= 'repl',master_host='10.170.113.181',master_password='Repl_2022',master_port=3306,MASTER_AUTO_POSITION=1;

最后查看状态：

show slave status\G

keepalived 安装配置

前期准备

安装依赖包

yum -y install gcc gcc-c++ ncurses-devel bison libaio-devel cmake libnl* libpopt* openssl-devel

创建目录

mkdir -p /home/toptea/apps/mysql/tools/keepalived
mkdir -p /home/toptea/apps/mysql/conf/keepalived
mkdir -p /home/toptea/apps/mysql/shell/keepalived
mkdir -p /home/toptea/apps/mysql/package

keepalived 解压编译

# 下载 keepalived-2.0.19（如果已在本地下载则不需要）
# 官网地址 https://www.keepalived.org/download.html
wget https://www.keepalived.org/software/keepalived-2.0.19.tar.gz

# 上传 keepalived 至 /home/toptea/apps/package
# 解压并编译
tar -xf keepalived-2.0.19.tar.gz -C /home/toptea/apps/mysql/package
cd /home/toptea/apps/mysql/package/keepalived-2.0.19
./configure --prefix=/home/toptea/apps/mysql/tools/keepalived
make && make install

# cp /usr/local/apps/shell/keepalived/keepalived.conf /home/toptea/apps/mysql/conf/keepalived
cp /home/toptea/apps/mysql/tools/keepalived/etc/keepalived/keepalived.conf /home/toptea/apps/mysql/conf/keepalived

# keepalived 启动脚本（源码目录下），放到 /etc/init.d/ 目录下就可以使用 service 命令便捷调用，/usr/local/src/keepalived-2.0.19 为安装包解压路径
# cp /usr/local/etc/rc.d/init.d/keepalived /etc/init.d
cp /home/toptea/apps/mysql/tools/keepalived/sbin/keepalived /etc/init.d

# keepalived 启动脚本变量引用文件，默认文件路径是 /etc/sysconfig/，也可以不做软链接，直接修改启动脚本中文件路径即可（安装目录下）
# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig
cp /home/toptea/apps/mysql/tools/keepalived/etc/sysconfig/keepalived /etc/sysconfig

# 将 keepalived 主程序加入到环境变量（安装目录下）
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
cp /home/toptea/apps/mysql/tools/keepalived/sbin/keepalived /usr/sbin/

chown toptea:toptea /home/toptea/apps/mysql/conf/keepalived
chown toptea:toptea /etc/init.d/keepalived 
chown toptea:toptea /etc/sysconfig/keepalived 
chown toptea:toptea /usr/sbin/keepalived

vim /etc/sysconfig/keepalived
KEEPALIVED_OPTIONS="-D -f /home/toptea/apps/mysql/conf/keepalived/keepalived.conf"
vim /home/toptea/apps/mysql/tools/keepalived/etc/sysconfig/keepalived
KEEPALIVED_OPTIONS="-D -f /home/toptea/apps/mysql/conf/keepalived/keepalived.conf"

# 建立软连接
# ln -s /home/toptea/apps/mysql/tools/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/keepalived

which keepalived
keepalived -version
systemctl enable keepalived.service

keepalived 主节点配置

chmod 644 keepalived.conf 

vrrp_script check_mysql {
    script "/home/toptea/apps/mysql/shell/keepalived/check_mysql.sh"
    interval 10
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth33	# 修改为本机网卡名
    virtual_router_id 51
    nopreempt
    priority 150    # 从节点
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    unicast_src_ip 192.168.121.128    ##单播模式本地地址IP
    unicast_peer {
        192.168.121.129     ##单播模式对端地址IP
    }
    track_script {
        check_mysql
    }
    virtual_ipaddress {
        192.168.200.16  # 改为实际分配的 VIP
        #192.168.200.17
        #192.168.200.18
    }
    
    notify_fault  "/home/toptea/apps/mysql/shell/keepalived/switch_fault.sh"
    notify_master  "/home/toptea/apps/mysql/shell/keepalived/switch_master.sh"
    notify_backup  "/home/toptea/apps/mysql/shell/keepalived/switch_backup.sh"
    notify "/home/toptea/apps/mysql/shell/keepalived/switch_status.sh"
}

创建用户用于高可用切换操作（主从架构已经搭建，主库执行即可）

创建 2 个专门的数据库账号用于 mysql 的高可用切换操作，此 2 个数据库账号需要最大的权限。

create user rootha@'192.168.121.%' identified by 'Nfjd_2021';
create user rootha@'localhost' identified by 'Nfjd_2021';
GRANT ALL PRIVILEGES ON *.* TO 'rootha'@'192.168.121.%';
GRANT ALL PRIVILEGES ON *.* TO 'rootha'@'localhost';
show grants for 'rootha'@'192.168.121.%';
show grants for 'rootha'@'localhost';
flush PRIVILEGES;

上传配置文件（两个节点操作）

# 脚本功能
check_mysql.sh     ##检测mysql是否正常运行的脚本
mysql_config.list  ##本地MySQL的相关配置
switch_backup.sh   ##keepalived进入backup状态执行的脚本，即切换MySQL数据库为从节点角色
switch_fault.sh    ##keepalived进入fault状态执行的脚本，即MySQL数据库检测失败，执行切换操作脚本
switch_master.sh   ##keepalived进入master状态执行的脚本，即mysql数据库为主节点角色
switch_status.sh   ##keepalived每一个状态的切换，都会输出，建议配置告警平台部署为keepalived切换告警指标监控

注意事项：这6个文件必须存储在同一个目录。这里存放在 /home/toptea/apps/mysql/shell/keepalived

修改 mysql_config.list 配置文件

Keepalived 的运行和切换脚本都涉及到 MySQL 数据库，因此，将 MySQL 的相关参数作为配置文本存储在 mysql_config.list 中，mysql_config.list 文件中除了参数，不能够有其他的任何符号，包括注释符 #。（修改相应的 IP 跟 binlog 路径和密码）

MYSQL_CMD="/home/toptea/apps/mysql/bin/mysql"
MYSQLBINLOG_CMD="/home/toptea/apps/mysql/bin/mysqlbinlog"
MYSQLADMIN_CMD="/home/toptea/apps/mysql/bin/mysqladmin"
LOCAL_HOST=192.168.121.128      # 本地 ip
REMOTE_HOST=192.168.121.129     # 对端 ip
WRITE_VIP=192.168.200.16        # VIP
MYSQL_PORT=3306         # 数据库端口
MYSQL_USER=rootha
MYSQL_PASS=Nfjd_2021
SOCKET='/tmp/mysql.sock'        # sock 文件
CHECK_TIME=3    # 3 秒输出
CHECK_COUNT=3   # 执行 3 次
CHECK_SQL="select CONCAT('COUNTER:',count(*)) from mysql.user"
MYSQL_PROTOCOL=socket
LOCAL_BINLOG="/home/toptea/apps/mysql/sharedstorage/logs/mysql_3306/binlog/binlog"      # log_bin 参数值

sqlpath=/tmp/masterha_3306
SLAVE_USR=rootha
SLAVE_PWD=Nfjd_2021
SLAVE_PORT=3306
PINGDOWN_C=5
PINGDOWN_TOTAL=3
REP_USER=repl
REP_PWD=Repl_2021
REP_PORT=3306

LOGFILE="/home/toptea/apps/mysql/sharedstorage/logs/keepalived/logs/alert_myha.log"     # keepalived 日志

启动 Keepalived（两个节点操作）

确认主从 MySQL 数据库启动正常后启动 Keepalived 进程

systemctl start keepalived #启动
systemctl restart keepalived #重启
systemctl stop keepalived #关闭
systemctl status keepalived # 状态
# 加为系统服务：
chkconfig --add keepalived
# 开机启动：
chkconfig keepalived on
# 查看开机启动的服务：
chkconfig --list
# 查看VIP是否挂载
ip a
# 查看keepalived启动日志
tail -f /var/log/message
# 查看脚本监控日志
tail -f /var/log/keepalived/alert_myha.log