how to sync time of my server to my database on logging?

[Zirius 1]

Hello! I have a host which is using a shared SQL (it works great, saves resources), the problem is, the SQL is installed on a server with a different timezone with my VPS. Now, all my logs time are recorded with different time, it is pretty hard for me to use the logs with this...

 

Any workaround?

 

Thanks in advance!

[Rand 6]

double sorry

Edited January 17, 2015 by Rand

[Rand 6]

Hello! I have a host which is using a shared SQL (it works great, saves resources), the problem is, the SQL is installed on a server with a different timezone with my VPS. Now, all my logs time are recorded with different time, it is pretty hard for me to use the logs with this...

 

Any workaround?

 

Thanks in advance!

try:

MySQL: SET time_zone='-03:00';"-03:00" - timezone u need. 

 

if no what os at your vps?

Edited January 17, 2015 by Rand

[Zirius 1]

 

Hello! I have a host which is using a shared SQL (it works great, saves resources), the problem is, the SQL is installed on a server with a different timezone with my VPS. Now, all my logs time are recorded with different time, it is pretty hard for me to use the logs with this...

 

Any workaround?

 

Thanks in advance!

try:

MySQL: SET time_zone='-03:00';"-03:00" - timezone u need. 

 

if no what os at your vps?

 

Oh, so that's how it should works?

I'll try this tomorrow on my server's maintenance.

 

Should I only query this on LOG database or also on main sql tables?

 

Thanks!

[Rand 6]

no matter but if restart mysql this setting resets. 

 

Variant 2.

  U have full access to mysql server and u can enter SSH as a root user:

 

 

run below command:

#mysql or mysql -u root -p 

than enter the password

 

1) Check time_zone* tables in mysql database.

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

2) To change / update timezone 

set global time_zone = timezone; Example: set global time_zone = "Asia/Calcutta";

restart mysql.

 

You can change the time zone as per the requirement without root user.

set time_zone = timezone; 

You can see the full list of possible values for ZONEINFO at /usr/share/zoneinfo or

 

VPS. From an SSH:

ls /usr/share/zoneinfo/ 

Then simply delete the current timezone:

rm /etc/localtime

And replace it with a symbolic link to the new timezone from /usr/share/zoneinfo. For example if your chosen zone is Pacific time:

ln –s /usr/share/zoneinfo/PST8PDT /etc/localtime

Or you can use the tzselect command:

 

You may need to remove the existing symlink before using tzselect

rm -f /etc/localtime

than use 

tzselect 

You can make this change permanent for yourself by appending the line

 

TZ='timezone'; 

export TZ to the file '.profile' in your home directory; then log out and log in again.

 

Synchronizing time with NTP server

 

The first step is to make sure you have the ntp program installed. Do a:

which ntpdate

Once ntp is installed synchronize your computer clock with:

 

 

ntpdate pool.ntp.org 

If ntp is not install, you may run this to install ntp

 

yum install ntp 

 To set time and date

 

date MMDDhhmmYYYY 

MM - Two digit month number
DD - Two digit date
hh - Two digit 24 hour system hour
mm - Two digit minute
YYYY - Four digit year code

Edited January 17, 2015 by Rand