Jan 232015
 

A system administrator or DBA may need to connect quite often to a MySQL database from the command line, to perform administration and optimization tasks, and such. The common way to connect is executing the mysql client, with a set of arguments to specify the user and password to use, as well as the host, database and port where the instance is listening.

This post explains how to specify de defaults for all these arguments in a configuration file, to avoid having to type it again and again, every time a new connection to the database needs to be made.

Continue reading »

 Posted by at 6:07 pm
Aug 252014
 

In a default MySQL installation on a Linux system, the MySQL data files are located under the /var/lib/mysql directory.

But on many Linux systems, this location might not be the best place. If the system disk is partitioned into several partitions, the size of file system where the ‘/var/lib/mysql’ directory belongs might not be dimensioned to hold the required databases.

Fixing this issue is easy, as explained in this post.

Continue reading »

 Posted by at 5:04 pm
Aug 192014
 

When an INSERT statement is executed to add a set of records to a table that has a unique key defined, it may happen that the value of the key in some of the records to be inserted is the same as that of records already in the table.

The default behaviour in this case is that the execution fails with an error message “execute failed: Duplicate entry”. When this happens, no record is inserted, not even those whose keys do not clash with the keys of previously existing records.

Continue reading »

 Posted by at 11:45 am
Aug 172014
 

Tables in a MySQL database can use  different types of storage engines, the most common among them being MyISAM and InnoDB. Each of these types of storage has advantages and drawbacks. The decision about which storage type es most adequate for a given table depends on several factors.

Anyway, the first step is knowing which type of storage engine is used by existing tables in a database. This post explains several ways to obtain this information.

Continue reading »

 Posted by at 2:42 pm
Aug 172014
 

Sometimes, the system administrator may find that a user, or application, has started the execution of a SQL sentence that is taking minutes, hours or even days to execute. This may happen for large databases where a suitable index has not been defined to speed up the query. The long running query may be taking a large amount of the available CPU and memory resources, hindering the normal execution of other applications and services running in the system. Killing the offending application does not help, as the query continues being executed by the database server.

This post explains how to identify and kill the MySQL process that is running the query, without affecting other operations that the database server might be performing in response to requests issued by other applications.

Continue reading »

 Posted by at 9:53 am
May 072014
 

A great number of websites are set up on top of the same set of software building blocks: a Linux operating system, an Apache web server, a MySQL dabase management system, and a set of PHP scripts that generate the dynamic content. Among these elements, the database might be one of the more influencial in the overall performance of the site.

This post uses a real-world example to show the existing possibilities when it comes to analyze the site performance, and tune the MySQL configuration to optimize it.

Continue reading »

 Posted by at 2:06 pm
Feb 242014
 

phpMyAdmin is probably the most well-known database management tool for MySQL databases. Besides allowing the administrator to browse the objects in the database through a graphical web frontend, phpMyAdmin also mase easy to carry out many common DBA maintenance tasks. This post explains how to install and configure phpMyAdmin on a Linux distro. Continue reading »

 Posted by at 2:44 pm
Feb 222014
 

Sometimes we may find that there are unwanted duplicate records in one of the tables of our database. Often we notice this issue when trying to create a unique key based on one or several of the fields in the table.

This post explains a couple of simple methods that can be used to remove duplicate rows from a mysql table, keeping only one of each group of duplicates.

Continue reading »

 Posted by at 9:05 pm