zabbix数据库迁移

erwerwe

一、环境
   192.168.1.163 zabbix 由于与线上业务争资源，影响比较大，故需要迁移，而现在是lamp部署
   192.168.1.190 把原有zabbix数据库迁到这个台机器上3306上

二、163机器步
1、导出zabbix数据表结构

1	[iyunv@dcserver2 ~]# mysqldump -uroot -pdy2013 -P3308 -h127.0.0.1 --opt -d zabbix > zabbix.sql

2、把文件传到190机器上，以便恢复

1	[iyunv@dcserver2 ~]# scp zabbix.sql root@192.168.1.190:/root/

3、创建可以远程登录数据库账号

1	GRANT all privileges ON *.* TO 'zabbix'@'%' IDENTIFIED BY '123456';

三、190机器上
  1、 先创建zabbix数据库

1	mysql> create database zabbix character set utf8;

2、把zabbix.sql导入到数据库zabbix

1	[iyunv@web4 ~]# mysql -uroot -p123456 -P3306 -h127.0.0.1 qingyun < zabbix.sql

3、写四个脚本，从163机器把zabbix库下所有表导出到本地，然后再从本地导入zabbix数据库中
   a.sh

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

upwd="-uzabbix -p123456 -P3308 -h192.168.1.163" dopt="--skip-comments --opt -t" db="" mysqldump ${upwd} ${dopt} ${db} zabbix acknowledges >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix actions >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix application_template >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix applications >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix auditlog >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix auditlog_details >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix autoreg_host >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix conditions >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix config >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix dbversion >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix dchecks >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix dhosts >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix drules >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix dservices >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix escalations >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix events >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix expressions >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix functions >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix globalmacro >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix globalvars >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix graph_discovery >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix graph_theme >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix graphs >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix graphs_items >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix group_discovery >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix alerts >> $1.sql mysql -uroot -p123456 -P3306 -h127.0.0.1 -A zabbix < $1.sql

  b.sh

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

upwd="-uzabbix -p123456 -P3308 -h192.168.1.163" dopt="--skip-comments --opt -t" db="" mysqldump ${upwd} ${dopt} ${db} zabbix group_prototype >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix groups >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix history_log >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix history_str >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix history_text >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix history_uint >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix host_discovery >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix host_inventory >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix hostmacro >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix hosts >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix hosts_groups >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix hosts_templates >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix housekeeper >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix httpstep >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix httpstepitem >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix httptest >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix httptestitem >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix icon_map >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix icon_mapping >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix ids >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix images >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix interface >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix interface_discovery >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix item_condition >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix items >> $1.sql mysql -uroot -p123456 -P3306 -h127.0.0.1 -A zabbix < $1.sql

c.sh

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

upwd="-uzabbix -p123456 -P3308 -h192.168.1.163" dopt="--skip-comments --opt -t" db="" mysqldump ${upwd} ${dopt} ${db} zabbix item_discovery >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix items_applications >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix maintenances >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix maintenances_groups >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix maintenances_hosts >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix maintenances_windows >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix mappings >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix media >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix media_type >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opcommand >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opcommand_grp >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opcommand_hst >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opconditions >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix operations >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opgroup >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opmessage >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opmessage_grp >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix opmessage_usr >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix optemplate >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix profiles >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix proxy_autoreg_host >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix proxy_dhistory >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix proxy_history >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix regexps >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix rights >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix screens >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix trends_uint >> $1.sql mysql -uroot -p123456 -P3306 -h127.0.0.1 -A zabbix < $1.sql

d.sh

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

mysql -uroot -p123456 -P3306 -h127.0.0.1 -A zabbix < $1.sql [iyunv@web4 zabbix]# cat d.sh upwd="-uzabbix -p123456 -P3308 -h192.168.1.163" dopt="--skip-comments --opt -t" db="" mysqldump ${upwd} ${dopt} ${db} zabbix screens_items >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix scripts >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix service_alarms >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix services >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix services_links >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix services_times >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix sessions >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix slides >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix slideshows >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix sysmap_element_url >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix sysmap_url >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix sysmaps >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix sysmaps_elements >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix sysmaps_link_triggers >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix sysmaps_links >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix timeperiods >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix trends >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix trigger_depends >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix trigger_discovery >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix triggers >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix user_history >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix users >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix users_groups >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix usrgrp >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix valuemaps >> $1.sql mysqldump ${upwd} ${dopt} ${db} zabbix history >> $1.sql mysql -uroot -p123456 -P3306 -h127.0.0.1 -A zabbix < $1.sql

4、执行脚本

1 2 3

nohup /bin/bash a.sh d1 > a.log & nohup /bin/bash b.sh d2 > b.log & nohup /bin/bash d.sh d4 > d.log &