There are many discussions about this topic and an endless debate between which database engine to choose InnoDB or MyISAM.

Both options are practical and efficient  the oldest and the default used is usually MyISAM  and in most cases we can change our engine when we want or need. Now I come with this question:

What engine is more efficient?

MyISAM:

  • The structure of the tables in MyISAM are simpler and is most recommended for users.
  • MyISAM allows table locking.
  • With MyISAM you get high speed on SELECT because they don’t have integrity checks.

InnoDB:

  • Support for transaction type ACID (Atomicity, Consistency, Isolation and Durability) that guarantees the integrity of the tables.
  • Record locking.
  • Recovery of unexpected problems through the transaction log.

Continue Reading…

This job can be done in many ways like dumping all databases and replacing myisam with innodb in all created tables. However this solution can take very long time.

The simplest way is to use maatkit package. I will show you how to convert your MySQL databases from MyISAM to InnoDB using maatkit:

Step1. Install maatkit package:

apt-get install maatkit

Step2. Now we need to find a list with all your databases. You can do that using the following command:

mysql -u root --password=your_password --skip-column-names -e "show databases;" > databases.list

Step3. Now convert all your MySQL databases from MyISAM to InnoDB

for databases in `cat databases.list`; do /usr/bin/mk-find $databases --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=INNODB" --print; done

This command will extract all database names from databases.list file and send that name to mk-find  which is executing ”ALTER TABLE” command on all of its tables to convert their storage engine from MyISAM to InnoDB.

Enjoy!

A new version of the favored database   PostgreSQL has been released and has a pelnty of performance and stability enhancements. This version of PostgreSQL will scale itself upto 64 cores and carry on index only scan with significant CPU usage reduction improvements.

The new PostgreSQL has the potential of:

  • Up to 350,000 read queries per second (more than 4X faster)
  • Index-only scans for data warehousing queries (2–20X faster)
  • Up to 14,000 data writes per second (5X faster)

Also this release includes the native JSON support which will enable to use this RDBMS in an exceedingly fully totally different approach.

Debian and Ubuntu packages from Percona are signed with a key. Before using the repository  you ought to add the key to apt.

For that run the following commands:

Step1. Add the Percona repository:

gpg –keyserver hkp://keys.gnupg.net –recv-keys 1C4CBDCDCD2EFD2A
gpg -a –export CD2EFD2A | sudo apt-key add -
echo “deb http://repo.percona.com/apt squeeze main” >> /etc/apt/sources.list
echo “deb-src http://repo.percona.com/apt squeeze main” >> /etc/apt/sources.list

Step2. Install Percona Server:

apt-get update
apt-get install  percona-server-server-5.5

Continue Reading…

phpMyAdmin is a popular software  and as such is a target for various scripted attacks. If you run phpMyAdmin installation somewhere you should really make sure it is secured.

There are some things you can do to keep you safer:

1. Remove setup directory from phpMyAdmin. You will not use it after initial setup:

 rm -rf /usr/share/phpmyadmin/setup/

2. Prevent access to libraries directory from browser.  You can use a .htaccess file for this.

3. In case you don’t want all  users  to access phpMyAdmin  you can use AllowDeny rules to limit them:

Use the following in the file config,inc.php of phpMyAdmin to restrict access to from remote machines.

vim /usr/share/phpmyadmin/config.inc.php


Continue Reading…

Page 2 of 512345