StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There isn't much performance to be gained from replicating your database on two masters. However there is a nifty bit of failover if you code your application correct.</p> <p>Master-Master setup is essentially the same as the Slave-Master setup, but has both Slaves started and an important change to your config files on each box.</p> <p>Master MySQL 1:</p> <pre><code>auto_increment_increment = 2 auto_increment_offset = 1 </code></pre> <p>Master MySQL 2:</p> <pre><code>auto_increment_increment = 2 auto_increment_offset = 2 </code></pre> <p>These two parameters ensure that when two servers are fighting over a primary key for some reason, they do not duplicate and kill the replication. Instead of incrementing by 1, any auto-increment field will by default increment by 2. On one box it will start offset from 1 and run the sequence 1 3 5 7 9 11 13 etc. On the second box it will start offset at 2 and run along 2 4 6 8 10 12 etc. From current testing, the auto increment appears to take the next free number, not one that has left before. E.g. If server 1 inserts the first 3 records (1 3 and 5), when Server 2 inserts the 4th, it will be given the key of 6 (not 2, which is left unused).</p> <p>Once you've set that up, start both of them up as Slaves. Then to check both are working ok, connect to both machines and perform the command <code>SHOW SLAVE STATUS</code> and you should note that both <code>Slave_IO_Running</code> and <code>Slave_SQL_Running</code> should both say “YES” on each box. </p> <p>Then of course, create a few records in a table and ensure one box is only inserting odd numbered primary keys and the other is only incrementing even numbered ones.</p> <p>Then do all the tests to ensure that you can perform all the standard applications on each box with it replicating to the other.</p> <p>It's relatively simple once it's going. But as has been mentioned, MySQL do discourage it and advise that you ensure you are mindful of this functionality when writing your application code.</p> <p>Edit: I suppose it's theoretically possible to add more masters if you ensure that the offsets are correct and so on. You might more realistically though, add some additional slaves.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload