This site is now 100% read-only, and retired.

Simple MySQL cookbook

Posted by Steve on Tue 28 Dec 2004 at 18:16

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

Determining MySQL version

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 |
+---------------------+
Logging Slow Queries

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

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'
Resetting AutoIncrement variables

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

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

SELECT * FROM table ORDER BY RAND() LIMIT 1
Selecting Items and Counts

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 |
+-------------------+--------+
Resizing a Column

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);
Add a new MySQL User

Adding a new user to MySQL Databases.

 

 


Changing root password for mysql
Posted by Steve (82.41.xx.xx) on Tue 4 Jan 2005 at 17:46
[ View Weblogs ]

Changing the root password for mysql is simple, run:

mysql --user=root --pass=blah mysql

This will give you access to the MySQL administration table, if your current password is "blah".

Change it by running:

update user set Password=PASSWORD('new-value') WHERE User='root';
flush privilege;

Now your new root password is "new-value".

Steve
-- Steve.org.uk

[ Parent ]

Re: Changing root password for mysql
Posted by Anonymous (62.255.xx.xx) on Wed 1 Jun 2005 at 20:03
The last line of that code should read:
flush privileges;
--jaycee

[ Parent ]

Re: Simple MySQL cookbook
Posted by Anonymous (204.13.xx.xx) on Tue 21 Oct 2008 at 23:20
"log = /var/log/mysql/mysql.log" doesn't log all queries.
"query_log" would, no?

[ Parent ]