master
log-bin=mysql-bin
server-id=1
systemctl restart mysqld.service
slave
server-id=2
systemctl restart mysqld.service
创建授权用户
grant replication slave, replication client on *.* to 'slave'@'10.0.5.78' identified by 'KX_ss3_DB@httcsec';
flush privileges;
查看position号
show master status;
登陆从库,添加相关参数
change master to master_host='10.0.5.78', master_user='slave', master_password='KX_ss3_DB@httcsec', master_port=3306, master_log_file='mysql-bin.000005', master_log_pos=2217, master_connect_retry=30;
查看主从同步状态
show slave status\G;
定时删除过期数据
mysql是否开启了事件功能
show variables like '%sc%';
临时开启事件功能
SET GLOBAL event_scheduler = ON; SET GLOBAL event_scheduler = 1; — 0代表关闭
永久开启
event_scheduler=ON
创建事件
每5s定时删除定时删除表中过期两分钟的数据
create event sys_delete_sysdept on schedule every 10 second do delete from sys_dept where timestamp < (CURRENT_TIMESTAMP() + INTERVAL -2 MINUTE);
修改事件
ALTER EVENT sys_delete_sysdept
on SCHEDULE EVERY 10 SECOND
DO
delete from sys_dept where sys_dept.money < 1000;
开启事件
alter event sys_delete_sysdept on completion preserve enable;
关闭事件
alter event sys_delete_sysdept on completion preserve disabled;
定期删除日志文件
添加定时任务
crontab -e
每天12点,13点执行删除日志文件记录
0 12,13 * * * /shell/delete_schedule.sh