• Jan
  • 10

Step-by-step: how to setup MySQL Database replication

This tutorial will go through the setup of MySQL database replication. I will also talk about how to get everything working smoothly again after a server crash, or if you wish to switch databases. I will try to explain what is going on behind the scenes for every step (something I've found missing from other tutorials). This is written specifically for MySQL 5.0 on Centos 4, but should be very similar on other Linux distributions. It should also work this way with MySQL 4.x.

The theory

We have 2 servers, one of which is a Master and the other which is a Slave. We tell the Master that it should keep a log of every action performed on it. We tell the slave server that it should look at this log on the Master and whenever something new happens, it should do the same thing.

You should follow the instructions below with two console windows open - one for the Master and one for the Slave. Also note that I will capitalise the first letters of Master and Slave to indicate I am talking about the servers.

Configuring the Master

First of all, we need to create a user on the Master server that the Slave will connect as. I call mine 'slave_user'. Log into mysql as root and create the user:

mysql -u root -p (log into MySQL)

CODE:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

Now, we should edit the my.cnf file (usually in /etc/my.cnf), in the [mysqld] section and tell MySQL that it's going to be a Master:

CODE:
log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=my_database
server-id=1

The first line tells MySQL to start writing a log, and tells it where to write the log. Make sure this directory is empty of all replication logs, especially if you're starting again after replication has already been used.

The second line chooses the database to write the log for. You should change this to your database. The third line gives the server an ID (to distinguish it from the Slave).

You should also make sure skip-networking has not been enabled.

You should now restart the Master:

CODE:
/etc/rc.d/init.d/mysqld restart

(MySQL restart commands may vary)

Configuring the Slave

Again, we should change the /etc/my.cnf of the Slave server, in the [mysqld] section:

CODE:
server-id=2
master-host=128.0.0.1
master-connect-retry=60
master-user=slave_user
master-password=slave_password
replicate-do-db=my_database

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

Line 1 gives the Slave its unique ID. Line 2, tells the Slave the I.P address of the Master server - so you need to change the I.P here.

The remaining lines set a retry limit, and tell the Slave the user, password and database it needs to replicate. We also tell the slave what to use as its relay log. It's best to set this directly, or MySQL will create the name from the hostname and should you change hostname, replication will fail.

You should also make sure skip-networking has not been enabled.

You should now restart the Slave:

CODE:
/etc/rc.d/init.d/mysqld restart

Getting the data onto the Slave

On the Master...

I'm assuming you have a live Master server, and an as yet empty Slave server. This stage depends on whether data is constantly being added to the Master. If so, we will have to prevent all database access on the Master so nothing can be added. This means your server will hang during the next step. If no data is being added to the server, you can skip this step. On the Master server, log into MySQL and do the following:

mysql -u root -p (log into MySQL)

CODE:
FLUSH TABLES WITH READ LOCK;

Now we will use mysqldump to get the data out. So, still on the Master server:

CODE:
mysqldump my_database -u root -p > /home/my_home_dir/database.sql;
gzip /home/my_home_dir/database.sql;

Make sure you change my_database to your database name, and my_home_dir to the name of your home directory (or another directory of your choosing). You wll now have a file called database.sql.gz in your home directory. This is a gziped copy of your database.

On the Slave...

Now we need to copy over the gzipped file. On the Slave run the following:

CODE:
scp root@128.0.0.1:/home/my_home_dir/database.sql.gz /home/my_home_dir/

Make sure 128.0.0.1 is the I.P of the Master. This will copy the file from the Master and put it in your home directory on the Slave. Now we just need to import into MySQL:

mysql -u root -p (log into MySQL)

CODE:
CREATE DATABASE `my_database`;

CODE:
gunzip /home/my_home_dir/database.sql.gz
mysql -u root -p my_database  </home/my_home_dir/database.sql

Ready to rumble...

On the Master...

Now we're ready to kick things off. We need to find the position the Master is at in the logs. So, log into MySQL and run the following:

mysql -u root -p (log into MySQL)

CODE:
SHOW MASTER STATUS;

This should give you an output along these lines:

CODE:
+---------------------+----------+-------------------------------+------------------+
| File                | Position | Binlog_Do_DB                  | Binlog_Ignore_DB |
+---------------------+----------+-------------------------------+------------------+
| mysql-bin.000001    | 21197930 | my_database,my_database       |                  |
+---------------------+----------+-------------------------------+------------------+

Keep that on-screen.

On the Slave...

Log into MySQL and do the following:

mysql -u root -p (log into MySQL)

CODE:
slave stop;
CHANGE MASTER TO MASTER_HOST='128.0.0.1', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=21197930;
slave start;

First we stop the Slave. Then we tell it exactly where to look in the Master log file. We use the values for our previous SHOW MASTER STATUS; command on the Master. You should change 128.0.0.1 to the I.P of the Master, and change the user and password accordingly.

The Slave will now be waiting. So all that's left is to...

Back on the Master...

We shoud already be logged into MySQL, so all you have to do is:

CODE:
unlock tables;

To release the tables from lock. Note you only have to do this if you previously ran FLUSH TABLES WITH READ LOCK;

And the recovery part?

Several times it's happened to me that a server has crashed, or a hostname changed or whatever, and I've had a real trouble getting replication to work again. The solution has been to clear out the logs.

On the Slave

Clear out any replication logs from /var/lib/mysql or whever the logs are being stored, as stated in my.cnf. This usually does the trick:

CODE:
rm *relay*
rm master.info

On the Master

Again, get rid of the logs, as per where they are stored in my.cnf. For me it's the following:

CODE:
cd /home/mysql/logs/
rm -f *

This should give you a fresh start on things. You can now start again from the beginning...

Final Notes

My database doesn't use InnoDB tables - it's all MyISAM. However, the MySQL manual recommends adding this to my.cnf for InnoDB databases:

CODE:
innodb_flush_log_at_trx_commit=1
sync_binlog=1

See here for more info: http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterbaseconfig.html

References

Thanks to the following for their help:
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
http://www.howtoforge.com/mysql_database_replication
http://www.gra2.com/article.php/setting-up-database-replication-on-mysql

Posted by admin in mysql, tutorials
22 comments
 
  • Dec
  • 17

Quick, easy and free nearest store postcode/ZIP finder

This tutorial will describe step-by-step how to create a store locator for your website. It works for post codes in any country, and if you're UK based that means you won't have to pay hundreds to the Post Office for the data.

If you want to just straight to the demo it's online here: Easy Store Finder Demo. To see a live example go to http://www.pitch-invasion.com and enter a UK postcode (or address) into the "Your nearest league" box in the right bar, or check out this search for a plumber in central London from simplifydiy.com

Step 1 – Preparing the data

You will need to have the data for your stores in a database. For each store, you will have to know the postcode. This should be stored in a "postcode" field. You should also create fields for "lat" and "lng". Note that the longitude is stored as "lng" as "long" is a reserved word in MySQL. Optionally, you can include a field for the domain of the address. This allows for easy lookups for multiple countries - as you can see below we have addresses from around the world.

Here is an example table, in MySQL format.

MySQL:
CREATE DATABASE `postcode_finder` ;

CREATE TABLE IF NOT EXISTS `store` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(75) NOT NULL DEFAULT '',
`address` TEXT NOT NULL,
`postcode` VARCHAR(10) NOT NULL DEFAULT '',
`lat` DOUBLE NOT NULL DEFAULT '0',
`lng` DOUBLE NOT NULL DEFAULT '0',
`domain` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY  (`id`)
) ENGINE=MyISAM

INSERT INTO `store` (`name` , `address` , `postcode`, `domain`)
VALUES (
'Waterloo Station', 'Lambeth, London', 'SE1', 'co.uk'
), (
'Gatwick Airport ', 'South Terminal, Gatwick', 'RH6', 'co.uk'
), (
'Edinburgh Waverley Railway Station ', 'Network Rail, Room 255, North Block, Edinburgh', 'EH1 1BB', 'co.uk'
), (
'Beverly Hills', 'California', '90210', 'com'
), (
'Penn Station ', '17 W 32nd St New York', '10001', 'com'
), (
'Sagrada Familia', 'Barcelona', '08013', 'es'
), (
'FC Bayern Munchen', 'Sabener Str. 51, Munchen, Germany ', '81547', 'de'
)

Step 2 – Inserting the latitudes and longitudes

For each of the stores in your database you need to know the latitude and longitude. You can find this out from Google Maps very easily. They have a Geocoding service which will return the latitude and longitude for any address. So all we need to do is loop through our database and update each store with the lat and long.

Note that the API key below (the part after key=) is the key for aciddrop.com. You should get your own key here: http://code.google.com/apis/maps/signup.html

PHP:
/**
* Update lat lngs
*
*/

function update_lat_lngs() {

//Get the list of stores
$query = "SELECT * FROM store";
$stores = $this->db->executeQuery($query);
$stores = $stores['result'];

//Run through stores and get lat / lng
foreach($stores AS $store) {

$latlng = $this->get_lat_long($store['postcode'],$store['domain']);

//Update store with its lat lng
$this->db->quick_update("store",
array('lat','lng'),
array($latlng['lat'],$latlng['lng']),
array('id'=>$store['id'])
);

}

}

/**
* Returns a lat / long of a given postcode
*
*/

function get_lat_long($postcode,$domain=null) {

if(!$domain) {
$domain = "co.uk";
}

$url = "http://maps.google." . $domain . "/maps/geo?q=" . urlencode($postcode) . "&output=json&key=ABQIAAAAWjc0ZH2RENLxziofASg9ABQH987j_SlqISv1l93HS7ksPkvN9xRAXjKLSj-Yj2Xw7I6gP3RHQb4UQj";

$json = $this->curl->get_page(array("url"=>$url));

$store_data = json_decode(str_replace(""","\"",htmlentities($json))); //Take care of accents

$lng = $store_data->Placemark[0]->Point->coordinates[0];
$lat = $store_data->Placemark[0]->Point->coordinates[1];

//Return
if($lng && $lat) {

return array('lat'=>$lat,
'lng'=>$lng
);

} else {

return false;

}

}

The first function gets the list of stores form the database, and stores it in the array $stores. For each store postcode (and domain), we then run the function get_lat_long. This queries the Google maps geocoder, which returns a json string. This string is then decoded, and the lat and long taken from the resulting object. Finally, the stores table is updated with the lat and long for each store. Note that this uses curl and json_decode, which need to be installed on your server. There are alternatives, however.

The time that this takes will vary depending on how many stores you have. If you have hundreds the script will take a while to process. Also, the Google API restricts you to 15,000 queries a day so if you have more stores than this you may have to run it a few times on different days. Note that this will also be a problem if you have more than 15,000 lookups a day from your users.

Step 3 – Querying the stores database

Now that we have all our data set all we have to do is query our stores database with the latitude and longitude of the postcode that is our starting point. The query does the maths directly in MySQL so it's very quick – even for thousands of records. It uses the Haversine formula to take into account the curvature of the earth, so it's pretty accurate (certainly more so than other formulas which use Pythagoras).

Note that we also use a checkPostcode() function, which is UK specific. If the postcode entered is a UK postcode it will format it correctly. Thanks to John Gardner at http://www.braemoor.co.uk/software/postcodes.shtml for the code.

PHP:
/**
* Get a list of our stores, sorted by distance to this postcode
*
*/

function get_stores_list($postcode) {

//If it's a UK postcode then format correctly
$postcode = $this->checkPostcode($postcode);

$latlng = $this->get_lat_long($postcode);

if(!$latlng) { //Unrecognised postcode
return false;
}

$latitude = $latlng['lat'];
$longitude = $latlng['lng'];
//    print_r($latlng);

$query = "SELECT *,
(((acos(sin(("
.$latitude."*pi()/180)) * sin((`lat`*pi()/180))
+cos(("
.$latitude."*pi()/180)) * cos((`lat`*pi()/180))
* cos((("
.$longitude."- `lng`)*pi()/180))))*180/pi())*60*1.1515)
as distance
FROM `store`
ORDER BY distance ASC
"
;
$stores = $this->db->executeQuery($query);
$stores = $stores['result'];

return $stores;

}

This will return a list of all the stores, sorted by distance. You can now display as you see fit. If you wish to limit the amount of stores returned, this can be done directly in the query.

So there you have it – a completely free store finder that will work for any international postcode.

The Demo

No tutorial is complete without a demo. You can see it online here: Easy Store Finder Demo. The source code for the demo is available here: Easy Store Finder Demo Source

References

Many thanks to the following sites for useful information in putting this tutorial together:
http://ben.milleare.com/2006/09/03/calculating-distance-with-latitude-and-longitude/
http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html
http://www.petefreitag.com/item/622.cfm
http://www.douglaskarr.com/2007/09/15/calculate-distance/
http://www.pjenkins.co.uk/blog/index.php/2007/04/04/uk_post_code_distance_calculation/

Posted by admin in mysql, php, tutorials
17 comments