MYSQL操作笔记

修改用户密码

[官方链接]（https://dev.mysql.com/doc/mysql-windows-excerpt/5.7/en/resetting-permissions-windows.html） 方法一（老版本）：使用set password命令 格式`mysql> set password for username@loclhost=password(‘passwd’);

mysql> set password = password('Gepoint');
Query OK, 0 rows affected (0.36 sec)

方法二：使用alter命令

alter user 'root'@'localhost' identified by '123321';

方法三：修改user表

UPDATE mysql.user
    SET authentication_string = PASSWORD('MyNewPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

定时任务

  1. 打开定时器

     -- 开启event
 SET GLOBAL event_scheduler='ON';
 -- 查看定时器
 SHOW VARIABLES LIKE 'event_scheduler';

  2. 写存储过程供定时任务调用

    ```sql

DELIMITER $$DROP PROCEDURE IF EXISTS backup_procedure$$ CREATE PROCEDURE backup_procedure() BEGIN DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; START TRANSACTION; SELECT * FROM user INTO OUTFILE ‘f:backda.sql’; IF t_error=1 THEN ROLLBACK; ELSE COMMIT; END IF; SELECT t_error; END $$ DELIMITER ;

DROP EVENT IF EXISTS backup_event; CREATE EVENT backup_event ON SCHEDULE EVERY 6 SECOND ON COMPLETION PRESERVE DISABLE DO CALL backup_procedure();

ALTER EVENT myevent_insert ON COMPLETION PRESERVE DISABLE;

ALTER EVENT backup_event ON COMPLETION PRESERVE ENABLE

SHOW VARIABLES LIKE ‘event_scheduler’

set GLOBAL event_scheduler = off

SELECT * FROM mysql.event

```