online prescription solutions
online discount medstore
pills online
buy lorazepam without prescription
xanax for sale
buy xanax without prescription
buy ambien without prescription
ambien for sale
buy modafinil without prescription
buy phentermine without prescription
modafinil for sale
phentermine for sale
lorazepam for sale
buy lexotan without prescription
bromazepam for sale
xenical for sale
buy stilnox without prescription
valium for sale
buy prosom without prescription
buy mefenorex without prescription
buy sildenafil citrate without prescription
buy adipex-p without prescription
librium for sale
buy restoril without prescription
buy halazepam without prescription
cephalexin for sale
buy zoloft without prescription
buy renova without prescription
renova for sale
terbinafine for sale
dalmane for sale
buy lormetazepam without prescription
nobrium for sale
buy klonopin without prescription
priligy dapoxetine for sale
buy prednisone without prescription
buy aleram without prescription
buy flomax without prescription
imovane for sale
adipex-p for sale
buy niravam without prescription
seroquel for sale
carisoprodol for sale
buy deltasone without prescription
buy diazepam without prescription
zopiclone for sale
buy imitrex without prescription
testosterone anadoil for sale
buy provigil without prescription
sonata for sale
nimetazepam for sale
buy temazepam without prescription
buy xenical without prescription
buy famvir without prescription
buy seroquel without prescription
rivotril for sale
acyclovir for sale
loprazolam for sale
buy nimetazepam without prescription
buy prozac without prescription
mogadon for sale
viagra for sale
buy valium without prescription
lamisil for sale
camazepam for sale
zithromax for sale
buy clobazam without prescription
buy diflucan without prescription
modalert for sale
diflucan for sale
buy alertec without prescription
buy zyban without prescription
buy serax without prescription
buy medazepam without prescription
buy imovane without prescription
mefenorex for sale
lormetazepam for sale
prednisone for sale
ativan for sale
buy alprazolam without prescription
buy camazepam without prescription
buy nobrium without prescription
mazindol for sale
buy mazindol without prescription
buy mogadon without prescription
buy terbinafine without prescription
diazepam for sale
buy topamax without prescription
cialis for sale
buy tafil-xanor without prescription
buy librium without prescription
buy zithromax without prescription
retin-a for sale
buy lunesta without prescription
serax for sale
restoril for sale
stilnox for sale
lamotrigine for sale

MySQL Replication Series: How does MySQL replication work?


Setting up replication is not hard and can be done by following simple directions. You can learn setting up two type of replication MySQL offers here: master-master replication and master-slave replication. Once you have replication set up, you can start playing with it without doing any more changes to the configuration. But let us face it, using replication in production environment and for sites which are either going live or are live, requires deeper knowledge of how replication can be used and what it can (or can not) do. We will start this “MySQL Replication Series” with briefly going over how MySQL replication works. Later in the series I will be posting some tips on how to fine tune the settings to get replication to do what you want it to do. I will be keeping all the tips separate and will add links to all of the tips at the bottom of this post as I post them. Let us start by quick overview of how replication works.

In a MySQL replication setup master server puts all the commands it is executing which affects data (insert, delete, update, truncate, etc) into a file referred to as bin-log file. Slave(s) than pick up the big-log files and execute those commands on themselves. You can see which bin file is currently being used by typing: show master status, you would see output similar to:

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 519132576

This tells us that right now we are writing to mysql-bin.000001 and it is at position 519132576. Why does this position matter? It matters if you are looking at your slaves and trying to figure out where it is currently as far as position goes. If you were to do mysqldump and do it with master info, it is this number which gets added to the dump file so when you import on slave, it knows where to start from.

At this point in time if you go to slave, and type: show slave status\G, you would see something similiar to below:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.1
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 519132576

As you can see it is at the same point meaning it is all caught up to the master (this snapshot was taken from a site which was not accepting traffic at the time). Another thing you can look at, even though it is not the most accurate way to measure how behind you are, is to check last row in “show slave status” labeled: Seconds_Behind_Master: 0 <- if this is greater than 0, than you are lagging behind the master. Which in fact just means that it has not played back some of the queries. If it is NULL, it means that your slave is either not started or it has errors which needs attention. You can start and stop the slave by typing: start slave; and stop slave; respectively.

Hopefully this gives an overview of how MySQL replication works. As always, please feel free to correct me, add on to what I have said or just make comments. I am, like many others, learning new things every day.

Tip #1: What should be replicated and what should not be replicated?
————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.


2 Responses to “ MySQL Replication Series: How does MySQL replication work? ”

  1. April 21st, 2008 | 6:26 pm

    [...] NOTE:  This post is part of series on MySQL Replication.  Here is the original post: MySQL Replication Series:  How does MySQL replication work? [...]

  2. July 1st, 2012 | 11:02 pm

    Fine overview.. i was confused with binary log file which was usually change when ever i follow howto to take dump.

    Binary log is a file name on mysql master server.

Leave a reply

*
To prove that you're not a bot, enter this code
Anti-Spam Image