As our database grew, and the data within became more complex, we were starting to run into more and more situations where a complicated query would accidentally lock up our site. I decided it was time that Xobni put on some britches and started using mysql replication.
There are many reasons why you should set up a master-slave relationship for your mysql server. Redundancy and fail-over, fast live backups (dump your slave instead of your master), speed (you can read from your slave, and write to your master), scaling potential (it’s much easier to add a new slave to an existing master-slave pair), the list goes on! No matter your reason, IMHO this is by far the easiest way to start replicating a database from one server to another with a live server.
Most tutorials I found deal with new set ups without existing data, or use deprecated transfer methods, or have you copy down bin-log locations, and some go so far as mysqldump->scp->mysql. I wanted a single command, more or less, to ensure as little downtime as possible.
Since our web site is live and handling over 400 requests/sec off-peak, downtime wasn’t an option. The method had to transfer existing data, and do it quickly. With our ~2GB database, and using my technique, our mysql server was locked for less than 5 minutes.
First, make some config changes:
For me, this meant editing my my.cng file on my existing mysql machine (on my debian machine, that’s ‘vim /etc/mysql/my.cnf’) and adding the following:
# settings for master-slave replication
log-bin=mysql-bin
server-id=1
expire-logs-days = 20
max_binlog_size = 104857600
log-bin tells the server where to store the logs, server-id basically tells the server it’s the master, expire/max tell the server how to clean up the logs (we don’t want them to grow forever!).
Now, log in to mysql on the commandline (for me: ‘mysql -uuser -p’), and run the following to set up our replication user:
GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'{ip_of_your_slave_server}' IDENTIFIED BY '{your_password}';
Now, log into your to-be-slave server, and edit its my.cnf file and add the following:
# who's your daddy
server-id=2
master-host = {server_ip}
master-port = 3306
master-user = replicant
master-password = {pass}
replicate-do-db={db_name}
replicate-ignore-db={db_name}
replicate-do-db and replicate-ignore-db are optional and only needed if you want to restrict your replication to a particular database and not another. Be sure to exclude the curly brackets and change the port if needed.
Then restart both mysql servers on both machines to effect the changes.
/etc/init.d/mysql restart
or
/etc/rc.d/init.d/mysqld restart
The slaving will start on the restart, but we don’t want it to do anything yet, because our servers aren’t yet synced up. So after restart, you’ll need to log into mysql on your slave machine, and type:
STOP SLAVE;
On to the magic! On the master server, run:
mysqldump --extended-insert --master-data -u{mysql_user} -p{pass} {db_name} | mysql --compress -h{ip_of_slave} -u{mysql_user} -p{pass} {db_name}
Here, you need the username and password of a privileged user on each machine and the ip of the slave machine. If you want to set up replication for all databases, replace {db_name} with ‘–all-databases’. Be sure to exclude the curly brackets.
What this is going to do is make a special dump that 1) locks the server to any new writes, and 2) appends a tag at the end for the slave server to use so it knows where to start its slaving. It pipes the output directly to mysql, which copies the data directly to the server. No need to find and scp the file over; it’s easier and faster this way.
Once that’s done, all that you need to do is log into mysql on your slave machine and type:
START SLAVE;
That’s all there is to it! Check it by inserting some data in your master and then selecting on your slave. It works? Awesome! Found a better way? Let me know so I can update or link!
Hopefully your site suffered very little downtime during the transfer, and now you have a working slave server to do your bidding. Muhahaha!
Update: It seems that by default, a slave server will stop replication if an error occurs. For me, I did not exclude databases that I didn’t want replicated — using replicate-do-db needs to be used in conjunction with replicate-ignore-db (added above). So it kept on trying to replicate changes in other databases that didn’t exist on the slave server, and I ended up with a disobeying slave.
If your slave stops running, type ‘SHOW SLAVE STATUS;’ in mysql, which will tell you if an error has occurred. Once you fix the error, add:
slave-skip-errors={error-code-from-slave-status}
Replace the error code with the code reported in the slave status, restart mysql, and the slave should start up again.
Posted on December 12th, 2008 by plusbryan
Filed under: Infrastructure | No Comments »
I love rounded corners. If I had my way, right angles would cease to exist - in web sites, in applications, in cars, in cell phones. Don’t get me wrong: squared corners, sparingly used, can give definition and even meaning to a design. Take the font Georgia, for example; a distinctive and refined serif font, with the soft curves you don’t see in default Times.
