melo / mysql-master-master-how-to

A simple how-to setup a MySQL master/master system

This URL has Read+Write access

raw normal view history
05cdc44e » melo 2008-06-27 Added HTML version for peop... 1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1 plus MathML 2.0//EN"
3 "http://www.w3.org/TR/MathML2/dtd/xhtml-math11-f.dtd">
4
5 <html xmlns="http://www.w3.org/1999/xhtml">
6 <head>
7 <!-- Processed by MultiMarkdown -->
8 <meta name="Author" content="Pedro Melo" />
3d24221b » melo 2008-06-27 Added first sample outputs ... 9 <link type="text/css" rel="stylesheet" href="css/multimarkdown.css" />
05cdc44e » melo 2008-06-27 Added HTML version for peop... 10 <meta name="Copyright" content="2008 Pedro Melo.
11 This work is licensed under a Creative Commons License.
12 http://creativecommons.org/licenses/by-sa/2.5/" />
13 <meta name="Date" content="2008-06-26" />
14 <meta name="Format" content="complete" />
15 <meta name="Keywords" content="mysql, master/master, replication" />
16 <title>How-to: Master/Master replication for MySQL</title>
17 <meta name="XMP" content="CCAttributionShareAlike" />
18 </head>
19 <body>
20
21 <h1 id="introduction">Introduction</h1>
22
23 <p>This How-To will walk you through the steps to create a MySQL master/master
24 setup.</p>
25
26 <p>Before you setup your database like this, make sure your application supports this. You should
27 <a href="http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html#qandaitem-17-3-4-5" title="MySQL :: MySQL 5.0 Reference Manual :: 16.3.4 Replication FAQ">read this FAQ entry</a>.</p>
28
29 <h1 id="mastermasterreplicationpre-setup">Master/Master replication pre-setup</h1>
30
31 <p>This will create two mysql servers, running on the same hardware, on
32 ports 7001 and 7002.</p>
33
34 <p>To make the process easier, define these two environment variables:</p>
35
36 <ul>
37 <li><code>MY_DIR</code>: the directory where the mysql is installed, usually
38 <code>/usr/local/mysql</code>;</li>
39 <li><code>HOWTO_DIR</code>: the directory where this file is located.</li>
40 </ul>
41
42 <h2 id="schemafiles">Schema files</h2>
43
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 44 <p>I'll use the
45 <a href="http://www.tigase.org/" title="tigase.org | Open Source and Free (GPLv3) Jabber/XMPP environment.">Tigase</a>
46 <a href="http://projects.tigase.org/server/trac/browser/trunk/database/mysql-schema.sql" title="/trunk/database/mysql-schema.sql - Tigase Server - Trac">MySQL schema file
47 </a>
48 as my example schema.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 49
50 <p>The sample data I'll insert into the database, are just made up values
51 and probably not even valid from the point of view of Tigase.</p>
52
53 <h2 id="configurationfiles">Configuration files</h2>
54
55 <p>The two configuration files available in the <code>etc/</code> directory are based
56 on the <code>my-small.cnf</code> standard configuration file.</p>
57
58 <p>The following changes where done:</p>
59
60 <ul>
61 <li>all paths where changed to enable two MySQL servers running
62 simultaneously on the same hardware;</li>
63 <li>the <code>server-id</code> setting of each server is different: for a sucessful
64 replication setup, each server on the replication group must have a
65 different <code>server-id</code>;</li>
66 <li>"binlogs" are activated: they record all updates to the database;</li>
67 <li><code>port</code> and <code>socket</code> settings: updated to be different for each
68 server.</li>
69 </ul>
70
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 71 <p>For any MySQL command to use a specific server, you just need to
72 add a <code>--defaults-file=$HOWTO_DIR/etc/server-1.cnf</code> (replace 1 with 2 to work
73 on the second server).</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 74
75 <h2 id="starttwoservers">Start two servers</h2>
76
77 <p>Setup the two new servers:</p>
78
79 <pre><code>sudo -s
80 mkdir -p /tmp/server-{1,2}
81 chown mysql:wheel /tmp/server-{1,2}
82 cd $MY_DIR
83 ./scripts/mysql_install_db --defaults-file=$HOWTO_DIR/etc/server-1.cnf --user=mysql
84 ./scripts/mysql_install_db --defaults-file=$HOWTO_DIR/etc/server-2.cnf --user=mysql
85 </code></pre>
86
87 <p>Start them up:</p>
88
89 <pre><code>cd $MY_DIR
90 ./bin/mysqld_safe --defaults-file=$HOWTO_DIR/etc/server-1.cnf --user=mysql
91 ./bin/mysqld_safe --defaults-file=$HOWTO_DIR/etc/server-2.cnf --user=mysql
92 </code></pre>
93
94 <p>At this point in time, both servers should be running. You can test by
95 connecting to each one:</p>
96
97 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-1.cnf -u root
98 mysql --defaults-file=$HOWTO_DIR/etc/server-2.cnf -u root
99 </code></pre>
100
101 <p><em>Please note</em> that this procedure did not set a <code>root</code> account password.
102 This means that anybody can connect as <code>root</code> to your database.</p>
103
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 104 <p>For production environments it is <strong>strongly recommended</strong> that you change
05cdc44e » melo 2008-06-27 Added HTML version for peop... 105 the <code>root</code> password, and delete the anonymous accounts.</p>
106
107 <p>For our tests purposes, we will not do that.</p>
108
109 <h2 id="createsampledbwithdataonserver1">Create sample db with data on server1</h2>
110
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 111 <p>On <code>server-1</code>, connect as root, and create the user used by the slave:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 112
113 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-1.cnf -u root &lt; $HOWTO_DIR/sql/repl_user.sql
114 </code></pre>
115
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 116 <p>We will also create the <code>tigasedb</code> database, and load our test schema:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 117
118 <pre><code>mysqladmin --defaults-file=$HOWTO_DIR/etc/server-1.cnf -u root create tigasedb
119 mysql --defaults-file=$HOWTO_DIR/etc/server-1.cnf -u root tigasedb &lt; $HOWTO_DIR/sql/tigase-mysql-schema.sql
120 </code></pre>
121
122 <p>We should be able to see the <code>tigasedb</code> database and its tables. Lets
123 insert some data on them to test:</p>
124
125 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-1.cnf -u root tigasedb &lt; $HOWTO_DIR/sql/test-data-1.sql
126 </code></pre>
127
128 <h1 id="server1asmaster">Server 1 as Master</h1>
129
130 <p>We need to obtain the current master replication position, create a
131 stable snapshot of the data.</p>
132
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 133 <p>Connect as <code>root</code>:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 134
135 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-1.cnf -u root tigasedb
136 </code></pre>
137
138 <p>This connection must be left open for the rest of this procedure. This
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 139 is required to leave the <code>LOCK</code> in place.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 140
141 <p>Execute the following statements:</p>
142
143 <pre><code>FLUSH TABLES WITH READ LOCK;
144 SHOW MASTER STATUS;
145
146 +---------------------------+----------+--------------+------------------+
147 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
148 +---------------------------+----------+--------------+------------------+
149 | mysql-server-1-bin.000003 | 4514 | | |
150 +---------------------------+----------+--------------+------------------+
151 1 row in set (0.00 sec)
152 </code></pre>
153
154 <p>This information tells you that the master position is the
155 <code>mysql-server-1-bin.000003</code> file at 4514.</p>
156
157 <p>Now create a dump of the data in the master:</p>
158
159 <pre><code>mysqldump --defaults-file=$HOWTO_DIR/etc/server-1.cnf \
160 -u root \
161 --lock-all-tables \
162 tigasedb &gt; tigasedb_dump.sql
163 </code></pre>
164
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 165 <p>Now we need to setup the slave. First, create the <code>tigasedb</code> database and
166 load the SQL dump from the master:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 167
168 <pre><code>mysqladmin --defaults-file=$HOWTO_DIR/etc/server-2.cnf -u root create tigasedb
169 mysql --defaults-file=$HOWTO_DIR/etc/server-2.cnf -u root tigasedb &lt; tigasedb_dump.sql
170 </code></pre>
171
172 <p>You should have the same data on both servers now.</p>
173
174 <p>To finish the setup, you need to set the other master parameters on the
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 175 slave. Connect as <code>root</code>:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 176
177 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-2.cnf -u root
178 </code></pre>
179
180 <p>And setup the master link:</p>
181
182 <pre><code>CHANGE MASTER TO
183 MASTER_HOST='127.0.0.1',
184 MASTER_PORT=7001,
185 MASTER_USER='repl_tig',
186 MASTER_PASSWORD='pray4sync',
187 MASTER_LOG_FILE='mysql-server-1-bin.000003',
188 MASTER_LOG_POS=4514;
189 START SLAVE;
190 </code></pre>
191
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 192 <p>You can now end the server-1 session where the <code>LOCK</code> was created.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 193
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 194 <p>You should have a proper master/slave relation working between <code>server-1</code>
195 and <code>server-2</code>.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 196
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 197 <p>If you look at <code>SHOW MASTER STATUS</code> on <code>server-1</code> and compare to
05cdc44e » melo 2008-06-27 Added HTML version for peop... 198 <code>SHOW SLAVE STATUS\G</code> (use <code>\G</code> query terminator for a more useful layout)
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 199 on <code>server-2</code>, you should see the same <code>File</code> and <code>Position</code> on <code>server-1</code>
200 as <code>Master_Log_File</code> and <code>Read_Master_Log_Pos</code> on <code>server-2</code>.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 201
202 <p>To test, insert a new row in the <code>tig_user</code> table.</p>
203
204 <pre><code>INSERT INTO tig_users (uid, user_id) VALUES (3, 'user3');
205 </code></pre>
206
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 207 <p>If you <code>SELECT * FROM tig_users</code> on <code>server-2</code>, you should see the
208 <code>user3</code> row.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 209
210 <p>You can also re-check the <code>SHOW MASTER STATUS</code>/<code>SHOW SLAVE STATUS</code> and
211 compare the <code>File</code>/<code>Position</code>.</p>
212
213 <h1 id="server2asmaster">Server 2 as Master</h1>
214
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 215 <p>The last step is to set <code>server-2</code> as master of <code>server-1</code>.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 216
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 217 <h2 id="beforeweprocedesomenotes">Before we procede, some notes</h2>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 218
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 219 <p>A master/master setup requires some configurations to make <code>AUTO_INCREMENT</code>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 220 work correctly.</p>
221
222 <p>Our config files already include the proper <code>auto_increment_increment</code> and
223 <code>auto_increment_offset</code> values.</p>
224
225 <p>You should read:</p>
226
227 <ul>
228 <li><a href="http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_auto-increment-increment" title="MySQL :: MySQL 5.0 Reference Manual :: 5.1.3 System Variables">Explanation for <code>auto_increment_*</code> variables</a>;</li>
229 <li><a href="http://dev.mysql.com/doc/refman/5.0/en/replication-features-auto-increment.html" title="MySQL :: MySQL 5.0 Reference Manual :: 16.3.1.1 Replication and AUTO_INCREMENT">Notes about <code>AUTO_INCREMENT</code> and replication</a>.</li>
230 </ul>
231
232 <p>It boils down to this:</p>
233
234 <ul>
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 235 <li><code>auto_increment_increment</code> should be equal to the number of masters you have, <code>N</code>;</li>
236 <li><code>auto_increment_offset</code> should be different, from 1 to <code>N</code>, on each master.</li>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 237 </ul>
238
239 <h2 id="makeserver1slavetoserver2">Make server 1 slave to server 2</h2>
240
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 241 <p>You need a replication user on <code>server-2</code>. We'll use the same one:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 242
243 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-2.cnf -u root &lt; $HOWTO_DIR/sql/repl_user.sql
244 </code></pre>
245
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 246 <p>Connect to <code>server-2</code>, lock tables and record the master position:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 247
248 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-2.cnf -u root tigasedb
249
250 FLUSH TABLES WITH READ LOCK;
251 SHOW MASTER STATUS;
252
253 +---------------------------+----------+--------------+------------------+
254 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
255 +---------------------------+----------+--------------+------------------+
256 | mysql-server-2-bin.000004 | 482 | | |
257 +---------------------------+----------+--------------+------------------+
258 1 row in set (0.00 sec)
259 </code></pre>
260
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 261 <p>Then hop over to <code>server-1</code>, and connect as <code>root</code>:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 262
263 <pre><code>mysql --defaults-file=$HOWTO_DIR/etc/server-1.cnf -u root tigasedb
264 </code></pre>
265
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 266 <p>Make <code>server-1</code> a slave with the proper master position:</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 267
268 <pre><code>CHANGE MASTER TO
269 MASTER_HOST='127.0.0.1',
270 MASTER_PORT=7002,
271 MASTER_USER='repl_tig',
272 MASTER_PASSWORD='pray4sync',
273 MASTER_LOG_FILE='mysql-server-2-bin.000004',
274 MASTER_LOG_POS=482;
275 START SLAVE;
276 </code></pre>
277
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 278 <p>Release the lock on <code>server-2</code>. The easiest way is just quiting the
279 <code>mysql</code> shell.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 280
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 281 <p>You should have a proper master/master relation working between <code>server-1</code>
282 and <code>server-2</code>.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 283
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 284 <p>If you look at <code>SHOW MASTER STATUS</code> on <code>server-2</code> and compare to
05cdc44e » melo 2008-06-27 Added HTML version for peop... 285 <code>SHOW SLAVE STATUS\G</code> (use <code>\G</code> query terminator for a more useful layout)
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 286 on <code>server-1</code>, you should see the same <code>File</code> and <code>Position</code> on <code>server-2</code>
287 as <code>Master_Log_File</code> and <code>Read_Master_Log_Pos</code> on <code>server-1</code>.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 288
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 289 <p>Do the same procedure again, but switch <code>server-1</code> with <code>server-2</code>. You
290 should also see equal values.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 291
292 <h2 id="finaltests">Final tests</h2>
293
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 294 <p>I recommend that you start two new connections, one to <code>server-1</code> and
295 the other to <code>server-2</code>.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 296
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 297 <p>Select, insert, update, delete from each table, and see if everything
298 is working.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 299
300 <p>Also try on one of the connections:</p>
301
302 <pre><code>CREATE TABLE test_ainc ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY);
303 </code></pre>
304
305 <p>And then insert rows with:</p>
306
307 <pre><code>INSERT INTO test_ainc VALUES (null), (null), (null);
308 </code></pre>
309
f9a717bb » melo 2008-06-27 Tweaks all over, minor ajus... 310 <p>Do this on both connections and see how the <code>AUTO_INCREMENT</code> columns
311 just work.</p>
05cdc44e » melo 2008-06-27 Added HTML version for peop... 312
313 <h1 id="finalnotes">Final Notes</h1>
314
315 <h2 id="innodb_flush_log_at_trx_commit">innodb_flush_log_at_trx_commit</h2>
316
317 <p><a href="http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/" title="Innodb Performance Optimization Basics | MySQL Performance Blog">One of the recommendations for InnoDB performance</a>
318 is to set <code>innodb_flush_log_at_trx_commit</code> to 2. The
319 <a href="http://dev.mysql.com/doc/refman/5.0/en/replication-howto-masterbaseconfig.html" title="MySQL :: MySQL 5.0 Reference Manual :: 16.1.1.2 Setting the Replication Master Configuration">MySQL documentation recommends setting to 1 as the most safe option</a>.</p>
320
321 <h2 id="replicate-do-db">replicate-do-db</h2>
322
323 <p>You could limit the replication to just the <code>tigasedb</code> database with a:</p>
324
325 <pre><code>replicate-do-db = tigasedb
326 </code></pre>
327
328 <p>in the configuration file, <code>mysqld</code> section.</p>
329
330 <p>Alternative, you could use this:</p>
331
332 <pre><code>replicate-ignore-db=mysql
333 </code></pre>
334
335 <p>This will replicate all the databases, except the system mysql. This is
336 usefull because it will replicate everything except users, passwords and
337 <code>GRANT</code>/<code>REVOKE</code> statements.</p>
338
339 <h2 id="masterconfig">master config</h2>
340
341 <p>You should notice that I didn't set the master host, port and other
342 settings on the configuration file.</p>
343
344 <p>The <code>CHANGE MASTER</code> command writes that information in a <code>master.info</code>
345 file. This file takes precedence over the configuration file.</p>
346
347 <h1 id="links">Links</h1>
348
349 <p>First, the best resource for all about replication will probably be the
350 <a href="http://www.highperfmysql.com/" title="High Performance MySQL &raquo; Home">High Performance MySQL (2nd. ed.) book</a>.
351 You should buy it.</p>
352
353 <p>A list of useful links:</p>
354
355 <ul>
356 <li><a href="http://dev.mysql.com/doc/refman/5.0/en/replication.html" title="MySQL :: MySQL 5.0 Reference Manual :: 16 Replication">The MySQL documentation about replication is very good</a>.
357 If you follow each step carefuly you should be ok;</li>
358 <li><a href="http://www.mysqlperformanceblog.com/" title="MySQL Performance Blog">The High-Performance MySQL site</a>
359 is one of the best for all things MySQL. Make sure you
360 <a href="http://www.mysqlperformanceblog.com/?s=replication" title="replication | MySQL Performance Blog">search for <code>replication</code></a>;</li>
361 <li>Browse
362 <a href="http://dev.mysql.com/doc/refman/5.0/en/replication-notes.html" title="MySQL :: MySQL 5.0 Reference Manual :: 16.3 Replication Notes and Tips">the Notes and Tips section</a>;</li>
363 <li><a href="http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html" title="ONLamp.com -- Advanced MySQL Replication Techniques">An old article about MySQL replication at OnLamp.com</a>.
364 You should double-check the commands, but the graphics are pretty and usefull.</li>
365 </ul>
366
367 <p>The top hit on
368 <a href="http://www.google.com/search?q=master/master+replication+mysql" title="master/master replication mysql - Google Search">Google for MySQL master/master setup</a>
369 is a
370 <a href="http://www.howtoforge.com/mysql5_master_master_replication_debian_etch" title="Setting Up Master-Master Replication With MySQL 5 On Debian Etch | HowtoForge - Linux Howtos and Tutorials">how-to at HowtoForge</a>.
371 Personally I found it a bit dense, but that's just me.</p>
372 </body>
373 </html>