 |
 |
 |
|
12.16.08 Setting Up MySQL Replication  By Jay Fougere This is the second part of an article that I wrote discussing replication in MySQL. The first part of the article was a more general primer on what replication is and how it differs from clustering. The first part of the article can be found here. Today we will discuss actually setting up replication with MySQL. I am assuming you are using a Unix based system such as Linux, OpenSolaris or one of the BSDs. I am also assuming you already have MySQL (server and client tools) installed on your machine; installing MySQL is a bit beyond the scope of this article. The official MySQL documentation can be found here for versions 5.0.X and for versions 5.1.X. 1). Creating a user You will need to create a user on each master server you have that will have permissions to the database(s) that you will be replicating. I have only ever set up replication with machines on the same rack (usally even sharing the same network switch), thus I have always assigned IP addresses to these machines on a private network. I do this mainly for security reasons. First of all, using a private network adds a hurdle to any potential hackers; they cannot directly access MySQL via the network. Secondly, this makes for easier firewalling of the database machines. Anyway, log in to mysql and create the user and give that user permissions to the databases that will be replicated: #mysql -u root -p
The previous 3 statements will grant all permissions on database1 and database2 to user cluster on the network 192.168.1.0/24 with password "mypassword". You may want to be more creative with your naming :) Also, a quick note on using IP addresses. You can use host names for your connections amongst MySQL servers, however this can introduce latency and another level of management should DNS or whatever name service you use become unreachable. Considering this, it is simpler and more reliable to simply use IP addresses rather than rely on external name services. 2). Assigning each server a unique ID After creating a user for replication, you will need to assign each server in the replication chain a unique server ID number. This number is arbitrary, but must be unique for each server. I tend to use numbers in powers of 10. In other words the first server would be 10, the second, 20 etc... To assign the server number, simply add a line like the following to your my.cnf (usually located in either /etc/ or /usr/local/etc depending on the location of your MySQL installation) on each server: server-id=10
3). Set up binary logging on the master server(s) Next, you will need to do is set up binary logging on the master server. If you are going to use multi-master replication, you will need to set up binary logging on each server that will act as a master. Continue reading this article. About the Author: Jay Fougere is the IT manager for the iEntry network. He also writes occasional articles. If you have any IT questions, please direct them to Jay@ientry.com. |
|
| ||
| --
SQLProNews is an iEntry, Inc. publication -- iEntry, Inc. 2549 Richmond Rd. Lexington KY, 40509 2008 iEntry, Inc. All Rights Reserved Privacy Policy Legal advertising info | news headlines | free newsletters | comments/feedback | submit article |
SQLproNews is brought to you by: