MySQL Master-Slave Replication

The advantages of replication:-
1) Offload some queries from one server to other.
2) Use master for all writes and Use slave for all reads.

Some basic stuff to remember before we go ahead:-
1. Master and slave installations will be on different server instances.
2. The master should not be in use during the installation process (if master is already present).

1) Setup Master server:-

   install MySQL Server
   sudo apt-get install mysql-server

after installation, Configure it to make this as Master server.

Edit

   /etc/mysql/my.cnf

MySQL should listen to all IP Addresses, so we comment out the following lines:

   #skip-networking
   #bind-address = 127.0.0.1

Set unique server ID

   server-id=1

Enable binary logging

   log-bin = /var/log/mysql/mysql-bin.log

Restart MySQL by using the command

   sudo service mysql restart

Log in to the MySQL shell

   mysql -u root -p

Create a replication user:
Its recommended to create a separate user for mysql replication to which slaves can authenticate.  Slaves will be connecting to the master using this user’s credentials.

   GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' IDENTIFIED BY   
 '<a_real_password>';
   FLUSH PRIVILEGES;
   FLUSH TABLES WITH READ LOCK;
   SHOW MASTER STATUS;

After running the above command, you should be able to see binary log position

  +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      107 |              |                  |
   +------------------+----------+--------------+------------------+

Write down the position, this would be needed later.

Note: If you already have a master setup with data, dump the data so that it can be imported to the slave for the data to be in sync.

Leave the shell.

   quit;

2) Setup Slave server:-

   install MySQL Server
   sudo apt-get install mysql-server

after installation, Configure it to make this as slave server.

Edit

   /etc/mysql/my.cnf

Set unique server ID

   Server-id=2

Restart MySQL by using the command

   sudo service mysql restart

Use below command to load the initial data from master

   mysql -u root -p<password> database_name < /path/to/masterdump.sql

Log in to the MySQL shell

   mysql -u root -p

We need to inform our slave server the details of master server like host name, replication username and password, etc. Other things that slave server need is master log file name and log position, which we have obtained by entering show master status on master server. Now we can connect slave with the master by issuing the following command

   CHANGE MASTER TO MASTER_HOST = '<host_name>', MASTER_USER ='slaveuser', 
 MASTER_PASSWORD='<a_real_password>', MASTER_LOG_FILE = 'mysql-bin.000001', 
MASTER_LOG_POS =107;

Finally, start the slave

   START SLAVE;
   SHOW SLAVE STATUS\G; 
   quit;

Now in the master host run the following command to release the lock

   mysql> UNLOCK TABLES;

And now, each write to the master gets instantly replicated on the slave as well.

How to make your application work for both MySQL and PostgreSQL?

I am using rails 3 and ruby 1.9.2 in my application.
For development environment, I am using MySQL and for staging environment, I am using PostgreSQL Database.
After hosting, we have faced some issues.

1) Quoting styles:
MySQL allows you to quote table and column names with backquotes, whereas PostgreSQL uses double quotes.
For ex:
One of our tables has a column in it called when, which must be quoted whenever we use it.
Rails will of course handle the quoting for you if you do something like

Meeting.find_by_when(Time.now)

But if you are constructing your own SQL conditions then you have to handle the quoting problem.

In MySQL, it would be like

Meeting.where("`when` < ?", Time.now)

In PostgreSQL, it would be like

Meeting.where("\"when\" < ?", Time.now)

Solution:

Meeting.where("#{Meeting.connection.quote_column_name(when)} < ?", Time.now)

2) Boolean type:
MySQL lacks a native BOOLEAN type, so if you create a boolean column in Rails, you will end up with a TINYINT(1) column which has values of 0 and 1 for false and true respectively. PostgreSQL has a native BOOLEAN type, it will accept only false/true unlike MySQL.

In MySQL, it would be like

Meeting.where("import=1") OR Meeting.where("import=?", true)

In PostgreSQL, it would be like

Meeting.where("import=?", true)

Solution:

so replace 0 and 1 with false and true in your all files then it work in both 
MySQL and PostgreSQL.

3) Other differences:
(i) Fulltext Search: PostgreSQL is case sensitive. MySQL is not case sensitive.
(ii) To select random records from DB, Mysql has a function called “rand()” and PostgreSQL has a function called “random()”.
(iii) PostgreSQL ALTER TABLE supports ADD COLUMN, RENAME COLUMN and RENAME TABLE only. MySQL has all options in ALTER TABLE.
(iv) In PostgreSQL, attribute name starting with numbers, like “360_degree” are not allowed.

Good luck!

Incompatible character encodings error in ruby 1.9

Problem:
Incompatible character encodings error while importing csv files in ruby 1.9 which have data in multiple languages.

I am using rails 3 and ruby 1.9.2 in my application.
While importing/parsing the CSV, I get an error “Incompatible character encodings: ASCII-8bit and UTF-8″. I quickly checked my database encoding, it was UTF-8 only and also in application.rb, I had

'config.encoding = "utf-8"'.

I had no idea what was going wrong…

After googling a bit, I found that couple of posts mentioned some workarounds for this issue, so I tried:

# encoding: utf-8 => in my class
and
"hello ümlaut".force_encoding("UTF-8")

That output was

"hello ?mlat" 

With this the Error was fixed (no rails error) but the converted string value is incorrect. It was working correctly in some places but not everywhere.

I searched a bit more and then I found that the sequence of bytes that represent an “ü” is different in different encodings and could not be recognized in UTF-8, so such characters were replaced with a “?”.

Solution:
We have to find out that the original encoding of the string and then convert to UTF-8. To achieve this in ruby 1.9.2, we can’t do it directly.
so, we need to install the gem ‘rchardet19′
and then add this to the top of your class, require ‘iconv’

now,

  data = CharDet.detect(value)
  puts "Detected encoding- #{data.encoding}"

and,

  value = (data.confidence > 0.6 ? Iconv.iconv("UTF-8", data.encoding, value)
           : value)

we are just converting to UTF-8 from the detected encoding.

This fixes the issue.

Tips for faster loading web sites(Optimizing page load time)

1) Make fewer HTTP request(Js, CSS & image)
     Most of the end-user response time is spent on the front-end and tied up in downloading all the components in the page like images, stylesheets, scripts, etc. Reducing the number of components in turn reduces the number of HTTP requests.
     a) Combined files => its a way to reduce the number of HTTP requests by combining all files into a single file. ex: js & css
         In our rails app, we used bundle_fu(https://github.com/timcharper/bundle-fu). Its used to bundle all your assets very easy. It can speed your load time up around 50%.

         Example put the following around your stylesheets/javascripts:
         -bundle :name => "default_bundle" do
           = javascript_include_tag "http://w.sharethis.com/button/buttons.js"
           = stylesheet_link_tag 'jquery-ui', 'auto_complete/token-input.css'
           = javascript_include_tag 'jquery-1.4.2.js', 'jquery-ui.js', 'auto_complete/jquery.tokeninput.js', 'auto_complete/setup.js', 'underscore.js', 'date.js', 'cal.js', 'application.js', 'time_picker/jquery.timePicker.js', 'ajax_pagination.js'
           = stylesheet_link_tag 'compiled/certification.css','compiled/error.css', 'compiled/elements.css', 'compiled/messages.css', 'compiled/calendar.css', 'compiled/common.css', 'time_picker/timePicker.css', :media => 'screen, projection'
           = stylesheet_link_tag 'compiled/print.css', :media => 'print'
           = javascript_include_tag "markerCluster/jsapi", "markerCluster/map.js", "markerCluster/markerclusterer.js", "jquery-jtemplates"

     b) CSS sprites     => Its used to reducing the number of image requests. Combine your background images into a single image and use the CSS background-image and background-position properties to display the desired image segment.

2) Avoid empty src or href
     You may expect a browser to do nothing. But most browsers makes a request to server(sending a large amount of unexpected traffic).

3) Compress components with gzip
     This is used to reduce their file size over the wire by approximately 70%. This can be set up using your Apache(needs Apache 2, mod_deflate, mod_headers and access to server config) or Nginx config.

     example for apache(in the server config file):
     # Compress some text file types
     AddOutputFilterByType DEFLATE text/html text/css text/xml application/x-javascript
            
     # Deactivate compression for buggy browsers
     BrowserMatch ^Mozilla/4 gzip-only-text/html
     BrowserMatch ^Mozilla/4\.0[678] no-gzip
     BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
            
     # Set header information for proxies
     Header append Vary User-Agent

4) Add expires headers for JS & CSS
     There are two aspects to this rule:
     a) For static components : implement "Never expire" policy by setting far future Expires header
     b) For dynamic components: use an appropriate Cache-Control header to help the browser with conditional requests
        
     This means that A first-time visitor to your page may have to make several HTTP requests, but by using the Expires header you make those components cacheable. The next request, Browser use a cache to reduce the number and size of HTTP requests, making web pages load faster. A web server uses the Expires header in the HTTP response to tell the client how long a component can be cached. However, it creates an additional problem too. The problem is what happens if you change these files? The browser will be stuck with the old files. The solution is to send a last modified timestamp with your requests (Ex: "<img src='/images/rails.png?84392578943' />"). Now your browser will know to ask for the file again. The "timestamp" is the default behavior of rails.

5) Put CSS at top
     Yahoo discovered that moving stylesheets to the document HEAD makes pages appear to be loading faster. This is because putting stylesheets in the HEAD allows the page to render progressively.

6) Minify JS & CSS
     Minification is the practice of removing unnecessary characters from code like comments and unneeded white space characters (space, newline, tab and etc). This improves response time performance & load times. You can use JSMin and YUI Compressor for minifying your JS code. Also some plugins are there, please check it here: https://github.com/sinefunc/sinatra-minify and https://github.com/ericbarnes/ci-minify.

please check the screenshot. Right now, as you can see, I have made it Yslow grade from "F" to "B" very easily. I am sure, we can easily get grade "A" too... We need some support from the server side regarding "Add expires header" and "Use Cookie-free Domains for Components". I have requested engineyard(hosting server) for the same. Waiting for the reply from them. By next week, it will turn into grade "A".

and also found one good link from rubyquicktips. Benchmark.ms is very nice. its used to track how long some bit of code takes to process. please check it here: http://rubyquicktips.tumblr.com/post/2838217166/benchmark-ms-rails-you-sneaky-devil

Before optimization:


After optimization:

“cycle” helper in Rails

If you wanna display the list of records with different(alternate) classes for table rows, then you can use this helper(you no need to check odd-even records). Rails has so many awesome feature like this.

for ex: if you want to apply ‘odd’ and ‘even’ class for alternate record

 -season_hash.each do |k, v|
   %tr{:class => "#{cycle('odd', 'even')}"}
     %td= v["games"]
     %td= v["goals"]
     %td= v["assists"]
     %td= v["practices"]

“ordinalize” in Rails

How to display a date with suffix like “th”, “st”, “nd”, or “rd”?

for example, I wanna display like this Mon, 7th April

Rails has inbuilt function – “ordinalize”

It turns a number into an ordinal string used to denote the position in an ordered sequence such as 1st, 2nd, 3rd, 4th

date.strftime(“%a, #{date.day.ordinalize} %B”)

Date/Time validation class methods

Summary:

This class method has the ability to do date and time validation checking with ActiveRecord.
The validators can be used to parse strings into Date and Time objects as well as range check.

#{RAILS_ROOT}/config/initializers/validates_date.rb
ActiveRecord::Base.class_eval do

  def self.validates_date(*attr_names)

    # Set the default configuration
    new_options = attr_names.extract_options!
    if new_options[:compare_time]
      configuration = { :after => Time.now.utc}
    else
      configuration = { :after => Date.today }
    end

    # Update defaults with any supplied configuration values
    configuration.update(new_options)

    # Validate each attribute, passing in the configuration
    validates_each(attr_names, configuration) do |record, attr_name, value|
      unless value.nil?
        value = new_options[:compare_time] ? value : Date.parse(value.strftime('%Y/%m/%d'))
        if configuration[:after].is_a?(Time) || configuration[:after].is_a?(Date)
          after_date = new_options[:compare_time] ? configuration[:after] : Date.parse(configuration[:after].strftime('%Y/%m/%d'))
        else
          s_date = record.send(configuration[:after])
          after_date = new_options[:compare_time] ? s_date : (s_date.present? ? Date.parse(s_date.strftime('%Y/%m/%d')) : s_date)
        end

        unless after_date.nil?
          e_msg = configuration[:after].is_a?(Time) ? configuration[:after].strftime("%B %d, %Y %H:%M%p").to_s : (configuration[:after].is_a?(Date) ? (configuration[:after]-1).strftime("%B %d, %Y").to_s : configuration[:after])
          record.errors.add(attr_name, I18n.t('must_be_after') + e_msg.to_s.humanize) if value <= after_date
        end

      end
    end
  end
end

class Project < ActiveRecord::Base
    # Validates that start_date is in the future
    validates_date :start_date,  n => :create
    # Validates that end_date is later than start_date
    validates_date :end_date, :after => :start_date
end

class Slot < ActiveRecord::Base
    # Validates that start_date is in the future
    validates_date :start_date, :compare_time => true,  n => :create
    # Validates that end_date is later than start_date
    validates_date :end_date, :after => :start_date, :compare_time => true
end

== Example
  p=Project.new
  p.start_date = Date.today-1
  p.end_date = Date.today -2
  p.valid? #false
  p.errors.full_messages #["Start date must be after September 04, 2010", "End date must be after Start date"]

You can customise the error messages for dates or times using I18n.
Suggestions and comments are welcome.