Convert Database ENGINE from InnoDB to MyISAM

We can use various methods to change database engine. In order to convert Database ENGINE from InnoDB to MyISAM use the following methods;

First you should take all the databases backup.

root@serv [~]#mysqldump DBname > DBBacupname.sql

Method : 1

Once you secure the Database you may create a file called DBlist.txt and add databases which you need to convert from InnoDB to MyISAM.

Create file and add your databases.

root@serv [~]#vi /root/DBlist.txt
Create file and add the following script.

root@serv [~]#vi


for db in `cat /root/DBlist.txt`; do
for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e’s/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/’|grep -v ‘Create Table’`
if [ $TABLE_TYPE = "InnoDB" ] ; then
mysqldump $db $table > $db.$table.sql


Save and Execute the script after changed permission to 755.

root@serv [~]#sh


Method : 2

Use the following php script to change database ENGINE from InnoDB to MyISAM


// connect your database here first

mysql_connect('host', 'user', 'pass');

$databases = mysql_query('SHOW databases');

while($db = mysql_fetch_array($databases)) {
echo "database => {$db[0]}\n";

$tables = mysql_query('SHOW tables');

while($tbl = mysql_fetch_array($tables)) {
echo "table => {$tbl[0]}\n";
mysql_query("ALTER TABLE {$tbl[0]} ENGINE=MyISAM");


How to change DATABASE engine to InnoDB?


You can use same above methods to INNODB instead of MyISAM.

Otherwise, You can change manually with below is a syntax to change storage engine to InnoDB.


How To: 5 Steps to Install phpMyAdmin on Linux

Do you have a MySQL database in your environment? Did you know that the easy (and most effective) way to manage MySQL database is using phpMyAdmin?

phpMyAdmin is a web-based tool written in PHP to manage the MySQL database. Apart from viewing the tables (and other db objects), you can perform lot of DBA functions through the web based interface. You can also execute any SQL query from the UI.

This article will provide step-by-step instructions on how to install and configure phpMyAdmin on Linux distributions.

1. phpMyAdmin Pre requisites

Make sure you have PHP 5 (or above) installed.

# php -v
PHP 5.3.2 (cli) (built: May 19 2010 03:43:49)

Make sure you have MySQL 5 (or above) installed.

# mysql -V
mysql  Ver 14.14 Distrib 5.1.47, for pc-linux-gnu (i686) using readline 5.1

Make sure Apache is installed and running.

PHP5 Modules

If you don’t have PHP, I recommend that you install PHP from source. Following is the configure command I executed while installing PHP from source. This includes all the required PHP modules for phpMyAdmin.

./configure --with-apxs2=/usr/local/apache2/bin/apxs --with-mysql --with-bz2 --with-zlib --enable-zip --enable-mbstring --with-mcrypt

If you don’t compile PHP5 with the above options, you’ll get following error message from phpMyAdmin web interface.

  • GZip – GZip compression and decompression requires functions (gzencode) which are unavailable on this system.
  • Bzip2 – Bzip2 compression and decompression requires functions (bzopen, bzcompress) which are unavailable on this system.
  • ZIP – Zip decompression requires functions (zip_open) which are unavailable on this system.
  • ZIP – Zip compression requires functions (gzcompress) which are unavailable on this system.

2. Download and Install phpmyadmin in Apache DocumentRoot

Identify your Apache’s DocumentRoot.

# grep DocumentRoot /usr/local/apache2/conf/httpd.conf
DocumentRoot /usr/local/apache2/htdocs

Download the latest version of phpMyAdmin. Currently the stable version of phpMyAdmin is 3.3.7

# cd /usr/local/apache2/htdocs

# tar xvfz phpMyAdmin-3.3.7-all-languages.tar.gz

# mv phpMyAdmin-3.3.7-all-languages phpmyadmin

3. Secure the phpmyadmin Directory – Create phpmy user

Create phpmy Unix user.

# adduser phpmy

# passwd phpmy

Check which user and group is used by Apache.

# egrep 'User|Group' /usr/local/apache2/conf/httpd.conf
User daemon
Group daemon

Modify the phpmyadmin directory’s user ownership to phpmy user.

# cd /usr/local/apache2/htdocs

# chown -R phpmy.daemon phpmyadmin/

4. Update phpMyAdmin from Wizard

You need to setup the file properly for phpMyAdmin to work. You can either create and edit this file manually, or use the config phpMyAdmin installer setup wizard. I recommend that you use the setup wizard, as it is very straight forward. To do that, you should create the following dummy with proper permission.

# cd /usr/local/apache2/htdocs/phpmyadmin/

# mkdir config

# chmod o+rw config

# cp config/

# chmod o+w config/

Now, invoke the phpMyAdmin setup wizard from the browser using the URL: http://{your-ip-address}/phpmyadmin/setup/index.php . This will show the following setup wizard.

Fig: phpMyAdmin Setup Wizard

Click on “New Server”, which will display following server wizard.

Fig: phpMyAdmin Create New Server

Fill-out following information in the new server screen. Leave other fields to default values.

  • Verbose Name of the Server – Give some descriptive server name.
  • Password for Config Auth – Enter the MySQL root password here.
  • Authentication Type – The default selection is cookie. Just use that.

Click on Save to save the configuration. You might see following warning messages. Ignore it for now.

  • Use SSL – You should use SSL connections if your web server supports it
  • PHP extension to use – You should use mysqli for performance reasons
  • Blowfish secret – You didn’t have blowfish secret set and have enabled cookie authentication, so a key was automatically generated for you. It is used to encrypt cookies; you don’t need to remember it.

5. Launch phpmyadmin

Invoke phpMyAdmin from the browser using the URL: http://{your-ip-address}/phpmyadmin/index.php

If you’ve installed phpMyAdmin on a Linux desktop distribution, you can also access phpMyAdmin using the localhost URL: http://localhost/phpmyadmin/index.php

Login with your MySQL root password. i.e use “root” for phpmyadmin username. Use MySQL root’s password for phpmyadmin password.

If you see the “Cannot load mcrypt extension. Please check your PHP configuration.” message, you didn’t compile your PHP with mcrypt. Make sure you have libmcrypt and libmcrypt-devel packages installed on your Linux before you compile PHP with –with-mcrypt option.

You will also see the message : “Directory config, which is used by the setup script, still exists in your phpMyAdmin directory. You should remove it once phpMyAdmin has been configured.”

Just like the message says, remove the config directory.

# cd /usr/local/apache2/htdocs/phpmyadmin

# rm -rf config

After moving the config directory, if you go to setup/index.php url, you’ll see following message. This is a good thing, as you’ve already configured the phpMyAdmin.

“Cannot load or save configuration. Please create web server writable folder config in phpMyAdmin top level directory as described in documentation. Otherwise you will be only able to download or display it.”

Once you’ve logged-in you should be able to manage all the MySQL databases from your browser.

Fig: phpMyAdmin DB Structures

Howto Install MySQL on Linux

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 .  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.

  • MySQL-client-community-5.1.25-0.rhel5.i386.rpm
  • MySQL-server-community-5.1.25-0.rhel5.i386.rpm
  • MySQL-devel-community-5.1.25-0.rhel5.i386.rpm

2. Remove the existing default MySQL that came with the Linux distro

Do not perform this on an system where the MySQL database is getting used by some application.

[local-host]# rpm -qa | grep -i mysql

[local-host]# rpm -e mysql --nodeps warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[local-host]# rpm -e mysqlclient10

3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.

[local-host]# rpm -ivh MySQL-server-community-5.1.25-0.rhel5.i386.rpm MySQL-client-community-5.1.25-0.rhel5.i386.rpm Preparing...                ########################################### [100%]
1:MySQL-client-community ########################################### [ 50%]
2:MySQL-server-community ########################################### [100%]

This will also display the following output and start the MySQL daemon automatically.

To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h medica2 password 'new-password'

Alternatively you can run:
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available at
Support MySQL by buying support/licenses from

Starting MySQL.[  OK  ]
Giving mysqld 2 seconds to start

Install the “Header and Libraries” that are part of the MySQL-devel packages.

[local-host]# rpm -ivh MySQL-devel-community-5.1.25-0.rhel5.i386.rpm Preparing...                ########################################### [100%]
1:MySQL-devel-community  ########################################### [100%]

Note: When I was compiling PHP with MySQL option from source on the Linux system, it failed with the following error. Installing the MySQL-devel-community package fixed this problem in installing PHP from source.

configure: error: Cannot find MySQL header files under yes.
Note that the MySQL client library is not bundled anymore!

4.  Perform post-install security activities on MySQL.

At a bare minimum you should set a password for the root user as shown below:

[local-user]# /usr/bin/mysqladmin -u root password 'My2Secure$Password'

The best option is to run the mysql_secure_installation script that will take care of all the typical security related items on the MySQL as shown below. On a high level this does the following items:

  • Change the root password
  • Remove the anonymous user
  • Disallow root login from remote machines
  • Remove the default sample test database
[local-host]# /usr/bin/mysql_secure_installation 
In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

5.  Verify the MySQL installation:

You can check the MySQL installed version by performing mysql -V as shown below:

[local-host]# mysql -V mysql  Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1

Connect to the MySQL database using the root user and make sure the connection is successfull.

[local-host]# mysql -u root -p Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


Follows the steps below to stop and start MySQL

[local-host]# service mysql status MySQL running (12588)   [  OK  ]
[local-host]# service mysql stop
Shutting down MySQL.                                       [  OK  ]
[local-host]# service mysql start
Starting MySQL.                                            [  OK  ]

Installing phpMyAdmin

This guide will help you install phpMyAdmin into your server and discuss some of the more popular ways of configuring it to operate.

The first step is to download phpMyAdmin. The current version as of the time of this writing can be found at:

If is no longer the current version, you can download the newest version from this page:

When you download the file, place it into it’s own directory on your server. I personally prefer a folder called ‘myadmin’ inside my website’s root directory. Go ahead and untar/gunzip the file:
# tar zxvf phpMyAdmin-*.gz

Move the contents of your unzipped folder to the current so ‘myadmin’ is the root folder:
# mv phpMyAdmin-*/* .

Create the config file and set the ownership to your apache webserver. Please note that you will want to change the user & group below to match what your Apache runs as.

# touch ; chown apache:apache

Next you will want to create a temporary folder to store uploads of databases. Again you will want to replace the user & group with that of your Apache server.
# mkdir tmp ; chown apache:apache tmp

Next create a mySQL username, password, and database for phpMyAdmin to use for advanced features.

Using your favorite editor, open the file You will want to add the following as contents, modifying it as stated below:

//You’ll want to replace your blowfish_secret with a random sequence of letters and numbers.
$cfg[‘blowfish_secret’] = ‘441b523f1c6c00.312729902’;

//This is the address to your mysql server. In most cases this should be left alone.
$cfg[‘Servers’][1][‘host’] = ‘localhost’;

//This is the extension that phpmyadmin will use to communicate with your mysql server.
$cfg[‘Servers’][1][‘extension’] = ‘mysql’;

//This is the absolute path to your mysql.sock file
$cfg[‘Servers’][1][‘socket’] = ‘/var/lib/mysql/mysql.sock’;

//You will want to leave this at socket unless you are connecting to a remote mysql server
$cfg[‘Servers’][1][‘connect_type’] = ‘socket’;
$cfg[‘Servers’][1][‘compress’] = true;

//This is your root mysql username
$cfg[‘Servers’][1][‘controluser’] = ‘phpmyadmin’;

//This is your root mysql password
$cfg[‘Servers’][1][‘controlpass’] = ‘myadmin4ogr’;
$cfg[‘Servers’][1][‘auth_type’] = ‘cookie’;

//This is the mySQL database that you have set aside for phpMyAdmin to use
$cfg[‘Servers’][1][‘pmadb’] = ‘phpmyadmin’;

//You’ll want to leave the rest of these options alone as they are related to the phpMyAdmin database.
$cfg[‘Servers’][1][‘bookmarktable’] = ‘pma_bookmark’;
$cfg[‘Servers’][1][‘relation’] = ‘pma_relation’;
$cfg[‘Servers’][1][‘table_info’] = ‘pma_table_info’;
$cfg[‘Servers’][1][‘table_coords’] = ‘pma_table_coords’;
$cfg[‘Servers’][1][‘pdf_pages’] = ‘pma_pdf_pages’;
$cfg[‘Servers’][1][‘column_info’] = ‘pma_column_info’;
$cfg[‘Servers’][1][‘history’] = ‘pma_history’;

$cfg[‘UploadDir’] = ‘tmp’;
$cfg[‘SaveDir’] = ‘tmp’;
$cfg[‘GD2Available’] = ‘auto’;

Once you are done modifying this, merely save the config file and pull up your phpMyAdmin site and log in!


Standard MYSQL my.cnf configuration

I have found that many people face a lot of issues relating to MYSQL since their server is not configured properly to handle all the database load, they need to check their my.cnf file.

I’ve created mine as follows;

key_buffer=256M # 64M for 1GB, 128M for 2GB,
256 for 4GB
join_buffer_size=4M # 1M for 1GB, 2M for 2GB, 4M
for 4GB
sort_buffer_size=4M # 1M for 1GB, 2M for 2GB, 4M
for 4GB
read_buffer_size=4M # 1M for 1GB, 2M for 2GB, 4M
for 4GB
read_rnd_buffer_size=3072K # 768K for 1GB, 1536K for 2GB, 3072K
for 4GB
myisam_sort_buffer_size=128M # 32M for 1GB, 64M for 2GB, 128 for 4GB





key_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
sort_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
read_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB
write_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB

key_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
sort_buffer=256M # 64M for 1GB, 128M for 2GB,
256M for 4GB
read_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB
write_buffer=64M # 16M for 1GB, 32M for 2GB,
64M for 4GB


Mysql Useful Commands

[Shell] > mysql -h <host_ip_address> -u <username> -p
Enter password:<password>

create database [databasename];

Use / Select a particular database
use [db name];

show databases;
show tables;
show columns from [table name];
show variables;
show grants for user@localhost; ( eg :- show grants for test123@localhost; )
show columns from <database_name>.<table_name>; ( show columns from test_db.table1; )
show privileges;
show character set;

Describe table structure
describe [table name];

drop database [database name];
drop table [table name];

Select Commands
SELECT * FROM [table name];
SELECT * FROM [table name] WHERE [field name] = “whatever”;
SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444′;
SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444′ order by phone_number;
SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444′;
SELECT * FROM [table name] WHERE rec RLIKE “^a$”;
SELECT DISTINCT [column name] FROM [table name];
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
SELECT COUNT(*) FROM [table name];
SELECT SUM(*) FROM [table name];

Myql Admin
mysqladmin -u root -h <host-ip-address> -p password ‘new-password’
mysqladmin extended-status
mysqladmin status
mysqladmin variables
mysqladmin version
mysqladmin create db_name
mysqladmin drop db_name
mysqladmin flush-privileges
mysqladmin ping
mysqladmin reload
mysqladmin kill ps_id,ps_id…
mysqladmin –user=root shutdown

Alter Table
alter table [table name] drop column [column name];
alter table [table name] add column [new column name] varchar (20);
alter table [table name] change [old column name] [new column name] varchar (50);
alter table [table name] add unique ([column name]);
alter table [table name] modify [column name] VARCHAR(3);
alter table [table name] drop index [colmn name];

Grant Privileges
grant usage on *.* to bob@localhost identified by ‘passwd’;
grant all privileges on databasename.* to username@localhost;

Backup and Restore
mysqll db_name < input.sql
mysqldump db_name my_table > output.sql
mysqldump –no-data db_name my_table > dump_table_name.sql
mysqldump –add-drop-table db_name my_table > dump_table_name.sql
mysqldump –compatible=mysql40 db_name my_table > dump_table_name.sql

Load a CSV file into a table.
LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);

MySQL Quota-Tool

The MySQL Quota-Tool helps you to set a size limit on MySQL databases.

It works by checking the size of each database and revoking the INSERT- and CREATE-priveleges for the databases, which exceed the given size limit. When the size of the database falls below the given limit, the INSERT- and CREATE-priveleges are granted again.

This doesn’t work for users who have global priveleges, because the quota is database and not user based, but in most environments privileges are given in the “db”-table which is modified by the MySQL Quota Tool.

The source code:
#!/usr/bin/php -q

* MySQL quota script
* written by Sebastian Marsching

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

* Create table for quota data with the following statement:
* `Exceeded` ENUM(‘Y’,’N’) DEFAULT ‘N’ NOT NULL,
* PRIMARY KEY (`Db`), UNIQUE (`Db`));
* The field ‘db’ stores the information for which database
* you want to limit the size.
* The field ‘limit’ is the size limit in bytes.
* The field ‘exceeded’ is only used internally and must be
* initialized with ‘N’.

* Settings

$mysql_host  = ‘localhost’;
$mysql_user  = ‘root’; // Do NOT change, root-access is required
$mysql_pass  = ”;
$mysql_db    = ‘quotadb’; // Not the DB to check, but the db with the quota table
$mysql_table = ‘quota’;

* Do NOT change anything below

$debug = 0;

// Connect to MySQL Server

if (!mysql_connect($mysql_host, $mysql_user, $mysql_pass))
echo “Connection to MySQL-server failed!”;

// Select database

if (!mysql_select_db($mysql_db))
echo “Selection of database $mysql_db failed!”;

// Check quota for each entry in quota table

$sql = “SELECT * FROM $mysql_table;”;
$result = mysql_query($sql);

while ($row = mysql_fetch_array($result))
$quota_db = $row[‘db’];
$quota_limit = $row[‘limit’];
$quota_exceeded = ($row[‘exceeded’]==’Y’) ? 1 : 0;

if ($debug)
echo “Checking quota for ‘$quota_db’…\n”;

$qsql = “SHOW TABLE STATUS FROM $quota_db;”;
$qresult = mysql_query($qsql);

if ($debug)
echo “SQL-query is \”$qsql\”\n”;

$quota_size = 0;

while ($qrow = mysql_fetch_array($qresult))
if ($debug)
{ echo “Result of query:\n”; var_dump($qrow); }
$quota_size += $qrow[‘Data_length’] + $qrow[‘Index_length’];

if ($debug)
echo “Size is $quota_size bytes, limit is $quota_limit bytes\n”;

if ($debug && $quota_exceeded)
echo “Quota is marked as exceeded.\n”;
if ($debug && !$quota_exceeded)
echo “Quota is not marked as exceeded.\n”;

if (($quota_size > $quota_limit) && !$quota_exceeded)
if ($debug)
echo “Locking database…\n”;
// Save in quota table
$usql = “UPDATE $mysql_table SET exceeded=’Y’ WHERE db=’$quota_db’;”;
if ($debug)
echo “Querying: $usql\n”;
// Dismiss CREATE and INSERT privilege for database
$usql = “UPDATE db SET Insert_priv=’N’, Create_priv=’N’ WHERE Db=’$quota_db’;”;
if ($debug)
echo “Querying: $usql\n”;

if (($quota_size <= $quota_limit) && $quota_exceeded)
if ($debug)
echo “Unlocking database…\n”;
// Save in quota table
$usql = “UPDATE $mysql_table SET exceeded=’N’ WHERE db=’$quota_db’;”;
if ($debug)
echo “Querying: $usql\n”;
// Grant CREATE and INSERT privilege for database
$usql = “UPDATE db SET Insert_priv=’Y’, Create_priv=’Y’ WHERE Db=’$quota_db’;”;
if ($debug)
echo “Querying: $usql\n”;

Originally posted on :