Nix World

blog

Archive for July, 2010

InnoDB: The InnoDB memory heap is disabled

July 29th, 2010

I enabled the innodb_plugin-1.0.4 made the necessary changes into the my.cnf file to bring the innodb plugin into the effect . Restarted MySQL and got the log entry into the MySQL error log InnoDB: The InnoDB memory heap is disabled , on some checking I got in this page (http://www.innodb.com/doc/innodb_plugin-1.0-doc-single/innodb-plugin.html#innodb-performance-use_sys_malloc) that I need to set
The value of innodb_use_sys_malloc as 0. so I made this entry innodb_use_sys_malloc =0 into the my.cnf and restarted the MySQL and the error was gone , happy MySQling

Thanks
Pankaj Joshi

View Comments »

Posted in Linux, Mysql

Tags: ERROR innodb MEMORY HEAP Mysql

InnoDB: Fatal error: cannot allocate the memory for the buffer pool

July 29th, 2010

I got this error while I was trying to setup a MySQLServer as    Copy
of another server I did not check the memory available in the system
and I got the error
“InnoDB: Fatal error: cannot allocate the memory for the buffer pool”
I realized that the memory of the system is less then the  allocated
innodb_buffer_pool_size of 1 GB , I set it less then the   available
memory <1GB , restarted the MySQL and it was done , error gone .

Thanks
Pankaj

View Comments »

Posted in Mysql

Tags: BUFFER POOL innodb INNODB ERROR InnoDB: Fatal error: cannot allocate the memory for the buffer pool Mysql

Using ReiserFS and Linux

July 20th, 2010

Introduction

In this article, I’ll show you how to get ReiserFS running under a typical Linux distribution. I’ll also share lots of technical information on a variety of topics, such as performance considerations and more. Since I’ll be covering installation first, I recommend that you read this article in its entirety before following the installation instructions. That way, you’ll have all the technical notes in the back of your head as you start getting ReiserFS running on your systems, allowing you to make any necessary adjustments along the way.

The Search For A Good Kernel

To enable ReiserFS on your system, you’ll first need to find a suitable kernel. At the time this article was orginally written (way back in 2001,) finding a suitable kernel for running ReiserFS required some effort, as ReiserFS was still an experimental filesystem. However, these days ReiserFS is no longer considered to be experimental and it’s quite easy to find a kernel that supports ReiserFS. Unfortunately, Red Hat has removed ReiserFS support from the official Red Hat Enterprise Linux kernel, but it available in the stock Linux kernel sources, as well as various RHEL-based kernels.

If for some reason you are using a 2.4 kernel version, try to avoid early revisions of the kernel and use something that is fairly recent, like 2.4.30 or higher.

Are You Ready?

The first step in using ReiserFS is to make sure that your kernel has ReiserFS support available. To do this, type:

cat /proc/filesystems

If you see “reiserfs” listed in the resultant list, then reiserfs support is currently available. You’re ready to go!

Now, what happens if you don’t see ReiserFS support listed? Well, it’s possible that it’s actually there, but available as a kernel module and not yet loaded. To see if this is the case, first become the root user if you aren’t root already, and type:

# modprobe reiserfs

Was modprobe able to load the reiserfs module? Run the cat /proc/filesystems command again and see if reiserfs is in the list. If it is, great! If not, then it looks like you are going to need to either upgrade to a kernel with ReiserFS support or recompile your existing kernel, if you enjoy doing that kind of thing.

If you’re up for recompiling your own kernel, here’s how you’d go about enabling ReiserFS. First, type make menuconfig in your kernel source tree. Under the Code maturity level options section, make sure that the Prompt for development and/or incomplete code/drivers option is enabled if you are using a 2.4 kernel. Then, head over to the File systems section, and enable ReiserFS support. It’s recommended that you enable ReiserFS support to be compiled directly into your kernel (not as a module.) You may enable the extra ReiserFS capabilities if you wish. Now, save your settings, compile your kernel (make dep (for kernel 2.4 only); make bzImage; make modules; make modules_install) and configure your boot loader to load the new ReiserFS-enabled kernel.
Important:
It’s always a good idea to save your current kernel and configure your boot loader so that you can boot with your original kernel, just in case your new kernel doesn’t work.

Installing the Tools

Now, before you potentially reboot with a brand-new, ReiserFS-enabled kernel, we need to make sure that you have the “reiserfsprogs” tools installed. Most modern distributions will have these tools available. To determine if they are installed, run the following command as root:

# reiserfsck

Was it found? If so, great! This means that the reiserfsprogs are installed and available. If they aren’t available, I recommend using your distribution’s package manager to track down these tools and install them. Once the tools are installed, now would be a good time to reboot to enable a new ReiserFS-enabled kernel if you installed one.

Creating and Mounting the Filesystem

Once rebooted, you’ll be able to create a ReiserFS filesystem on an empty partition as follows:

# mkreiserfs /dev/hdxy

In the above example, /dev/hdxy should be a device node corresponding to a free partition. Mount it as you would any other filesystem:

# mkdir /mnt/reiser
# mount /dev/hdxy /mnt/reiser

And, if you’d like to add a ReiserFS filesystem to your /etc/fstab file, simply set the freq and passno fields to 0, as follows:

/dev/hdc1 /home reiserfs defaults 0 0

From this point forward, your ReiserFS filesystems should act identically to their ext2 counterparts, except that you’ll no longer need to worry about long fsck times, and overall performance will be much better — especially for small files.

ReiserFS Technical Notes

Filesystem Stability

With a modern kernel, ReiserFS is generally just as reliable as ext2 or ext3. As with all filesystems, a spattering of older kernels had some filesystem corruption bugs in them and should be avoided. However, if you are relying on your Linux distribution’s selection of kernels, then you should not experience any issues with ReiserFS.

No dump/restore

Yes, it’s true; ReiserFS does not yet have a dump and restore implementation. If you want to user ReiserFS and happen to be a dump fan, you’ll have to find some alternate way of backing data. In reality, this turns out to be a non-issue, since 2.4+ kernels are incompatible with dump and restore in the first place. For more information on the dump/kernel incompatibility, read the LKML posting by Linus Torvalds where he says that “Dump was a stupid program in the first place. Leave it behind.”

Performance Issues

While ReiserFS generally blows the socks off ext2, ReiserFS does have a few special-case performance weaknesses. The first is sparse file performance. ReiserFS sparse file performance will be significantly worse than ext2. This will change at some point, when the Namesys developers get around to optimizing that part of ReiserFS for ReiserFS 4. Until then, ext2 is a better solution for applications that place heavy demands on sparse (small) files.

Performance Tweaks

Fortunately, there are a couple of easy general performance tweaks you can use to make these problems less severe. The first is to mount your ReiserFS filesystem with the noatime mount option (a mount option that’s available for other filesystems as well as ReiserFS). As you probably know, UNIX systems record an atime, or access time, for each object on the filesystem that gets updated every time a file is read. For most people, the atime stamp isn’t very useful and hardly any applications (none I can think of) rely on the atime for any critical task. For this reason, it can usually be safely turned off, which gives a nice all-around performance boost. Generally, unless you specifically know that you need atime support, you should be mounting your filesystems with the noatime option. Use an /etc/fstab entry like this:

/dev/hdc1 /home reiserfs noatime 0 0

In first ReiserFS article, I mentioned that ReiserFS has a special feature called “tail packing”. In ReiserFS lingo, “tails” are files that are smaller than a filesystem block (4k) or the trailing portions of files that don’t quite fill up a complete filesystem block. ReiserFS has really excellent small-file performance because it is able to incorporate these tails into its b*tree (its primary organizational data structure) so that they are really close to the stat-data (ReiserFS’ equivalent of an i-node). However, since tails don’t fill up a complete block, they can waste a lot of disk space (relatively speaking, of course). To solve this problem, ReiserFS uses its “tail packing” functionality to squish tails into as small a space as possible. Generally, this allows a ReiserFS filesystem to hold around 5% more than an equivalent ext2 filesystem.

Tail Packing Pros and Cons

However, tail packing also has its disadvantages. For one, it does give you a small but significant performance hit. Fortunately, the ReiserFS guys anticipated that some people would be willing to sacrifice around 5% of their disk capacity for a little extra performance, so they created the notail mount option. When a filesystem is mounted with this option, tail packing will be turned off, giving you greater speed and less storage capacity. In general, filesystem performance freaks mount their filesystems with both notail and noatime enabled, producing a noticeable performance improvement:

/dev/hdc1 /home reiserfs noatime,notail 0 0

Even if you want to save some disk space, there are times when temporarily mounting your filesystem with the notail option can be a good thing. In particular, some older boot-loaders have problems loading kernels that were created on a ReiserFS filesystem with tail packing enabled. If you’re using a LILO earlier than version 21.6, you’ll have this problem. You will also have problems with old versions of GRUB, which will not be able to load its stage1 and stage1_5 files. If you’re already experiencing this problem, you can fix it by mounting the filesystem with the notail option, moving the files to another filesystem, and then moving them back. When they’re recreated, they won’t have tails. Also, remember that you can easily remount a filesystem (with new options) without unmounting it. This particular example remounts the root filesystem with the notail option:

# mount / -o remount,notail

Conclusion

I’ve found ReiserFS to be a truly incredible filesystem, offering oodles of small file performance and great (normally better than ext2) regular file performance. For example, updates to source code repositories using cvs and ReiserFS have been observed to complete in only fifteen seconds, where the same source code tree takes about two minutes to update with ext2. ReiserFS makes many peoples’ lives more pleasant, and allows servers to handle large amounts of simultaneous IO without thrashing hard drives and negatively affecting interactive performance.

In future articles, I hope to take a look at Reiser4, a completely new (and incompatible) version of ReiserFS which sports some interesting new capabilities.

Repairing ReiserFS file system with reiserfsck

The idea and commands in this article submitted by Jacques Wagener via email. In his own words, “After nuking my partition by accident (and through my stupidity) I was really disappointed in myself, especially in losing my bookmarks and rss-feeds”. The following article is based upon our email communication. I am just putting them as an article.

We have already written about ext2/ext3 file repair using fsck and other utilities. Linux comes with different filesystems and different repair utilities. To repair a ReiserFS filesystem you need to run reiserfsck command, which is a checking tool for the ReiserFS filesystem (just like fsck command for ext2/ext3 file system).

Reiserfsck searches for a Reiserfs filesystem on a device, replays any necessary transactions, and either checks or repairs the file system. ReiserFS saves data or log in a special file for pending disk updates and later on it commit updates to disk resulting into very good filesystem consistency.

Step # 1: Install reiserfsck

You need to install reiserfsprogs package, which includes reiserfsck user level tools for ReiserFS filesystems.
# apt-get install reiserfsprogs

Or if you are using RedHat/Fedor Core Linux:
# yum install reiserfsprogs

Step # 2: Backup partition/disk

Take system down to runlevel 1.
# init 1

Unmount /dev/sda1 (if it is still mounted as read/write only):
# umount /dev/sda1; umount /dev/sda2

Before using any one of the following command you are strongly advised to make a backup copy of the whole partition using dd or ddrescue (recommended) command.
# ddrescue /dev/sda /dev/sdb

OR use dd command:
# dd if=/dev/sda of=/dev/sdb conv=noerror
Step # 3: Check filesystem consistency

Above command makes a backup of your drive. Next check filesystem consistency which will reports problem to you with the following command (assuming that /dev/sda1 is your partition):
# reiserfsck –check /dev/sda1

If you get an error Bad root block 0If you get following message:
Running with –rebuild-tree is required

Then you need to run following command rebuilds the entire filesystem tree using leaf nodes found on the device (this is nothing but indication of corrupted :
# reiserfsck –scan-whole-partition –rebuild-tree /dev/sda1

Where,

* –scan-whole-partition: This option causes –rebuild-tree to scan the whole partition but not only the used space on the partition. You should always use this option with –rebuild-tree.
* –check: Checks filesystem consistency and reports, but does not repair any corruption that it finds.
* –rebuild-tree: This option rebuilds the entire filesystem tree using leaf nodes found on the device. Once you passed this option, do not stop or interrupt building operating.

Recovering corrupted superblock

Each file system has a superblock, which contains information about file system such as:

* File system type
* Size
* Status
* Information about other metadata structures

If this information lost, you are in trouble (data loss) so Linux maintains multiple redundant copies of the superblock in every file system.

During check (reiserfsck –check /dev/sda1) if you get an error superblock was missing, use following command to fix superblock:
# reiserfsck –rebuild-sb /dev/sda1

Where,

* –rebuild-sb: This option recovers the superblock on a Reiserfs partition. Normally you only need this option if mount reports “read_super_block: can’t find a reiserfs file system”.

Caution: Do not run above command twice on same drive. You will damage your partition (data).
Final note

Next logical step is mount your partition /dev/sda1 and check for your data:
# mkdir -p /mnt/data
# mount /dev/sda1 /mnt/data
# cd /mnt/data
# ls
# ls lost+found/ -l

lost+found is a special directory where recovered files are kept by Linux/reiserfsck. You can examine these files and restore the data.

Better backup entire partition using tar or ssh session:
# tar cvf /dev/nst0 /mnt/data

OR use scp to dump data to remote system:
# scp -r /mnt/data you@myserver.com:/backup

Thanks
Manoj Chauhan

View Comments »

Posted in CentOS, Linux, Monitoring Tools

Tags: Disk tunning in linux performace in linux ReiserFS and Linux Using ReiserFS Using ReiserFS and Linux what ReiserFS

Accessing Remote Tables Data in MySQL

July 16th, 2010

We sometime need to access the data (tables ) which lies on another remote MySQL DB Server , MySQL provides an engine type federated which does exactly what we want .

The FEDERATED storage engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology. When using a FEDERATED table, queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables.

federated table in action

check if the federated engine is enabled

Mysql > show engines;

+————+———+—————————————————————-+
| Engine | Support | Comment |
+————+———+—————————————————————-+

if it is not enabled go to /etc/my.cnf add federated=ON int the [mysqld] section and restart the MySQL .

Creating original table on remote server

CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT ”,
other INT(20) NOT NULL DEFAULT ’0′,
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

Create table on the local server

Two methods

1) Creating a FEDERATED Table Using CONNECTION

CREATE TABLE test_table(
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT ”,
other INT(20) NOT NULL DEFAULT ’0′,
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION=’mysql://fed_user@remote_host:9306/federated/test_table’;

more details can be checked on http://dev.mysql.com/doc/refman/5.1/en/federated-create-connection.html

2)Creating a FEDERATED Table Using CREATE SERVER

For example, to create a server connection identical to the CONNECTION string:

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql

This server name is used while creating the federated table in CONNECTION

CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT ”,
other INT(20) NOT NULL DEFAULT ’0′,
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION=’fedlink/test_table’;

more details can be checked at http://dev.mysql.com/doc/refman/5.1/en/federated-create-server.html

Access the data of the federated table on local server

mysql>use test ;

mysql>select * from test_table ;

federate table acts as a local table and all the select and DML operations can be done as if the table is lying on the local server ,
please note that no actual data is stored on the local sever.

now we can have the cross MySQL server data access

mysql > select * from localtable join test_table on localtable.id =test_table.id ;

Important things to remember

• FEDERATED tables may be replicated to other slaves, but you must ensure that the slave servers are able to use the user/password combination that is defined in the CONNECTION string (or the row in the mysql.servers table) to connect to the remote server.
• The remote server must be a MySQL server.
• The remote table that a FEDERATED table points to must exist before you try to access the table through the FEDERATED table.
• The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, and indexes. It does not support ALTER TABLE, or any Data Definition Language statements that directly affect the structure of the table, other than DROP TABLE. The current implementation does not use prepared statements. Transactions are not supported.
• There is no way for the FEDERATED engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database system. The integrity of the data in the local table could be breached if there was any change to the remote database.
• Any DROP TABLE statement issued against a FEDERATED table drops only the local table, not the remote table.
• FEDERATED tables do not work with the query cache.

Thanks
Pankaj Joshi

View Comments »

Posted in Linux, Mysql

Tags: Accessing Remote Tables Accessing Remote Tables Data in MySQL DML operations FEDERATED storage engine FEDERATED table remote MySQL DB Server

htaccess to stop files listing & executing any file inside folder

July 13th, 2010

# htaccess to stop files listing on folder and stop executing any file inside folder like config.ini file etc which are not need to display in the browser

IndexIgnore */* (It will stop files listing for specific folder)

# deny access to all files of folder

Order allow,deny
Deny from all
Satisfy All

Forbidding a particular file:

You can forbid a particular file using its name and extension.

order allow,deny
deny from all

View Comments »

Posted in Apache, PHP

Tags: htaccess htaccess to stop executing any file inside folder htaccess to stop files listing

How to find per-process I/O statistics on Linux

July 13th, 2010

How to find per-process I/O statistics on Linux:

Need to monitor Linux server performance? Try these built-in command and a few add-on tools. Most Linux distributions are equipped with tons of monitoring. These tools provide metrics which can be used to get information about system activities. You can use these tools to find the possible causes of a performance problem. The commands discussed below are some of the most basic commands when it comes to system analysis and debugging server issues such as:

1. Finding out bottlenecks.
2. Disk (storage) bottlenecks.
3. CPU and memory bottlenecks.
4. Network bottlenecks.

Newer Linux kernels have per-process I/O accounting and you can use the iotop tool to find out what’s performing I/O, but in many cases I’m trying to find the source of an I/O problem in an older kernel. I found sort of a hack-ish way to do that today, while trying to figure out why a system was basically unresponsive.

I found a post on Stack Overflow that showed a way you can get per process I/O statistics from the kernel even in older kernels. I adapted this to my needs, and wrote a little script.

Here’s how you use it. First, get it:

wget http://aspersa.googlecode.com/svn/trunk/iodump

Then turn on kernel messages about I/O:
echo 1 > /proc/sys/vm/block_dump

This makes the kernel start writing messages about every I/O operation that takes place. Now all you have to do is get those messages and feed them into my script:

while true; do sleep 1; dmesg -c; done | perl iodump

Wait a little while, then cancel the script. The results should look something like the following:

[root@manoj]# while true; do sleep 1; dmesg -c; done | perl iodump

Please wait for some time to get the stats from the dmesg and after 4 to 5 minute, please press Ctrl+c to abort the recording and below stats will come to screen.

# Caught SIGINT.
TASK                   PID      TOTAL       READ      WRITE      DIRTY DEVICES
kjournald              442       2849          0       2849          0 dm-0
nfsd                 29036       2731        217       2514          0 dm-0
nfsd                 29041       2495        111       2384          0 dm-0
nfsd                 29037       2120         74       2046          0 dm-0
nfsd                 29039       1996         94       1902          0 dm-0
nfsd                 29038       1380        110       1270          0 dm-0
nfsd                 29040        727        109        618          0 dm-0
pdflush              29423        234          0        234          0 dm-0
nfsd                 29042         89         89          0          0 dm-0
nfsd                 29035         87         87          0          0 dm-0
bash                 19067          2          2          0          0 dm-0
perl                 19066          2          2          0          0 dm-0
bash                 19068          1          1          0          0 dm-0
httpd                16476          1          1          0          0 dm-0

I deliberately generated a bunch of I/O by deleting my Firefox history and cache. If i see the above stats, it shows that NFSD (NFS Deamon) is doing lot’s of I/O and write operation is very -2 high.

Why not write a single script to do everything required, something like:

We can use below shell script to get the same stats, first it will turn on kernel messages about I/O after stats it will turn off kernel messages about I/O, as it is not good practice to keep kernel messages about I/O on.

#!/bin/sh
dmesg -c
/etc/init.d/klogd stop
echo 1 > /proc/sys/vm/block_dump

# allow 30 seconds of stats to be logged
sleep 30

dmesg -c | perl iodump

echo 0 > /proc/sys/vm/block_dump
/etc/init.d/klogd start

Thanks
Manoj

View Comments »

Posted in CentOS, Linux, Monitoring Tools

Tags: find per-process I/O statistics find per-process I/O statistics on Linux I/O statistics on Linux per-process I/O accounting per-process I/O statistics

Monitoring DML Stats per table in MySQL

July 12th, 2010

Most of time as an MySQL admin we need to keep track of the DML statements per table , If we are not using any specific tool for doing this
We can use the shell script for doing this which I came across the idea is to use the binlog to get all of the DML.

The script which we can use is mysqlbinlog options to filter the specific data based on time etc..

The output of the command would be

33389 update e_acc
17680 insert into r_b
17680 insert into e_rec
14332 insert into rcv_c
13543 update e_rec
10805 update loc
3339 insert into r_att
2781 insert into o_att

Thanks

Pankaj Joshi

Most of time as an MySQL admin we need to keep track of the DML statements per table , If we are not using any specific tool for doing this

We can use the shell script for doing this which I came across the idea is to use the binlog to get all of the DML.

The script which we can use is mysqlbinlog options to filter the specific data based on time etc..

The output of the command would be

  33389 update e_acc

  17680 insert into r_b

  17680 insert into e_rec

  14332 insert into rcv_c

  13543 update e_rec

  10805 update loc

   3339 insert into r_att

   2781 insert into o_att

View Comments »

Posted in Mysql

Tags: DML Monitoring Mysql query stats stats

MySQL Replication Monitoring

July 8th, 2010

MySQL Replication is widely and we need to have strong replication monitoring for these things

1) To monitor if the replication is working.

2) To check the latency between the master and slave .

3) TO check the consistency between the master and slave , as sometime due to manual or master server crash , master and slave may go out of sync.

lets see how we can set up monitoring for each of the scenarios.

MySQL replication status on the can be checked via

mysql> show slave status \G;

output would be like

Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000542
Read_Master_Log_Pos: 231260599
Relay_Log_File: relaylog.000496
Relay_Log_Pos: 231260744
Relay_Master_Log_File: mysql-bin.000542
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: 231260599
Relay_Log_Space: 231260935
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: 2013
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

more details on this can be found onhttp://dev.mysql.com/doc/refman/5.1/en/show-slave-status.html

Case 1) MySQL Replication is working
As we know that MySQL Replication uses two threads Slave_IO and Slave_SQL , slave_IO is responsible for reading the master binary log and writing events from master to the relay log on the slave and slave_SQL is responsible for executing the events from the relay log on the slave .so for MySQL Replication to work both the threads must be running , to check that from the show slave status \G the following values must be Slave_IO_Running: Yes and Slave_SQL_Running: Yes

we can check the variables using the shell or script or Nagios to monitor.

Case 2) To check the latency between the master and slave

MySQL Replication is real time most of the time , but because of many reasons( Slave heavily loaded , IO issue on slave) slave may not be able to catch up with the master.

I have seen many people using the “Seconds_Behind_Master” from the output of the show slave status , it works most of the time but not always .That value is comparing the slave’s current time with the time on the master when it executed the currently replicating SQL statement. If the slave is low-volume (or not properly slaving) you can end up with misleading information or even a false sense of security.Consider another case when you have more level of replication example A is master server B is slave of A and Server C which is slave of B , if there is some issue in replication between A and B , the “Seconds_Behind_Master” on C will still be showing as 0 but in actual the replication is broken in the sense that C is not getting the latest data from A.

The correct way of monitoring the replication is using

http://www.maatkit.org/doc/mk-heartbeat.html

how to use the mk-heartbeat

on master download the script from
wget http://www.maatkit.org/get/mk-heartbeat

make it executable

on master(192.168.2.80)
create table heartbeat on Database heart

CREATE TABLE heartbeat (
id int NOT NULL PRIMARY KEY,
ts datetime NOT NULL
);

It needs to have at least one row

INSERT INTO heartbeat (id) VALUES (1);
now run the script and make it a daemon

./mk-heartbeat -D heart –table heartbeat -u heartbeat -p XXXXXXXXX –update -h 192.168.2.80

on slave(192.168.2.82) download the script from
wget http://www.maatkit.org/get/mk-heartbeat
make it executable

./mk-heartbeat -D heart –table heartbeat -u heartbeat_slave -p XXXXXXXXX –monitor -h 192.168.2.82

the output would be something like

1s [ 0.02s, 0.00s, 0.00s ]
1s [ 0.03s, 0.01s, 0.00s ]
1s [ 0.05s, 0.01s, 0.00s ]
0s [ 0.05s, 0.01s, 0.00s ]
0s [ 0.05s, 0.01s, 0.00s ]
0s [ 0.05s, 0.01s, 0.00s ]
0s [ 0.05s, 0.01s, 0.00s ]
1s [ 0.07s, 0.01s, 0.00s ]

the output will tell if mysql replication is having any lag or not.

case 3) Inconsistency between Mater and Slave

Some time because of manual error or master crash or unclean shutdown the master and slave may be out of sync, its very important to check that and take corrective action .
if not detected on time slave might go completely out of sync and might need to be set completely.

how do we check that master and slave are completely sync.

we can gr8 tool mk-table-checksum from maatkit http://www.maatkit.org/doc/mk-table-checksum.html

lets see how we can use the mk-table-checksum

download
wget http://www.maatkit.org/get/mk-table-checksum
make it executable

create table checksum on the master 192.168.2.10

on database Test

CREATE TABLE checksum (
db         char(64)     NOT NULL,
tbl        char(64)     NOT NULL,
chunk      int          NOT NULL,
boundaries char(100)    NOT NULL,
this_crc   char(40)     NOT NULL,
this_cnt   int          NOT NULL,
master_crc char(40)         NULL,
master_cnt int              NULL,
ts         timestamp    NOT NULL,
PRIMARY KEY (db, tbl, chunk)
);

for checking we will create a test table

create table testreplication( i int , b varchar(100))

we will insert a row

insert into testreplication
select 1,’name’

–replicate=test.checksum will replicate the checksum run on master(192.168.2.10) to slave (192.168.2.12)
–tables test.testreplication will check only the table test.testreplication

./mk-table-checksum -u test -p XXXXXXX –replicate=test.checksum –tables test.testreplication 192.168.2.10

When we run it we get the output on command line

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
test testreplication 0 192.168.2.10 MyISAM 1 1e5504e6 0 NULL NULL NULL

we now log in to the slave (192.168.2.12) and on the database test we run this query

SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
AS crc_diff
FROM checksum
WHERE master_cnt <> this_cnt OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc);

out put will be blank as the replication is working properly

db tbl chunk cnt_diff crc_diff

now we remove the row directly from the slave (192.168.2.12)

delete from testreplication ;

we again run the

./mk-table-checksum -u test -p XXXXXXX –replicate=test.checksum –tables test.testreplication app146

output on command line

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
test testreplication 0 192.168.2.10 MyISAM 1 1e5504e6 0 NULL NULL NULL

when we run the query to check the result

SELECT db, tbl, chunk, this_cnt-master_cnt AS cnt_diff,
this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
AS crc_diff
FROM checksum
WHERE master_cnt <> this_cnt OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc);

now we get

db tbl chunk cnt_diff crc_diff
test testreplication 0 -1 1

as now the master and slave data differs , the output is giving table name and the crc_diff , one very important thing to understand that this finds the tables which are not in sync even when the new data is getting logged into the master DB as the checksum calculating command on the master is replicated exactly on the slave and we know that the mysql replication is syncrnous so on the slave when this command to calculate the checksum is executed its considering the data as it was on master.using this we can find out all the tables which are not in sync in master and slave and take corrective action.

in next post I would be writing in detail how to find the difference on the individual tables between master and slave and how to rectify that and how to automate that process .

Thanks
Pankaj Joshi

View Comments »

Posted in Monitoring, Mysql, Nagios

Tags: How to monitor mysql how to monitor mysql replication monitoring Mysql Mysql Monitoring MySQL Replication MySQL Replication Monitoring Mysql slave monitoring

PHP 5.3 ,MySql 5.1 on RHEL 4

July 6th, 2010

For those who are still using RHEL 4.x and in need of deploying PHP 5.3 & MySQL 5.1 , you can get the required rpm packages from

http://rpms.famillecollet.com/enterprise/4/

You can add the repo and update php and MySQL packages via yum, or download and install manually. I think the bare minimum will require these packages :

PHP

* php-cli-5.3.1-1.el4.remi
* php-5.3.1-1.el4.remi
* php-common-5.3.1-1.el4.remi
* php-mysql-5.3.1-1.el4.remi
* php-pdo-5.3.1-1.el4.remi

MySQL

* mysqlclient15-5.0.67-1.el4.remi
* mysql-server-5.1.42-1.el4.remi
* mysqlclient14-4.1.22-1.el4.remi
* mysql-libs-5.1.42-1.el4.remi
* mysql-5.1.42-1.el4.remi

Also you might need the packages that I listed below for php 5.3 & MySQL 5.1 to install correctly. Some of them are not available at Remi’s repo, so I upload them to my dropbox account in case somebody need them :

* perl-DBD-MySQL-4.006-1.el4.centos

http://go2.wordpress.com/?id=725X1342&site=outhereinthefield.wordpress.com&url=http%3A%2F%2Fdl.dropbox.com%2Fu%2F362618%2Fperl-DBD-MySQL-4.006-1.el4.centos.i386.rpm&sref=http%3A%2F%2Fouthereinthefield.wordpress.com%2Fcategory%2Fred-hat%2F

* libedit-2.11-1.20080712cvs.el4.remi
* sqlite2-2.8.17-2.el4.remi
* sqlite-3.3.6-2

http://go2.wordpress.com/?id=725X1342&site=outhereinthefield.wordpress.com&url=http%3A%2F%2Fdl.dropbox.com%2Fu%2F362618%2Fsqlite-3.3.6-2.i386.rpm&sref=http%3A%2F%2Fouthereinthefield.wordpress.com%2Fcategory%2Fred-hat%2F

Of course I think this will void your service warranty with Redhat. This is tested on RHEL 4.7

View Comments »

Posted in CentOS, Linux, Mysql, PHP

Tags: Mysql PHP RHEL 4

migrating/moving /usr and /var

July 6th, 2010

Space problem with /var and /usr , The server is running mysql .

My Plan is to move my /usr and /var to “root” (/). To do that I have to make sure that all security settings and symlink are preserved.

Prepare the new partitions
In my case, this is simply my “/” partitions, so what i really do is just create a new folders on “/”.

Backup the /usr and /var partitions
I will boot up the server in rescue mode using the installation CD, and use rsync to move them out of the way.
Create folders to backup the content of /usr and /var on the target partitions, in my case it the “/” partition.

#mkdir /var1
#mkdir /usr1

If you’re planning to relocate the folders to new partitions, you can label and mount them on these folders.

#e2label /dev/hda5 /var1
#e2label /dev/hda6 /usr1

#mount /dev/hda5 /var1
#mount /dev/hda6 /usr1

Backup your /etc/fstab file so that you can roll back your change easier.

#cd /etc
#cp -p fstab fstab.bk

Edit /etc/fstab so that /usr and /var partition are not mounted on the next reboot.
#vi /etc/fstab

Comment any line that refers to /var and /usr. This is how my fstab files looks like:

LABEL=/ / ext3 defaults 1 1
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
LABEL=/home /home ext3 defaults 1 2
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
LABEL=/tmp /tmp ext3 defaults 1 2
#LABEL=/usr /usr ext3 defaults 1 2
#LABEL=/var /var ext3 defaults 1 2
LABEL=SWAP-cciss/c0d0p swap swap defaults 0 0

Reboot your system using rescue mode with your install CD, or a live cd. On RHEL machine, press F5 on the first screen, and type “linux rescue” at the prompt. your installation will be mounted on /mnt/sysimage.

Get inside the installation by issuing

cd /mnt/sysimage

Copy the content of both folders to their respective backup target

#rsync -avurlp /var/ /var1
#rsync -avurlp /usr/ /usr1

The command above will copy your existing /usr and /var to their respective backup folder, while maintaining all the original attributes. Now we’re ready to replace both of the folders

Replacing the folders after making sure that /mnt/sysimage/usr and /mnt/sysimage/var are empty

As I mentioned above, in my case, I would like to move /var and /usr to root (/), so what I do is I just simply delete /mnt/sysimage/var and /mnt/sysimage/usr, and rename /mnt/sysimage/var1 and /mnt/sysimage/usr1 to /mnt/sysimage/var and /mnt/sysimage/usr. The
command should be like these:

#rm /mnt/sysimage/var
#rm /mnt/sysimage/usr
#mv /mnt/sysimage/var1 /mnt/sysimage/var
#mv /mnt/sysimage/usr1 /mnt/sysimage/usr

Since /var and /usr is now technically under “/”, all I have to do are making sure that /var and /usr are not mounted on a partitions and simply reboot my system. If you’re going to replace the partitions with new ones, you can open the fstab file, and edit them so that it will mount the new partitions as /var and /usr respectively. Open fstab by typing:

vi /mnt/sysimage/etc/fstab

Point /var and /usr to their new partition. Mine will look like this:

LABEL=/ / ext3 defaults 1 1
none /dev/pts devpts gid=5,mode=620 0 0
none /dev/shm tmpfs defaults 0 0
LABEL=/home /home ext3 defaults 1 2
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0
LABEL=/tmp /tmp ext3 defaults 1 2
LABEL=/usr1 /usr ext3 defaults 1 2
LABEL=/var1 /var ext3 defaults 1 2
LABEL=SWAP-cciss/c0d0p swap swap defaults 0 0

Also make sure on the new location , there may be little problem with permissions .

# chown -R mysql.mysql /var/lib/mysql
Some times when mysql does not start ,u can start mysql in safe mode, on next reboot it will be started normally.

Now, Save the changes, and reboot. The changes will be active the moment we reboot the system.

After all the steps above the installation should be using/var and /usr on the new area. I need keep the old partitions intact until I’m sure that everything is working properly. You can try running applications that is installed under /usr/bin to check if it’s ok.

If rollback is needed the, simply reboot the system into rescue mode again, and undo the changes from fstab by:

cd /mnt/sysimage/etc
cp fstab fstab.bk2
mv fstab.bk fstab

And reboot the system. /usr and /var should be mounted on the old partitions.

This method should work for different folders. You can also move /home and /tmp using the same steps, and it works flawlessly

View Comments »

Posted in CentOS, Linux

Tags: Space problem on Linux usr Var

Mysql Master Slave Replication

July 2nd, 2010

MySql replication scenario Solves

a.)One of the biggest advantages to have master-slave set up in MySQL is to be able to use master for all of the inserts and send some, if not all, select queries to slave. This will most probably speed up your application without having to diving into optimizing all the queries or buying more hardware. It will Offload some of the queries from one server to another and spread the load.

b) Do backups from slave: One of the advantages people overlook is that you can use MySQL slave to do backups from. That way site is not affected at all when doing backups. This becomes a big deal when your database has grown to multiple gigs and every time you do backups using mysqldump, site lags when table locks happen. For some sites, this could mean that site goes down for few secs to minutes. If you have slave, you just take slave out of rotation (should be built into code) and run backups off the slave. You can even stop slave MySQL instance and copy the var folder instead of doing mysqldump.

In this scenario i have used

Master server ip: 192.168.10.1
Slave server ip: 192.168.10.2
Slave username: pawan
Slave pw: pawan123
data directory is: /usr/local/mysql/var/

(192.168.10.2) master my.cnf file under [mysqld] section:

# changes made to do master
server-id = 1
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
log-bin = /usr/local/mysql/var/mysql-bin
# end master

(192.168.10.2) slave’s my.cnf
[mysqld] section:

# changes made to do slave
server-id = 2
relay-log = /usr/local/mysql/var/mysql-relay-bin
relay-log-index = /usr/local/mysql/var/mysql-relay-bin.index
log-error = /usr/local/mysql/var/mysql.err
master-info-file = /usr/local/mysql/var/mysql-master.info
relay-log-info-file = /usr/local/mysql/var/mysql-relay-log.info
datadir = /usr/local/mysql/var
# end slave setup

Create user on master:
mysql> grant replication slave on *.* to ‘pawan’@’10.0.0.2′ identified by ‘pawan123′;

Do a dump of data to move to slave
mysqldump -u root –all-databases –single-transaction –master-data=1 > masterdump.sql

import dump on slave
mysql CHANGE MASTER TO MASTER_HOST=’192.168.10.1′, MASTER_USER=’pawan’, MASTER_PASSWORD=’pawan123′;

Let us start the slave:
mysql> start slave;

You can check the status of the slave by typing
mysql> show slave status\G

The last row tells you how many seconds its behind the master. Don’t worry if it doesn’t say 0, the number should be going down over time until it catches up with master. If it shows NULL, it could be that slave is not started (you can start by typing: start slave) or it could be that it ran into an error (shows up in Last_errno: and Last_error under show slave status\G).