数据备份
#!/bin/bash
backupdir=/home/q/www/yshow.qunar.com/backup/
time=` date +%Y%m%d%H `
/home/q/mysql/cellar/mysql57/bin/mysqldump -h127.0.0.1 -P3306 -u root -pYSHOW yshow | gzip > $backupdiryshow_$time.sql.gz
exec /bin/su -c "find $backupdir -name "yshow_*.sql.gz" -type f -mtime +5 -delete"
问题 5.6 版本在命令行中输入密码报错
mysqldump: [Warning] Using a password on the command line interface can be insecure.
解决办法：
my.cnf中添加配置
[mysqldump]
user=your_backup_user_name
password=your_backup_password
修改完配置文件后, 只需要执行mysqldump 脚本就可以了。备份脚本中不需要涉及用户名密码相关信息。
定时清理过期数据
编写sql语句检验是否出错
DELETE FROM _mysql_session_store WHERE expires < TIMESTAMPDIFF(SECOND, '1970-1-1 8:0:0', NOW())*1000;
-
使用存储过程创建函数
- 查看是否开启
mysql> show variables like '%event_sche%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
```
没有的话使用 set global event_scheduler=1; 开启
重启数据库生效需求改 my.cnf 文件
[mysqld]
event_scheduler=ON //这一行加入mysqld标签下
- 创建 procedure
mysql> delimiter //
mysql> create procedure clear_expire() -> begin -> DELETE FROM _mysql_session_store WHERE expires < TIMESTAMPDIFF(SECOND, '1970-1-1 8:0:0', NOW())*1000;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
- 创建定时任务
mysql> create event clear_expire
-> on schedule every 1 day
-> on completion preserve disable
-> do call clear_expire();
Query OK, 0 rows affected (0.00 sec)
查看定时任务状态 & 开启或关闭
mysql> SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
+--------------+---------------------+----------------+----------------+----------+
| event_name | event_definition | interval_value | interval_field | status |
+--------------+---------------------+----------------+----------------+----------+
| clear_expire | call clear_expire() | 1 | DAY | DISABLED |
+--------------+---------------------+----------------+----------------+----------+
1 row in set (0.00 sec)
mysql> alter event clear_expire on completion preserve enable;
Query OK, 0 rows affected (0.00 sec)
