Welcome to my blog. I write Instruction Guides, How-Tos, and Tips & Tricks on Linux, Database, Hardware, Security and Web. Visit Best of the Blog section to get started.

Starting from MySQL 5, when you execute show databases command, information_schema will be listed in the database list. information_schema is a virtual MySQL database that stores metadata information of all the MySQL databases.

Typically you will execute either DESCRIBE or SHOW commands to get the metadata information. However using the information_schema views, you can execute the standard select SQL command to get the metadata information. In this article, let us review few practical examples on how to use the information_schema database.
[Read more →]

Bugzilla, an open source bug tracking system uses MySQL for the database. When you try to attach a document to the bug that is greater than 1MB in size, you will get the error message shown below. This article explains how to fix this problem by changing the MySQL max_allowed_packet database parameter and bugzilla maxattachmentsize parameter.
 
When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection.
 
MySQL client’s default max_allowed_packet value is 16MB. MySQL server’s default max_allowed_packet is 1MB. For bugzilla attachment, we need to increase the max_allowed_packet size of the MySQL server.
[Read more →]

Uploading several rows of data from a text, csv, and excel file into a MySQL table is a routine task for sysadmins and DBAs who are managing MySQL database. This article explains 4 practical examples on how to import data from a file to MySQL table using both mysqlimport and “load data local infile” method. Instead of importing data, if you want to backup and restore MySQL database, please use mysqldump or mysqlhotcopy.
[Read more →]

mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database,  execute the *.sql file on destination database.  For MyISAM, use mysqlhotcopy method that we explained earlier, as it is faster for MyISAM tables.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore.
[Read more →]

Previously we discussed about how to install Apache and PHP from source. Installing LAMP stack from source will give you full control to configure different parameters.

Installing LAMP stack using yum is very easy and takes only minutes. This is a good option for beginners who don’t feel comfortable installing from source. Also, Installing LAMP stack using yum is a good choice, if you want to keep things simple and just use the default configuration.
[Read more →]

MyISAM is the default storage engine for MySQL database. MyISAM table gets corrupted very easily. In this article, I’ll explain how to use myisamchk to identify and fix table corruption in MyISAM. When a table is created under MySQL, it creates three different files: *.frm file to store table format, *.MYD (MyData) file to store the data, and *.MYI (MyIndex) to store the index. I prefer to use InnoDB as the storage engine for bigger database, as it resembles Oracle and provides commit, rollback options.
[Read more →]

Most of the open source application uses MySQL database. To debug an issue with the open source application, it is very important to understand how to quickly get high level information about your MySQL database. In this article, I have explained how you can view the database, table, column and index information on any MySQL database using 9 examples.
[Read more →]

All Linux distributions comes with PHP. However, it is recommended to download latest PHP source code, compile and install on Linux. This will make it easier to upgrade PHP on an ongoing basis immediately after a new patch or release is available for download from PHP. This article explains how to install PHP5 from source on Linux.
[Read more →]

mysqlhotcopy is a perl script that comes with MySQL installation. This locks the table, flush the table and then performs a copy of the database. You can also use the mysqlhotcopy to automatically copy the backup directly to another server using scp
[Read more →]

Most of the Linux distro comes with MySQL.  If you want use MySQL, my recommendation is that you download the latest version of MySQL and install it yourself. Later you can upgrade it to the latest version when it becomes available. In this article, I will explain how to install the latest free community edition of MySQL on Linux platform.

1. Download the latest stable relase of MySQL

Download mySQL from mysql.com .  Please download the community edition of MySQL for your appropriate Linux platform. I downloaded the “Red Hat Enterprise Linux 5 RPM (x86)”. Make sure to download MySQL Server, Client and “Headers and libraries” from the download page.
[Read more →]