mysql8.0.26安装

服务器优化

关闭大叶内存transparent_hugepage

vi /etc/rc.local
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

chmod +x   /etc/rc.local
查看结果
cat /sys/kernel/mm/transparent_hugepage/enabled
cat  /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]  括号中是never就没问题

文件句柄

cat >> /etc/security/limits.conf <<EOF
*    soft  nofile 1000000
*    hard  nofile 1000000
*    soft  nproc  16384
*    hard  nproc  16384
*    soft  stack  32768
*    hard  stack  32768
*    soft  memlock unlimited
*    hard  memlock unlimited
EOF
ulimit -n 180000

内存优化

cat >> /etc/sysctl.conf  <<EOF
fs.file-max = 6553600  #  系统所有进程一共可以打开的文件数量
fs.nr_open = 6553500
vm.swappiness = 5 
vm.dirty_ratio = 20           # 脏叶比例达到总内存的20%  
vm.dirty_background_ratio = 10  # 脏叶比例达到总内存的10% 异步刷新
vm.dirty_expire_centisecs=30

net.ipv4.tcp_max_syn_backlog = 65536 # 表示SYN队列的长度，默认为1024，建议加大队列的长度为8192或更多，这样可以容纳更多等待连接的网络连接数
net.core.netdev_max_backlog = 32768 # 表示当每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许发送到队列的数据包最大数
net.core.somaxconn = 32768  # 该选项默认值是128，这个参数用于调节系统同时发起的TCP连接数，在高并发的请求中，默认的值可能会导致链接超时或重传，因此，需要结合并发请求数来调节此值。

net.core.wmem_default = 8388608 # 发送套接字缓冲区大小的默认值
net.core.rmem_default = 8388608 # 接收套接字缓冲区大小的默认值
net.core.rmem_max = 16777216 # 接收套接字缓冲区大小的最大值
net.core.wmem_max = 16777216 # 发送套接字缓冲区大小的最大值

net.ipv4.tcp_timestamps = 0  # 关闭时间戳 启用了会有问题
net.ipv4.tcp_synack_retries = 2 # 参数的值决定了内核放弃连接之前发送SYN+ACK包的数量。
net.ipv4.tcp_syn_retries = 2 # 表示在内核放弃建立连接之前发送SYN包的数量

net.ipv4.tcp_tw_recycle = 1  # 表示开启TCP连接中TIME-WAIT sockets的快速回收 服务器time_wait网络状态数量过多设置的
#net.ipv4.tcp_tw_len = 1  # 用于快速减少在TIME-WAIT状态TCP连接数   共用同一个NAT设备环境下有问题不要开
net.ipv4.tcp_tw_reuse = 1 # 表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认值为0，表示关闭

net.ipv4.tcp_mem = 786432 2097152 3145728
net.ipv4.tcp_rmem = 4096 4096 16777216
net.ipv4.tcp_wmem = 4096 4096 16777216
net.ipv4.tcp_max_orphans = 3276800 # 用于设定系统中最多有多少个TCP套接字不被关联到任何一个用户文件句柄上

net.ipv4.tcp_fin_timeout = 2  # 表示套接字由本端要求关闭，这个参数决定了它保持在FIN-WAIT-2状态的时间，默认值是60秒
#net.ipv4.tcp_keepalive_time = 1800 # 表示当keepalive起用的时候，TCP发送keepalive消息的频度。缺省是2小时，改为30分钟
net.ipv4.ip_local_port_range = 1024 65535 # 该选项用来设定允许系统打开的端口范围，即用于向外连接的端口范围
net.ipv4.route.gc_timeout = 100   #  路由缓存刷新频率，当一个路由失败后多长时间跳到另一个路由，默认是300
net.inet.udp.checksum=1   #防止不正确的udp包的攻击
net.ipv4.icmp_ignore_bogus_error_responses = 1   # 开启恶意icmp错误消息保护
net.ipv4.icmp_echo_ignore_broadcasts = 1    # 避免放大攻击
EOF
sysctl -p

说明:
vm.swappiness = 5  内存使用还剩5%才开始使用swap
vm.dirty_ratio = 20 内存脏叶到达20%时候刷新 异步刷新
vm.dirty_background_ratio = 10 内存脏叶到达10%时候强制刷新 强制刷新

IO调度策略

cat /sys/block/sda/queue/scheduler   如果是noop [deadline] cfq没问题
选用规则
SAS 盘： deadline
SSD&PCI-E盘： noop
修改
临时:
echo deadline >/sys/block/sda/queue/scheduler

永久:
vi /boot/grub/grub.conf
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

安装

下载软件

https://downloads.mysql.com/archives/community/

解压

tar xf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.26-linux-glibc2.12-x86_64  mysql && mv mysql /usr/local/mysql

添加用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

添加环境变量

cat >> /etc/profile <<EOF
export PATH=$PATH:/usr/local/mysql/bin
EOF

source /etc/profile
mysql -V

安装依赖

yum install libaio library numactl -y

创建目录

mkdir -p /data/3306/data
chown -R mysql.mysql /data/3306

简单编写配置文件

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql 
basedir=/usr/local/mysql
datadir=/data/3306/data 
socket=/tmp/mysql.sock 
port=3306
server_id=3306

innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2
gtid-mode=on
enforce-gtid-consistency=true
log_bin=mysql-bin
slow_query_log=1
slow_query_log_file=slow.log
long_query_time=4
log_queries_not_using_indexes
log_error=error.log
innodb_flush_method =O_DIRECT
transaction_isolation=READ-COMMITTED
innodb_flush_log_at_trx_commit=2
sync_binlog =100
log_bin_trust_function_creators=1
default_authentication_plugin=mysql_native_password
max_allowed_packet = 128M
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M
secure-file-priv=
[mysql]
socket=/tmp/mysql.sock
EOF

初始化数据库

5.7以上无密码
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data/

注意: mysqld --defaults-file=/etc/my.cnf   --initialize-insecure 可以指定配置文件
5.7以上有密码
mysqld --initialize --user=mysql --basedir=/usr/local/mysql80 --datadir=/data/3306/data/

5.6初始化方法
/usr/local/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf

启动方式

systemctl启动

cat > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 50000
EOF


systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

mysqld_safe启动

# 当mysqld 异常crash会尝试去启动mysqld
  mysqld_safe --defaults-file=/etc/my.cnf &
关闭:
  admin -uroot -p123 shutdown
  登录数据库以后执行 shutdown;

mysqld启动

# 启动日志全部会打印到屏幕 ----> 可以进行问题排查
mysqld &

/etc/init.d启动

cp /usr/local/mysql/support-files/mysql.server   /etc/init.d/mysqld
/etc/init.d/mysqld restart

# 添加服务
chkconfig --add mysqld

systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

备份

db_fullbackup.sh

#/bin/bash
#备份位置
BK_PATH=/db_backup

# 备份命令mysqldump路径
Mysqldump_dir=/usr/local/mysql/bin/

# 数据库登陆信息
mysql_user=kxmkxm
mysql_pass='Kxm@123456'
ip_addr='127.0.0.1'

# 如果目录不存在创建目录
if [ ! -d $BK_PATH ]; then
  mkdir -p $BK_PATH
fi

# 开始备份
$Mysqldump_dir/mysqldump  -u$mysql_user -p$mysql_pass -h$ip_addr  -A -R -E --master-data=2 --single-transaction --triggers > $BK_PATH/full_$(date +%F_%H-%M-%S).sql


# 保留七天的备份

find $BK_PATH  -mtime +7 -name "full*.sql" |xargs rm -rf

chmod +x db_fullbackup.sh

主从搭建

资源划分

角色	IP	端口	server_id
主库	10.0.0.12	3306	server_id=3306
从库	10.0.0.13	3306	server_id=3307

注意

2台以上的MySQL实例具备不同的 server_id, server_uuid，

时间同步

主库"开启binlog, 创建复制用户。

基于position号搭建主从

主库操作

创建复制用户和远程管理用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%';

备份主库数据
mysqldump  -A --source-data=2 --single-transaction -R -E --triggers >/tmp/full.sql

从库操作

还原主库数据
source /tmp/full.sql

启动主从复制
CHANGE MASTER TO 
MASTER_HOST='10.0.0.12', 
MASTER_USER='repl', 
MASTER_PASSWORD='123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1187, 
MASTER_CONNECT_RETRY=10;

开启主从   查看状态
start slave;
show slave status \G

基于gtid搭建主从

注意

mysql 8.0.26以后 --master-data=2 改为 --source-data=2

备份时加上参数--set-gtid-purged=on 在sql文件中有 set @@session.sql_log_bin= 0; 关闭二进制日志

则在还原时候不用设置gtid_purged 默认--set-gtid-purged=on

主库操作

创建复制用户和远程管理用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%';

备份主库数据
mysqldump  -A --source-data=2 --single-transaction -R -E --triggers >/tmp/full.sql

从库操作

还原主库数据
source /tmp/full.sql

启动主从复制
CHANGE MASTER TO 
MASTER_HOST='10.0.0.12',
MASTER_USER='repl', 
MASTER_PASSWORD='123', 
MASTER_PORT=3306, 
MASTER_AUTO_POSITION=1;

清除从库二进制日志从新设定位置
reset master;
set global gtid_purged = '0f8adf5f-c9c8-11ec-8fbb-000c2944ead6:1-3';

开启主从   查看状态
start slave;
show slave status \G

Clone-plugin搭建主从

介绍

克隆过程中可以发生DML操作但是不允许发生DDL操作

克隆过程中把产生的redo归档等备份完成把redo拷贝过去CR 属于物理备份

克隆过程中会把从数据库清空

版本要一致包括小版本

从库修改启动方式 mysqld_safe启动从库复制完不需要设置gtid_purged

主库操作

# 安装插件 创建用户 授权
INSTALL PLUGIN clone SONAME 'mysql_clone.so';  
create user test_s@'%' identified by '123';
grant backup_admin on *.* to test_s@'%';

主库创建复制用户
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%';

从库操作

从库修改启动方式
mysqld_safe --defaults-file=/etc/my.cnf &

# 安装插件 创建用户 授权 设置主库白名单
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
create user test_t@'%' identified by '123';
grant clone_admin on *.* to test_t@'%';
SET GLOBAL clone_valid_donor_list='10.0.0.12:3306';

# 开始克隆
CLONE INSTANCE FROM test_s@'10.0.0.12':3306 IDENTIFIED BY '123';


gtid方式构建主从
CHANGE MASTER TO 
MASTER_HOST='10.0.0.12',
MASTER_USER='repl', 
MASTER_PASSWORD='123', 
MASTER_PORT=3306, 
MASTER_AUTO_POSITION=1;


开启主从   查看状态
start slave;
show slave status \G

查看备份结果

#bin_log日志信息在这里
SELECT BINLOG_FILE, BINLOG_POSITION FROM performance_schema.clone_status;
use performance_schema ; select stage,stage from clone_progress;
sq
# 如果是gtid复制
SELECT @@GLOBAL.GTID_EXECUTED;

半同步复制

介绍

主库发送二进制日志给从库,并不关心从库是否落盘到relay log中
如果做个限制等从库把数据写入relay log以后,在确认成功就是半同步复制（ACK的线程完成）

loose- 后面的参数如果配置有问题就不执行

主库操作

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
rpl_semi_sync_master_wait_for_slave_count=1  # 只要有一个从库 返回ACK就认为 成功了
# 配置文件添加
loose-rpl_semi_sync_master_enabled = 1

查看是否加载成功:
show plugins;
show status like '%Rpl_semi_sync_master_status%';

从库操作

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

# 配置文件添加
rpl_semi_sync_slave_enabled = 1

查看是否加载成功:
show status like 'Rpl_semi_sync_slave_status';

重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

增强半同步复制

介绍

增强半同步复制也是在从库 relay log 返回ACK后主库才提交成功 ,区别是等待ack的时机不一样

主库commit 提交 redo日志落地,然后binlog日志落地,然后开始等待从库 relay log 收到消息后返回ack

如果从库正常返回ack 则主库在binlog处打标记, 更新事务状态执行成功

如果从库未收到没返回ack 则主库 binlog 未打标记, 不更新事务状态 ,事务回滚

主库操作

# 配置文件添加
rpl_semi_sync_master_wait_point=AFTER_SYNC
rpl_semi_sync_master_timeout=10000           # 默认为10秒  半同步复制超过10秒还没收到ACK就切换为异步复制
rpl_semi_sync_master_wait_for_slave_count=1  # 只要有一个从库 返回ACK就认为 成功了
# 查看
show variables  like '%wait_point%';

从库操作

过滤复制

介绍

前提: 主从已经搭建好

过滤复制可以限制主库也可以限制从库,一般限制从库

限制从库,其实日志也过来了只是从库sql线程没执行而已

主库操作

从库操作

vi /etc/my.cnf

解决跨库更新的问题
loose-replicate_wild_do_table=world.%         # 白名单 只复制world库下的所有表
loose-replicate_wild_ignore_table=world.t1    # 黑名单 不复制world.t1表

重启数据库
systemctl restart mysqld

延时从库

介绍

前提: 主从已经搭建好

主库操作

从库操作

stop slave;
CHANGE MASTER TO MASTER_DELAY = 300;    # 延时300秒=5分钟
start slave;

延时故障恢复

image-20220502232909128

关掉延时
stop slave;
CHANGE MASTER TO MASTER_DELAY = 0;

查看relaylog 找到误删除position号-比如是10   localhost-relay-bin.000002在数据目录
show relaylog events in "localhost-relay-bin.000002";

恢复到误删除之前  gtid号 图上info位置 创建完t5表, 
START SLAVE UNTIL SQL_BEFORE_GTIDS = '5f4c68b1-efac-11eb-af07-000c294497d3:9';

多源复制(MSR)5.7+

两个主库中的数据复制到一个从库中, 默认不会复制系统库包括用户(mysql)库

8.0以后支持主库指定那个库复制到从库 ,其他库不复制过去

从库GTID会有3个两个主库的和自己一个

如果有定时任务则从库定时任务是禁用状态主库开启从库也不会开启

IP规划

主机名	IP	端口	角色
db01	10.0.0.11	3306	master
db02	10.0.0.12	3306	master
db03	10.0.0.13	3306	slave

准备配置文件

必须开启这个否则主库数据不会同步过去

log-slave-updates=1 # 从库是否将复制线程执行的内容记录到binlog

主库1

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=6
socket=/tmp/mysql.sock
log_bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=2
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db01 [\\d]>
socket=/tmp/mysql.sock
EOF

主库2

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=7
socket=/tmp/mysql.sock
log_bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=2
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db02 [\\d]>
socket=/tmp/mysql.sock
EOF

从库

cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=8
socket=/tmp/mysql.sock
log_bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
log-slave-updates=1
secure-file-priv=/tmp
innodb_flush_method=O_DIRECT
slow_query_log=ON
slow_query_log_file=/data/3306/data/db01-slow.log
long_query_time=2
log_queries_not_using_indexes
master_info_repository=TABLE
relay_log_info_repository=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]
prompt=db03 [\\d]>
socket=/tmp/mysql.sock
EOF

启动数据库

# 初始化数据   启动数据库
pkill mysqld 
rm -rf /data/3306/data/* 

mysqld --initialize-insecure --user=mysql -- basedir=/usr/local/mysql --datadir=/data/3306/data
cp /usr/local/mysql/support-files/mysql.server   /etc/init.d/mysqld
/etc/init.d/mysqld start
mysql

2个主节点操作

创建复制用户  用户名 repl 密码 123
set sql_log_bin=0;
create user repl@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave on *.* to repl@'10.0.0.%' ;
set sql_log_bin=1;

从节点操作

# 连接 12
CHANGE MASTER TO 
MASTER_HOST='10.0.0.11',
MASTER_USER='repl', 
MASTER_PASSWORD='123', 
MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_1';

# 连接 13
CHANGE MASTER TO 
MASTER_HOST='10.0.0.12',
MASTER_USER='repl', 
MASTER_PASSWORD='123', 
MASTER_AUTO_POSITION=1 FOR CHANNEL 'Master_2';

启动主从
start slave for CHANNEL 'Master_1';
start slave for CHANNEL 'Master_2';

多源复制监控 13上

SHOW SLAVE STATUS FOR CHANNEL 'Master_1'\G
SHOW SLAVE STATUS FOR CHANNEL 'Master_2'\G
select * from replication_connection_configuration\G
SELECT * FROM replication_connection_status WHERE CHANNEL_NAME='master_1'\G
select * from performance_schema.replication_applier_status_by_worker;

多源复制配置过滤

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR CHANNEL "master_1";  # FOR CHANNEL "master_1"; 是8.0以后才有
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR CHANNEL "master_2";  # FOR CHANNEL "master_2"; 是8.0以后才有

keepalived

介绍

创建vip 当主库宕机可以切换到从库继续提供服务
当主库mysql宕机 停主库keealived vip漂移到从库 ，当主库修复完成 启动主库 启动keepalived此时vip还会漂移回来（可以修改优先级不让漂移）  
涉及到： vip  检测脚本   vrrp通信端口

资源划分

角色	IP	端口	安装软件
主库	10.0.0.12	3306	keepalived，mysql
从库	10.0.0.13	3306	keepalived，mysql
vip	10.0.0.10

主库配置

安装keepalived

yum install keepalived -y

vim /etc/keepalived/keepalived.conf

vrrp_script chk {
  script "/root/jiaoben"
  interval 2
  weight 2
}

vrrp_instance VI_1 {

    state MASTER
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
     10.0.0.13/24 dev eth0 label eth0:1
    }
    track_script {
    chk
    }
}

vi /root/jiaoben

#!/bin/bash
count=`ps -ef |grep -c '[m]ysqld'`
if [ $count -lt 2 ];then
systemctl stop keepalived.service
fi

从库配置

安装keepalived

yum install keepalived -y

vim /etc/keepalived/keepalived.conf

vrrp_script chk {
  script "/root/jiaoben"
  interval 2
  weight 2
}

vrrp_instance VI_1 {

    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
     10.0.0.13/24 dev eth0 label eth0:1
    }
    track_script {
    chk
    }
}

vi /root/jiaoben

#!/bin/bash
count=`ps -ef |grep -c '[m]ysqld'`
if [ $count -lt 2 ];then
systemctl stop keepalived.service
fi

1 网卡名字etho对应修改

2 virtual_router_id 51 主从要一样

3 state MASTER （主库修好还会自动切回来）， state BACKUP（两边都是backup 主库修好也不会切换）

4 10.0.0.13/24 dev eth0 label eth0:1 是vip地址

5 interval 2 脚本每2秒检测一次

6 script "/root/jiaoben" 脚本路径

mysql8.0.26安装

mysql8.0.26安装

服务器优化

关闭大叶内存transparent_hugepage

文件句柄

内存优化

IO调度策略

安装

下载软件

解压

添加用户

添加环境变量

安装依赖

创建目录

简单编写配置文件

初始化数据库

启动方式

备份

db_fullbackup.sh

主从搭建

资源划分

基于position号搭建主从

基于gtid搭建主从

Clone-plugin搭建主从

半同步复制

增强半同步复制

过滤复制

延时从库

多源复制(MSR)5.7+

keepalived

介绍

资源划分

主库配置

从库配置

推荐阅读更多精彩内容