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;

定时任务

打开定时器

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

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


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`