Difference between revisions of "MySQL database replication"
| Line 1: | Line 1: | ||
| − | __TOC__ | + | __TOC__ |
= Description = | = Description = | ||
| Line 5: | Line 5: | ||
We use mysql replication to mirror mysql databases from a single master mysqld server to a series of slave servers on back end nodes. This process is asynchronous and asymmetrical - back end nodes are read-only. | We use mysql replication to mirror mysql databases from a single master mysqld server to a series of slave servers on back end nodes. This process is asynchronous and asymmetrical - back end nodes are read-only. | ||
| − | = | + | = Configuring the Replication Master server = |
Two options must be set to enable replication. First, binary logging must be enabled. Second, every mysqld server in the pool needs a unique ID. | Two options must be set to enable replication. First, binary logging must be enabled. Second, every mysqld server in the pool needs a unique ID. | ||
These parameters should be set in the my.cnf file (/etc/my.cnf on WormBase servers). | These parameters should be set in the my.cnf file (/etc/my.cnf on WormBase servers). | ||
| − | |||
<pre>[mysqld] | <pre>[mysqld] | ||
log-bin=mysql-bin | log-bin=mysql-bin | ||
| Line 16: | Line 15: | ||
</pre> | </pre> | ||
| − | |||
<code>brie3</code> (the primary WormBase development machine) acts as the master server, with <code>server-id = 1</code>. | <code>brie3</code> (the primary WormBase development machine) acts as the master server, with <code>server-id = 1</code>. | ||
| + | |||
| + | = Configuring the Replication Slave servers = | ||
| + | |||
| + | The only required configuration for replication slaves is the server-id: | ||
| + | |||
| + | <pre>[mysqld] | ||
| + | server-id=2 | ||
| + | </pre> | ||
| + | |||
| + | Binary logging is not required for slaves but can be useful for data recovery and more complicated architectures. I have it off on our read-only slaves for performance. | ||
| + | |||
| + | ''See [[WormBase Infrastructure|WormBase_Infrastructure]] and [[Hardware catalog|Hardware catalog]] for server IDs.'' | ||
Revision as of 19:00, 17 May 2008
Contents
Description
We use mysql replication to mirror mysql databases from a single master mysqld server to a series of slave servers on back end nodes. This process is asynchronous and asymmetrical - back end nodes are read-only.
Configuring the Replication Master server
Two options must be set to enable replication. First, binary logging must be enabled. Second, every mysqld server in the pool needs a unique ID.
These parameters should be set in the my.cnf file (/etc/my.cnf on WormBase servers).
[mysqld] log-bin=mysql-bin server-id=1
brie3 (the primary WormBase development machine) acts as the master server, with server-id = 1.
Configuring the Replication Slave servers
The only required configuration for replication slaves is the server-id:
[mysqld] server-id=2
Binary logging is not required for slaves but can be useful for data recovery and more complicated architectures. I have it off on our read-only slaves for performance.
See WormBase_Infrastructure and Hardware catalog for server IDs.
