Well i had this problem at my work. I developed an intensive piece of software that during work hours would cause certain tables to lock. Being that we have one monolithic database server this is a huge problem. Normally i could care less with only a couple people working on the software they’d only notice a few seconds longer to submit there entry. Well the problems is that the live sites would be working in the same database, and so being affected and in some situations would cause the sites to lose connection all together. The solution finally was to set up a second MYSQL server as a slave and turn on the replication aspect of MYSQL. This solved all our problems and is actually very easy to maintain.

So what im going do for you is give you a basic run down on what you need to do to set this up.

What you need:

• 2 Servers
• MYSQL installed on both of them
• Some SQL/linux knowledge

Step 1:
The first step that i would take is make sure that all the data in the two servers is the same. This can be done usually with a mysqldump, however you do it make sure the information you want replicated is on both mysql servers.
Step 2:
Choose which server will the master and which will be the slave. So the first thing is to locate the my.cnf file on the master server. This file is usually located in the /etc/ directory. Once located edit and add the entry under the [mysqld] section.

1
2
3

[mysqld]
server-id=1
log-bin

The server id can be whatever you want, but no server should have the same id. Next do the same thing with the slave except dont add the log-bin entry. like so:

1
2

[mysqld]
server-id=1

Step 3:
Your gonna need an account for the slave to access the master. I would advise greatly against using root, or any other day to account. Instead create an account for this use only. You can create a basic user like this from inside mysql like so:

1

mysql>GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%' IDENTIFIED BY 'somepassword';

Step 4:
At this point in the game i would probably go through and restart both your mysql servers. Super simple, the basic way to do this if your server i set up standardly is.. go to /etc/rc.d/init.d/ then type ./mysqld restart

Now that your to this point what we need to do is find out your Binary Log Position. This is vital so that the slave knows where to start pulling sql statements from. So what you need to do is log in to the master mysql server and excute this statement:

1
2
3
4
5
6
7

mysql> show master status;
+--------------------------------+----------+-----------------+--------------------+
| File                                  | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------------------+----------+-----------------+--------------------+
| thename-bin.000001           | 6376597 |                     |                          |
+--------------------------------+----------+-----------------+--------------------+
1 row in set (0.00 sec)

Remember all of this as you’ll need it in the next and final step.
Step 5:
YAY final step. Go to your slave server and login. Then type this in:

1
2
3
4
5
6
7
8

mysql> change master to
master_host='<ip-address or hostname>',
master_user='replication_user',
master_password='somepassword',
master_log_file='thename-bin.000001',
master_log_pos=6376597;
 
mysql> start slave;

and that was all she wrote. There are now a bunch of commands and other things you can do but ill write down a few vital commands you should know for maintaining these:

1
2
3
4

stop slave;  //stops the slave from pulling from the master
start slave; //starts slave to pull from master
show slave status; //this command is run on the slave server to check its status
show master status; //this command is run on the master server to check its status

i like the last two alot so that when i check the binary log position i can make sure that there both sync’d at the right record.

Author: Jason Rogers