If you followed the instructions in the November-December 2004 Uplink, a MySQL server should be running on your system or network, though you would hardly know it. The only evidence you'll see is an entry for mysqld or mysqld-nt in the Windows Task Manager processes tab.
So how do you run MySQL and work with data?
MySQL is different from a standalone database manager like Microsoft Access. The programs (or clients) that you use to connect to the database engine (or server) are separate.
Technically speaking, any software interface used to connect to a database is a client – that includes Web pages or even the DOS command line. In order to set up our newsroom database system, we want something a little more specialized: a client to help manage our server, data and system users.
Some database servers come packaged with an official client (Enterprise Manager, for example, in the case of Microsoft SQL Server), but not MySQL. Due to the explosion of interest in MySQL, there are plenty of excellent options.
Many of the best are free, open source or very inexpensive shareware. If you have the money, Navicat (www.navicat.com) is worth it every penny of its $100 price for its unparalleled speed, stability, import filters and feature set.
MySQL will import ASCII text files at mind-bending speeds
We'll continue building our system with a pair of newer no-cost entries – MySQL Administrator and MySQL Query Browser – from the company that makes MySQL. The former program is designed for administering the database and the latter for running queries. Download both from http://dev.mysql.com.
After downloading, install the programs and accept the defaults. Launch MySQL Administrator. Enter "root" for the user name and then the password that you selected during the initial installation of MySQL.
If the host field is empty, enter "localhost" if you are working on the computer that's running MySQL. If you're not, type the IP address of the server. The port field should default to 3306, but if it's empty you can enter that. Click OK and you should be greeted with a page showing some basic information about your MySQL installation.
You can use this interface for most of the tasks necessary to keep your MySQL data warehouse up and running. Many of the functions available through the interface are helpful in enhancing the program's performance. That's outside the scope of this article, but you can learn more by reading the reference manual at http://dev.mysql.com/doc/administrator/en.
Using this interface, you can do just about anything you need to do to keep your MySQL data library up and running. Most of the functions are designed to help tweak MySQL and get the most performance possible, and much of that is beyond the scope of this article. MySQL Administrator is extremely well documented: http://dev.mysql.com/doc/administrator/en.
Security first
One of the big advantages of moving your newsroom data from standalone Access or Microsoft FoxPro databases into a centralized MySQL database system is you can better regulate security. As newsrooms gather more and more and more reporters start doing their own data work, maintaining your newsroom database collection can become a logistical nightmare. More importantly, when anyone can open, change, delete, update and edit data, it becomes impossible to ensure the integrity of the data.
MySQL, like all database servers, allows you to control who has access to which databases, and what they can do with the data. You can give certain users access to only certain databases, specific tables within those databases, or even particular fields.
You can use MySQL Administrator to easily create, manage and administer user accounts by pointing and clicking. (See Chapter 9 of the user manual for detailed instructions). You can also perform these functions with the MySQL command line (see http://dev.mysql.com/doc/mysql/en/User_Account_Management.html). You can create a user-permissions scheme to reflect the expertise groupings among your newsroom staff and assigned shared users names to these groups.
Connecting users
Once you have your user scheme in place, you can connect users with a client program designed for end users. There are a number of excellent client programs available; Query Browser (above) is good for making a more robust connection to the database, though the program is still in early development. In addition, users must run queries using Structure Query Language. (For other options, read "Putting a useful face on MySQL" in the May-June 2003 Uplink).
To connect users using one of the many MySQL-specific clients, follow the same procedure you did with Administrator. Enter the host name of the server (usually "localhost," or an IP address), as well as the user name and password – that's it.
But the easiest client for users initially is one you probably already have on your newsroom computers: Access. After going through a simple setup, users can link to data tables stored in MySQL and then write and save queries on those tables just like any other Access table. The only thing users cannot do from Access – and this is a problem with Open Database Connectivity (ODBC), not MySQL – is create or alter a table in any way. That is an operation that must be done through Query Browser or another MySQL-specific client.
To use Access as a front end, first you will need to make sure you have the proper drivers installed for each user who wants to connect to MySQL using Access. Here's how:
• Open Windows Control Panel and go to Administrative tools\Data sources.
• Click on the drivers tab and look for MySQL ODBC driver. If it is there, skip the next two steps.
• If the driver is not there, download it from: www.mysql.com/products/connector/odbc.
• Install the driver on all PCs that will use Access to query data stored in MySQL.
Then you will need to configure the ODBC driver:
• Open Windows Control Panel and go to Administrative tools\Data sources.
• Double-click on the Data sources icon and then select either the user (data source name) or system DSN tab (either will work, though you may not be able to save system DSN connections, depending on Windows security settings.
• Click Add and then find the MySQL driver, and double click on it.
• In the dialog box that opens, give your connection a name that your users will be able to understand. Enter the IP address of the server, or "localhost" if MySQL is running on the local machine. Enter the name of the MySQL database that you want to connect to. By default, MySQL installs two databases: MySQL and Test. MySQL is the database that contains all the user, password and login information – never, ever, ever give anyone access to this database. Choose Test.
• Enter the user name and password you would like this user to log in under (one of the accounts we created earlier) and click the test data source button to ensure the connection works. If it doesn't, you might have to go back to Administrator and grant that user account specific privileges to that database, done under the "Schema Privileges" tab in the user accounts section. Once you've successfully connected, your users can begin using tables within the database.
Now the user can use Access' File\Get external data menu item to link to or import the MySQL tables. For example, to import:
• Select Import from the menu. • Select ODBC databases in the Files of type list that appears in the dialog box that opens. • Navigate to the DSN list and select the desired data source name
Data in
Now that we've connected to the database, we can put data in it.
There are a number of ways to get data into a MySQL database. The easiest is to export tables from Access, for data tables with 500,000 or fewer records:
• Right click on the table in Access and select Export. • Choose ODBC as the file export type • Click OK in the next dialog box • Select the DSN into which you wish to import
Many of the clients available for download or purchase have data import fil-