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. 写存储过程供定时任务调用

    
    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`