Change AWS RDS mysql time zone-excerpt from Network

AWS RDS

When building a database on AWS, not DB on EC2 is RDS, but when RDS is selected, what does timezone do with it?

"For AWS, which is offered globally, utc" is a natural, and RDS is no exception. " When migrating a server to AWS, the "database can use Chinese time" is a common problem. DB on EC2, you can configure the system's timezone, but RDS is not a direct login, so we need to use the MySQL feature to implement.

Describes how to modify the RDS MySQL timezone.

The master user in RDS is different from the MySQL root user and therefore does not have super privileges (Administrator privileges). Therefore, you cannot modify timezone using the Set global command. Use the set session command here to modify the timezone using the mysql init_connect parameter. The Init_conect parameter is actually modified in the parameter group.

Precautions

• Rdsadmin users seem to be the users AWS uses to manage RDS instances and cannot judge the extent of the impact so that the Rdsadmin timezone is not modified.
• The direct fill command in the Init_connect parameter does not work, so define stored Procedure and execute it by call

The operation flow is as follows

1. Create stored Procedure
2. Create parameter Group
3. Parameter Grouup Association to RDS
4. Restart RDS

1. Create Stored Procedure

Log in to the rds,shared schema with the master user to create the stored Procedure (shared.store_time_zong).

Configure the time to Chinese standard Time (asia/chongqing).

1234567891011

Mysql> CREATE DATABASE shared; QueryOK, 1 row affected (0.00 sec) Mysql> DELIMITER | Mysql> CREATE PROCEDURE shared. ' Store_time_zone ' () -> IF (POSITION (' [email protected] ' in current_user ()) =< c18> 1) Then-> SET SESSION time_zone = ' asia/chongqing '; -> END IF | QueryOK, 0 rows affected (0.01 sec) Mysql> DELIMITER ;

Confirm Stored Procedure

123456789101112131415161718

Mysql> Select now (); +---------------------+|Now () | +---------------------+|-04-09 05:10:41 | +---------------------+1 row in set (0.00 sec) Mysql> Call shared.store_time_zone; QueryOK, 0 rows affected (0.07 sec) Mysql> Select now (); +---------------------+|Now () | +---------------------+|-04-09 13:10:52 | +---------------------+1 row in set (0.00 sec)

Confirm that the time is changed to Chinese standard time (8 hours faster than UTC).

2. Create parameter Group

RDS is managed in the form of parameter group, MySQL parameters, where the default parameter group is not modified, creating a parameter group.

1. Click: Parameter Groups
2. Click: Create Parameter Group

1. Parameter Group Family: Select mysql5.6 (Because our RDS MySQL version is 5.6.22)
2. Group name:timezone-chongqing
3. Description:init_connect Call Store_time_zone

1. Choice: timezone-chongqing
2. Click: Edit Parameters

1. Init_connect:call Shared.store_time_zone

1. Click: Save Changes

1. Select: RDS Instance (awspack)
2. Click: Instance Actions
3. Click: Modify

1. DB Parameter group:timezone-chongqing
2. Click: Continue

1. Confirm: DB Parameter Group is timezone-chongqing
2. Click: Modify DB Instance

Stored Procedure is called through Init_connect when the user connects to RDS.

3. Restart RDS

Before restarting the RDS instance, confirm that the status of the Parameter group is applying.

1. Click: RDS Dashboard Instances
2. Select: RDS Instance Awspack
3. Click: Instance Actions
4. Click: Reboot

After restarting the RDS instance, confirm that the status of Parameter group is In-sync

Aws_rds user Login to MySQL after viewing, time in China time.

1234567891011121314151617181920212223242526272829

$MySQL- h Awspack.crhydmkxhg6d.ap-northeast-1.rds.amazonaws.com-uaws_rds- p Enter Password: WelcomeTo the MySQL Monitor. Commands End with ; or \g. YourMySQL connection ID is 7 Server version: 5.6.22-log MySQL Community server (GPL) Copyright(c), Oracle and /or its affiliates. All rights reserved. Oracleis a registered trademark of the Oracle Corporation and/or its Affiliates.other names trademarks of their respective Owners.Type ' help; ' or ' \h ' for help . Type ' \c ' to clear the current input statement. Mysql> show global variables like ' init_connect '; +---------------+-----------------------------+|variable_name | Value | +---------------+-----------------------------+|Init_connect | Call shared.store_time_zone | +---------------+-----------------------------+1 row in set (0.00 sec) Mysql> Select now (); +---------------------+|Now () | +---------------------+|-04-09 13:44:10 | +---------------------+1 row in set (0.00 sec)

Change AWS RDS mysql time zone-excerpt from Network