Zabbix Database Migration

First, the environment

192.168.1.163 Zabbix because of competing with the online business resources, the impact is relatively large, it needs to be migrated, and now is the lamp deployment

192.168.1.190 the original Zabbix database to 3306 on this machine.

Two, 163 machine step

1. Export Zabbix data table structure

[Email protected] ~]# mysqldump-uroot-pdy2013-p3308-h127.0.0.1--opt-d Zabbix > Zabbix.sql

2. Upload the file to the 190 machine to restore

[Email protected] ~]# SCP zabbix.sql [email protected]:/root/

3, create can telnet database account

GRANT all privileges on * * to ' Zabbix ' @ ' percent ' identified by ' 123456 ';

Three, 190 on the machine

1. Create the Zabbix database first

Mysql> CREATE database Zabbix character set UTF8;

2. Import the Zabbix.sql into the database Zabbix

[Email protected] ~]# mysql-uroot-p123456-p3306-h127.0.0.1 Qingyun < Zabbix.sql

3, write four scripts, from the 163 machine to export all tables under the Zabbix library to local, and then import from the local Zabbix database

a.sh

Upwd= "-uzabbix -p123456 -p3308 -h192.168.1.163" dopt= "--skip-comments --opt -t" db= "" Mysqldump ${upwd} ${dopt} ${db} zabbix acknowledges >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix actions >> $1.sqlmysqldump  ${upwd} ${dopt} ${db} zabbix application_template >> $1.sqlmysqldump  ${upwd} ${dopt} ${db} zabbix applications >> $1.sqlmysqldump ${ upwd} ${dopt} ${db} zabbix auditlog >> $1.sqlmysqldump ${upwd} $ {dopt} ${db} zabbix auditlog_details >> $1.sqlmysqldump ${upwd} ${ DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;AUTOREG_HOST&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}  ${db} zabbix conditions >> $1.sqlmysqldump ${upwd} ${dopt} ${db } zabbix config >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix dbversion >>  $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix dchecks >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix dhosts >> $1.sqlmysqldump &NBSP;${UPWD}&NBSP;${DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;DRULES&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}  ${dopt} ${db} zabbix dservices >> $1.sqlmysqldump ${upwd} ${ DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;ESCALATIONS&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT} &NBSP;${DB}&NBSP;ZABBIX&NBSP;EVENTS&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}&NBSP;${DB}  zabbix expressions >> $1.sqlmysqldump ${upwd} ${dopt} ${db}  Zabbix functions >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  globalmacro >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix globalvars >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix graph_discovery >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix graph_theme >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix graphs >> $1.sqlmysqldump  ${upwd} ${dopt} ${db} zabbix graphs_items >> $1.sqlmysqldump ${ UPWD}&NBSP;${DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;GROUP_DISCOVERY&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}  ${dopt} ${db} zabbix alerts >> $1.sqlmysql -uroot -p123456  -p3306 -h127.0.0.1 -a zabbix < $1.sql

b.sh

Upwd= "-uzabbix -p123456 -p3308 -h192.168.1.163" dopt= "--skip-comments --opt -t" db= "" Mysqldump ${upwd} ${dopt} ${db} zabbix group_prototype >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix groups >> $1.sqlmysqldump  ${upwd} ${dopt} ${db} zabbix history_log >> $1.sqlmysqldump ${ UPWD}&NBSP;${DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;HISTORY_STR&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}  ${dopt} ${db} zabbix history_text >> $1.sqlmysqldump ${upwd} ${ DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;HISTORY_UINT&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}  ${db} zabbix host_discovery >> $1.sqlmysqldump ${upwd} ${dopt}  ${DB}&NBSP;ZABBIX&NBSP;HOST_INVENTORY&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}&NBSP;${DB}  zabbix hostmacro&nbsP;>> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix hosts >>  $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix hosts_groups >> $ 1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix hosts_templates >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix housekeeper >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix httpstep >> $1.sqlmysqldump  ${upwd} ${dopt} ${db} zabbix httpstepitem >> $1.sqlmysqldump ${ upwd} ${dopt} ${db} zabbix httptest >> $1.sqlmysqldump ${upwd} $ {dopt} ${db} zabbix httptestitem >> $1.sqlmysqldump ${upwd} ${dopt} &NBSP;${DB}&NBSP;ZABBIX&NBSP;ICON_MAP&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}&NBSP;${DB}  zabbix icon_mapping&Nbsp;>> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix ids >>  $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix images >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix interface >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix interface_discovery >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix item_condition >> $1. sqlmysqldump ${upwd} ${dopt} ${db} zabbix items >> $1.sqlmysql - Uroot -p123456 -p3306 -h127.0.0.1 -a zabbix < $1.sql

c.sh

Upwd= "-uzabbix -p123456 -p3308 -h192.168.1.163" dopt= "--skip-comments --opt -t" db= "" Mysqldump ${upwd} ${dopt} ${db} zabbix item_discovery >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix items_applications >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix maintenances >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix maintenances_groups >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix maintenances_hosts >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix maintenances_windows >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix mappings >> $1.sqlmysqldump &NBSP;${UPWD}&NBSP;${DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;MEDIA&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}  ${dopt} ${db} zabbix media_type >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  Opcommand >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix opcommand_ Grp >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix opcommand_hst  >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix opconditions  >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix operations >>  $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix opgroup >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix opmessage >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix opmessage_grp >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix opmessage_usr >> $1. Sqlmysqldump ${upwd} ${dopt} ${db}&nbSp;zabbix optemplate >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  profiles >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix proxy_ Autoreg_host >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix proxy_ Dhistory >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix proxy_ history >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix regexps  >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix rights >>  $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix screens >> $1. Sqlmysqldump ${upwd} ${dopt} ${db} zabbix trends_uint >> $1.sqlmysql  -uroot -p123456 -p3306 -h127.0.0.1 -a zabbix < $1.sql

d.sh

Mysql -uroot -p123456 -p3306 -h127.0.0.1 -a zabbix < $1.sql[[email  protected] zabbix]# cat d.shupwd= "-uzabbix -p123456 -p3308 -h192.168.1.163 "Dopt="--skip-comments --opt -t "db=" "Mysqldump ${upwd} ${dopt} ${db} zabbix  screens_items >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  Scripts >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix service_ alarms >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix services  >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix services_links > > $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix services_times >>  $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix sessions >> $1. Sqlmysqldump ${upwd}&nbsP;${DOPT}&NBSP;${DB}&NBSP;ZABBIX&NBSP;SLIDES&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}  ${db} zabbix slideshows >> $1.sqlmysqldump ${upwd} ${dopt} ${db }&NBSP;ZABBIX&NBSP;SYSMAP_ELEMENT_URL&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}&NBSP;${DB}  zabbix sysmap_url >> $1.sqlmysqldump ${upwd} ${dopt} ${db}  zabbix sysmaps >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  sysmaps_elements >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  Sysmaps_link_triggers >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  sysmaps_links >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  Timeperiods >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix trends  >> $1.sqlmysqldumP ${upwd} ${dopt} ${db} zabbix trigger_depends >> $1.sqlmysqldump  ${upwd} ${dopt} ${db} zabbix trigger_discovery >> $1.sqlmysqldump  ${upwd} ${dopt} ${db} zabbix triggers >> $1.sqlmysqldump ${upwd } ${dopt} ${db} zabbix user_history >> $1.sqlmysqldump ${upwd} $ {dopt} ${db} zabbix users >> $1.sqlmysqldump ${upwd} ${dopt} ${ DB}&NBSP;ZABBIX&NBSP;USERS_GROUPS&NBSP;&GT;&GT;&NBSP;$1.SQLMYSQLDUMP&NBSP;${UPWD}&NBSP;${DOPT}&NBSP;${DB}  zabbix usrgrp >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  valuemaps >> $1.sqlmysqldump ${upwd} ${dopt} ${db} zabbix  history >> $1.sqlmysql -uroot -p123456 -p3306 -h127.0.0.1 -a  Zabbix < $1.sql

4. Execute script

Nohup/bin/bash a.sh d1 > A.log & nohup/bin/bash b.sh d2 > B.log & Nohup/bin/bash d.sh d4 > D.log &

This article is from the "Scattered People" blog, please be sure to keep this source http://zouqingyun.blog.51cto.com/782246/1711512

Zabbix Database Migration