數(shù)據(jù)備份
#!/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 版本在命令行中輸入密碼報(bào)錯(cuò)
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
修改完配置文件后, 只需要執(zhí)行mysqldump 腳本就可以了。備份腳本中不需要涉及用戶名密碼相關(guān)信息测柠。
定時(shí)清理過期數(shù)據(jù)
編寫sql語(yǔ)句檢驗(yàn)是否出錯(cuò)
DELETE FROM _mysql_session_store WHERE expires < TIMESTAMPDIFF(SECOND, '1970-1-1 8:0:0', NOW())*1000;
-
使用存儲(chǔ)過程創(chuàng)建函數(shù)
- 查看是否開啟
mysql> show variables like '%event_sche%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
```
沒有的話使用 set global event_scheduler=1; 開啟
重啟數(shù)據(jù)庫(kù)生效需求改 my.cnf 文件
[mysqld]
event_scheduler=ON //這一行加入mysqld標(biāo)簽下
- 創(chuàng)建 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 ;
- 創(chuàng)建定時(shí)任務(wù)
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)
查看定時(shí)任務(wù)狀態(tài) & 開啟或關(guān)閉
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)
