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?
Mysql – Master/Slave without populating tables
disaster recoveryMySQLreplication
Related Solutions
Using replicate-wild-do-table
=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:
The
replicate-*
options are restrictive by their presence. The*-do-*
options seem to be telling the server what to "do," but in fact they are telling the server what to only do.The complete absence of any
replicate-*
configuration variables means "replicate everything."
In the simplest case, when there are no
--replicate-*
options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html
That, I think, is the point you are needing. It holds true for all MySQL 5.x.
Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ...
, and then START SLAVE
, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.
If you started out with identical data sets, everything should behave exactly as you would expect it to.
What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."
When you use the --replicate-*
options, the document cited above also offers this tip:
it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options
You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.
It isn't possible with replication as has been correctly pointed out, since that's not how replication works, nor can it be configured to work that way. Replication either does or does not handle any given table (depending on configuration) but when replication does handle a given table, the data is replicated and replication depends on the rows in the tables starting out and remaining identical.
What I'm hearing you say is that you want some of the same schemas and tables on the 2nd server as exist on the master, but they will be populated via external means, and you want those tables to go away when they are dropped from the master.
Here is a high-level view of how this could be accomplished via automatic means using only things built-in to MySQL.
Obviously this is a dangerous operation if not carefully written since we're talking about the automated dropping of tables and databases, so... caveat lector.
- enable the event scheduler on the archive machine
- enable the federated storage engine on the archive machine
- within another schema you create solely for the purpose of this exercise, define 2
FEDERATED
tables that connect back to the master. One of these points to information_schema.tables and the other points to information_schema.schemata. - write a stored procedure that selects from the archive machine's information_schema.schemata table, left join the federated schemata table on catalog_name, schema_name where schema_name not in ('mysql','information_schema','performance_schema','any','other','schemas','you want not','to be deleted','for sure')
- take values from the resulting rows, which will be all of the databases that exist on the local machine but not on the master... and, using dynamic sql, drop those databases from the archive machine.
- do the same thing with the information_schema.tables table, which will identify tables you can drop.
- use the event scheduler to automate the running of this stored procedure. Don't schedule it too often, perhaps only once a day, because selecting from the information_schema.tables table can be a resource-intensive operation. MySQL gathers stats when you do this that can be costly especially in terms of I/O.
Related Question
- Percona XtraDB Cluster – Native Replication to a Stand-Alone Slave
- MySQL – Fix Slave Skipping Some Updates
- Mysql – Error replicating password change in MySQL slave
- Patroni – Handling Long Disconnected Replica from Primary
- Mongodb creating historical cluster replicaset
- MySQL DB as Master for some tables and Slave for others
Best Answer
@RolandoMySQLDBA's answer is accurate, but I would advise against using the
replicate-*-table
andreplicate-*-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...
...then...
...identify the tables you don't care about on the replica and for each such table, on the slave:
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.