MySQLmysql-5.5replication
I have established a master A and master B replication between two servers. Both are getting replicated, but it is limited to only one database.
My questions:
How to create a new database in Master A has to replicated in Master B or doing the same in vice versa?
Need to Replicate more than one database
Does the DML operations committed by db2 during the replication process gets included in its own bin-log?
Yes it will, provided you have this in /etc/my.cnf on both db1 and db2
[mysqld]
log-slave-updates
If you do not have this, add it and restart mysql
Would the resulting bin-log in db2 be exactly the same with the bin-log of db1, to the letter?
Yes. Make sure the clocks on both DB servers are synchronized
What happens to the entries in db2 relay-log once they are committed to the database during the replication process, are they discarded? What role does the relay-log info log has in this?
In MySQL Replication, the IO Thread of a Slave will read its Master's bin-log entries and store them in a FIFO queue. For each relay log in a slave, when every entry in the currently processed relay is executed it is rotated out and discarded. If relay logs are piling up, this quickly indicates that the SQL thread died because of any SQL error. Just do SHOW SLAVE STATUS\G to find out what stopped the SQL thread. The IO Thread would conitnue collecting completed SQL statements from its Master.
How does db1 know where in the bin-log of db2 (somehow dependent on the answer of Question 2), it will start the replication process?
When you do SHOW SLAVE STATUS\G, look for the following lines:
If you enable log-slave-updates on both databases i.e. dB1 & dB2, then that would mean all items from the binary log of dB1, which was successfully replicated by dB2 will be written into dB2's binary log and vice-versa. Would not this result to some sort of infinite circular replication or duplications of entries on both databases, if it's possible at all, considering the possible key-collision issues that would arise? What I'm trying to say is, How would dB1 know once it checks on the binary log of dB2 that, "I should not replicate those entries in there because they all just came from me"?
You must have log-slave-updates available on both DB servers in order to have an audit trail that the SQL executed on on DB server made it to the other. If you don't, you would have to do your due diligence to compare the data explicitly. Such ways would include:
You need not worry about infinite circular replication unless you are dealing with more that two masters. There have been rare times when someone with, let's say four Masters, removes one of the four servers from circular rep cluster. Let's suppose the the server_id is 13. It is remotely, but still, possible for binary log entries whose server_id belongs to the server that removed to be inside the relay logs on other servers. Only in such a scenario would you worry about infinite circular replication.
To circumvent such situations, MySQL 5.5 has a new option for the CHANGE MASTER TO command called IGNORE_SERVER_IDS. You would do the following to repair things on all the remaining servers:
STOP SLAVE;
CHANGE MASTER TO IGNORE_SERVER_IDS = (13);
START SLAVE;
In fact, here is what the MySQL Documentation says on this:
IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log.
In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more than once. Thus, this option is useful in circular replication when one of the servers in the circle is removed. Suppose that you have a circular replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and server 3 fails. When bridging the gap by starting replication from server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you issue on server 4 to tell it to use server 2 as its master instead of server 3. Doing so causes it to ignore and not to propagate any statements that originated with the server that is no longer in use.
On INSERT queries on the master, what form of the query is written into the binary log? Is it the 'raw' form of the query, or the one which already has the auto-generated value of the auto-increment key?
Whichever form is presented. Here is what I mean: The raw form would usually not include the auto_increment column expressed explicitly. On the other hand, it you import a mysqldump into a DB server with binary logging, the rows being inserted would explicitly be given. Either version of INSERT would be allowed execution in mysqld. In like fashion, either version of INSERT would be recorded AS IS...
(I'm putting this in an answer, as it's way too long for a comment.)
We have a scenario similar to yours for our bug tracking system. We use it internally, of course, but customers can also submit issues through a page we created on our customer SharePoint site.
What we decided to do was host the database and website only at the office and provide external access from there (which we were already doing for some of our SaaS customers). If the internet totally bombs out (rare), it's more important that we can continue to work than for our customers to be able to submit new issues.
In your scenario, I don't know how critical the data is, how much data there is, or how important it is for external users to be able to write data.
Perhaps you could consider using a database at the alternate location as a read-only secondary, but direct all writes to the primary. While this will probably involve some application changes to separate read-only and read-write connections, this type of solution might be enough to satisfy the requirements for the small amount of time the office internet is down.
Regardless, I stick by my recommendation to not mix MySQL/SQL Server if you can avoid it. IMO, you'll be better off long-term by directing resources into proceeding with the existing migration plan, and hold off on developing a more robust replication solution until that stage of the project is complete.
Also, definitely try your best to avoid any master-master replication scenario. These can be highly non-trivial to configure and support at the best of times. The $ and time that will be spent developing and debugging a solution involving master-master heterogeneous replication will be astronomical, and probably won't ever work correctly 100% of the time (actually, probably nowhere close to that). Not that a built-in homogeneous replication solution will be perfect either, but at least in that case, you can call for customer support if something blows up and you don't know how to fix it; if you roll your own solution, you're on your own.
Best Answer
Could you give us some info on the replication config in your config files.
For sure if you replicate one db at a time and you need to add one, you won't have a choice to change your config then to restart your instances.
If you can I'll suggest more a full replication. less trouble and if you need to add dbs, there is no problem there.