| |
Setting Up MySQL Replication
By Jay Fougere
Expert Author
Article Date: 2008-12-16
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 (usually 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
mysql> GRANT REPLICATION SLAVE ON database1.* TO 'slave'@'192.168.1.%' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON database2.* TO 'slave'@'192.168.1.%' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
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.
Setting up this directory is simple with a couple of caveats. First off, you will need to create a directory where MySQL will keep the binary logs. This directory will need to be writable by the mysql user. If the directory is not writable by the mysql user, the server will refuse to start. Next, if your database is extremely volatile (ie. frequent writes) you may consider putting this directory on a seperate physical disk drive/volume. This will help prevent competition between the binary logging writes and actual database file changes for write access to the drive.
One last consideration regarding your binary logging directory; If you create a directory outside of MySQL's normal location (usually /var/lib/mysql on Linux systems) and assign what you know to be the correct file system permissions and you still cannot log to that directory (you receive errors regarding write permissions when trying to start mysql) and you are running Linux you may look to see if you have selinux running. If so, you can either disable it and reboot or you can reassign permissions using the selinux framework - this is also beyond the scope of this article.
Once you have created a directory to write the logs, you will need to add the following to your my.cnf (substituting real values).
log-bin=/path/to/mysql-binlog/mysql-binlog.bin
This line will cause MySQL to store its binary logs in /path/to/mysql-binlog of the form "mysql-binlog.XXXX" where XXXX is a serial number. If you do not specify a full path, the files will be placed in the default MySQL directory (ie. if the line used looks like: log-bin=mysql-binlog.bin).
You can also add optional lines for your logging that will tell MySQL to delete old logs and to limit log size amongs other things. The complete list of options can be found here - see section 16.2.
You will need to restart the MySQL server in order to begin logging, however, if you have a pre-existent database that is going to be replicated you will want to make a snapshot of that database for your slave server(s) before you begin binary logging. This is because your slave servers will need to know what changes are applicable to its local snapshot of the database.
You can create a snapshot in one of two ways. The easiest way is to copy the database files directly. Make sure that once you have copied them, they are owned by the mysql user.
You could also create a snapshot using mysqldump, however this tends to be time-consuming, especially if there are a lot of indexes on your tables.
Once you have made a snapshot of the database(s) that you will be replicating, you can restart mysql on the master server(s); you should not get any errors.
4). Set up user/database info on slave server(s)
First, create a relay-log file. This is similar to the binary logging we set up on the master, except it is how changes to the database are queued from the master. In other words, in order to make up for delays, the master will write its changes to the binary logs we set up earlier. The slave reads these logs into its relay logs so that it knows what changes to replicate. Every thing mentioned about the binary logs applies to the relay logs (regarding permissions, selinux etc...).
relay-log=/path/to/relay-log.bin
You will then need to set up the login information for the user you created in section 1:
master-host=192.168.1.1 #Master IP
master-user=slave
master-password=mypassword
You can assign many other variables (such as port number - master-port=3306 ) if needed. The complete list can be found here.
By default, if no exclusions or inclusions are present, the slave will replicate all databases on the master. This is probably not what you want to do, though it may be.
You can define individual databases to replicate:
replicate-do-db=database1
replicate-do-db=database2
Or, if you have several databases you want to replicate but only a few you want to ignore, you could exclude the databases you want to ignore:
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
You can also ignore certain tables, and you can even use wild cards in these definitions:
replicate-wild-ignore-table=database1.table1
or to ignore all tables whose name begins "table" :
replicate-wild-ignore-table=database1.table%
Lastly, if you have problems with replication breaking because of errors that you want to ignore, you can ignore certain errors on the slave:
slave-skip-errors = 1062,1053
You will want to be very very careful ignoring errors - your results may not be what you expect. For instance, the first error - 1062, is caused by duplicate (key) entries. If you ignore the error, the data associated with that key will be updated which may not be what you want. A complete list of MySQL error codes can be found here.
Now you can start up the slave server. You may notice it is fairly busy when you first start it if your master has been running for a while and has had a large number of updates; this is just the slave catching up.
If you look at your MySQL logs, you should see a line like the following to see that replication has started and is running:
081216 12:11:06 [Note] Slave SQL thread initialized, starting replication in log 'mysql_binlog.000001' at position 520134924, relay log '/mysqlbackups/binlogs/relay-bin.000038' position: 404465738
081216 12:11:06 [Note] Slave I/O thread: connected to master 'slave@192.168.1.1:3306', replication started in log 'mysql_backups.000001' at position 520134924
5). Start/Stop and other considerations
If you need to stop the master for any reason, it is recommended that you stop the slave first. You don't have to shut the server completely down, you can simply issue "slave stop" on each slave. Once the master has been brought back up, you can then run "slave start" on all of the slaves.
You can also check the status of master/slave servers with a couple simple commands. On the master, issue "show master status" to see a large list of information about the master server. The same holds true for the slave servers; "show slave status" will give you a fair amount of information.
That is about it. As you have probably surmised, this is just an intro to replication that should get you up and running. There are a multitude of options regarding replication and I really suggest you run on over to http://dev.mysql.com and read through the official documentation for more detailed information.
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.
|
|
