MySQL server time synchronization problem processing

1. Adjust database server time

Because the app shook a shake activity found out of date 15 minutes, but also can continue to shake, the problem is that the database server time than the application server time is quite 18 minutes, and the app is the database now (), so it is caused. 2 solutions, 1 is to modify the program code, not through the database now () to fetch but through the application server, 2 is the time to modify the database directly. After the tradeoff, the second option is adopted.

1.1, modified by date-s time

 修改linux服务器时间date -s 2015-08-03修改时间：date -s 10:18:00

1.2, manual adjustment time using Ntpdate adjustment

安装ntpdate    yum install -y ntpdate手动调整时间(需要ntpdate)    ntpdate us.pool.ntp.org[[email protected] ~]# ntpdate us.pool.ntp.org25 Sep 22:33:44 ntpdate[11419]: the NTP socket is in use, exiting[[email protected] ~]#  [[email protected] ~]# service ntpd stop关闭 ntpd：[确定][[email protected] ~]# /usr/sbin/ntpdate 10.254.1.21;/sbin/clock --systohc25 Sep 22:48:12 ntpdate[11445]: step time server 10.254.1.21 offset 814.746911 sec[[email protected] ~]#  

1.3, write an automatic synchronization time of the script, join the scheduled task, synchronize once a day

2,linux time settings and synchronization 2.1 about time and time zone

If someone asks you what time it is, You looked at the watch and replied that he said it was 8 o'clock in the evening. The answer seems to have no problem, but if you ask this person in Europe then your answer will make him very confused, because he is still in the sun?

There is a question of how to define time. Because in the 24 hours that the Earth revolves around the sun, the time of sunrise and sunset is different around the world. So we have to divide the time zone (timezone) the need to divide the world into 24 different time zones. So we can interpret the definition of time as a time value plus the local time zone (note that this location can be accurate to the city)

We've all learned about Greenwich Mean Time (GMT) in geography class, which is 0 timezone. But what we often see in computers is UTC. It is shorthand for coordinated Universal time. Although the values of UTC and GMT can be considered equal (the error is quite small), UTC has been identified as an international standard, so we should all abide by the standards using UTC only.

So if the local time in China now is 8 o'clock in the evening, we can have the following two ways to express
20:00 CST
12:00pm UTC
The CST here is Chinese standard time, which is what we usually call Beijing. Because China is in the utc+8 time zone, and so on, so is UTC.
First, regardless of the time that we want to synchronize the system through any channel, usually the provider gives only the utc+0 time value and does not provide the time zone (because it does not know where you are). So when we set the system time, setting the timezone is the first thing to do.
Second, many countries have daylight saving time (China was set once in 2010 years), that is, one day of the year the clock dials one hour faster (for example, from Utc+8 to utc+9), then the same time will have to slow back again. If we set the correct timezone, The system automatically adjusts for us when we need to change the time.

Now let's take a look at how to set the TimeZone under Linux, which is time zone

2.2 Setting up the Linux time Zone

Under Linux glibc provides a number of pre-compiled timezone files, they are placed in the/usr/share/zoneinfo directory, which basically covers most of the countries and cities

[[Email protected]_idc_mon_1_12 ~]# ls-f/usr/share/zoneinfo/africa/asia/canada/cuba EST GB GMT-0 HST Iso3166.tab Kwajalein MIDEAST/NZPORTUGALPST8PDT Singapore us/zone.tabamerica/atlantic/cet EETEST5EDT GB-Eir E gmt+0 Iceland Israel Libya MST nz-chat posix/right/turkey UTC zuluantarctica/australia/chile/egy   PT ETC/GMT Greenwich indian/jamaica metmst7mdt pacific/posixrules ROC uctwetarctic/brazil/cst6cdt Eire Europe/gmt0 Hongkong Iran japanmexico/navajopolandprc ROK Universal W-su[[email protected]_idc_mon_1_12 ~]# [[E Mail protected]_idc_mon_1_12 ~]# ll/usr/share/zoneinfo/asia/shanghai-rw-r--r--. 2 root root 405 January 2013/usr/share/zoneinfo/asia/shanghai[[email protected]_idc_mon_1_12 ~]# Cp/usr/share/zonei NFO/ASIA/SHANGHAI/ETC/LOCALTIMECP: Do you want to overwrite "/etc/localtime"?  Y[[email protected]_idc_mon_1_12 ~]# date2015 September 26 Saturday 12:12:20 Cst[[email protected]_idc_mon_1_12 ~]#

Go to directory:/usr/share/zoneinfo/asia/, see there are many city areas of the name, find Shanghai for the Shanghai, and then you can use the zdump to check under, the time is normal, as follows:

[[email protected]_idc_mon_1_12 ~]# zdump /usr/share/zoneinfo/Asia/Shanghai/usr/share/zoneinfo/Asia/Shanghai  

So how do we tell the system where we are in time zone? There are many ways to do this, and here are two types:

The first one is to modify the/etc/localtime file, which defines the local time zone where I am located.

The second method also sets the value of the TZ environment variable. Many programs and commands will use the value of this variable. The value of TZ can be in multiple formats, and the simplest way to set up is to use the Tzselect command, as follows:

[[Email protected]_idc_mon_1_12 ~]# tzselectplease Identify a location so this time zone rules can be set CORRECTLY.P Lease Select a continent or ocean. 1) Africa 2) Americas 3) Antarctica 4) Arctic Ocean 5) Asia 6) Atlantic Ocean 7) Australia 8) Europe 9) Indian Ocean10) Pa Cific Ocean11) None-i want to specify the time zone using the Posix TZ format.#? 5Please Select a country.        1) Afghanistan Israel) Palestine 2) Armenia) Japan (Philippines 3) Azerbaijan Jordan Notoginseng) Qatar 4) Bahrain) Kazakhstan) Russia 5) Bangladesh (N  Orth) Saudi Arabia 6) (Bhutan) Korea (south), Singapore 7) Brunei) Kuwait 41)        Sri Lanka 8) Cambodia) Kyrgyzstan) Syria 9) Laos) Taiwan10 Lebanon) Tajikistan11) (East Timor) Macau) Thailand12) Georgia A         TURKMENISTAN13) Hong Kong (Mongolia) (Arab Emirates14) India) Myanmar (Burma) Uzbekistan15) (Indonesia) Nepal) Vietnam16) Iran) Oman) Ye MEN17) pakistan# Iraq)? 9Please Select one of the following time zone Regions.1) east China-beijing, Guangdong, Shanghai, Etc.2) Heilongjiang (E Xcept Mohe), Jilin3) Central China-sichuan, Yunnan, Guangxi, Shaanxi, Guizhou, Etc.4) Most of Tibet & Xinjiang5) Wes T Tibet & xinjiang#? 1The following information has been Given:china East China-beijing, Guangdong, Shanghai, etc. Therefore tz= ' Asia/shanghai ' would be used. Local time is Now:sat Sep 13:21:14 CST 2015.Universal time is now:sat Sep 05:21:14 UTC 2015.Is the above Informa tion ok?1) Yes2) no#? Yesplease Enter 1 for Yes, or 2 for no.#? 1You can make this change permanent to yourself by appending the line tz= ' Asia/shanghai '; Export Tzto the file '. ProfIle ' in your home directory; Then logs out and logs in again. Here is the TZ value again, this time on the standard output so that youcan use the/usr/bin/tzselect command in shell script S:asia/shanghai[[email protected]_idc_mon_1_12 ~]# [[Email protected]_idc_mon_1_12 ~]# more/etc/  LocalTime Tzif2cst-8[[email protected]_idc_mon_1_12 ~]#

The value of the TZ variable is override/etc/localtime. This means that when the TZ variable is not defined, the system uses/etc/localtime to determine the time zone.

So if you want to change the time zone permanently, then you can write the TZ variable's settings to/etc/profile.

2.3 Read Time Clock (RTC) and System Clock

Said set the time to make it clear that another concept is that we have two clocks on a single computer: A hardware time clock (RTC), and a system clock.

Hardware clock is embedded in the motherboard of the special circuit, its existence is usually after we shut down can also calculate the reason for the time;
The system clock is the clock that the kernel of the operating system uses to calculate the time. It is from January 1, 1970 00:00:00 UTC time to the value of the sum of the seconds so far, and the system time in Linux is synchronized with the hardware time (synchronization), and then it runs independently;

So since two clocks run alone, then the time will inevitably produce an error, the following we look at an example;

See the various uses of Hwclock with the HWCLOCK–HELP command:

[[email protected]_idc_mon_1_12 ~]# hwclock --show2015年09月26日 星期六 13时58分34秒  

Hwclock–hctosys set hardware time to system time

HWCLOCK–SYSTOHC set the system time to hardware time

Hwclock–set–date= "Mm/dd/yy hh:mm:ss" Set hardware time we can set it up in the BIOS when we boot. You can also use the Hwclock command

Date-s "dd/mm/yyyy hh:mm:ss" modifies the system time with the date command is the simplest.

Now we know how to set the system and hardware time. But the question is, what if these two times are inaccurate? Then we need to find a server on the Internet that can provide us with accurate time and then synchronize our system time with a protocol, then this Protocol is NTP. And then all we have to say about synchronization is the synchronization between the system time and the network server.

2.4 Preparation before setting up NTP server

In fact this title should be changed to set "NTP Relay Server" before the preparation more appropriate. Because no matter how well our computer is configured to run for a long time, there will be errors, so it is not enough to make NTP server for other servers on the Internet. The true ability to accurately measure time is still an atomic clock. But because atomic clocks are so expensive that only a small number of organizations have them, they connect to the computer and become a real NTP Server. All we have to do is connect to these servers to synchronize the time of our system and then make our own servers into NTP Relay server and then provide synchronization services to the Internet or to users in the LAN.

1). To set up an NTP Relay server is actually very simple, we first put the required RPM packaging

RPM-IVH ntp-4.2.2p1-5.el5.rpm

2). Find the NTP Server that provides synchronization services to us on the internet

Http://www.pool.ntp.org is the official NTP website, where we can find the nearest NTP Server from our city. NTP recommends that we find at least two NTP servers in order to guarantee the accuracy of the time

So for example, in the UK, you can choose the following two servers

0.uk.pool.ntp.org1.uk.pool.ntp.org它的一般格式都是number.country.pool.ntp.org

China's NTP server address:

server 133.100.11.8 preferserver 210.72.145.44server 203.117.180.36server 131.107.1.10server time.asia.apple.comserver 64.236.96.53server 130.149.17.21server 66.92.68.246server www.freebsd.orgserver 18.145.0.30server clock.via.netserver 137.92.140.80server 133.100.9.2server 128.118.46.3server ntp.nasa.govserver 129.7.1.66server ntp-sop.inria.fr

Server (National Service Center IP address)

3). Make a synchronization with these servers before opening the NTP server, so that our machine's time is as close as possible to standard time.

We can use the Ntpdate command here.

ntpdate 0.uk.pool.ntp.org6 Jul 01:21:49 ntpdate[4528]: step time server 213.222.193.35 offset -38908.575181 secntpdate 0.pool.ntp.org6 Jul 01:21:56 ntpdate[4530]: adjust time server 213.222.193.35 offset -0.000065 sec

If you have an outrageous time difference, you will see a larger adjustment at the first, so it is safe to run two times. So why run synchronization manually before you open the NTP service?

1. Because according to NTP settings, if your system time is faster than the correct time, then NTP will not help you adjust, so either you set the time back, or do a manual synchronization

2. NTP takes a long time to adjust when your time setting differs greatly from the time of the NTP server. So manual synchronization can reduce this time

2.5 Configuring and running NTP Server

Now we're going to create the NTP configuration file, which is/etc/ntp.conf. All we need to do is join the NTP server above and a driftfile.

 vim /etc/ntp.confserver 0.uk.pool.ntp.orgserver 1.uk.pool.ntp.orgdriftfile /var/lib/ntp/ntp.drift

It's very simple. Next we start the NTP Server and set it to run automatically after powering on

 /etc/init.d/ntpd start chkconfig --level 35 ntpd on

2.6 Viewing the health of an NTP service

Now that we have started the NTP service, is our system time synchronized with the server? This NTP provides a good viewing tool: NTPQ (NTP query)

I recommend that you run the NTPQ command after you open the NTP server to monitor the operation of the server. Here we can use the Watch command to see the changes in server values over time

 watch ntpq -pEvery 2.0s: ntpq -p                                  Sat Jul 7 00:41:45 2007     remote           refid      st t when poll reach   delay   offset jitter==============================================================================+193.60.199.75   193.62.22.98     2 u   52   64 377    8.578   10.203 289.032*mozart.musicbox 192.5.41.41      2 u   54   64 377   19.301 -60.218 292.411

Now I'm going to explain what it means.

Remote: It refers to an NTP server that is connected to the local machine

refID: It refers to a server that provides time synchronization to a remote server (e.g. 193.60.199.75)

ST: the level of the remote server. Because NTP is a layered structure, there is a top-level server, multi-tier relay server, and then to the client. So the server can be set to 1-16 from the high to the low level. To slow down the load and network congestion, you should avoid connecting directly to a Level 1 server in principle.

T: This ... I don't know what that means. ^_^

When: I personally think of it as a timer to tell us how long the local machine needs to synchronize with the remote server once

Poll: How much time the local and remote servers synchronize (in seconds). The poll value will be smaller at the beginning of the NTP run, and the frequency of synchronization with the server will increase, as soon as possible to the correct time range. Then the poll value will gradually increase and the frequency of synchronization will decrease correspondingly.

Reach: This is an octal value that is used to test whether a connection to the server can be made. Each successful connection will increase its value

Delay: Round trip time to send synchronization requests from the local machine to the server

Offset: This is the most critical value, which tells us the time difference between the local machine and the server. The closer the offset is to 0, the closer we get to the server.

Jitter: This is a value used to do statistics. It counts the distribution of offset in a particular contiguous number of connections. To put it simply, the smaller the absolute value, the more accurate the time we have and the server.

Then the careful words will find two questions: The first we connect is 0.uk.pool.ntp.org why and remote server is not the same? What does the second and last + and * mean?

The first question is not difficult to understand, because NTP provides us with a cluster server so every connection to the resulting server may be different. This also tells us that you should use hostname instead of IP when specifying NTP server

The second question is related to the first one, since there are so many servers that are designed to serve us properly in the event of a problem. So how do you know the state of these servers? This is the message that the first sign will tell us.

• : It tells us that the remote server has been identified as our primary NTP server and that the time of our system will be provided by this machine
• : It will provide synchronization services for us as a secondary NTP server and a server with a number. When the server is unavailable, it can take over.
• : The remote server was clustering algorithm considered to be a non-conforming NTP server
  x: Remote server not available
  Knowing this, we can monitor the time synchronization of our system in real time.

2.7. NTP security settings

Running an NTP server does not require a lot of system resources, so you can provide time synchronization services to many clients without having to specifically configure a separate server, but some basic security settings are necessary.

So here's a very simple idea is the first we allow only a subset of the local area network users to connect to our server. The second one is that these clients can't modify the time on our servers.

In the/etc/ntp.conf file we can use the Restrict keyword to configure the above requirements

First, we reject all actions for the default client

Then allow the operation of the native address everything:

restrict default kod nomodify notrap nopeer noquery

Finally, we allow all clients in the LAN to connect to this server for synchronization time. But refused to let them modify the time on the server:

restrict 127.0.0.1

By adding these three strips to/etc/ntp.conf, we have completed our simple configuration. NTP can also use key to do Authenticaiton, here is not detailed introduction:

restrict 192.168.1.0 mask 255.255.255.0 nomodify

2.8. NTP Client Settings

To do this we already have a relay server of our own. If we want to make time synchronization for other clients in the LAN, then we should build another relay server, and then point all the client to both servers ( Be careful not to point all the clients to servers on the Internet. Just add your own server to the client's ntp.conf.

Code:

server ntp1.leonard.comserver ntp2.leonard.com

2.9. Some additions and supplements 1. What is driftfile in the configuration file?

Each of our system clock frequency has a small error, this is why the machine is running for a period of time is not accurate. NTP automatically monitors the error values of our clocks and adjusts them. But the problem is that this is a lengthy process, so it writes the recorded error first to Driftfile. So that the results of the calculation will not be lost even after you reboot.

2. How do I synchronize my hardware clock?

NTP typically synchronizes only the system clock. But if we also want to synchronize the RTC, then just open the following option to it.

You can check whether your NTP service is started by Ps–ef |grep NTP or by using PGREP–LF NTP. The NTP can then be detected by the Snoop command.

Snoop |grep–i NTP for detection.

After the NTP service is established, the NTP service can be managed with 2 tool commands:

One is NTPQ is an interactive application command, under which there are many subcommands available for everyone to use. Use peers to view the synchronization process. If you need additional commands, you can enter help to view them.

There is also a tool command that is ntpdate This command is commonly used by clients of NTP. You can see the synchronization information for NTP in/var/adm/messages. If you need more detailed information about NTPQ and ntpdate, you can use man to help with queries.

3, the other network time server address is as follows:

China National GMT Service Center:

http://www.time.ac.cn/stime.asp

Some of the other centres:

time.nist.gov(美国)ntp.fudan.edu.cn(复旦)(国内用户推荐使用此服务器)timekeeper.isi.edusubitaneous.cpsc.ucalgary.causno.pa-x.dec.comtime.twc.weather.comswisstime.ethz.chntp0.fau.dentp3.fau.detime-a.nist.govtime-b.nist.govtime-nw.nist.govnist1-sj.glassey.com

reference article record:http://www.cnblogs.com/liuyou/archive/2012/07/29/2614338.html

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL server time synchronization problem processing