One Feature That's Missing

One feature missing from MySQL, which developers may notice, is the sub-select. For example, the query

SELECT id FROM some_table WHERE id IN (SELECT id FROM
 some_other_table);

won't work in MySQL. It's possible to get around the problem by using joins and temporary tables, but sub-selects are considerably more straightforward and convenient. MySQL's developers have promised to add sub-select support in future versions, but there's no definite timeline.

Documentation on the MySQL Web site contains strategies for dealing with the sub-select issue, which highlights one of the major advantages of open-source software over commercial software; namely, that open-source software creators have the freedom to be completely honest about their product's shortcomings and failings. Commercial software publishers have more incentive to cover up known bugs than to help developers deal with them.

All MySQL's administrative functions can be performed from the command line, making it easy to set up cron jobs for tasks which need to occur regularly, such as backup and replication. The mysqldump and mysqlhotcopy functions are particularly useful for regularly scheduled tasks, so I'll take a moment here to describe how they work.

The "mysqldump" function dumps the structure and data of a single table, several tables, a single database, or several databases. It outputs a text file with all the data definition language necessary to construct the tables and populate them in standard SQL, which is particularly useful if you need to move data from one DBMS to another. Because it functions as a command-line tool, it's possible to pipe it through other commands, including other mysql utilities. So if I want to take a MySQL database called db1 on one server and dump it to another MySQL installation on another server, I can it do it with the following command:

shell> mysqldump --user username --password=pass --opt db1 | mysql 
--user username --password=pass --host=another_server db1 

Next: 'Dump' Function Has Several Uses