In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
In this issue, the editor will bring you the problem of how to analyze the time zone of mysql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.
Problem: when the files from mysqldump are migrated to another library, the timestamp field is always 8 hours less! 1. When you see this, you quickly think of the time zone. Let's take a look at it first.
Mysql time zone
Mysql > show variables like'% zone%'
+-+ +
| | Variable_name | Value |
+-+ +
| | system_time_zone | CST |
| | time_zone | SYSTEM |
+-+ +
System time zone
[root@iZ2ze66bhrbxkc31nljgjnZ] # date-R
Fri, 23 Jun 2017 16:06:46 + 0800
Good. No problem.
two。 View table structure
MariaDB [ecejmaster] > desc svc_street_tmp_170623
+-- +
| | Field | Type | Null | Key | Default | Extra | |
+-- +
| | street_id | int (11) | NO | | 0 | |
| | city_id | int (11) | YES | | NULL |
| | street_name | varchar (50) | NO | |
| | status | tinyint (4) | YES | | NULL |
| | create_user | int (11) | YES | | NULL |
| | create_time | datetime | NO | | CURRENT_TIMESTAMP |
| | update_user | int (11) | YES | | NULL |
| | update_time | datetime | YES | | NULL |
| | del_flag | tinyint (4) | NO | | 0 | |
| | screate_time | timestamp | NO | | CURRENT_TIMESTAMP |
| | supdate_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |
+-- +
3 View data
MariaDB [ecejmaster] > select * from svc_street_tmp_170623 where street_id=17615 limit 1
+-- -+
| | street_id | city_id | street_name | status | create_user | create_time | update_user | update_time | del_flag | screate_time | supdate_time |
+-- -+
| | 17615 | 69 | 123 | 2 | 1 | 2017-06-23 15:37:24 | 1 | 2017-06-23 15:37:24 | 0 | 2017-06-23 15:37:24 | 2017-06-23 15:38:36 |
+-- -+
1 row in set (0.00 sec)
4 backup
[dbaadmin@YZ-PRO-DB-04] $mysqldump-udbmanager-pendant 12fAK1aR'-h 10.32.14.78 ecejmaster svc_street_tmp_170623-- where= "street_id=17615"-t
-- MySQL dump 10.15 Distrib 10.0.23-MariaDB, for Linux (x86 / 64)
--
-- Host: 10.32.14.78 Database: ecejmaster
-
-- Server version 10.0.23-MariaDB-log
/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /
/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /
/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /
/ * 40101 SET NAMES utf8 * /
/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /
/ * 40103 SET TIME_ZONE='+00:00' * /
/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /
/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /
/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /
--
-- Dumping data for table `svc_street_tmp_ 170623`
--
-- WHERE: street_id=17615
LOCK TABLES `svc_street_tmp_ 170623` WRITE
/ *! 40000 ALTER TABLE `svc_street_tmp_ 170623` DISABLE KEYS * /
INSERT INTO 'svc_street_tmp_ 170623` VALUES (17615, 69, 69, and 123).
/ *! 40000 ALTER TABLE `svc_street_tmp_ 170623` ENABLE KEYS * /
UNLOCK TABLES
/ * 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /
-see, the time is less than 8 hours. If you look closely, you will find that the timestamp timestamp will be less than 8 hours, and the datetime time type will not! Insert it into another library, and the time will be added to normal for another 8 hours.
However: if you specify parameters such as-t when dump (ignoring the set time_zone above), you will really lose 8 hours if you plug it back in. It just so happens that all we back up are insert statements, and all other information has been removed. 5 take a look at the instructions of mysqldump.
[dbaadmin@YZ-PRO-DB-04 ~] $mysqldump-- help | grep-I zone
-- tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
Zones or data is being moved between servers with
Different time zones.
It is exported in 0 time zone by default, which affects the timestamp time type
6 solution:
Mysqldump-uroot-S / data/3306/mysql.sock-pHP2T9wypjr6oEZRV ecejmaster3 $I-- compact-c-t-- skip-extended-insert-- skip-tz-utc skip the time zone
Mysqldump will cause an error of 8 hours for the field of timestamp time type. When saving insert, use skip-tz-utc to skip the time zone to solve the problem.
The above is the problem of how to analyze the pit ratio time zone of mysql shared by the editor. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 242
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.