With a basic knowledge of MySQL, IT pros can setup MySQL replication between a master and a slave server. Replication
helps IT managers consistently back up MySQL data or, if they choose, set up master/slave connections between servers for load balancing and/or fault tolerance. This tip examines both use cases and more.
We begin by setting up master to slave. This will enable replication of everything that happens on the master to be sent directly to the slave. It's a one way communication, as the slave does not send any data to the master. Why is this configuration useful? Imagine you have three mission critical servers: one is the master, replicating all information to two slave servers. Should the central server go offline for any reason, either one of the slave servers can do the work of the central server.
You can start by going into the MySQL monitor line and granting replication rights to a user called repl to any database in the following command line (note that you can create a user with any name that you want):
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'password'
Now exit from MySQL monitor mode by typing exit and edit the global MySQL configuration file, normally held at /etc/my.cnf. You will see an area named [mysqld]. Under this area , add these configuration lines:
The first command changes MySQL's logging mechanism to bin files. This makes replication/clustering much easier and faster. The second command creates the server-id; every server in any kind of replication/cluster setup must have a unique server –id. In our case, we have set the master server's id to 1.
Save and exit this file. Then restart the MySQL service and go back into MySQL monitoring mode.
Assuming that the two servers being setting up for replication are brand new, they already contain the same data (default).
Under the MySQL monitor mode on the master server, run this command:
mysql> show master status;
It should give you output similar to this (see below). I've edited the output to show only what we need. Your output should be very similar.
This tells us that the current log file is mysql-bin.000001 and the current log position number is 98. Make sure you remember or keep this information in clipboard.
Start by editing the /etc/my.cnf file and add a server-id, I set mine to:
Notice that we didn't change the logging type to binary. It's not necessary, but it would make things much cleaner for future use. So I've also added a line to change its logging type:
Once you've edited the file accordingly, save the file. Then, restart the MySQL service, go into MySQL monitor mode on the slave server, and type the following exactly:
mysql> CHANGE MASTER TO
Make sure not to put a ";" at the end of that statement. If you do it properly, you'll get a ">" prompt. Then type the rest of the following:
> MASTER_HOST='masteripaddress', > MASTER_USER='repl', > MASTER_PASSWORD='password', > MASTER_LOG_FILE='mysql-bin.000001', > MASTER_LOG_POS=98;
Notice that the first four lines ended with a "," and the last line ended with a ";" to notify the end of the entire command. This set of commands tells the slave server where to get MySQL updates from.
Now we'll type a command that will make the slave server start getting updates from the master server, this command is:
mysql> start slave;
Then, to view the slave status, you can run:
mysql> show slave status;
The status could be "waiting for master to send event" or "connecting to master." If it displays "connecting to master" for more than a couple of minutes, it cannot make a connection. Iptables or a firewall may be the problem.
Clustering, master-master replication
Time to setup clustering and master-master replication, so that each server has identical information regardless of which server triggered the change. The current master/slave configuration stays the same except you'll configure the slave as a master, and make the current master the new slave, making each server both master and slave.
On the original slave, make sure that binary logging is enabled. Under the my.cnf file:
Restart the slave service. Now, you'll go into monitor mode under this server. Run:
mysql> show master status;
This information, will be nearly the same information as when we were configuring our master/slave setup. Now, under the original master server (we can call it server 1), go into MySQL monitor mode, and do the "change master to" sequence. Then "start slave".
Now, under server 1, run "create database test;" then log into the original slave (or server 2), and run "show databases;". You'll see that database test exists in here as well. Now under server 2, run "drop database test;". Log into server1 and run "show databases;" and you'll notice that it's no longer there.
Congratulations, you now have full MySQL replication.
About the author: Austin Jordan is a systems and network administrator for a large wireless internet service provider located in downtown Dallas, Texas. He has a genuine love for Linux, networking and wireless technologies.