Category: MySQL I : MySQL Fundamentals

MySQL Resources
The current stable version of MySQL is 3.23.52. Version 4.x is the current development release and is not yet considered stable. Both versions can be downloaded from http://www.mysql.com/downloads
MySQL Books
*** Excellent
** OK
* Don’t waste your money
*** Beginning Databases with MySQL, by Neil Matthew and Richard Stones. WROX Press. 2002. ISBN 1-861-006-92-6. $39.99. This one is quite well written. It gets into advanced topics like C++ and Java integration.
*** MySQL Cookbook, by Paul DuBois. O’Reilly & Associates; ISBN: 0596001452; 1st Edition, (November 2002). This one is indispensable!
*** MySQL, by Paul DuBois. New Riders Publishing; ISBN: 0735709211; 1st edition (December 28, 1999). Another well-written introduction to MySQL.
** MySQL Visual Quickstart Guide, by Larry Ullman. Peachpit Press. ISBN: 0-321-12731-5. $21.99 (Note: I would have given our classroom text three stars, except that it has a higher-than-usual number of errors and typos for a Peachpit Press book. Hopefully the second edition will be more carefully proofread.
** Sams Teach Yourself PHP, MySQL and Apache in 24 Hours. OK if you need to get started quickly, but otherwise, there are better books available.
MySQL Links
• http://www.mysql.com – the source for the MySQL product, including it’s many different versions and configurations. Also includes third party tools (like MyCC, MySQL GUI, and MyODBC), and online product documentation. The MySQL Documentation is at: http://www.mysql.com/documentation. Get MySQL and associated tools at: http://www.mysql.com/downloads.
Associated Software
• Access-To-MySQL. This is a program that converts Microsoft Access databases to MySQL, using a GUI interface. It is not a free program, but does have a free demo that will convert table structures, but not data. Available on the web at: http://www.convert-in.com/acc2sql.htm
• KSQL. KDE, the open source Linux window manager, has a new GUI for managing MySQL in progress. Available at http://ksql.sourceforge.net

MySQL Discussion Forums
• Forums and Mailing Lists (Listservs) are email-based discussion groups. Beginners and experts help each other out. Highly recommended, and the best way to solve a technical problem quickly.
• A list of MySQL forums is at: http://www.mysql.com/documentation/lists.html
• Another source of answers is Google Groups Advanced Search, at: http://www.google.com/advanced_group_search?hl=en
MySQL Online Tutorials:
• An excellent collection of online tutorials is available at: http://www.devshed.com/Server_Side/mysql/
• A good tutorial on quickly configuring MySQL, Apache, and PHP on Linux is at: http://sysbotz.com/articles/amp.htm

1. Logon to the MySQL Monitor client.
2. Use the accounting database: USE accounting;
3. Make sure the expenses table is populated: SELECT * FROM expenses;
4. Create a full text index:
ALTER TABLE expenses ADD FULLTEXT (expense_description);

– a full text index must be created on the columns you wish to search.
5. Do a full text search using the word “visual”:
SELECT expense_id, expense_description FROM expenses
WHERE MATCH (expense_description) AGAINST (‘visual’);
6. Search for multiple words and sort by relevancy:
SELECT expense_id,
MATCH (expense_description) AGAINST (‘visual guide’)
AS rel, expense_description
FROM expenses
WHERE MATCH (expense_description)
AGAINST (‘visual guide’);
7. Using regular expressions, find which clients have a toll-free number:
SELECT client_name, client_phone FROM clients
WHERE client_phone REGEXP ‘(800)|(888)|877’;
8. Using regular expressions, retrieve all valid contact email addresses:
SELECT client_name, contact_first_name, contact_email FROM clients
WHERE contact_email REGEXP ‘[[:alnum:]]+@.*\.[[:alnum:]]{2,3}’;

– See page 273 for a table of regular expression characters.
– PERL Poetry: using regular expressions is powerful, but does not promote code readability. Perl gets its bad reputation mostly from regular expressions.

1. Login to the MySQL Monitor client.
2. Use the “accounting” database:
USE accounting;
3. Lock the tables you will be using:
LOCK TABLES expenses READ, expense_categories READ;
4. Flush the tables (put table in its most current state):
FLUSH TABLES expenses, expense_categories;
5. Backup the tables to your “c:\temp” directory:
BACKUP TABLE expenses, expense_categories TO ‘c:/temp’;

– Note: This is yet another way to do backups.
6. Unlock the tables so that others may use them:
UNLOCK TABLES;

Don’t forget to do this step or no one else will be able access data!
7. Check your “c:\temp” file for MyISAM “MYD” files.

1. Login to the MySQL Monitor client.
2. Use the new “movies_db” InnoDB database:
USE movies_db;
3. Start a transaction:
BEGIN;
INSERT INTO directors (first_name, last_name) VALUES (‘Martin’, ‘Scorsese’);
INSERT INTO movies (title, director_id) VALUES (‘The Age of Innocence’, LAST_INSERT_ID());
4. Assuming you typed these correctly you should get no errors. If you get an error, fix and re-enter. To check the relate, type:
SELECT * FROM movies, directors
WHERE movies.director_id=directors.director_id;
5. If acceptable, commit these changes. Type:
COMMIT;

If not acceptable, you would type: ROLLBACK; and begin the transaction again.
6. Check that your changes are commited:
SELECT * FROM movies, directors
WHERE movies.director_id=directors.director_id;

1. There are a number of excellent graphical MySQL clients that make development and administration of MySQL much easier than if using the MySQL Monitor command line client. The best so far is “MyCC”, though it is still in development. Another, equally functional, but not so well designed is “MySQLGUI”. Both are free open source packages available for Windows and Linux, or for compilation on the platform of your choice. At this point, it is recommended that you use the “MyCC” client, which seems the most evolved.
2. [Note: Your instructor may give you different directions for this step.] To obtain a MySQL graphical client, go to http://www.mysql.com/downloads , and scroll down to “Graphical Clients” section. Click on the “MyCC” link, and download from the mirror site nearest to you. To install, extract the zipped archive to the directory where you downloaded it. Double click “Setup.exe” and follow the onscreen instructions, installing to the default directory c:\program files\MyCC” directory.
3. Go to “Start > Programs > MyCC” to launch the MyCC client.
4. To connect to the local MySQL server, right click in the “MySQL Servers” pane, and select new. Set properties like this:
Name: localhost
Host Name: localhost
User Name: Root
Password: mypass
Port: 3306

Note that a port is like a door to a computer that can be open or closed to bar access. The standard port number for MySQL is 3306. Check with your systems administrator to confirm this.
5. Click test to see if the connection is working. If so, you get a “Connection Successful” message. Click “Add” to close the dialog and add the server connection.
6. Right click on “localhost” and select “Connect”. Your MySQL server will open showing three areas: Databases, Server Administration, and User Administration.
7. Open the database folder by clicking the “+”. The default MySQL databases are shown (mysql, test) and the databases that you created are shown (accounting, accounting2, account_management, alpacas, catalog).
8. Double click the “accounting” database. The tables object is shown. Click on the “+” to see the database tables.
9. Click on each table to see its properties. Properties are: Field, Type, Null, Key, Default, Extra.
10. Click to select the “Clients” table. Double click the “client_id” field to get a detailed report about how that field is set up. When done, close the dialog by clicking on the “x” button for that window.
11. SQL Queries can be performed through the MyCC client. To do so, click to select the accounting > tables icon, and from the toolbar click the “SQL” button. A query dialog will pop up. Type:
SELECT * FROM clients WHERE client_id >= 2

Click the exclamation point button in the toolbar to execute a query.

The recordset for your query is returned. All SQL database and table operations can be performed this way. Repetitive queries can be saved by selecting “File > Save” and naming a text file.
12. Roll your mouse over the toolbar and find the “Query Type” button. This provides a very basic wizard to help you formulate your queries, providing the basic query syntax, that you then modify.

Security, User and Connection Management Using the GUI

1. Find a partner to work with.
2. Identify the IP network address of your machine and your partner’s machine. Open a DOS shell prompt and type: ipconfig

You are looking for the “IP address” information. Write both you and your partners IP down, and confirm these with your instructor.
3. Open a DOS shell and type:
ping <remote_ip_address>

This confirms that you have an internet connection to your partners machine.
4. Launch MyCC.
5. Right click on the “localhost” object you created in the last exercise, and select “Connect”.
6. Double click the “User Administration” icon and view the users for your server.
7. Add a new user by right clicking “User Administration” and selecting “New User”.
8. Set properties like this:
Allow access to “Accounting” should be checked.
User Name: webuser
Host: your partner’s IP address
Password: letmein
Privileges: Only “Select” should be highlighted. Note the way this widget works is funky.

Click “Add”, the “Close”. The new user appears in the user list.

Note that MyCC flushes privileges automatically. Now your partner can attempt to connect to your MySQL server.

1. Error logging is enabled by default. With a text editor, examine the file “c:\mysql\data\mysql.err”
2. To enable update tracking, stop the MySQL service or daemon.
Windows:
– Open Start > Settings > Control Panel > Administrative Tools > Services
– Stop the MySQL service
– Double click the MySQL service to open its properties.
– Click “Stop” to stop the service.
– Find the “Start Parameters” field, and type: –log-update
– Restart the service.
– Update logging is stored in the /data directory, and named <hostname>.00n, where n corresponds to the last log number.

Linux/UNIX:
– There are many different ways to do this on Linux, often which are distribution dependant. It’s probably best to look at you /etc/rc.d/init.d/mysql script to see how it is being started up, and either add the –log-update argument there, or elsewhere if the script is starting MySQL in some other way.

3. To see a change in the transaction log, enter the MySQL client:
mysql –u root –p
USE accounting;
INSERT INTO expense_categories VALUES (NULL, ‘Employee Benefits’);

Look at the log with a text editor, for example: localhost.001. You should see the update you just performed.
4. Note that the reason that update logging is not enabled by default is that it slows down database performance, sometimes considerably.
5. Note that update logs quickly can grow large. If your disk is filling up and you don’t know why, always suspect your logs.
6. Archiving your logs is a good idea. Why?
7. UNIX shell commands (like “tail” and “grep”) are very handy for looking at parts of large logs. These are available on UNIX/Linux or on Windows if you have the CYGWIN toolkit installed.

1. Create a new database. From the shell prompt, in the bin directory type:
mysqladmin -u root -p create accounting2
2. We can use the MySQLDump files as scripts to recreate our tables. These .sql text files are located in the “c:\temp” directory, where we dumped to in the backup exercise. Locate “c:\temp\expenses.sql” and examine it with a text editor. Close the file when done.
3. Recreate the expenses table. Type:
mysql -u root -p -D accounting2 < c:\temp\expenses.sql
4. Recreate the expense_categories table. Type:
mysql -u root -p -D accounting2 < c:\temp\expense_categories.sql
5. Recreate the remaining tables:
mysql -u root -p -D accounting2 < c:\temp\invoices.sql
mysql -u root -p -D accounting2 < c:\temp\clients.sql
mysql -u root -p -D accounting2 < c:\temp\users.sql
6. To see the results of your batch files on UNIX you can add the ” | more” command to view output.
7. Now that you have recreated the table structure, repopulate data from external text files. Remember that to import data, the target table must exist, and the text file that you are importing must have the same name as the table.
8. Data is often imported from an external text file. Before importing, check the formatting of the text file by opening “c:\temp\invoices.txt” in a text editor. Notice that a tab delimits fields. In your text editor (Notepad for example), find and replace the tab with a comma. You can use a fancy text editor like EMACS, VI, or TextPad to automate the replacement process.
9. Notice that the data in the last field has comma’s as part of the string. MySQL would see this as separate fields upon import, and the import would fail, so we need to escape the extra commas by inserting a backslash before them. When done save the file. It should look like: (see next page)

10. 1,4,2002-04-24,1902.34,Conjugation: verbs\, nouns\, adjectives.
2,4,2004-07-20,942.00,Technical writing.
3,2,2003-07-20,54.25,Hand wringing
4,3,2002-04-24,1.00,Miscellaneous Services
5,1,2002-04-10,654.34,Work\, work\, work.
6,1,2004-01-20,98754.00,Technical writing.
7,2,2003-07-18,54.25,Pacing.
8,3,2002-04-24,3210.84,Pondering
9,4,2003-04-08,6.64,Shady dealings.
10,4,2004-11-20,2.00,Brilliance.
11,5,2001-07-03,9.96,Don’t ask.
12,2,2002-04-22,315.94,Miscellaneous Services
13,3,2002-12-24,9751.94,Reading.
14,4,2002-07-20,321.55,HTML\, PHP\, MySQL Web development.
15,6,2001-07-27,2.55,Hand wringing
16,7,2003-10-24,64.64,Miscellaneous Services

Text editors like EMACS, VI, or TextPad are great at automating this sort of formatting. Such automation is called a “Macro”. Find a text editor and GET TO KNOW IT WELL! EMACS is the most powerful of many. HTMLKit, Homesite are others that you might consider using, but are not as powerful.
11. Enter the MySQL Monitor client and type: USE accounting2;
12. Load the external data into the invoices table. Type:
LOAD DATA INFILE ‘c:/temp/invoices.txt’
INTO TABLE invoices
FIELDS TERMINATED BY ‘,’;

Note that we are using a UNIX style directory delimiter here, the forward slash (/). MySQL does not care which you use, but for the sake of portability, consider using the forward slash.
13. Check that your import worked. Type:
SELECT * FROM invoices;
14. The trickiest part of loading external data is making sure your data format matches the table format. Spend time making sure that you external data text file is formatted correctly. EMACS is invaluable.
15. See the documentation for more examples of importing data.

Basic maintenance

1. Back up all data, in case something goes wrong during the repair and optimization process. To do this, stop MySQL, create a fold named <todays_date>, and manually copy the data directory there. Restart MySQL.
2. Determine which tables you want to analyze and repair.
3. cd to the bin directory: cd c:\mysql\bin
4. Run an analysis: myisamchk -a c:\mysql\data\accounting\expenses.myi
5. To repair a table: myisamchk -r c:\mysql\data\accounting\expenses.myi
6. Repeat steps for other tables.
7. Reload the tables into MySQL, so that MySQL sees the changes:
mysqladmin –u root –p reload
8. You can use wildcards. For example:
myisamchk –r c:\mysql\data\accounting\*.myi
9. Optimally, database maintenance is scripted and run from “Cron” or “Task Manager”. You might want to script this nightly or weekly. The larger the table, the longer the maintenance can take!
10. To optimize an individual table, log into the MySQL Monitor client:
mysql –u root –p
11. Select the database you want to work on. Type: USE accounting;
12. Optimize, a table at a time:
OPTIMIZE TABLE clients;
OPTIMIZE TABLE expense_categories;
OPTIMIZE TABLE expenses;
OPTIMIZE TABLE invoices;
OPTIMIZE TABLE users;
13. Such repair and optimization is limited to the ISAM table type.

Patching

Linux

1. First, you’re going to patch manually only if you installed from a .tar.gz and compiled mysql yourself.
2. Download the patch, place it in your source directory, and move to that directory.
3. Command: gunzip patch-name.tar.gz
4. Command: rm config.cache
5. Command: make clean
6. Now run config, make, and make install again.

1. Techniques described are the same on Linux/UNIX, Windows, and Mac OS X.
2. One way to find out where your MySQL data files are stored is to look at the MySQL variables. CD to your “bin” directory, and type: mysqladmin –u root –p variables

On Windows you can also launch “Winmysqladmin.exe” and click the “Variables” tab.
3. cd to your data directory. On Windows: cd c:\mysql\data or open Windows Explorer to look at this directory.
4. Each database has a directory, for instance you have a directory named: c:\mysql\data\accounting
5. cd into your “accounting”directory: cd c:\mysql\data\accounting. Type: dir
6. Notice that there are three files for each table that you have created. For example, for the clients table:
clients.frm, clients.myd, clients.myi
7. To backup your data, first stop MySQL (very important lest you corrupt your files)!
Windows: “NET STOP mysql”, stop from the Services Manager, or use Winmysqladmin.exe to stop the service.
Redhat Linux/UNIX: ./etc/rc.d/init.d/mysqld stop
8. Copy the “c:\mysql\data” directory to your archive. For example, on Windows, open Windows Explorer and copy the data directory. Paste it into your “My Documents\mysql_excercises\archives” folder, and rename it with the current date. Then, restart MySQL: NET START mysql
9. On Redhat Linux this procedure might be:
./etc/rc.d/init.d/mysqld stop
cd /usr/local/mysql
tar –cvf data.tar data
mv data.tar \home\username\archive\.

More fancy backups, with date incrementing, can be created using shell scripting like Perl or Python. Before you write one yourself, search the Internet, and you will probably find one you can modify. See the example in your scripts directory named: daily_backup_and_scp.pl.
10. Automate timed backups using Windows “Task Manager” or UNIX “Cron”.
11. Restart MySQL.
12. You can use mysql dump to back up your database structure, data, or both. Create a directory named “c:\temp” if it does not already exist. From the “c:\mysql\bin” directory, type:
mysqldump -u root -p -T c:\temp accounting

Then look in your “c:\temp” directory for the dump files. Table structures are saved with the “.sql” file extension. Data is stored with the “.txt” file extension. Open these with any text editor.
13. There are many more options for the MySQLDump utility. Check the manual!
14. You might want to run MySQLDump daily using Windows “Task Manager” or UNIX “Cron”.

4. Grouping functions allow you to group records by category and perform some minimal statistics on the category. For example: to find the maximum and minimum values of a group:
SELECT MAX(invoice_amount) FROM invoices;
SELECT MIN(invoice_amount) FROM invoices;
5. To determine how much has been spent under each expense category:
SELECT SUM(expense_amount), expense_category FROM expenses
LEFT JOIN expense_categories USING (expense_category_id)
GROUP BY (expenses.expense_category_id);
6. To see how many invoices have been billed to each client, using the COUNT() aggregation function:
SELECT COUNT(*) AS num, client_name
FROM invoices LEFT JOIN clients
USING (client_id)
GROUP BY (clients.client_id)
ORDER BY num DESC;
7. You can alter the preceding query to reflect how many invoices are tied into each total amount:
SELECT COUNT(*), SUM(expense_amount), expense_category FROM expenses
LEFT JOIN expense_categories USING (expense_category_id)
GROUP BY (expenses.expense_category_id);
8. To use the INSERT_LAST_VALUE function, begin by inserting a new user into the users table.
INSERT INTO users VALUES (NULL, PASSWORD(‘D\’oh’), ENCODE(‘homerjsimpson’, ‘hello’));

Question: What is happening in the password?
9. Retrive that user’s “user_id”:
SELECT LAST_INSERT_ID();

Note: This can be used programmatically to capture a new record’s primary key for insertion as a foreign key in another table.
10. DISTINCT can be used to remove duplicate records from your view. For example, instead of listing every client for every invoice, this query will list only every unique client:
This shows duplicates:
SELECT invoices.client_id, client_name FROM invoices, clients
WHERE invoices.client_id = clients.client_id;

This removes duplicates:
SELECT DISTINCT(invoices.client_id), client_name FROM invoices, clients
WHERE invoices.client_id = clients.client_id;
11. To count how many different DISTICNT clients have been billed:
SELECT COUNT(DISTINCT(client_id)) FROM invoices;