So I have some Zabbix instances which are using MySQL for their backends. What I would like to do is, for DR purposes, backup the DBs for each instance using a Master/Slave configuration, with the slave node sitting at the DR site. However, I don't want to capture trends/historical data, all I want is configuration data. Is there a way to replicate Zabbix's database without populating the tables which hold all of the applications historical monitoring metrics?
@RolandoMySQLDBA's answer is accurate, but I would advise against using the
replicate-*-db options because of the complexity of how MySQL evaluates replication rules.
When these aren't used, everything is replicated, and I would contend that replicating everything the most reliable configuration. As a rule, I never do anything else.
If you don't want to replicate the historical data, I would start by setting up a full, standard master/slave replication setup...
...identify the tables you don't care about on the replica and for each such table, on the slave:
ALTER TABLE table_name ENGINE=BLACKHOLE;
This will discard all of the data in those tables on the slave and convert them to the blackhole storage engine, which accepts inserts (but doesn't store them), returns empty result-sets from selects, and has "0 rows affected" on updates and deletes... so your schemata are still fully compatible but you're not actually storing anything in the tables you're not interested in.
If you're using
mysqldump to initially set up the replica, you can also manually edit the dump files and change the table engine on those tables' declarations.
All of the data will still be "replicated to" the slave, but not "saved on" the slave... the data written to these tables will not be stored. Writing rows to a blackhole table is a very resource-friendly operation -- I've seen my servers handle 40,000+ queries per second when replicating "into" blackhole tables.
If you're concerned about the fact that the data is still transferred to the replica... or, really, even if you're not, just turn on
slave_compressed_protocol on the slave and don't think about it -- consider it the cost of a cleaner replication setup. When the slave connects to the master, it will negotiate a connection using the MySQL compressed client protocol, substantially reducing the actual bytes of data transferred on the wire between the machines, but still sending everything.
If you can afford to lose up to a few hours of configuration data, and the configuration part of the database isn't big, maybe replication isn't quite the right answer for you. As @michael-sqlbot mentioned, replication options have a lot of caveats.
Consider using mysqldump and scripting something up using shell scripts and your server's crontab.
mysqldump --nodata databasename > zabbixconfig.sqlto get all of the table creation scripts
mysqldump databasename table1 table2 table3 >> zabbixconfig.sql(add
--single-transactionif the database is using InnoDB, to minimise locking) for all of the configuration tables you care about
- Have your remote server pull the created sql file (via scp, nfs, etc.), and push it to the DR database with
cat zabbixconfig.sql | mysql databasename
(If you want the data for most of your tables, you could consider the
You'd also want to put something in place to ensure that you're notified if the process stops working.
While it's less elegant than using replication, it's arguably simpler to maintain.
For zabbix you need replicate all tables, but you don't replicate the history*, the trends* and the events tables.
More about zabbix, http://puppetlinux.blogspot.com.es/search/label/zabbix