MySQL

Navigation

computing: software: databases: engines: MySQL

Overview

MySQL is a FOSS database engine and server. It fills roughly the same niche as Microsoft SQL Server.

The MySQL package (at least in Linux) comes with a number of helper utilities, including MySQL Administrator and MySQL Query Browser. The Administrator utility is useful for making backups, and the Query Browser serves as a front-end for interacting with databases.

Articles

• 'How-To:
  • Using MySQL with MS Access
  • backing up MySQL data
• Components:
  • MySQL Administrator
  • MySQL Query Browser

Criticisms

MySQL has been criticized by at least one user of lacking production-quality features, with version 5.0 only just starting to add them, where as other FOSS alternatives such as PostgreSQL had such features in their very earliest versions. MySQL has also been criticized for bad performance, e.g. locking entire tables when it only should have locked a row, as well as general incompatibility (Editor's note: with what?).

• Wikipedia: Criticisms of MySQL
• 2007-05-26 Re:MySQL the db for people who don't understand db

Files & Folders

The Installation Layouts manual page gives a listing of folders created by a mysql installation, but does not list all files (some of them added to existing folders) upon which a working installation is dependent.

RPM distributions

(The Debian/Ubuntu setup is similar; folders marked with * indicate those found in Ubuntu and not given in the documentation.)

Directory	Contents of Directory
*/etc/init.d/mysql	daemon start/stop/restart script
*/etc/mysql	my.cnf: configuration file; also other configuration files /etc/mysql/conf.d/
/usr/bin	Client programs and scripts
/usr/include/mysql	Include (header) files
/usr/lib/mysql	Libraries
/usr/sbin	The mysqld server
/usr/share/info	Manual in Info format
/usr/share/man	Unix manual pages
/usr/share/mysql	Error message and character set files; *also scripts
/usr/share/sql-bench	Benchmarks
/var/lib/mysql	Log files, databases

Working Notes

external connections

If mysql refuses to respond to connection attempts from anywhere but the local machine, you need to edit /etc/mysql/my.conf and comment out the following line:

bind-address  = 127.0.0.1

and then restart mysqld.

miscellaneous

To restart the mysql daemon (mysqld) when you don't have a user with SHUTDOWN privileges but you do have system root:

sudo /etc/init.d/mysql restart

Query Browser bugs

On Ubuntu 6.06 and earlier (I don't know if this has been fixed yet), the MySQL Query Browser has a tendency to crash when editing tables (this happens, unfortunately, before it even displays the required SQL, so you can't use it to work that out for you). This problem does not seem to affect the SQL window, so you can make the needed changes using SQL either in that window or via the CLI client – but working out the exact syntax can be tricky.

Command-line Client Example

editing a blank table: To work around the Query Browser bug (fixed in the latest version) which won't let you edit a blank table, create the first entry using MySQL's command line interface:

woozle@camilla:~ $ mysql -hyourhost -uyourusername -pyourpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12531702 to server version: 4.1.13-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use yourdatabasename
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO tablename () VALUES();
Query OK, 1 row affected (0.05 sec)

mysql>

...where yourhost is the name of your MySQL server, and can be anything that can resolve to an IP address, e.g. localhost, yourmachinename, yourserverdomain.com, or an actual IP address (I think).

SQL Examples

creating a table: If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:

create table table_name (field_name field_type, field_name field_type

1. REDIRECT Template:arg/opt);

Example: <sql>create table example (key varchar(255));</sql>

• More examples: importing data into ZenCart
• Documentation: CREATE TABLE Syntax

making a field autonumbered:

ALTER TABLE tablename MODIFY fieldname fieldtype AUTO_INCREMENT;

Example: <sql>ALTER TABLE Tracks_Queued MODIFY id INTEGER AUTO_INCREMENT;</sql>

changing the name of a field (column):

ALTER TABLE tablename CHANGE oldname newname new_fieldtype;

This also changes the field's definition, if new_fieldtype is not the same as the field's prior definition.

Example: <sql>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</sql>

• Documentation: ALTER TABLE Syntax

Links

• MySQL Home Page
  • Version 5.1 Manual
  • Version 4.1 Manual
• Wikipedia
• HashMySQL wiki: official wiki of the unofficial-but-sanctioned #mysql IRC channel on Freenode