Introduction to MySQL

Written by Kevin Korb as a presentation for GOLUG Presented on 2006-08-03

What is MySQL?

MySQL is a database engine that uses the standard SQL interface. MySQL is very popular as a back end for web applications. The MySQL engine can be accessed from most major programming/scripting languages such as perl, php, and ruby making it easy to develop applications.

What is MySQL good for?

• Web Applications: Because MySQL can be easily integrated into scripting languages such as perl and php it is very popular for web applications. Many web applications use or can use MySQL to store their data. This removes the need to write and optimize your own method of storing data when developing a web application. Most web hosting providers also provide a MySQL engine with a hosting account or as a value added service.
• GUI Applications: The same scripting language integration that makes MySQL work well with web applications also makes it work well with local GUI and even command line applications or anything else that stores structured data.

What isn't MySQL good for?

• Super fast small queries. MySQL or any other major database engine is not really suited for dealing with lots of very tiny queries when answers need to be fast. A perfect example of this is DNS. There are DNS server packages out there that can pull their data from MySQL or some other database engine however they are not very scalable. A simple hash table (like cdb or berklydb) is much better at serving this type of query.
• Storing large chunks of binary data (blobs). One thing that many newbies do is to make a database of pictures for a simple photo gallery style web application (I did this myself at http://www.sanitarium.net/pics/. The mistake they all try to make is to store the jpg files themselves into the database table. While this is possible it is very inefficient because the engine has to crunch through all that data looking for the much smaller values stored in other columns. A much better solution is to simply store the filename of the picture in the database and let apache serve out the referenced file on its own.

MySQL vs. PostgreSQL

PostgreSQL is the "other" popular free SQL database. Both are very capable and have almost the same list of features. Most people will not need any of the features that only one or the other supports which means they can usually just pick whichever one they find easier. I believe that MySQL is more of a system administrator's database while PostgreSQL is more of a DBA's database. If you are a sysadmin that has never used a database before you will probably be able to learn MySQL much easier. OTOH if you are a DBA (from Oracle or one of the other big commercial database vendors) you will probably find PostgreSQL easier to learn.

Common SQL commands

• use <database>: Changes the active database (kind of like 'cd' in the shell).
• create database <database>: Creates a new database (kind of like mkdir in the shell).
• create table <table> (...): Creates a new table with the columns defined in the parentheses.
• desc table <table>: Shows the layout of a table.
• show create table <table>: Shows the create command that created an existing table (including any changes you have made since with the alter command).
• select <column>[,column2...] from <table> [where ...] [order by ...] [group by ...] [limit ...]: Extracts data from a table. The where portion is a filter, the group portion is a uniqueness filter, the order part is a sort, and the limit part allows you to limit the number of records produced.
• insert into <table> values (<value1>[,value2 ...]): inserts a new record into a table.
• update <table> set <column>=<value>[column2=value2...] [where ...]: changes values of an existing record that is matched by the where clause. Note that if you leave off the where clause you will change ALL records.
• delete from <table> [where ...]: deletes records from a table that match the where clause. Note that if you leave out the where clause you will simply empty the entire table.
• drop table <table>: deletes a table and all of its data.
• drop database <database>: deletes a database and everything in it (kind of like rm -rf in the shell).
• explain <SQLcommand>: Shows a brief description of what a command would do without actually doing it. This is useful for figuring out if you have good indexing.

Indexes

Indexes are a way to increase performance and efficiency in a database table. If you have a table with many columns but you are always doing searches on one or two of those columns you can tell MySQL to index those columns. When you do a search (or a sort) using an indexed column the MySQL engine only has to process the much smaller index instead of the entire table to find the right field. You can also specify that an index is unique which is an even bigger performance benefit because once the engine finds the value it can stop because there can't be another one like it. You can add an index to a table with the command: alter table <table> add <index|unique> <index> (<column>[,column2...]). Indexing is a must on large tables. The performance can be horrible without them.

Table types

MySQL supports several different types of tables. They all have strengths and weaknesses and should be used for different kinds of data. Some must be enabled during compile time or in the config file.
• MyISAM: This is the default table type. It is very fast and usually what you want.
• InnoDB: This is a more advanced table type. It supports extra features such as transactions. It should be used whenever transactions are required.
• Memory/HEAP: This is a special type of table that exists only in RAM. Any data stored in this table will be LOST if the engine is restarted. Only the table structure is permanent. These tables are very fast but should only be used for temporary storage. They are often used to store sessions in web applications.
• BDB: This is the BerklyDB hash table. While it supports transactions like InnoDB it is a hash table which means it is more suited for a read-only application.
• Archive: This is a space saving table format. It has no indexing support at all so it doesn't have very good performance but it is very small.

Data types

When you create a table you define what records will be kept in it. You also define what kind of data each record will hold. It is important to choose the correct data type for the data you plan to store. This will allow the engine to use the data in the most efficient way possible.

Type	Bytes per record	Type of data
tinyint	1	integers -127 through 127
smallint	2	integers -32768 to 32767
mediumint	3	integers -8388608 to 8388607
int	4	integers -2147483648 to 2147483647
bigint	8	integers -9223372036854775808 to 9223372036854775807
float	4	floating point numbers 3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38
double	8	floating point numbers -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308
date	3	Stores a date 1000-01-01 to 9999-12-31.
time	3	Stores a time -838:59:59 to 838:59:59.
datetime	8	Stores a date and time 1000-01-01 00:00:00 to 9999-12-31 23:59:59.
timestamp	4	Stores a timestamp. Setting this to a null causes it to store the current time. This is often used to record the last time a record was updated. Allowed values are the same as unixtime (1970-2037).
year	1	Stores a year 1901 to 2155 (and 0000). Can be displayed with 2 or 4 digits.
char (n)	n	Stores a string n chars. n <= 255. Note that this is case specific but not case sensitive.
varchar (n)	numchars+1	Like char except the length of the string can be up to n chars instead of exactly n chars. Note that this is case specific but not case sensitive.
binary (n)	n	Stores a string n chars. n <= 255. Unlike char this allows any binary character and is case sensitive.
varbinary (n)	numchars+1	Like binary except the length of the string can be up to n chars instead of exactly n chars. Unlike varchar this allows any binary character and is case sensitive.
blob/text	numchars+2	Like varbinary/varchar except this allows up to 2^16 chars.
mediumblob/mediumtext	numchars+3	Like varbinary/varchar except this allows up to 2^24 chars.
longblob/longtext	numchars+4	Like varbinary/varchar except this allows up to 2^32 chars.
enum	1 or 2 depending on size of list	This is an enumeration of set string values. They can be any string you want and each record has one of the listed values.
set	1,2,3,4, or 8 depending on the number of members	This is like enum except each record can have 0-64 of the set members.

Shell commands for MySQL

• mysqladmin: Provides many administrative functions. Lists and kills queries, reloads settings, changes the root mysql password, etc.
• mysqlcheck: Checks databases for corruption. Repairs found errors if run with --auto-repair. This is kind of like fsck for MySQL.

Backing up MySQL databases

Like all other data your MySQL databases should be backed up. Because the database files are held open by mysqld and modified in place as changes are made they should NOT be backed up using standard file backup tools. There are 4 different methods for backing up MySQL databases:
• shutdown: and backup: If you can tolerate the outage and you just can't stand using any special backup tools you can simply shutdown mysqld, backup the files like normal files, then start mysqld back up. This means that the databases will simply be offline during the backup.
• mysqldump: Mysqldump is a utility that dumps mysql databases to a text file. The text file is a series of SQL commands that will recreate the data that was backed up. The backup will be bigger than the original data but it compresses well. The advantage of mysqldump is that you can keep multiple backups and diff them easily. The down side is that if there is a problem with a database mysqldump might not work correctly and restoration definitely will not. A common example is an auto_increment field that contains a record with a 0 value. MySQL simply will not restore that table until you edit the dump file and remove the bogus data.
• mysqlhotcopy: Mysqlhotcopy is a utility that temporarily locks a database and then copies the files to another directory. The advantage here is that the databases are copied exactly as they are and any issues will be restored as is later. The down side is that mysqlhotcopy does NOT support table types other than MyISAM.
• logs: It is also possible to configure MySQL to log all changes. Those logs can then be backed up and replayed later.

Accounts, Permissions, and Security

MySQL accounts are not the same as UNIX shell accounts (though they may have the same usernames). In MySQL there is a database called mysql that contains all of the account and permission settings. Each user will have an entry in the user table which specifies their username, what host they are allowed to log in from, their password, and what global permissions they have. If you want to limit a user to a specific set of databases you add them to the user table with no privileges at all and then add another entry to the db table specifying what database the account can access and what privileges they have within that database. The same can be done at the table or even the column level. These entries can be made using either the SQL grant command or manually using the standard insert/update/delete SQL commands. Note however that if you do not use the grant command you must also run 'mysqladmin flush-privileges' for the changes to take effect. Note that there are some privileges that are dangerous and should not be given to normal user accounts:
• Reload_priv: Allows the user to reload the config files. Not really that dangerous but if done rapidly it could constitute a DoS attack.
• Shutdown_priv: Allows the user to shutdown the MySQL engine. This would prevent any other user from accessing it.
• Process_priv: Allows the user to look at the process listing. This can contain sensitive information from other users.
• File_priv: Allows the user to manipulate database files directly. This can be abused to modify/destroy any file that the mysqld process can write to.
• Grant_priv: Allows the user to use the grant command which would allow them to grant themselves whatever privileges they want.
• Super_priv: Allows the user to kill processes (including ones started by other users).