CentOS define same date and timezone for system and MySQL

Question

I realized that some of my droplets in Digital Ocean have different timezone between OS and MySQL. By default, I've configured all vms to Brazilian timezone (America/Sao_Paulo) through tzselect, and, assuming that MySQL comes using default timezone as of system.

So, when I type for current timestamp, I get this from CentOS:

date

Thu Jun  9 09:43:03 BRT 2016

and from MySQL:

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2016-06-09 12:45:57 |
+---------------------+
1 row in set (0.00 sec)

mysql>

Reading docs of MySQL 5.5, I have option to use timezone as string, as 'America/Sao_Paulo' for instance, however, CentOS 6.5 comes with MySQL 5.1, and it doesn't has the same option, only using hours, as +3:00 ...I'm sure that I guess I should not worry about date/time of MySQL since I've configured it on system.

CentOS configs for timezone that I did:

I've used tzselect command to set Brazilian timezone, thinking that MySQL will assume this configuration. After reboot, I realize that system erase my configs on tzselect, to prevent this, I put this line at /etc/rc.local:

ln -sf /usr/share/zoneinfo/America/Sao_Paulo /etc/localtime

Even if this configs, I realize some vms are as UTC e some others with BRT...and at this moment, I really don't know how I'll configure all my servers, at least 40 machines, with same way.

Does someone can help me explaining how can I correctly set timezone for system a also for MySQL?

Additional Infos

System: CentOS 6.5 x64 MySQL: 5.1 (default)

Below, command asked by @lese:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2016-06-09 14:31:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

$ date
Thu Jun  9 11:31:22 BRT 2016

And machine time at /etc/sysconfig/clock file:

[root@xxxx]# cat /etc/sysconfig/clock
ZONE=Etc/UTC

Best regards.

Answer 1

I found the problem,

I've used tzselect to select my timezone, however, /etc/sysconfig/clock was with ZONE=America/New_York, causing a little mistake on system and apps that consuming system date/time info. To fix it, and keep bios clock synchonized with system's clock, did these steps:

sudo vim /etc/sysconfig/clock

ZONE="America/Sao_Paulo"
UTC=true
ARC=false

Then reboot system.

sudo reboot

Remove unneeded timezone parameters, such as default_time_zone, from /etc/my.cnf (this one for CentOS) or /etc/mysql/my.cnf (for Ubuntu based OS).

Then reboot MySQL server instance:

sudo /etc/init.d/mysqld restart

And finally, to check if all configurations are OK, use these commands:

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2016-06-09 15:18:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[carlos@xxxx-setup ~]$ date
Thu Jun  9 15:18:34 BRT 2016
[carlos@xxx-setup ~]$

Thanks @lese by your attention.

Answer 2

I hate to necro old posts, but this seemed like the only appropriate place to post this info. In CentOS 7, the default MariaDB 10 setup (using CentOS standard rpms) has the wrong location of the timezone files. We were having issues with applications that depend on MySQL for timezone info. No matter what settings we used within the applications, they always returned UTC time zone time.

This:

mysql_tzinfo_to_sql /usr/share/zoneinfo/right |mysql -u root mysql -p

Immediately corrected the issue. No reboot necessary! I cleared caches etc for good measure...