Tech Chorus

How To Setup MySQL Replication

written by Sudheer Satyanarayana on 2013-11-05

In this tutorial, I will provide step by step process to setup MySQL replication. We will create one master and one slave. We will use two CentOS 6 servers - one for master and the other for slave. This following steps have been tested on two virtual machines.

Our master server will have IP address 192.168.122.10. Our slave server will have IP address 192.168.122.12.

You might want to run SELinux in permissive mode.

Perform these steps on the master:

Install MySQL server.

yum install mysql-server -y

Configure binary log and assign server ID to master. In /etc/my.cnf append the following to mysqld section:

log-bin=mysql-bin
server-id=1

Each server will have a unique ID. Our master will have ID 1 and slave 2.

Start the MySQL server.

/etc/init.d/mysqld start

Set up the initial MySQL server settings.

mysql_secure_installation

Create a MySQL user account and provide REPLICATION SLAVE access to it.

mysql> CREATE USER 'repl'@'192.168.122.12' IDENTIFIED BY 'secretpassword';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.122.12';

Be sure to replace 'secretpassword' with the actual password. Also, note that 192.168.122.12 is the IP address of the slave server. repl MySQL user will be able to access the master server only from the IP address 192.168.122.12.

Let us create a sample database, table and a row.

mysql>CREATE DATABASE sample;
mysql>USE sample;
mysql>CREATE TABLE mytable (mycol varchar(255));
mysql>INSERT INTO mytable VALUES ('first entry');

At this point, we will prepare the master server for replication.

First, we lock all the tables.

mysql>FLUSH TABLES WITH READ LOCK;

Then, we note down the binary log file and position.

mysql>  SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     1377 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The log file is mysql-bin.000003 and position is 1377. The values on your server may vary.

Don't exit the MySQL client. In a separate session/terminal run the following commands.

Stop MySQL server.

/etc/init.d/mysqld stop

Copy the files to the slave server.

rsync --recursive /var/lib/mysql root@192.168.122.12:/var/lib --exclude="mysql/mysql" --exclude='mysql-bin.*' --exclude='auto.cnf' -v

If you use other methods to copy the data, be sure to exclude mysql.bin, auto.cnf and mysql directory placed inside /var/lib/mysql.

auto.cnf was added in MySQL 5.6.

Start the MySQL server.

/etc/init.d/mysqld start

Perform these steps on the slave server.

Install MySQL server.

yum install mysql-server -y

Make sure mysql is the owner of the files.

chown -R mysql.mysql /var/lib/mysql/

Edit /etc/my.cnf and append the following line in mysqld section.

server-id=2

Start the MySQL server.

/etc/init.d/mysqld start

Perform the initial settings.

mysql_secure_installation

Inform the slave how to access the master server.

mysql> CHANGE MASTER TO
        MASTER_HOST='192.168.122.10',
        MASTER_USER='repl',
         MASTER_PASSWORD='secretpassword',
         MASTER_LOG_FILE='mysql-bin.000003',
         MASTER_LOG_POS=1377;

Remember 1377 is the position and mysql-bin.000003 is the log file name after we locked the tables on master and copied the files.

Start the slave.

mysql> START SLAVE;

You can check the slave status using:

mysql> SHOW SLAVE STATUS\G;

Here's a sample output:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 214
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 359
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 214
              Relay_Log_Space: 660
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

ERROR: 
No query specified

The key fields are Slave_IO_State, Slave_IO_Running and Slave_SQL_Running.

Let's see if our sample database has been copied.

mysql> USE sample;
mysql> SELECT * FROM mytable;

On the master run a few SQL statements.

mysql> USE sample;
mysq;> INSERT INTO mytable VALUES ('second entry');

On the slave, verify whether replication is working.

mysql> USE sample;
mysql> SELECT * FROM mytable;

Reference: MySQL reference manual

How To Setup php-fpm And Nginx On CentOS 6

written by Sudheer Satyanarayana on 2013-05-19

FPM, FastCGI Process Manager is a PHP FastCGI implementation. Using php-fpm you can have a website running as its own user instead of a common user like Apache or www-data. In this post, we will go through the process of installing and configuring php-fpm with Nginx on CentOS 6.

php-fpm package is available in the official CentOS repository. There is no need to enable third party repositories in order to use php-fpm.

nginx package is available in Fedora EPEL repository.

Enable Fedora EPEL.

rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm

Install nginx and php-fpm packages.

yum install nginx php-fpm

Create a user for our website.

useradd webuser1

As user webuser1, create the directory where we will have our PHP scripts.

mkdir  /home/webuser1/www/public -p

Also create the log directory.

mkdir /home/webuser1/logs

Let web server execute files in our home directory.

chmod o+x /home/webuser1/

Create the file /home/webuser1/www/public/index.php and write some sample PHP code in it.

<?php
phpinfo();
?>

Configure php-fpm. Craete the file /etc/php-fpm.d/webuser1.conf and add the below contents.

[webuser1]
listen = 127.0.0.1:9001
listen.allowed_clients = 127.0.0.1
user = webuser1
group = webuser1

pm = dynamic
pm.max_children = 15
pm.start_servers = 3
pm.min_spare_servers = 1
pm.max_spare_servers = 5
pm.max_requests = 2000

request_slowlog_timeout = 5
slowlog = /home/webuser1/logs/webuser1.slow.log

php_admin_value[error_log] = /home/webuser1/logs/webuser1.error.log
php_admin_flag[log_errors] = on

The FastCGI server runs on port 9001 and the IP address 127.0.0.1. The process user and group should be webuser1. We also specify where to send the logs. Start php-fpm daemon.

/etc/init.d/php-fpm start

Configure Nginx. Within /etc/nginx/nginx.conf in the http block, add:

server_names_hash_bucket_size 64; Create the file, /etc/nginx/conf.d/webuser1.conf and append the following contents.

server {
    listen       80;
    server_name yourwebsite.example.com;

    root   /home/webuser1/www/public;
    index  index.html index.htm index.php;

    location ~ \.php$ {
      fastcgi_pass   127.0.0.1:9001;
      fastcgi_index  index.php;
      fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
      include        fastcgi_params;
    }
}

Start Nginx.

/etc/init.d/nginx start

That is all you have to do to setup php-fpm and Nginx on CentOS 6.

How To Comment Several Lines Quickly Using VIM

written by Sudheer Satyanarayana on 2012-12-20

If you have to insert comment on several lines do you do manually insert the comment character in every line? Stop. Vim is a really good editor and has a nice feature to accomplish this quickly. Here are the steps:

  1. Enter visual blocking mode by pressing CTRL V (CTRL key and the lowercase v). 1 .Make your selection using motion keys(jklm, etc).
  2. Press I(uppercase I) to enter block insert mode.
  3. Press #, the comment character.
  4. Press Esc key. The comment character # will be inserted on each line the visual block selection.

Read more about visual blocking mode using the vim help topic visual-block. At the command line(:), type help visual-block.

How Many Seconds Are There Till End Of Month?

written by Sudheer Satyanarayana on 2012-09-17

(datetime.datetime(datetime.datetime.today().year, datetime.datetime.today().month, calendar.monthrange(datetime.datetime.today().year, datetime.datetime.today().month)[1]) - datetime.datetime.today()).total_seconds()

There's a log going on in that one liner. Let's break it down.

The two key Python modules we need to calculate the number of seconds till the end of year are datetime and calendar.

calendar.monthrange(year, month) returns a tuple. The tuple's second element is the number of days in the month.

We create two date objects: date object 1: today date object 2: the last day of the month

We subtract object 1 from object 2. Finally, we call .total_seconds() on the resultant object.

>>> import datetime
>>> import calendar
>>> (datetime.datetime(datetime.datetime.today().year, datetime.datetime.today().month, calendar.monthrange(datetime.datetime.today().year, datetime.datetime.today().month)[1]) - datetime.datetime.today()).total_seconds()
1290914.259939
>>>

Mercurial - How To View A File Exactly Like It Appeared On A Particular Date

written by Sudheer Satyanarayana on 2012-03-19

Here's a quick tip for Mercurial users.

Travel back in time and see how the file appeared. :)

hg cat --rev "date('2011-05-03')" controllers/crons.py

Let's break it down.

hg cat is the command we use to print the given revision of file.

hg cat --rev 10885:ae28ee103fed controller/crons.py

would print the file for the revision 0885:ae28ee103fed.

Mercurial supports a functional language for selecting a set of revisions. To know more about it try:

hg help revsets

In our solution:

hg cat --rev "date('2011-05-03')" controllers/crons.py

"date('2011-05-03')" is the revset we used in cat. Notice that the revset has to be quoted. The date spec also requires quoting. Hence, a pair of single and double quotes.

To see what kind of date formats Mercurial supports run:

hg help dates

Writing A Python Script To Send Files To Amazon S3

written by Sudheer Satyanarayana on 2011-09-25

Amazon Simple Storage Service or Amazon S3 is a storage service with a web API. I use Amazon S3 to store backups of my blog and other sites. I made a simple python script to handle file uploads to S3.

In order to use Amazon S3, first create a bucket using your Amazon AWS account. As the name suggests, bucket is a container. You can create buckets using the AWS management console.

The script we're going to write will take two input parameters

Path to file, local Target S3 path Amazon S3 buckets don't support hierarchical directories. To simulate such a file system, you can use '/' in the target file name.

To use AWS from Python, boto is the de facto recommended library. You can install boto from your distribution package management system or download from code.google.com repository.

Import the libraries:

#!/usr/bin/env python

import os
import sys

from boto.s3.connection import S3Connection
from boto.s3.key import Key
Read the command line arguments:

local_file_path = sys.argv[1]
s3_path = sys.argv[2]

Using AWS Identity and Access Management (IAM) service, create an account which you can use from scripts. Make a note of the key and secret of the newly created account.

I have named my bucket 'j_bucket'.

Initialize the connection:

conn = S3Connection('your account key', 'your account secret')
pb = conn.get_bucket('j_backups')

Now, we're ready to create an S3 key. Once the key object is created, wet set the contents of the key and also the name of the key.

k = Key(pb)
file_name_to_use_in_s3 = "%s/%s"%(s3_path, os.path.basename(local_file_path))
k.name = file_name_to_use_in_s3
k.set_contents_from_filename(local_file_path)
sys.exit(0)

Save the file as send-to-s3.py and set execute permissions. We can now use the script like:

./send-to-s3.py path/to/my/local/file "mystore/mybackups"

In this example. the file "path/to/my/local/file" will be transferred to S3 bucket with the name "mystore/mybackups".

The script is also available on github.

Personal Wiki

written by Sudheer Satyanarayana on 2011-08-26

When someone asks how to become a programmer or a good programmer, the usual answer is "hack something". To clarify the jargon, hacking is not breaking into computer systems. When you start learning programming, it is good to write programs. You could hack on existing open source software projects or your own cool new project.

I have been blogging ever since I have started programming. I have urged a lot of developers to start blogging. In addition to working on a personal project, blogging about hacking is an awesome experience. Unfortunately, blogging doesn't tick for a lot of people. The usual reasons are lack of time and not having writing skills.

I have been a wiki user from a long time and I enjoy browsing wikis. I use Wikipedia everyday. Wikipedia search is the second search engine in my Firefox's list of search engines. At work, I use the wiki in Redmine. A wiki is a good tool for collaborative content creation and editing. It also works great for single users. Apart from using wikis at work and other places, I have been using a personal wiki. I use it to store

Takeaway: if you are not using a personal wiki, start today.

Web Scraping With lxml

written by Sudheer Satyanarayana on 2010-08-22

More and more websites are offering APIs nowadays. Previously, we've talked about XML-RPC and REST. Even though web services are growing exponentially there are a lot of websites out there that offer information in unstructured format. Especially, the government websites. If you want to consume information from those websites, web scraping is your only choice.

What is web scraping? Web scraping is a technique used in programs that mimic a human browsing the website. In order to scrape a website in your programs you need tools to

Make HTTP requests to websites Parse the HTTP response and extract content Making HTTP requests is a snap with urllib, a Python standard library module. Once you have the raw HTML returned by the website, you have to have an efficient technique to extract content.

Many programmers immediately think of regular expressions when talking about extracting information from text documents. But, there are better tools at your disposal. Enter lxml. Using tools like lxml you can transform an HTML document into an XML document. After all, an XHTML document is an XML document. As we all know that web authors seldom care for standards compliant HTML documents. Majority of websites have broken HTML. We have to deal with it. But hey, lxml is cool with it. Even if you supply a broken HTML document, lxml's HTML parser can transform it into valid XML document. However, regular expressions are still useful in web scraping. You can use regular expressions in conjunction with lxml, specifically when you're dealing with text nodes.

What you should know before you start?

W3Schools.com has good tutorials on these subjects. Head over to XML tutorial and XPath tutorial to brush up your knowledge.

Let's write a Python script to put our new found skills into practice.

The government of India has a web page where it lists the honourable members of the parliment. The goal of this exercise is to scrape the web page and extract the list of names of members of the parliment.

The web page in question is http://164.100.47.132/LssNew/Members/Alphabaticallist.aspx

Without further ado, let's begin coding.

import urllib
from lxml import etree
import StringIO

We can grab the web page using the urllib module. lxml.etree has the required parser objects.

result = urllib.urlopen("http://164.100.47.132/LssNew/Members/Alphabaticallist.aspx")
html = result.read()

At this point, we have the raw HTML in html variable.

parser = etree.HTMLParser()
tree   = etree.parse(StringIO.StringIO(html), parser)

We create the HTML parser object and then pass the parser to etree.parse. In other words, we tell etree.parse to use the HTML parser object. We also pass the file like string object using StringIO.StringIO.

Now, take a look at the source of the document.

The information we want is in the table whose id is "ctl00_ContPlaceHolderMain_Alphabaticallist1_dg1".

Let's begin constructing the XPath expression to drill down the document to those parts we care about.

//table[@id='ctl00_ContPlaceHolderMain_Alphabaticallist1_dg1']

The above XPath expression grabs the table node having the id "ctl00_ContPlaceHolderMain_Alphabaticallist1_dg1" irrespective of its location in the document.

The first row, , is not required since it contains the table heading. Let's grab all the rows of the table element except the first row.

//table[@id='ctl00_ContPlaceHolderMain_Alphabaticallist1_dg1']/tr[position()>1]

In each table row, the name of the member of the parliment is contained in the second cell, .

Filter the XPath expression to return only the second cell of each row.

//table[@id='ctl00_ContPlaceHolderMain_Alphabaticallist1_dg1']/tr[position()>1]/td[position()=2]

Within our target cell node, the name of the member of the parliment is contained in the anchor, <a>, element.

Further refine the XPath expression to grab the text nodes.

//table[@id='ctl00_ContPlaceHolderMain_Alphabaticallist1_dg1']/tr[position()>1]/td[position()=2]/a/child::text()

Apply the XPath expression to our tree.

xpath = "//table[@id='ctl00_ContPlaceHolderMain_Alphabaticallist1_dg1']/tr[position()>1]/td[position()=2]/a/child::text()"
filtered_html = tree.xpath(xpath)

That's all we need to do to grab the names of members of the parliment.

The filtered_html variable is a Python list. The elements of the list are the names of the members of the parliment.

Try it and see for yourself.

print filtered_html

Here's the sample output

['Aaroon Rasheed,Shri J.M.', 'Abdul Rahman,Shri ', 'Abdullah,Dr. Farooq', 'Acharia,Shri Basudeb', 'Adhalrao Patil,Shri Shivaji', 'Adhi Sankar,Shri ', 'Adhikari ,Shri Sisir Kumar', ...]

By the time you read this document, if the web page is moved or its contents altered, refer to the attached HTML document.

The complete script is posted as a gist .

Event Report - Richard M Stallman Spoke At Reva Institute of Science and Management, Bangalore

written by Sudheer Satyanarayana on 2010-08-09

he topic was Free Software Movement and GNU/Linux operating system.

It was a long drive to Reva Institute, 40 kilometers from home. I reached the venue in time thanks to the moderate traffic. The third floor was already filled. I had to go to the fourth floor to listen to the speech. The auditorium stage can be viewed from both third and fourth floor. The floor had two elevated blocks, one above the other. There were no chairs on the fourth floor. The floor was a bit dusty. Approximately five hundred people attended the event. The talk was usual as you would expect. RMS started off, explaining the meaning of free software. The four freedoms. Then he talked about the history of the free software movement, FSF, GNU, Linux, Emacs. Even though I am quite familiar with the topics, it was interesting to hear them from the horse's mouth.

RMS proceeded and made his case why you should not use proprietary software. Notable examples he presented were Skype and Microsoft Windows. He did mention about back doors of Windows and how the software owner takes control of the user's computer. He also mentioned the perils of Amazon Swindle.

From a few days, I was wondering what RMS had to say about GNU/Linux other than asking people to call it GNU/Linux or GNU+Linux instead of Linux. To my surprise there was nothing I hadn't already heard of on this topic. Naturally, the flow went to open source and distros. RMS recalled that someone called him father of open source. He said it was saying Mahatma Gandhi as father of BJP.

As usual, RMS recommended the distros BLAG, gNewSense, Ututo among others.

The man is humorous. He performed the part of the Saint IGNUcius. He wore the robe and the hat and delivered the saint talk. Everyone in the audience had a good laugh, enjoying the performance.

Before concluding his speech, he answered few questions. The questions were sent to him prior to the speech. He read the questions from the paper he held in his had and answered them one by one. People sitting in the front row of third floor had an opportunity to ask questions to which RMS answered rather fast.

The mike and speakers were not good enough. After the fans were turned off, the voice was better. The speakers squeaked a few times interrupting the speech for a few seconds, few times.

At the end of the speech, the GNU was auctioned to help raise funds for gnu.org.in. The bidding closed at Rs. 5500, approximately 118 US dollars. GNU stickers were sold. I bought a sheet of stickers thus contributing a tiny amount to FSF India.

Renuka Prasad, presumably one of the organizers, invited me and few others to lunch with RMS. At the lunch room, I had the honour of meeting few noteworthy people. The lunch and the gossip was rather quick.

My views on the talk It is hard not to like RMS and his views. His work on free software truly deserves accolades. The principal of Reva Institutes, said he hoped that RMS will be considered for Nobel Prize. I am with him on this.

Thinking practically, it is not easy to get people to call it GNU/Linux instead of Linux. The man himself said, GNU not being credited wasn't a big issue. Similarly, using the distros he recommends is not entirely practical either. A free software activist can go to this extent. Not an ordinary mortal me.

What do you think?

Make Your Own Script Appender In Mako Templates

written by Sudheer Satyanarayana on 2010-07-21

In a recently started Pylons project, I wanted to make an easy script appending facility in Mako templates.

The requirement:

base.mako contains the layout of the web page. Many templates inherit base.mako. Here's a snippet from base.mako

<html>
<head>
    <title>Some title</title>
    <script>...</script>
    <script>...</script>
</head>

my_page.mako inherits base.mako. From within my_page.mako we want to be able to append script tags in the head section of the web page. base.mako

# -*- coding: utf-8 -*-
<%! scripts = [] %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <title>${self.title()}</title>
    ${self.head_scripts()}
</head>
<body>
     ${self.menu()}
     ${next.body()}
     ${self.footer()}
</body>
</html>
...
<%def name="head_scripts()">
<% 
    all_scripts = []
    t = self
    while t:
      all_scripts = getattr(t.module, 'scripts', []) + all_scripts
      t = t.inherits
%>
% for script in all_scripts:
    <script src="${script}" type="text/javascript"></script>
% endfor

Notice the top portion of the template. We define a list variable called scripts. At this point scripts is empty.

<%! scripts = [] %>

We render the script tags by calling the function head_scripts().

 ${self.head_scripts()}

my_page.mako

<%inherit file="/base.mako"/>


<%! scripts = ['some_script.js'] %>

In my_page.mako, we define the variable scripts that contains the URLs. scripts is a list which lets you add any number of scripts to be appended.

<%! scripts = ['one.js', 'two.js', 'three.js'] %>

Looking back at base.mako, we have the function head_scripts() that grabs the scripts attribute in the inheritance chain . Once we have the list of all the URLs to be appended, we simply iterate and write the script tags.

<% 
    all_scripts = []
    t = self
    while t:
      all_scripts = getattr(t.module, 'scripts', []) + all_scripts
      t = t.inherits
%>
% for script in all_scripts:
    <script src="${script}" type="text/javascript"></script>
% endfor

getattr() ensures that if any template in the chain doesn't define scripts, there will be no error.

Once base.mako is setup, you can append the script tags by just defining a list in the inheriting templates. You can use the same technique to append title, link, style and other HTML tags.