Admin Linux ... MySQL DBA

You have a huge mysql table - maybe 100 GB. And you need to run alter on it - to either add an index, drop an index, add a column or drop a column. If you run the simple mysql "alter table" command, you will end up spending ages to bring the table back into production. Here is a simple hack to get the thing done. The benefit of the hack is that the alter runs quite fast. But since this is a hack, you will need to take care of the backups - in case anything goes wrong. I have seen this hack work effectively with both MyISAM and InnoDB tables. Here I have created a simple table to show this hack process. You can assume that this table has billions of rows and is more than 100GB in size. CREATE TABLE `testhack` ( `id` int(11) NOT NULL DEFAULT '0', `unq` varchar(100) DEFAULT NULL, `keyword` varchar(250) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq` (`unq`) ) ENGINE=MyISAM I need to drop the unique key. So, i have create a new table 'testhack_new' with the fol

The RPM packages usages and details: Ø MySQL-server-VERSION.glibc23.i386.rpm - The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine. Ø MySQL-client-VERSION.glibc23.i386.rpm - The standard MySQL client programs. You probably always want to install this package. Ø MySQL-devel-VERSION.glibc23.i386.rpm - The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules. Ø MySQL-debuginfo-VERSION.glibc23.i386.rpm - This package contains debugging information. debuginfo RPMs are never needed to use MySQL software; this is true both for the server and for client programs. However, they contain additional information that might be needed by a debugger to analyze a crash. Ø MySQL-shared-VERSION.glibc23.i386.rpm - This package contains the shared libraries (libmysqlclient.so*) that certain languages and applications need to dynamically load and use MySQL. It contains single-threaded an

Security Issues with MySQL ROOT Access MySQL offers simple but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks. In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements: * MySQL processes must run under a unique UID/GID that is not used by any other system process. * Only local access to MySQL need to be allowed.(some exceptions for jobs/backups) * MySQL root's account must be protected by a complex/hard to guess password. * The administrator's account (root) need to be renamed. * Anonymous access to the database (by using the nobody account) must be disabled. MySQL Security risks can be categorized into the following. * Filesystem security risks. MySQL Install

MySQL Development History - MySQL was first released internally on 23 May 1995 - Windows version was released on January 8, 1998 for Windows 95 and NT - Version 3.23: beta from June 2000, production release January 2001 - Version 4.0: beta from August 2002, production release March 2003 - Version 4.1: beta from June 2004, production release October 2004 - Version 5.0: beta from March 2005, production release October 2005 - Version 5.1: currently pre-production (since November 2005) - Sun Microsystems acquires MySQL AB on 26 February 2008 MySQL Features History - Version 3.23.23 Full-Text Search - Version 4.0 Full-Text Search (IN BOOLEAN MODE), UNIONS - Version 4.1 R-Tree and B-Tree, Sub-Queries, Prepared Statements - Version 5.0 Cursors, Stored Procedures, Triggers, Views, XA Transactions - Version 5.1 Event Scheduler, Partitioning, Plugin API, Row-Based Replication, Server Log Tables

In Unix/Linux world everything is treated as files. whether it is a devices, directories and sockets — all of these are files. Stat command displays file or file system status. [Prabhat@Server1 Archive]$ stat 1_16470_587807474.arc File: `1_16470_587807474.arc' Size: 208514560 Blocks: 407664 IO Block: 4096 regular file Device: fd02h/64770d Inode: 17006596 Links: 1 Access: (0640/-rw-r-----) Uid: ( 500/ oracle) Gid: ( 500/ dba) Access: 2010-06-12 23:28:58.000000000 -0700 Modify: 2010-06-12 23:31:22.000000000 -0700 Change: 2010-06-12 23:31:22.000000000 -0700 Details of Linux Stat Command Output * File: `1_16470_587807474.arc’ – Name of the file. * Size: 208514560 – File size in bytes. * Blocks : 407664 – Total number of blocks used by this file. * IO Block : 4096 – IO block size for this file. * regular file – Indicates the file type. This indicates that this is a regular file. Following are available file types. o regular file. ( ex: all normal fi

Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display). Table 'example' have following data. SELECT * FROM `example` ORDER BY `example`.`age` ASC LIMIT 0 , 30 id name age 3 Amit 26 5 pani 27 2 Sanjay 28 4 Lucky 29 6 atul 30 1 Kumar 31 SELECT * FROM `example` ORDER BY `example`.`age` ASC LIMIT 3 , 1 id name age 4 Lucky 29 This will show records 3rd only ( remember the first record is 0 )

Allocating memory for caches in MySQL If the server is dedicated to MySQL, any memory you don’t reserve for the operating system or for query processing is available for caches. MySQL needs more memory for caches than anything else. It uses caches to avoid disk access, which is orders of magnitude slower than accessing data in memory. The operating system may cache some data on MySQL’s behalf (especially for MyISAM),but MySQL needs lots of memory for itself too. The following are the most important caches to consider for the majority of installations: • The operating system caches for MyISAM data • MyISAM key caches • The InnoDB buffer pool • The query cache There are other caches, but they generally don’t use much memory. It is much easier to tune a server if you’re using only one storage engine. If you’re using only MyISAM tables, you can disable InnoDB completely, and if you’re using only InnoDB, you need to allocate only minimal resources for MyISAM (MySQL uses MyISAM tables intern

If your database is MySQL, though, there's still hope. Over the course of this article, I will show you how to use MySQL's built-in crash recovery tools to check your database, and hopefully recover all the data you just lost. Built-in tools When it comes to repairing and checking tables, MySQL offers two options: myisamchk designed specifically to check and repair MyISAM tables (the default table type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair corrupted tables. The myisamchk tool is invoked from the command line. MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE , REPAIR TABLE , and OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client. Most of the time, it's preferable to use myisamchk because it is significantly faster than using SQL commands. Checking tables If you're having trouble

Here is a way to calculate Query Executed per second : time=10 begin=`mysql -e "show status" | awk '{if ($1 == "Questions") print $2}'` sleep $time end=`mysql -e "show status" | awk '{if ($1 == "Questions") print $2}'` diff=`expr $end - $begin` avg=`expr $diff / $time` echo "$avg"

Over the years, we all have habit of using CTRL+S every few minutes during working on a document, because we all had too much work lost from stupid errors; In the Windows world, CTRL+S is used as the Save you work. But, this habit will be a problem on working in the Linux world. By accident while inside a terminal window (in PUTTY) we press CTRL+S, this accidental keystroke meant we had reconnect to my Linux server, kill whatever program we were running, and then start it again. But here is solution : CTRL+S actually does XOFF, which means the terminal will accept key strokes but won’t show the output of anything. It will appear as if your terminal is dead when it’s really just waiting to be turned back on. The fix? Simply press CTRL+Q to turn flow-control on (XON) . If you pressed a whole bunch of keys before pressing CTRL+Q, you’ll see the output from those keystrokes.

There is simple command to check for the overall usage of memory including physical memory and swap memory. What you is just type free and it will shows you the information of the memory usage. for example: total used free shared buffers cached Mem: 16442272 16355460 86812 0 129600 10120444 -/+ buffers/cache: 6105416 10336856 Swap: 10223608 263608 9960000 The value shows at the first line of total column, is the total physical memory I have in kilobytes. I have 16GB ram, the results (16442272kb) shown above is approximate in kb. So the system have already used almost all the physically memory, which just left about 84M as free memory. Out of 15.6G from 5.8G that stored in the system, is actually used. And it still left 9.8G in cache. I have total 10G of swap , and it uses 9.4GM and remain 9.4G unused. The memory usage seems high but still consider healthy. I have loaded a lots of applications and uses some m

Kontrollbase - MySQL monitoring tool. Past few days I am looking for a centralized MySQL database monitoring tool, Which will give complete details of MySQL database. I have looked various tools but, Kontrollbase is given me the complete solution. Kontrollbase is a monitoring, analytics, reporting, and historical analysis webapp for MySQL database administrators and advanced users of MySQL databases. Important links: software-kontrollbase screenshots demo U sername: demo , Password: password note: do not use IE, use Firefox or other browser. downloads Documentation

Various time we required system (on Windows 2000, XP, 2003/8, Vista and Windows 7) details like hardware (motherboard, CPU , RAM , etc), installed software operating system’s security hotfixes and lots and lots of more. One such solution is LookInMyPC – a free program that automatically generates a detailed audit report of the hardware and software on your computer. It records essential information such as, operating system and processor details, the amount of RAM installed, drive specifications, and much, much, more. sample report