Easily Replicate your MySQL Database!
Replication is one of those things that is often overlooked when setting up a MySQL database for your web application. It’s one of those configurations that you should setup when you start out, as it can make a huge difference in the long run.
For the purpose of replication, MySQL defines a master and slave database. The master database is where records should be written/updated to. The slave database will automatically get updated every time there is a change on the master database.
There are two main advantages of setting up MySQL replication:
- Redundancy for your data (i.e. backup)
- Scaling your application
Other than for backing up your data in real time, replication can be used to improve the performance of your web application.
Let’s assume you run a website and a cron job that e-mails your users on a daily basis, such as:
Once you try to scale, whenever the e-mail cron job runs, it will probably put a lot of stress on your database.
That’s where replication can come in handy, and for the purpose of this tutorial, we will be using the following setup:
Setting up MySQL Replication
We’ll be using the above architecture for this tutorial, and we are assuming that you are starting off with an empty database. We will refer to the database as following:
- Master DB - 192.168.1.1
- Slave DB - 192.168.1.2
The first thing you need to do is to stop your database. We’ll start off by configuring the master database in the mysql config file located at /etc/mysql/my.cnf.
MySQL will need to listen on all IP addresses, so comment out this line:
To keep the master and the slave database in sync, we will enable binary log files to be generated. Make sure to have enough disk space, as these binary log files can take up a lot of space. Enable the binary log files as following:
log-bin = /var/log/mysql/mydatabase-bin.log binlog-do-db = mydatabase
Identify the MySQL master database with an ID:
server-id = 1
Now that we are done with the mysql.cnf , open up the mysql admin shell as following:
mysql -u username -p<password> #no space between the p and <password>
Create the database user that the slave master database will use, and grant it access:
CREATE USER 'slave_user'@'%' IDENTIFIED BY 'password'; GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
Restart your MySQL database:
service mysql restart
We are almost done setting up the master database, now we just need to write down the master database’s position in the binary log files:
USE mydatabase; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
The above command would return something like this:
+---------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------------+ | mysql-bin.006 | 183 | exampledb | | +---------------+----------+--------------+------------------+
We will use this information when configuring the slave, so write down the bin log file name, and the position.
Configuring the Slave Database
The Slave configuration is similar to what we did for the Master database, and is pretty self-explanatory:
server-id=2 master-host=192.168.1.1 #host IP master-user=slave_user #replication user master-password=password master-connect-retry=60 replicate-do-db=mydatabase #database to replicate from the master
Restart your slave database
service mysql restart
Go to the mysql admin shell, and stop the slave
mysql -u username root -p<password> #no space between the p and <password> slave stop
Tell the slave to start replicating from the master database:
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;
Let’s break that command down:
- MASTER_HOST is the IP address of the hostname
- MASTER_USER is the replication user that we created earlier on the master database
- MASTER_PASSWORD is the password for the MASTER_USER
- MASTER_LOG_FILE is the master’s binary log file name that we wrote down earlier
- MASTER_LOG_POS is the position in the master’s binary log file position that we wrote down earlier
We are done configuring the slave, let’s start the replication:
You can check if the replication is working by typing:
show slave status;
If the status returned is “Waiting for master to send event”, then you are done!
Lynkly has launched!
We are really excited to launch Lynkly today, as it’s been in the works for the past 5 months. We would love it if you could post some feedback, and let us know how to improve it!
How to enable Google +1
I am sure you have all heard about Google’s announcement of +1. In a nut shell, every google search result will have a +1 button allowing you to recommend that link. As described on TechCrunch, they will also be releasing a +1 button that will be embeddable on websites.
TechChrunch also posted steps on how to enable the +1, however, after following the instructions, I was not able to get it to work.
Out of pure concidence, these are the steps that worked for me using Chrome:
- Log into your Google account
- Go to http://www.google.com/experimental/
- Now in the Chrome address bar, type in some bogus website ie: sfsfsfsdfsfsfs.com
- You should get a “Oops Google Chrome could not find sfsfsfsdfsfsfs.com” page
- Now on that same page, type in some keywords in the search box then press enter
- +1 should now be enabled
Let us know if that worked for you, or if you found a different way!
We are working hard on putting the final touches on lynkly, hope you like what we have done so far … we will be doing an official launch in the coming weeks!
Prashant’s first post
Rob and myself found that our friends on facebook post pretty cool links sometimes. There is no way to know which ones are the best or to filter them. We kept searching for lynks posted by high school and university friends.
In trying to answer those questions we wrote lynk.ly
With a large enough user base we should be able to find the coolest links across Facebook.
I am excited to see where this goes!
BTW, Rob its awesome working with you!