Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL Server Time Synchronization Problem     - tar command to extract a file error: stdin has more than one entry (Linux)

- Ubuntu upgrade to Linux Kernel 4.2.3 Kernel (Linux)

- Linux command to view the system status (Linux)

- Installation and Configuration Munin monitoring server on Linux (Server)

- how to quickly find a file on Linux system (Linux)

- How to install Gnome 3.10 in Ubuntu 13.10 (Linux)

- Web server security policy (Linux)

- MySQL Online DDL tools of pt-online-schema-change (Database)

- IntelliJ IDEA run in Mac10.9 and JDK7 environment (Linux)

- To setup NOTRACK and TRACK of conntrack in iptables (Linux)

- In the case of using cgroups Ubuntu 14.04 and Docker (Linux)

- OpenGL shadow map (Programming)

- Linux Shell introduces (Linux)

- Examples 14 grep command (Linux)

- JBPM6 Installation and Configuration Tutorial (Linux)

- Netfilter / Iptables Comments (Linux)

- Linux Regular expressions grep and egrep (Linux)

- How to install Docker and basic usage on Ubuntu 15.04 (Server)

- Ubuntu 15.04 install Complete Guide (Linux)

- Acquaintance C ++: the most simple C ++ program (Programming)

  MySQL Server Time Synchronization Problem
  Add Date : 2018-11-21      
  1, Adjust the database server time
Because the application app a shake activities found outdated 15 minutes, we can continue to shake the root of the problem is that the database server time quite 18 minutes time than the application server, and database app is made now (), so it caused. Two solutions, one is to modify the code, not by the database now () to get through the application server but taking; 2 is to directly modify the time the database. After weighing the adoption of the second scenario.

1.1, modified by date -s
 Modify linux server time
date -s 2015-08-03
Change the time:
date -s 10:18:00
1.2, manually adjust the time using ntpdate adjustment
Install ntpdate
    yum install -y ntpdate
Manually adjust the time (required ntpdate)
    ntpdate us.pool.ntp.org

[Root @ testmysqldb ~] # ntpdate us.pool.ntp.org
25 Sep 22:33:44 ntpdate [11419]: the NTP socket is in use, exiting
[Root @ testmysqldb ~] #

[Root @ testmysqldb ~] # service ntpd stop
Close ntpd: [OK]
[Root @ testmysqldb ~] # / usr / sbin / ntpdate; / sbin / clock --systohc
25 Sep 22:48:12 ntpdate [11445]: step time server offset 814.746911 sec
[Root @ testmysqldb ~] #
1.3, write a script to automatically synchronize the time, adding the timing scheduled tasks, sync once per day
vim /etc/cron.daily/ntpdate
#! / Bin / bash
/ Usr / sbin / ntpdate -s us.pool.ntp.org
/ Usr / sbin / hwclock --systohc

Granted permission
 chmod + x /etc/cron.daily/ntpdate

Join crontab tasks, sync once every 20 minutes
[Root @ testmysqldb ~] # crontab -l
* / 20 * * * * sh /etc/cron.daily/ntpdate

[Root @ testmysqldb ~] #
2, Linux time settings and synchronization
2.1 About the time and time zone
If someone asks you what time it is? You looked at his watch told him that the 20 o'clock so the answer appears to be no problem, but if you ask this person, then in Europe, then your answer will make him very puzzled because he was there when the sun is also empty it?

Here there is a problem arises of how to define the time, because the earth rotates around the sun 24 hours, the time of sunrise and sunset around the world is not the same, so we have time zone (timezone) necessary to divide, that is, the global division into 24 different time zones, so we can define the time to understand the value of time plus a time zone location (note that this location can be accurate to the city)

Geography lesson we have learned Greenwich Mean Time (GMT), it is 0:00 time zone, but we often see in the computer is UTC. It is shorthand for Coordinated Universal Time. Although it is possible that the value of UTC and GMT equal (error quite small), but UTC has been identified as an international standard, so we should follow standard uses only UTC.

So if China local time now is 20:00, then we can have the following two representations
20:00 CST
12:00 UTC
Here CST is Chinese Standard Time, that is, we usually refer to the Beijing because China is in the UTC + 8 time zone, and so it is the 12:00 UTC.
First, we want through any channel regardless of time synchronization system, usually given only provider UTC + time value of 0 will not provide the time zone (because it does not know where you are). So when we set the system time time, set the time zone is the first work to be done
Second, many countries have daylight saving time (set in 2010, when China once), that is one day of the year the clock ahead one hour (eg from UTC + 8 look into UTC + 9), then Similarly, even when the time dial slowly come back. If we set the correct time zone, when you need to change the time when the system will automatically adjust for us

Now we look at how to set the time zone in Linux, which is time zone

2.2 Setting Linux Time Zone
In Linux glibc provides precompiled many timezone file, they placed in the / usr / share / zoneinfo directory, where basically covers most of the country and city

[Root @ wgq_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.tab
America / Atlantic / CET EETEST5EDT GB-Eire GMT + 0 Iceland Israel Libya MST NZ-CHAT posix / right / Turkey UTC Zulu
Antarctica / Australia / Chile / Egypt Etc / GMT Greenwich Indian / Jamaica METMST7MDT Pacific / posixrules ROC UCTWET
Arctic / Brazil / CST6CDT Eire Europe / GMT0 Hongkong Iran JapanMexico / NavajoPolandPRC ROK Universal W-SU
[Root @ wgq_idc_mon_1_12 ~] #

[Root @ wgq_idc_mon_1_12 ~] # ll / usr / share / zoneinfo / Asia / Shanghai
-rw-r -. r-- 2 root root 405 1 Yue 23 2013 / usr / share / zoneinfo / Asia / Shanghai
[Root @ wgq_idc_mon_1_12 ~] # cp / usr / share / zoneinfo / Asia / Shanghai / etc / localtime
cp: whether to override "/ etc / localtime"? y
[Root @ wgq_idc_mon_1_12 ~] # date
September 26, 2015 Saturday 12:12:20 CST
[Root @ wgq_idc_mon_1_12 ~] #
Go to the directory: / usr / share / zoneinfo / Asia /, see that there are many names urban areas, found Shanghai to Shanghai, then you can use to check zdump, the normal time as follows:

[Root @ wgq_idc_mon_1_12 ~] # zdump / usr / share / zoneinfo / Asia / Shanghai
/ Usr / share / zoneinfo / Asia / Shanghai Sat Sep 26 13:11:23 2015 CST
[Root @ wgq_idc_mon_1_12 ~] #
So how are we to tell the system where we TIME ZONE which it There are many ways, here are two ways that?:

The first is to modify / etc / localtime this document which defines what where my local time zone.

[Root @ wgq_idc_mon_1_12 ~] # cp / usr / share / zoneinfo / Asia / Shanghai / etc / localtime
cp: whether to override "/ etc / localtime"? y
[Root @ wgq_idc_mon_1_12 ~] # date
September 26, 2015 Saturday 13:19:00 CST
[Root @ wgq_idc_mon_1_12 ~] #
The second method will set the value of TZ environment variable will be used in many programs and commands the value of this variable can have the value of TZ variety of formats, the easiest method is to use tzselect set command, as shown below.:

[Root @ wgq_idc_mon_1_12 ~] # tzselect
Please identify a location so that time zone rules can be set correctly.
Please 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 Ocean
10) Pacific Ocean
11) none - I want to specify the time zone using the Posix TZ format.
#? 5
Please select a country.
 1) Afghanistan 18) Israel 35) Palestine
 2) Armenia 19) Japan 36) Philippines
 3) Azerbaijan 20) Jordan 37) Qatar
 4) Bahrain 21) Kazakhstan 38) Russia
 5) Bangladesh 22) Korea (North) 39) Saudi Arabia
 6) Bhutan 23) Korea (South) 40) Singapore
 7) Brunei 24) Kuwait 41) Sri Lanka
 8) Cambodia 25) Kyrgyzstan 42) Syria
 9) China 26) Laos 43) Taiwan
10) Cyprus 27) Lebanon 44) Tajikistan
11) East Timor 28) Macau 45) Thailand
12) Georgia 29) Malaysia 46) Turkmenistan
13) Hong Kong 30) Mongolia 47) United Arab Emirates
14) India 31) Myanmar (Burma) 48) Uzbekistan
15) Indonesia 32) Nepal 49) Vietnam
16) Iran 33) Oman 50) Yemen
17) Iraq 34) Pakistan
#? 9
Please select one of the following time zone regions.
1) east China - Beijing, Guangdong, Shanghai, etc.
2) Heilongjiang (except Mohe), Jilin
3) central China - Sichuan, Yunnan, Guangxi, Shaanxi, Guizhou, etc.
4) most of Tibet & Xinjiang
5) west Tibet & Xinjiang
#? 1

The following information has been given:

    east China - Beijing, Guangdong, Shanghai, etc.

Therefore TZ = 'Asia / Shanghai' will be used.
Local time is now: Sat Sep 26 13:21:14 CST 2015.
Universal Time is now: Sat Sep 26 05:21:14 UTC 2015.
Is the above information OK?
1) Yes
2) No
#? Yes
Please enter 1 for Yes, or 2 for No.
#? 1

You can make this change permanent for yourself by appending the line
    TZ = 'Asia / Shanghai'; export TZ
to the file '.profile' in your home directory; then log out and log in again.

Here is that TZ value again, this time on standard output so that you
can use the / usr / bin / tzselect command in shell scripts:
Asia / Shanghai
[Root @ wgq_idc_mon_1_12 ~] #
[Root @ wgq_idc_mon_1_12 ~] # more / etc / localtime
[Root @ wgq_idc_mon_1_12 ~] #
The value of the TZ variable will override / etc / localtime. That is when the TZ variable is not defined when the system was to use / etc / localtime to determine the time zone.

So you want to permanently modify the time zone, then you can set the TZ variable write / etc / profile Lane

2.3 Read Time Clock (RTC) and System Clock
Here I would like to set the time, said another concept is clearly on a computer we have two clocks: a so-called hardware time clock (RTC), also referred to as a system clock (System Clock);

Refers to the hardware clock embedded on the motherboard of a special circuit, its presence is usually shut down after we can also calculate the reasons for the time;
The system clock is the operating system kernel calculated time clock is used to it now value the sum of the number of seconds from January 1, 1970 00:00:00 UTC time; in the Linux system boot time will be the time and hardware time synchronization (synchronization), then run it independently;

Since then the two clocks run independently, so a long time will inevitably produce errors, let's look at an example;

By hwclock -help command to view the various uses hwclock:

[Root @ wgq_idc_mon_1_12 ~] # hwclock --show
September 26, 2015 Saturday 13:58:34 -0.219418 seconds
[Root @ wgq_idc_mon_1_12 ~] #
hwclock -hctosys the hardware time is set to the system time

hwclock -systohc the system time to hardware time

hwclock -set -date = "mm / dd / yy hh: mm: ss" Setting up the hardware of the time we can boot time settings in the BIOS can also use the hwclock command.

date -s "dd / mm / yyyy hh: mm: ss" command to change the system time with the date on the most simple

Now that we know how to set system and hardware time. But the problem is not accurate if the two time how to do? So we need to find on the Internet that can provide us with the exact time of the server and then be synchronized by a protocol our system time, then this agreement is the NTP. next we are saying we are all means to synchronize system time synchronization between server and network

NTP Server 2.4 Setup Preparation before
In fact, the title should be changed to set the "NTP Relay Server" Before you be more appropriate, because no matter how good our computer is configured to run a long time will produce an error, it is not sufficient to other servers on the Internet do NTP Server. Truly accurate atomic clocks to measure time or later. However, due to the atomic clock is very expensive, only a few organizations have their connection to the computer has become a true NTP server. and we have to do is connect to these servers to synchronize our system time, and then make our own server NTP Relay server is to give Internet or LAN users to provide synchronization service.

1). Setting up a NTP Relay Server is very simple, we first need RPM package
rpm -ivh ntp-4.2.2p1-5.el5.rpm

2). We found on the Internet to provide synchronization services NTP Server
http://www.pool.ntp.org is NTP's official website, in it we can find from our city nearest NTP Server. NTP suggested that we order to protect the accuracy of the time, at least two months to find the NTP Server

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

Its general format is number.country.pool.ntp.org
China's ntp server address:

server prefer
server time.asia.apple.com
server www.freebsd.org
server clock.via.net
server ntp.nasa.gov
server ntp-sop.inria.fr
server (National Center for timing server IP address)

3) Prior to the first open NTP server and these servers do a synchronized such that our machines as long as possible close to the standard time.
Here we can use the ntpdate command

ntpdate 0.uk.pool.ntp.org
6 Jul 01:21:49 ntpdate [4528]: step time server offset -38908.575181 sec
ntpdate 0.pool.ntp.org
6 Jul 01:21:56 ntpdate [4530]: adjust time server offset -0.000065 sec
If your time difference is ridiculous, then for the first time will see the adjustment is relatively large, it is safe to run twice. So why before opening the NTP service must first be run manually sync it?

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

When the time to set your time and NTP server vary greatly when, NTP will take a longer period of time to adjust, so you can reduce this time to manually synchronize

2.5 Configuring and running NTP Server
Now we have to create the NTP configuration file, and it is /etc/ntp.conf. We just need to add a driftfile NTP Server and above it

 vim /etc/ntp.conf
server 0.uk.pool.ntp.org
server 1.uk.pool.ntp.org
driftfile /var/lib/ntp/ntp.drift
Very simple. Then we start the NTP Server, and set it to run automatically at boot

 /etc/init.d/ntpd start
 chkconfig --level 35 ntpd on
2.6 View the NTP service health
Now that we have started the NTP service, but our time in the end system and the server does not do this synchronized NTP provides an excellent viewing tool:? Ntpq (NTP query)

I suggest that you open the NTP server can run after ntpq command to monitor the server is running. Here we can use the watch command to view the changes in the value of the server over time

 watch ntpq -p
Every 2.0s: ntpq -p Sat Jul 7 00:41:45 2007
    remote refid st t when poll reach delay offset jitter
================================================== ============================
+ 2 u 52 64 377 8.578 10.203 289.032
* Mozart.musicbox 2 u 54 64 377 19.301 -60.218 292.411
Now I will explain the meaning

remote: it refers to remote NTP server connected to the local machine

refid: It refers to the remote server (e.g. providing time synchronization server

st: The remote server level because NTP is a layer-type structure, at the top of the server, and then multilayer Relay Server client so the server can be set to low level in order to mitigate 1-16 load and network congestion... , in principle, should be avoided directly connected to the level of the server 1.

t: This ... .. What do you mean I do not know ^ _ ^

when: Personally, I understand it as a timer to tell us how long the local machine and the remote server requires a time synchronization

poll: local and remote synchronization server how much time (in seconds) at the beginning of this poll when running NTP value is relatively small, and as server synchronization frequency will increase, and can be adjusted to the correct time as soon as possible. range value after the poll will gradually increase, the frequency of the synchronization will be reduced accordingly

reach:. This is an octal value, used to test whether a successful connection and server connection every time its value will increase

delay: synchronization requires a round trip time to the server from the local machine to send

offset: This is the most critical value, it tells us the time difference between local and server offset closer to 0, and we are closer to the time server.

jitter: This is a statistical value used to do it a statistical distribution of offset in a specific consecutive number of connections in a nutshell, the more accurate the smaller the absolute value of the value of our time and the server.

So we carefully then we will find two problems: First, we are connected and remote server 0.uk.pool.ntp.org Why not the same as the second * + and foremost it all mean??

The first question is easy to understand, because NTP provide to us is a cluster server so the server has to give each connection may be different. Again, this tells us that in the time specified NTP Server hostname should be used instead IP

The second question and a related, since there are so many servers in the event of a problem is to other servers also normally provide services to us. So how do you know the status of these servers? This is the first mark It will tell us information

: It tells us that distal end server has been identified as our primary NTP Server, the time of our system will be provided by the machine
: It will serve as auxiliary NTP Server and server together with the number of services we provide synchronization when the number of the server is unavailable it can take over.
: The remote server clustering algorithm is considered to be unacceptable NTP Server
x: The remote server is unavailable
After that we can understand the real-time monitoring time synchronization status of our system of
2.7. NTP security settings
Running a NTP Server does not require a lot of system resources, so they do not specifically configured a standalone server, you can give a lot of client provides time synchronization services, but some basic security settings or necessary

So a very simple idea here is that we are only allowed within the first part of the LAN users to connect to our server. The second is that these client can not modify the time on our servers

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

First, we reject all the default client operations

Then allowed to address all the machine operations:

restrict default kod nomodify notrap nopeer noquery
Finally, we allow all LAN client to connect to this server to synchronize time but refused to allow them to modify the time on the server:

/etc/ntp.conf In these three added to complete our simple configuration NTP key can also be used to do authenticaiton, not described in detail here:

restrict mask nomodify
2.8. NTP client settings
Here we do have to own a Relay Server. If we want other client LAN time synchronization is the case then we should still be to build a Relay Server, and then all the client point two server (be careful not to point to all the client servers on the Internet). As long as the client's ntp.conf add this to your own server on it


server ntp1.leonard.com
server ntp2.leonard.com
2.9. Some complementary and Supplements
1. What is the profile driftfile that?
Each of our system clock frequency has a small error, this is why it will be inaccurate for some time after the machine is running. NTP to automatically monitor our error value and the clock to be adjusted, but the problem is that this is a lengthy process, so it will be recorded in the first error is written driftfile. so even after you reboot before the results will not be lost.

2. How to synchronize hardware clock?
NTP usually only synchronous system clock. But if we have to synchronize the RTC, then you just need to open the options below it.

By ps -ef | grep ntp or use pgrep -lf ntp check your ntp service is started. Ntp can then be detected by snoop command.

Snoop | grep -i ntp testing.

After the establishment of good ntp service, you can use two tools command ntp service management:

One is ntpq is an interactive application command, below it there are many sub-command for everyone to use. You can view the synchronization process using peers. If you need additional help you can enter commands for viewing.

There is a tool command ntp ntpdate This command is generally used for client use. You can see the situation ntp synchronization information in / var / adm / messages in. If you need more detailed information ntpq and ntpdate can use man to help with queries.

3, other network time server at the following address:
Beijing China National Time Service Center:

Other centers:

time.nist.gov (United States)
ntp.fudan.edu.cn (Fudan University) (domestic users recommend the use of this server)
- VMware installed Linux system and JDK deployment (Linux)
- Oracle 10g, 11g database silent installation of small differences (Database)
- Big Data Common Glossary (Linux)
- Java filter (Programming)
- LNMP summary of the issues common 502 Bad Gateway (Server)
- SVN common commands (Linux)
- Linux tmux tcpdump summary (Linux)
- Setting Derby as Linux / Windows running as a service from the start (Server)
- Linux environment variable configuration and save places (Linux)
- Python in os.path Magical (Programming)
- Getting the Linux shell variable test (Programming)
- Docker use Dockerfile created since the launch of the service support SSH container mirror (Server)
- Intrusion prevention network server security maintenance tips (Linux)
- Ubuntu under shadowsocks configuration instructions (Linux)
- There are more than one server, there is reason to try cloud operating system (Server)
- Java regular expression syntax (Programming)
- To install and deploy PHP environment under the CentOS (Server)
- Ubuntu 12.04 64-bit installation Redmine + Git + ReviewBoard (Linux)
- What happens after the MySQL disk space is full (Database)
- CentOS 6.6 install rsync server (Server)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.