High Availability MySQL

Basic Guideline for High Availability MySQL

  1. Replication
  2. DRBD HA

We are discussing DRBD HA here. So first things to go is

DRBD HA

Following Steps are required:

  • Preparation of OS :
  • DRBD

Preparation OS :

We need to reserve a huge physical volume which would be later used as a DRBD volume.
Don’t specify any file system type.

fdisk /dev/sda

Should print:

The number of cylinders for this disk is set to 9729.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:

  1.   Software that runs at boot time (e.g., old versions of LILO)
  2.   Booting and partitioning software from other Oss (e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p

Disk /dev/sda: 80.0 GB, 80026361856 bytes
255 heads, 63 sectors/track, 9729 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 2611 20972826 83 Linux
/dev/sda2 2612 2872 2096482+ 82 Linux swap
/dev/sda3 2873 3003 1052257+ 8e Linux LVM
/dev/sda4 3004 9729 54026595 5 Extended
/dev/sda5 3004 9729 54026563+ 8e Linux LVM

We are going to use /dev/sda5 as a DRBD device.

DRBD Setup

On Server1 and Server2

yum -y install drbd
yum -y install kernel-module-drbd-2.6.9-42.ELsmp
modprobe drbd

DRBD Configuration:
On both servers:

vi /etc/drbd.conf

#
# please have a a look at the example configuration file in
# /usr/share/doc/drbd/drbd.conf
#
# Our MySQL share
resource db
{
protocol C;
incon-degr-cmd “echo ‘!DRBD! pri on incon-degr’ | wall ; sleep 60 ; halt -f”;
startup { wfc-timeout 0; degr-wfc-timeout 120; }
disk { on-io-error detach; } # or panic, …
syncer {
group 1;
rate 6M;
}

on server1 {
device /dev/drbd1;
disk /dev/sda5;
address 10.10.150.1:7789;
meta-disk internal;
}

on server2 {
device /dev/drbd1;
disk /dev/sda5;
address 10.10.150.2:7789;
meta-disk internal;
}
}

Start DRBD

On both servers:
drbdadm adjust db
On server1:
drbdsetup /dev/drbd1 primary –do-what-I-say
service drbd start
On server2:
service drbd start

DRBD Status

On both servers (see status):

service drbd status

On server1:
mkfs -j /dev/drbd1
tune2fs -c -1 -i 0 /dev/drbd1
mkdir /db
mount -o rw /dev/drbd1 /db

On server2:
mkdir /db

Test failover

For manual switchover (This wont be needed as HA will do this for you):

On primary server

umount /db
drbdadm secondary db

On secondary server
drbdadm primary db
service drbd status
mount -o rw /dev/drbd1 /db
df

This finishes DRBD part of it. You have created a DRBD mount which will be used as a data directory for your MySQL.

MySQL

  1. We can do an RPM based or a BINARY or a SOURCE compilation.
  2. IMPORTANT:(Crucial for failover) Heartbeat uses either LSB Resource Agents or OCF Resource Agents or Heartbeat Resource Agents to start and stop heartbeat resources. Here, MySQL,DRBD and IP are our heartbeat resources.
  3. Refer this page on Resource Agent
  4. As you are aware of it many *nix services are started using LSB Resource Agents. They are found in /etc/init.d
  5. A service is started/stopped using: /etc/init.d/servicename start/stop/status
  6. We should see to it that we have similar LSB Resource Agent for MySQL.
  7. In, source based installation it will be created in $PREFIX/share directory as mysql.server. $PREFIX is one you give during source compilation.
  8. Fix that script and copy it to /etc/init.d/
  9. In case of RPM based installation you will get LSB Resource Agent in place.
  10. End objective is that, MySQL should be up and running.
  11. Now comes the hurdle.
  12. Stop MySQL.
  13. Move your data directory to a directory on DRBD share.
  14. Later, create a softlink. Check Unix Command : ln
  15. This is how I would have done assuming my initial data directory was /home/mysql/data :

server1:

mkdir /db/mysql
NOTE: /db should be mounted to do this
mkdir /db/mysql/data
chown -R mysql /db/mysql/data
chgrp -R mysql /db/mysql/data
mv /home/mysql/data /db/mysql/data
ln -s /db/mysql/data /home/mysql/data

server2:

mv /home/mysql/data /tmp
ln -s /db/mysql/data /home/mysql/data

Now, start MySQL on server1. Create some sample database and table. Stop MySQL. Do a manual switchover of DRBD. Start MySQL on server2 and query for that table. It should work. But, this is of no use if you have to switchover manually every time. Now we are heading to HA.

HA

 Installation:

yum -y install gnutls*
yum -y install ipvsadm*
yum -y install heartbeat*

Configuration:

vi /etc/sysctl.conf and set net.ipv4.ip_forward = 1
vi /etc/sysctl.conf
# Controls IP packet forwarding
net.ipv4.ip_forward = 1
/sbin/chkconfig –level 2345 heartbeat on
/sbin/chkconfig –del ldirectord

We need to setup the following conf files on both machines:
a. vi /etc/ha.d/ha.cf

#/etc/ha.d/ha.cf content
debugfile /var/log/ha-debug
logfile /var/log/ha-log
logfacility local0
keepalive 2
deadtime 30
warntime 10
initdead 120
udpport 694 #(If you have multiple HA setup in same network.. use different ports)
bcast eth0 # Linux
auto_failback on #(This will failback to server1 after it comes back)
ping 10.10.150.100 #(Your gateway IP)
apiauth ipfail gid=haclient uid=hacluster
node server1
node server2

On both machines:

b. vi /etc/ha.d/haresources
NOTE: Assuming 10.10.150.3 is virtual IP for your MySQL resource and mysqld is the LSB resource agent.

#/etc/ha.d/haresources content
server1 LVSSyncDaemonSwap::master IPaddr2::10.10.150.3/24/eth0 drbddisk::db Filesystem::/dev/drbd1::/db::ext3 mysqld

c. vi /etc/ha.d/authkeys
#/etc/ha.d/authkeys content
auth 2

2 sha1 YourSecretString
Now, make your authkeys secure:
chmod 600 /etc/ha.d/authkeys

Start:
On both machines(first on server1):
Stop MySQL.
Make sure MySQL does not start on system init.
For that:
/sbin/chkconfig –level 2345 MySQL off
/etc/init.d/heartbeat start
These commands will give you status about this LVS setup:
/etc/ha.d/resource.d/LVSSyncDaemonSwap master status
ip addr sh
/etc/init.d/heartbeat status
df
/etc/init.d/mysqld status

Access your HA-MySQL server like:
mysql -h10.10.150.3

Shutdown server1 to see MySQL up on server2.
Start server1 to see MySQL back on server1..

The BLACKHOLE Storage Engine

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result:

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;

Query OK, 0 rows affected (0.03 sec)

 

mysql> INSERT INTO test VALUES(1,’record one’),(2,’record two’);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

 

mysql> SELECT * FROM test;

Empty set (0.00 sec).

The cron command

The crontab command shown below will activate the cron tasks automatically on the hour:
0 * * * * wget -O – -q -t 1 http://www.jit.co.in/cron.php

In the above sample, the 0 * * * * represents when the task should happen. The first figure represents minutes – in this case, on the “zero” minute, or top of the hour. (If the number were, say, 10, then the action would take place at 10 minutes past the hour.) The other figures represent, respectively, hour, day, month and day of the week. A * is a wildcard, meaning “every time.”

The rest of the line basically tells the server to “ping” the url http://www.jit.co.in/cron.php.
Here is a diagram of the general crontab syntax, for illustration:

# +—————- minute (0 – 59)
# | +————- hour (0 – 23)
# | | +———- day of month (1 – 31)
# | | | +——- month (1 – 12)
# | | | | +—- day of week (0 – 7) (Sunday=0 or 7)
# | | | | | * * * * * command to be executed

Add ONE of the following lines:

45 * * * * /usr/bin/lynx -source http://jit.co.in/cron.php
45 * * * * /usr/bin/wget -O – -q -t 1 http://www.jit.co.in/cron.php
45 * * * * curl –silent –compressed http://jit.co.in/cron.php.

The Three Ways to Cloud Compute

 

SAAS – Software as a Service

Best Part

  1. Accessible from anywhere through internet
  2. Good for the collaborative environment
  3. Subscription basis can save cost instead of investing a huge money
  4. Reduce Infrastructure Cost, Maintenance Cost, etc.

Need to Think

  • Genric Application sometimes not suitable for all business
  • But can be archive though customized plugin

PAAS – Platform as a Service

Provide environment and tools for creating new online application

Example: Google App Engine, Iphone IOS, Microsoft Azure Platform

Best Part

  • Rapid development at low cost
  • Private and public deployments

Need to Think

  • Limited developer to provider languages and tools
  • Risk of vendor lock-in

IAAS – Infrastructure as a Service

Allow existing applications to sun on a cloud’s hardware

Example- Shared hosting.

Project Open ]po[

Project Open
Nice Project Management Tool;
http://www.project-open.com

]po[ iPhone App available
Follows PMI Guideline
Good UI & Structuring

But
Only CVS Support! SVN Integration is on the way; no Idea on GIT Support
Instead of MySQL they use PostgreSQL.

SOAP

SOAP, originally defined as Simple Object Access Protocol. It relies on Extensible Markup Language (XML) for its message format.SOAP has been adopted as the standard for Web services, and applications from major vendors have developed SOAP APIs for their products, thus making software systems integration easier.

Transport methods

Both SMTP and HTTP are valid application layer protocols used as Transport for SOAP

Message format

POST /InStock HTTP/1.1
Host: www.example.org
Content-Type: application/soap+xml; charset=utf-8
Content-Length: 299
SOAPAction: “http://www.w3.org/2003/05/soap-envelope”

<?xml version=”1.0″?>
<soap:Envelope xmlns:soap=”http://www.w3.org/2003/05/soap-envelope”>
<soap:Header>
</soap:Header>
<soap:Body>
<m:GetStockPrice xmlns:m=”http://www.example.org/stock”>
<m:StockName>IBM</m:StockName>
</m:GetStockPrice>
</soap:Body>
</soap:Envelope>

Advantages

  • SOAP is versatile enough to allow for the use of different transport protocols. The standard stacks use HTTP as a transport protocol, but other protocols such as JMS[7] and SMTP[8] are also usable.
  • Since the SOAP model tunnels fine in the HTTP get/response model, it can tunnel easily over existing firewalls and proxies, without modifications to the SOAP protocol, and can use the existing infrastructure.

Disadvantages

  • Because of the verbose XML format, SOAP can be considerably slower than competing middleware technologies such as CORBA.
  • When relying on HTTP as a transport protocol and not using WS-Addressing or an ESB, the roles of the interacting parties are fixed. Only one party (the client) can use the services of the other. Developers must use polling instead of notification in these common cases.

 

To start, First enabling the php SOAP extension  php_soap

When programming some soap server set the “soap.wsdl_cache_enabled” directive in php.ini file to 0:

soap.wsdl_cache_enabled=0

php Soap Client Call

<?php
$client = new SoapClient(“some.wsdl”);
$client = new SoapClient(“some.wsdl”, array(‘soap_version’   => SOAP_1_2));
$client = new SoapClient(“some.wsdl”, array(‘login’          => “some_name”,
‘password’       => “some_password”));

$client = new SoapClient(“some.wsdl”, array(‘proxy_host’     => “localhost”,
‘proxy_port’     => 8080));

$client = new SoapClient(“some.wsdl”, array(‘proxy_host’     => “localhost”,
‘proxy_port’     => 8080,
‘proxy_login’    => “some_name”,
‘proxy_password’ => “some_password”));

$client = new SoapClient(“some.wsdl”, array(‘local_cert’     => “cert_key.pem”));

$client = new SoapClient(null, array(‘location’ => “http://localhost/soap.php”,
‘uri’      => “http://test-uri/”));

$client = new SoapClient(null, array(‘location’ => “http://localhost/soap.php”,
‘uri’      => “http://test-uri/”,
‘style’    => SOAP_DOCUMENT,
‘use’      => SOAP_LITERAL));

$client = new SoapClient(“some.wsdl”,
array(‘compression’ => SOAP_COMPRESSION_ACCEPT | SOAP_COMPRESSION_GZIP));

$server = new SoapClient(“some.wsdl”, array(‘encoding’=>’ISO-8859-1’));

class MyBook {
public $title;
public $author;
}

$server = new SoapClient(“books.wsdl”, array(‘classmap’ => array(‘book’ => “MyBook”)));

?>

1
<code> 

.

Fedora 14: Wireless network is not working

Wireless Network is not working in Fedora 14 by default, here is the solution
su –
yum install b43-fwcutter wget

Next, download the Windows driver:
wget downloads.openwrt.org/sources/broadcom-wl-4.150.10.5.tar.bz2

Next decompress, extract and copy firmware to /lib/firmware directory.

tar xjf broadcom-wl-4.150.10.5.tar.bz2
cd broadcom-wl-4.150.10.5/driver
b43-fwcutter -w /lib/firmware/ wl_apsta_mimo.o .

REST Web Service & SOAP

Short Comparison with REST and SOAP

What is a REST Web Service

The acronym REST stands for Representational State Transfer, this basically means that each unique URL is a representation of some object. You can get the contents of that object using an HTTP GET, to delete it, you then might use a POST, PUT, or DELETE to modify the object (in practice most of the services use a POST for this).

Who’s using REST?

All of Yahoo’s web services use REST, including Flickr, del.icio.us API uses it, pubsub, bloglines, technorati, and both eBay, and Amazon have web services for both REST and SOAP.

Who’s using SOAP?

Google seams to be consistent in implementing their web services to use SOAP, with the exception of Blogger, which uses XML-RPC. You will find SOAP web services in lots of enterprise software as well.

REST vs SOAP

As you may have noticed the companies I mentioned that are using REST api’s haven’t been around for very long, and their apis came out this year mostly. So REST is definitely the trendy way to create a web service, if creating web services could ever be trendy (lets face it you use soap to wash, and you rest when your tired). The main advantages of REST web services are:

  • Lightweight – not a lot of extra xml markup
  • Human Readable Results
  • Easy to build – no toolkits required

SOAP also has some advantages:

  • Easy to consume – sometimes
  • Rigid – type checking, adheres to a contract
  • Development tools

.

MySQL Slow Query Log

Start MySQL Server:

/etc/init.d/mysqld start

Syntax for /etc/my.cnf in Red Hat Family Machine :

[mysqld]
set-variable=long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes

  • The 2nd line under [mysqld] turns on slow query log and logs all slow queries to log-slow-queries.log in the MySQL data directory.
  • The 1st line indicates that any queries that took more than 1 seconds to execute need to be logged.
  • The last line tells MySQL to log *any* queries that do not use indexes regardless of the setting in the second line.

You must create the file manually and change owners this way:

mkdir /var/log/mysql
touch /var/log/mysql/log-slow-queries.log
chown mysql.mysql -R /var/log/mysql

mysqldumpslow

http://dev.mysql.com/doc/refman/5.1/en/mysqldumpslow.html

shell> mysqldumpslow

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log Count: 1 Time=4.32s (4s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into t2 select * from t1

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into t2 select * from t1 limit N

Count: 3 Time=2.13s (6s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost insert into t1 select * from t1.