Move mysql database

[xibbaz]

I'm trying to move the whole zabbix-server (single child node) from one machine to another. I'm having problems transfering the mysql data for which I wish not to conserv the history as it is too big.

The problem is simply that when I open the php frontend, each pages have this message at the top... "SELECT * FROM config WHERE (configid div 100000000000000) in (2)" and it can appear once or twice.

The steps I have done to arrive at that point:

On the old machine, dump these tables...

• actions
• alerts
• applications
• auditlog
• conditions
• config
• dchecks
• dhosts
• drules
• dservices
• escalations
• functions
• graphs
• graphs_items
• groups
• help_items
• hosts
• hosts_groups
• hosts_profiles
• hosts_profiles_ext
• hosts_templates
• httpstep
• httpstepitem
• httptest
• httptestitem
• ids
• images
• items
• items_applications
• mappings
• media
• media_type
• node_cksum
• nodes
• opconditions
• operations
• profiles
• proxy_dhistory
• proxy_history
• rights
• screens
• screens_items
• scripts
• service_alarms
• services
• services_links
• services_times
• slides
• slideshows
• sysmaps
• sysmaps_elements
• sysmaps_link_triggers
• sysmaps_links
• trends_uint
• trigger_depends
• triggers
• users
• users_groups
• usrgrp
• valuemaps

mysqldump --opt -u root -p zabbix actions alerts applications auditlog conditions config dchecks dhosts drules dservices escalations functions graphs graphs_items groups help_items hosts hosts_groups hosts_profiles hosts_profiles_ext hosts_templates httpstep httpstepitem httptest httptestitem ids images items items_applications mappings media media_type node_cksum nodes opconditions operations profiles proxy_dhistory proxy_history rights screens screens_items scripts service_alarms services services_links services_times slides slideshows sysmaps sysmaps_elements sysmaps_link_triggers sysmaps_links trends_uint trigger_depends triggers users users_groups usrgrp valuemaps | gzip -9 > zabbix.cfg.sql.gz

On the new machine:
Copied all the configuration files, be it php or .conf to new machine.
Log onto mysql as user zabbix.

mysql> drop database zabbix;
mysql> create database zabbix;
mysql> use zabbix;
mysql> source /usr/share/zabbix-server/mysql.sql
mysql> source /usr/share/zabbix-server/data.sql
mysql> exit

Convert to a child node:

zabbix_server -n 2 -c /etc/zabbix/zabbix_server.conf

and then import the dump from the old machine.

Now, this configid, I don't quite get it since both config tables are identical on the old and new machine. Obviously, I missed something somewhere. Other than that, all the settings seems to be find when I log to the frontend and examine what is there. Did not spot anything missing, just that SELECT error that always appear.

Thanks for any pointers.

[nelsonab]

Judging by the query being run I'm guessing you setup the first machine for a Distributed Setup. If this is the case why do you want to copy the database to the second machine, the two machines will sync the appropriate fields in the database, all you need to do on the second machine is to setup a blank DB (use the normal initial config steps for the DB) and then convert that to a DM setup.

Also converting a pre-converted DB to a DM setup on a separate node if this is what you really want is very non-standard and will involve some funky scripting and sql queries.

[xibbaz]

Well, both machines were low (very low) end machines just to try out Zabbix for our business. It ended up that almost all our infrastructure was on it and the machines were crumbling under the load.

Since we invested a lot of hours in the setup, we did not want to loose it. The only real reliable way I have found to move zabbix from one machine to another was to copy the entire root filesystem onto another freshly installed Ubuntu server. Did not came without hickups, but I manaeged to transfer both machines this week.

Never the less, these methods I tried did not work:
- export xml and import in the new one (unless I do that individually, but as a whole it failed with some php fopen error).
- dump the database and rerun it on the new machine. Since the history made that too big, I dumped only what did not have "history" in it. it messed up completely Zabbix.

There should be something easier than what I just went through

Thanks for the help anyway.