zabbix的歷史數據都存於mysql中，而資料主要儲存於history、history_uint、trends_uint、trends，這4張表中，並且分
爲數值的歷史數據與圖形的歷史數據。
數值數據：history、history_uint
圖形數據：trends_uint、trends
嘗試直接使用資料庫表對表的方式倒回，結果是失敗的，原因在於zabbix2跟3的資料庫設計不太相同，無法直接互拉資料，需要先查詢在針對項目ID在進行增刪改查。
以下示例操作history資料表，以查詢的方式找出item與host，zabbix2和3進行比對之後，最後再將zabbix2的數值與zabbix的主機項目進行合併，進而取回先前zabbix2的所有資料，若要拉回所有資料還需要將其他三張表（history_uint、trends_uint、trends）導回才行。
查詢比對
zabbix2 舊表查詢
舊表 192.168.8.30 查出history中對應的item與host
‐‐ 查詢history的項目數
SELECT
history.itemid,
items.`name` ,
`hosts`.`host`
FROM
items
INNER JOIN `hosts` ON items.hostid = `hosts`.hostid
INNER JOIN history ON history.itemid = items.itemid
‐‐ 透過itemid去除重複
GROUP BY itemid
HAVING count(*)>1
查詢結果
表關聯
已經得到所有history對應出item,host的結果，這邊我只篩選出主機"FGT-100D", "NEWPC","IPCam"，因爲只需要這些主機的資料。
SELECT *
FROM (
SELECT
history.itemid,
items.`name` ,
`hosts`.`host`
FROM
items
INNER JOIN `hosts` ON items.hostid = `hosts`.hostid
INNER JOIN history ON history.itemid = items.itemid
GROUP BY itemid
HAVING count(*)>1 ) AS new
-- 篩選主機"FGT-100D", "NEWPC","IPCam"
WHERE new.`host` IN (
"FGT-100D",
"NEWPC",
"IPCam"
);
查詢結果 共67筆
192.168.8.30 中的itemID已經出來
zabbix3 新表查詢
換新表 192.168.8.28 zabbix3版本，操作方式也是相同的。
透過查詢會發現一樣的項目但itemID卻不一樣，所以直接倒回是沒有用的，所以需要進行ID匹配在倒回數據。
[图片上传失败...(image-5b0542-1520318815140)]
查詢對比
新舊表合併查詢
這邊我將剛剛查詢zabbix2跟3的結果拉出來，再次進行查詢找出同主機同項目對應的itemid。
-- history新舊表合併查詢
SELECT
zabbix3_history.itemid as zabbix3_itemid,
zabbix3_history.`name` as zabbix3_name,
zabbix3_history.`host` as zabbix3_host,
zabbix2_history.itemid as zabbix2_itemid,
zabbix2_history.`name` as zabbix2_name,
zabbix2_history.`host` as zabbix2_host
FROM
zabbix3_history
INNER JOIN zabbix2_history ON zabbix3_history.`name` = zabbix2_history.`name`
[图片上传失败...(image-b27db0-1520318815140)]
倒回資料
新建一張表 new_history
將"zabbix2"匹配的itemID搜索出來，新建一張表。
更新itemid
資料是要由zabbix2倒回zabbix3的，剛剛已經建立了一張zabbix2的表，現在同樣把zabbix3匹配的item找出來直接取代更換掉zabbix2的ID，這樣的方式目的在於只更換項目而數據是保留的。
使用試算表協助建立更新語法
在試算表中可以看到zabbix2跟3的對應，實際上就是把zabbix2的itemid取代成zabbix2的。
執行
UPDATE new_history SET new_history.itemid = 25541 WHERE new_history.itemid = 25089;
UPDATE new_history SET new_history.itemid = 25542 WHERE new_history.itemid = 25091;
UPDATE new_history SET new_history.itemid = 25554 WHERE new_history.itemid = 26710;
UPDATE new_history SET new_history.itemid = 25554 WHERE new_history.itemid = 26814;
UPDATE new_history SET new_history.itemid = 25555 WHERE new_history.itemid = 26711;
UPDATE new_history SET new_history.itemid = 25555 WHERE new_history.itemid = 26815;
UPDATE new_history SET new_history.itemid = 25557 WHERE new_history.itemid = 26710;
UPDATE new_history SET new_history.itemid = 25557 WHERE new_history.itemid = 26814;
UPDATE new_history SET new_history.itemid = 25558 WHERE new_history.itemid = 26711;
UPDATE new_history SET new_history.itemid = 25558 WHERE new_history.itemid = 26815;
UPDATE new_history SET new_history.itemid = 25648 WHERE new_history.itemid = 26819;
UPDATE new_history SET new_history.itemid = 25649 WHERE new_history.itemid = 26875;
UPDATE new_history SET new_history.itemid = 25650 WHERE new_history.itemid = 26823;
UPDATE new_history SET new_history.itemid = 25651 WHERE new_history.itemid = 26820;
UPDATE new_history SET new_history.itemid = 25652 WHERE new_history.itemid = 26821;
UPDATE new_history SET new_history.itemid = 25654 WHERE new_history.itemid = 26832;
UPDATE new_history SET new_history.itemid = 25656 WHERE new_history.itemid = 26833;
UPDATE new_history SET new_history.itemid = 25657 WHERE new_history.itemid = 26818;
...
取代對應ID
等了很久，總算更新完了...
匯出Sql
將new_history匯出 SQL檔
匯出後，打開Sql檔取代下列文字，若檔案較大建議使用支持大文件的編輯器（例如：emeditor）
INSERT INTO `new_history`
改爲
insert ignore into `history`
用insert是因為並不是要取代整個"history"表，只是要追加項目，而用insert ignore是要避免重複插入。
開始倒回數據
將取代完的sql，重新匯入。
倒回成功！！
