Boss told me to change the main database (MySQL) time!!! --UTC problems

Because of my company another operation and maintenance of small partner installed system, check the UTC this east. (which results in a time error of eight hours slower) The result is the experience of this one.

1, the system time is eight hours slow, I found that something is wrong, then changed back, can refer to my other blog.

2. The database was eight hours slow, and was unaware of the problem.

Suddenly in the experiment, the boss called me, said MySQL time is not right, let me help to solve. What? No, at that time the first is this anyway, to look after, sure enough time, and local time comparison, the difference of eight hours. I reacted immediately, and it was the UTC that caused the curse. OK, check it out, after all, it is the hard truth to solve the problem first.

Know:

5.10.8. mysql Server time zone support

The MySQL server has several time zone settings:

· The system time zone. When the server starts, it attempts to determine the host's time zone, which is used to set the System_time_zone system variable.

· The current time zone of the server. The Global system variable Time_zone represents the time zone that the server is currently using. The initial value is ' system ', which indicates that the server time zone is the same as the system time zone. You can explicitly specify an initial value with the --default-time-zone=timezone option. If you have super privileges, you can set the global variable value at run time using the following statement:

· mysql> SET GLOBAL time_zone = TimeZone;
· The time zone for each connection. Each client connection has its own time zone setting, given by the session Time_zone variable. Its initial value is the same as the global variable time_zone, but it can be reset with the following statement:

· mysql > SET time_zone = timezone;

You can query the current global variable values and the time zone for each connection in the following ways

Mysql> SELECT @ @global. time_zone, @ @session. Time_zone;

+--------------------+---------------------+

| @ @global. Time_zone | @ @session. Time_zone |

+--------------------+---------------------+

| SYSTEM | SYSTEM |

+--------------------+---------------------+

1 row in Set (0.00 sec)

Mysql>

solutions;

1. Interim Solutions

View Current Database time

Mysql> select Curtime ();

+-----------+

| Curtime () |

+-----------+

| 08:53:05 |

+-----------+

1 row in Set (0.00 sec)

Mysql> Select Now ();

+---------------------+

| Now () |

+---------------------+

| 2016-01-31 08:53:08 |

+---------------------+

1 row in Set (0.00 sec)

Mysql>

To modify the time zone:

mysql> SET time_zone = ' ""; # When this is Beijing, we are located in the East 8 district

Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

Mysql>

This method seems to be used only on the terminal, the time after exiting the terminal will become the original.

2. Permanent Solution

Vim/etc/my.cnf

[Mysqld]

Default-time_zone = ' the ' "

Restart the MySQL service.

/etc/init.d/mysqld restart

Note: My environment is not error, because I built a simulation environment, it is recommended to operate in a simulated environment, avoid the version, the environment caused unnecessary errors and losses.

Other questions:

If the database has a master-slave structure, restarting the database causes the master to never synchronize.

Individual suggested methods:

1. Stop slave First ( stop Slave;) then modify the slave time zone (unfortunately, the system is not installed), wait until the server restarts, review the location of the slave synchronization binlog, and then modify the master's time zone, reboot to see the current master location, re-authorize, Salve re-learning (can be viewed from the slave after restart), and finally start slave; no accident, this is feasible, after all, I do.

2, if not fail, then wait for master and slave time zone are changed, look at the current location of master, and then slave import the latest database, and then re-learn.

3, I tried slave do not import the latest data, from the beginning from the Master binglog location learning, but the experiment error. Later, the new data is imported and then learned. (Why the failure, it is not clear, on the Internet to restart the database, the main reason for never synchronizing is many.) ）

Personal Summary:

1, related to the problem of the database, it is best to simulate the real environment in the experimental environment, operate without error in the production environment, to avoid unnecessary errors. If the problem is relatively large, remember to make a backup, backup is the kingly way.

2, encountered problems, first calm, careful investigation. Don't complain, solve the problem first, then suggest.

3, do not neglect some unnecessary problems. A little UTC, actually brought so many small detours.

Above is the individual in the production environment encountered some small problems, share to everyone, hope and we share with each other, common progress.

qq:1145507435

Email: [Email protected]

This article comes from "? Only! "Blog, be sure to keep this provenance http://renzhiyuan.blog.51cto.com/10433137/1782399

Boss told me to change the main database (MySQL) time!!! --UTC problems