MySQL集群读写分离方案A-(MySQL+GTID+ProxySQL+Keepalive)之2/7：系统优化
一、需要准备的主机多台Linux操作系统
1、操作系统规划
（1）数据库服务器
8台（IP 地址范围：192.168.21.150~157,主机名：mysqldb，mysqldb01~07），不安装 MySQL，
（2）负载均衡服务器：
2台（IP 地址范围：192.168.21.161~162：mysqlfz01，mysqlfz02）
（3）数据库中间件：
2台（IP 地址范围：192.168.21.171~172：mysqlmr01，mysqlmr02）
2、系统环境(虚拟机环境）
Linux操作系统：RHEL/OEL 7.X MySQL版本：MySQL 5.7+
3、资源配置
CPU：4c 内存：8G
4、MySQL相关数据存放（创建相关数据库存放）
（1）规划数据目录
MySQL软件：/mysql/soft MySQL应用：/mysql/app MySQL数据：/mysql/data MySQL日志：/mysql/log MySQL备份：/mysql/backup
（2）创建目录
mkdir -p /mysql/soft mkdir -p /mysql/app mkdir -p /mysql/data mkdir -p /mysql/log mkdir -p /mysql/backup
三、系统环境配置（只安装一台,其他克隆）
1、host文件配置：
vi /etc/hosts
echo "192.168.21.151 mysql151" >> /etc/hosts echo "192.168.21.152 mysql152" >> /etc/hosts echo "192.168.21.153 mysql153" >> /etc/hosts echo "192.168.21.154 mysql154" >> /etc/hosts echo "192.168.21.155 mysql155" >> /etc/hosts echo "192.168.21.156 mysql156" >> /etc/hosts echo "192.168.21.157 mysql157" >> /etc/hosts echo "192.168.21.171 mysqlm01" >> /etc/hosts echo "192.168.21.172 mysqlm02" >> /etc/hosts
2、配置中文环境变量并生效
echo "export LANG=en_US.UTF8" >> ~/.bash_profile echo "export LANG=en_US.UTF8" >> /etc/profile
source ~/.bash_profile
3、挂载本地磁盘，进行yum源配置
mount /dev/cdrom /mnt cd /etc/yum.repos.d mkdir bk mv public-yum-ol7.repo bk/ echo "[EL7]" >> EL7.9.repo echo "name = linux 7.9 dvd" >> EL7.9.repo echo "baseurl=file:///mnt" >> EL7.9.repo echo "gpgcheck=0" >> EL7.9.repo echo "enabled=1" >> EL7.9.repo
yum list
4、修改系统为多用户模式
systemctl set-default multi-user.target systemctl get-default
5、配置系统资源限制
（1）资源限制1-（内存是8G）
echo "* soft memlock 7000000" >> /etc/security/limits.conf echo "* hard memlock 7000000" >> /etc/security/limits.conf echo "vm.nr_hugepages=2500" >> /etc/sysctl.conf echo "vm.swappiness=10" >> /etc/sysctl.conf echo "vm.min_free_kbytes=512000" >> /etc/sysctl.conf echo "#vm.min_free_kbytes=512000" >> /etc/sysctl.conf sysctl -p if [ $? != 0 ] then cat <<EOF>> /etc/sysctl.conf net.ipv4.tcp_max_tw_buckets = 6000 net.ipv4.ip_local_port_range = 1024 65000 #net.ipv4.tcp_tw_recycle = 0 net.ipv4.tcp_tw_reuse = 1 net.core.somaxconn = 262144 net.core.netdev_max_backlog = 262144 net.ipv4.tcp_max_orphans = 262144 net.ipv4.tcp_max_syn_backlog = 262144 net.ipv4.tcp_synack_retries = 2 net.ipv4.tcp_syn_retries = 1 net.ipv4.tcp_fin_timeout = 1 net.ipv4.tcp_keepalive_time = 30 net.ipv4.tcp_keepalive_probes = 6 net.ipv4.tcp_keepalive_intvl = 5 net.ipv4.tcp_timestamps = 0 EOF sed -i 's/net.bridge.bridge-nf-call-ip6tables = 0/#net.bridge.bridge-nf-call-ip6tables = 0/g' /etc/sysctl.conf sed -i 's/net.bridge.bridge-nf-call-iptables = 0/#net.bridge.bridge-nf-call-iptables =0/g' /etc/sysctl.conf sed -i 's/net.bridge.bridge-nf-call-arptables = 0/#net.bridge.bridge-nf-call-arptables= 0/g' /etc/sysctl.conf fi
sysctl -p
（2）资源限制2
echo "* - nproc 16384" > /etc/security/limits.d/90-nproc.conf
6、关闭Linux安全服务
（1）selinux关闭
echo "SELINUX=disable" >/etc/selinux/config echo "#SELINUXTYPE=trageted" >> /etc/selinux/config cat /etc/selinux/config setenforce 0
（2）防火墙关闭
systemctl status firewalld.service systemctl stop firewalld.service systemctl disable firewalld.service
7、关闭透明大页
echo "if test -f /sys/kernel/mm/transparent_hugepage/enabled; then">> /etc/rc.d/rc.local echo "echo never > /sys/kernel/mm/transparent_hugepage/enabled">> /etc/rc.d/rc.local echo "fi">> /etc/rc.d/rc.local echo "if test -f /sys/kernel/mm/transparent_hugepage/defrag; then">> /etc/rc.d/rc.local echo "echo never > /sys/kernel/mm/transparent_hugepage/defrag">> /etc/rc.d/rc.local echo "fi">> /etc/rc.d/rc.local chmod +x /etc/rc.d/rc.local
8、配置登录限制
echo "session required pam_limits.so" >> /etc/pam.d/login cat /etc/pam.d/login
9、配置安装资源限制
echo "* soft nproc 16384" >> /etc/security/limits.conf echo "* hard nproc 16384" >> /etc/security/limits.conf echo "* soft nofile 16384" >> /etc/security/limits.conf echo "* hard nofile 65536" >> /etc/security/limits.conf echo "* soft stack 16384" >> /etc/security/limits.conf echo "* hard stack 32768" >> /etc/security/limits.conf cat /etc/security/limits.conf
10、关闭avahi-daemon服务
systemctl stop avahi-daemon.socket avahi-daemon.service systemctl disable avahi-daemon.socket avahi-daemon.service
11、配置磁盘scheduler
（1）配置队列
cat /sys/block/sd*/queue/scheduler #--sas/stat echo 'deadline' > /sys/block/sdb/queue/scheduler echo 'deadline' > /sys/block/sdc/queue/scheduler echo 'deadline' > /sys/block/sdd/queue/scheduler echo 'deadline' > /sys/block/sde/queue/scheduler echo 'deadline' > /sys/block/sdf/queue/scheduler #--ssd #echo 'NOOP' > /sys/block/sd*/queue/scheduler
（2）开启后生效(下面两部分)
vi /etc/rc.local #减少预读 echo '16' > /sys/block/sdb/queue/read_ahead_kb echo '16' > /sys/block/sdc/queue/read_ahead_kb echo '16' > /sys/block/sdd/queue/read_ahead_kb echo '16' > /sys/block/sde/queue/read_ahead_kb echo '16' > /sys/block/sdf/queue/read_ahead_kb #增大队列 echo '512' > /sys/block/sdb/queue/nr_requests echo '512' > /sys/block/sdc/queue/nr_requests echo '512' > /sys/block/sdd/queue/nr_requests echo '512' > /sys/block/sde/queue/nr_requests echo '512' > /sys/block/sdf/queue/nr_requests
12、更改时区
cp -f /usr/share/zoneinfo/Asia/Shanghai /etc/localtime hwclock
13、禁用 NUMA:
vi /etc/default/grub GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off"
然后执行：
grub2-mkconfig -o /etc/grub2.cfg numastat numactl --show numactl --hardware
--重启数据库
shutdown -r now
14、删除文件
rm -f /etc/udev/rules.d/70-persistent-net.rules
四、克隆虚拟机并修改单独配置
1、克隆所有主机
记录所有主机的网卡的mac地址；（在虚拟机设置里查看） ifconfig |grep ether 192.168.21.150 mysqldb 00:0c:29:1a:fa:e3 192.168.21.151 mysqldb01 00:0c:29:28:35:22 192.168.21.152 mysqldb02 00:0c:29:6c:78:f2 192.168.21.153 mysqldb03 00:0c:29:26:74:30 192.168.21.154 mysqldb04 00:0c:29:db:88:75 192.168.21.155 mysqldb05 00:0c:29:4a:b1:0c 192.168.21.156 mysqldb06 00:0c:29:14:29:9b 192.168.21.157 mysqldb07 00:0c:29:bb:a9:ab 192.168.21.161 mysqlfz01 00:0c:29:b7:48:6c 192.168.21.162 mysqlfz02 00:0c:29:81:a2:d1 192.168.21.171 mysqlm01 00:0c:29:73:11:76 192.168.21.172 mysqlm02 00:0c:29:3d:03:77
2、每台主机单独配置主机名
echo "mysqldb01" > /etc/hostname
3、每台主机修改网络配置中 的UUID
vi /etc/sysconfig/network-scripts/ifcfg-ens34
4、修改 IP、删除 UUID
--修改IP
--删除UUID
注意把 /etc/sysconfig/network-scripts/ifcfg-ens33的UUID注释掉即可
5、给所有虚拟机做快照，
关机做快照，防止出现安装错误，需要重装，比较麻烦。
