MySQL is an important service in the Web-Hosting industry. Almost 80% of websites on the Internet are database driven. In a shared hosting environment, the availability of this service is critical.

Many times, I have come across the problem of some particular database query taking more time to execute or using more resources. Eventually the result is high load on the server. This can be due to a sequential query to select a particular value from a large table which is not optimized.

A simple approach can be adopted to find the query and table involved. To do this you can enable slow query logging.

Read the rest of this entry »

MySQL is the most widely used multi-user, multi-threading SQL database management system. To optimize MySQL performance, a DBA should have reasonable knowledge of MySQL system variables. This article covers the basics of MySQL server optimization. We will first discuss MySQL optimization during installation. Then we will discuss the seven most important and common system variables. Plus, a brief note on how to optimize the server using those seven variables.

We will also discuss MySQL status variables. This article would be very useful for a newbie MySQL DBA when setting up a MySQL server.

Read the rest of this entry »

This article describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (say we can call it as slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.

This is not a backup method or policy because an accidentally issued DELETE or ALTER command will also be carried out on the slave; but replication can help protect against hardware failures though.

Read the rest of this entry »

Sometimes we may require to run multiple versions of MySQL on the same Server. This can happen if you either need to test a new MySQL release or you need a new MySQL version and you don’t want to make any changes to the existing system.

The whole idea behind this is to compile the new MySQL server with different TCP/IP ports and Unix socket files so that each one is listening on different network interfaces. Compiling in different base directories for each installation also results in separate compiled-in data directory, log file, and PID file location for each server.
Read the rest of this entry »