Debian Administration

Simple MySQL cookbook

In the course of running this site, and working with MySQL generally I've had to lookup miscellaneous documentation. Hopefully these tips will prove useful to others too.

MySQL is described by its authors as "the worlds most popular open source database", despite missing several features offered by its leading competitor PostgreSQL it is very popular and often included in cheap webhosting packages.

In Debian the database may be installed by running:

apt-get install mysql-server

The client used to access the server is also available, and may be ran interactively:

apt-get install mysql-client
mysql --user=root databaseName

Other clients are available, such as the Perl DBI classes:

apt-get install libdbi-perl

Once it's up and running common tasks may be performed using the documentation available online. This small article includes a few non-obvious things which I had to lookup specially

To determine the version of MySQL you are connected to execute the following SQL:

SELECT VERSION();

For example you could run the following:

skx@lappy:~$ mysql --user=root -e 'SELECT VERSION();'
+---------------------+
| version()           |
+---------------------+
| 4.0.22_Debian-6-log |
+---------------------+

If you're running a website, or web application, which is centered around a MySQL database you might wish to check that your SQL is fully optimized.

It's hard to optimize things without knowing where the current bottlenecks are, but thankfully MySQL allows you to log slow queries so you can find the bottlenecks in your application.

Edit the file /etc/mysql/my.cnf and uncomment the following lines:

log-slow-queries        = /var/log/mysql/mysql-slow.log

Restart mysql by running /etc/init.d/mysql restart and you will find a logfile created containing all the slow queries executed against your server.

To speedup the server I usually make sure I disable other logging, by commenting out the following line:

log            = /var/log/mysql/mysql.log

This stops the server from logging all the statements executed.

Showing server status, and statistics can be achieved by running the following SQL

SHOW STATUS

Or from the command line:

steve@skx:~$ mysql --user=root -e 'SHOW STATUS'

Many tables are created with an auto-incrementing value, which means that every time you insert a record it will get a unique id of some sort.

If you delete all the records in your table you'd expect this to be reset to start from 0 - but it doesn't.

To reset the autoincrement values of your table run:

truncate table TABLENAME

This will cause the id to start from zero again.

Selecting a random row from a table is a common task, and very simple:

SELECT * FROM table ORDER BY RAND() LIMIT 1

If you have a table which contains entries such as the name of a person, and you wish to count how many times each name is listed you can use something like this:

SELECT name,COUNT(*) AS Number FROM table GROUP BY name  ORDER BY Number DESC, name"

This gives :

+-------------------+--------+
| Name              | Number |
+-------------------+--------+
| skx               |     15 |
| Anonymous         |      7 |
| Paul              |      7 |
| Toby Smith        |      6 |
| Steve             |      4 |
+-------------------+--------+

If you have a table called articles which has a field called title and you wish to make this larger:

ALTER TABLE articles MODIFY title VARCHAR(65);

Adding a new user to MySQL Databases.

Add Comment Printable version

 

<<< Setting up mail forwarding Intrustion detection and prevention for Apache with mod-security >>>