MySql

Help and howto do things in MySQL

MySQL is the world’s most popular open source database, recognized for its speed and reliability, and is frequently used with PHP to create websites. The Electric Toolbox website uses MySQL as its database backend, and I have personally been using MySQL since 1999. In that time it has proven itself to me to be an extremely fast and reliable database management system, although the earlier versions lacked some features found in other database systems, such as stored procedures, triggers, sub selects and joined table updates. Most of these are available from the 4.1 and 5.0 releases.

If you are looking for MySQL databases – for example geographical databases, I suggest you take a look at SQL Dumpster

Logging queries with MySQL

It is possible to log all queries done by the MySQL server. To enable MySQL logging, the MySQL manual indicates you should add –log[=file_name] when starting mysqld. This isn't really the best options, as most Linux distributions use init scripts to start up MySQL, and this means either modifying them, or calling mysqld from the command line, which isn't really the best way of doing this when the init scripts do the job of starting and stopping services so nicely. So instead, it is easier to modify the my.cnf file (located at /etc/my.cnf on many Linux distros) and add the logging option there.

MySQL Backups with a Command Line PHP Script

I run a number of web servers with PHP and MySQL and have a PHP command line script that runs on a daily basis to back up MySQL databases using the mysqldump command. It would be possible to do this using a simple bash script as well, and I know I used to use a bash script in the past but for some reason switched it to PHP at some stage.

SQL query to work out top selling categories

I sell Linux on CD on my Linux CD Mall website, and it has a page showing the most popular Linux and BSD distributions that it sells. For some reason, I’d never set this up to query the information from the database, instead manually updating it periodically. Today I decided to actually get around to making it read the information from the database and have decided to share the SQL queries etc used to get the information for top sellers by category.

MySQL Query Cache

MySQL has a query cache which caches the results of SELECT queries, if enabled. This means that frequently used database queries will run much faster, because the data resultset will be read from the cache instead of having to run the query again. The MySQL query cache is available from MySQL 4.0.1. Whenever tables in the database are modified the relevant entries in the query cache are flushed so you can be certain that even with the query cache enabled only up to date data is returned.

Backing up MySQL with mysqldump

A simple way to back up MySQL databases is with the mysqldump command line tool. Mysqldump can be used to back up a single database or multiple databases, and can backup MySQL databases into a text file conatining multiple SQL statements, or into CSV or tab delimited text files.

Selectively dumping data with mysqldump

mysqldump is a command line tool for outputting table structures and data and can be used for backups etc. By default mysqldump will dump all data from a table, but it is possible to select which data to be exported with mysqldump. This post looks at how to do this.