mysql 定期维护_Mysql数据库维护

数据备份

#!/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)