Home > Uncategorized > Change MySQL Timezone on Amazon RDS

1. Create procedure

DELIMITER |
CREATE PROCEDURE mysql.store_time_zone()
IF NOT (POSITION('[email protected]' IN CURRENT_USER()) = 1) THEN
SET SESSION time_zone = '+8:00';
END IF
| DELIMITER ;

2. Grant permission

GRANT EXECUTE ON PROCEDURE mysql.store_time_zone TO 'YOUR_DATABASE_USER'@'%';

3. Validate the procedure (Enter in one line)

CALL mysql.store_time_zone; select NOW();

4. Edit parameter
Login to AWS Manamgent Console > Go to Parameter Groups > Edit Parameters > Search "init_connect" > Edit Values > Enter "CALL mysql.store_time_zone " > Save

5. Reboot server

Other commands:

// drop Procedure
DROP PROCEDURE IF EXISTS mysql.store_time_zone;

// show current procedures
show procedure status;
show create procedure mysql.store_time_zone;

// revoke permission
REVOKE EXECUTE ON PROCEDURE `mysql`.`store_time_zone` FROM 'YOUR_DATABASE_USER'@'%'

Reference:
http://mlwmlw.org/2014/03/rds-mysql-timezone-setup/
http://gab-tech.blogspot.hk/2013/12/mysql-change-time-zone-in-rds.html



Flag Counter