| Downloads |
|---|
| 41982.zip |
The database server market is anything but small these days. Among the many players, powerful and comprehensive open-source solutions are often overlooked. Yet several such servers compete head-to-head with commercial solutions in terms of performance and reliability.
MySQL is an SQL database server that has a dual commercial and open-source license. Essentially, if you distribute any products that use MySQL and that aren't open source (i.e., governed by the GNU General Public License—GPL), you need to license MySQL. Otherwise, MySQL is free regardless of whether you're implementing a massive network management system or deploying a Web application on the Internet.
Differences between MySQL and other solutions, such as Microsoft SQL Server, include MySQL's lack of foreign key support and the absence of support for advanced SQL constructs such as subqueries. (These features are slated for support in upcoming releases that are currently in the alpha stage.) But similarities also abound. For example, MySQL supports replication; database, table, and column security; ODBC; and scalability. MySQL also supplies command-line tools that let you work with the database server from the command line or within shell scripts, in addition to GUI management tools such as the MySQL Control Center (MySQLCC).
Installing MySQL on Your Server
To install MySQL, begin by grabbing the most recent production release—version 4.0.13 as I write this article—of MySQL for Windows from the MySQL Web site (http://www.mysql.com/downloads). After downloading MySQL, unzip the archive and, as Administrator, run setup.exe to install the MySQL program files to your computer. (You can run MySQL on Windows 95 and later, but I advise using Windows 2000 or later.) By default, the setup program installs MySQL to C:\mysql. If you're installing MySQL on a production system, strongly consider placing your data and log files on a dedicated drive as you would if you were using SQL Server. Using a separate drive for data and log files means that you won't need to worry about database growth consuming your main drive and lets you tune the file system to increase performance and disk-usage efficiency without affecting other applications.
After the installation is complete, you need to install the MySQL service. Start the WinMySQLAdmin program from the \mysql\bin subdirectory. The program asks you for a username and password. If you supply them, WinMySQLAdmin stores them in %USERPROFILE%\windows\my.ini for later use when you log in to the MySQL server. For installation purposes, however, you can simply click Cancel, and WinMySQLAdmin will open, install and start the MySQL service and will automatically minimize to the System Tray as a traffic-light icon.
Now, download MySQLCC (http://www.mysql.com/downloads/mysqlcc.html). MySQLCC is similar to SQL Enterprise Manager (SEM) in that it lets you create and drop (i.e., delete) databases and tables, specify columns and indexes, and define users and ACLs. Although MySQL comes with command-line tools, some of which you can use to manage MySQL or to dump and load databases and tables for backup purposes, administrators who are new to MySQL will find MySQLCC easier to use. (An even better tool is in the works: When MySQL Administrator becomes available, it will provide a visual interface for performing tasks such as creating databases and viewing replication status. To learn more about MySQL Administrator, go to http://www.mysql.com/products/administrator.)
In the mysqlcc-0.9.4-win32.zip distribution archive, run setup.exe or mysqlcc.msi to open the MySQL Control Center Setup Wizard. The wizard will install MySQLCC, which you can then access by clicking Start, Programs, MySQL Control Center, MySQL Control Center. When MySQLCC initially opens, it asks you to supply a name for your connection (e.g., My Connection), the host name (e.g., localhost, mysql.example.com), and login information for accessing your MySQL server. For the User Name and Password fields, specify root and a blank password, respectively. (The default password for the MySQL root user, which is similar to the SQL Server systems administrator account, is blank. To learn how to correct this security risk, see the sidebar "Predefined MySQL Accounts.") Next, click Add, then double-click your new MySQL server profile to open the connection.
Creating and Accessing a Database
Now you can do something useful: create a database. When you open your connection, you'll see MySQLCC's Console Manager window. Right-click the Databases icon, choose New Database, and enter the database name NetworkData. Later, I provide a script that uses this database to identify which servers need specific patches and to install the patches.
To create a table in the NetworkData database, double-click NetworkData, then right-click Tables and choose New Table. MySQLCC displays a window labeled Creating Table in database "NetworkData." The window contains three columns: Field Name, Allow NULL (which specifies that the field can contain a null, or empty, value), and Data Type, as Figure 1 shows. Because we'll use this database to track the patches that particular servers need, we'll store two values: the name of the server and of the application that it's running. Enter Computer in the first Field Name field and keep varchar as the Data Type. Enter App in the second Field Name field and again keep varchar as the Data Type. Click the Save icon, save the table as ComputerApps, then close the table-creation window.
Like any multiuser relational database management system (DBMS), MySQL relies on user accounts to specify access to databases, tables, and columns. Before you create accounts, you need to decide from which computers a user will connect. For example, if you're creating an account that has administrative control over a MySQL database and you want administrators to access that account only from the MySQL server console, you must create the account with only local machine access. To allow access over a network, you must specify one account for each remote client machine address, where the address is either the client's name or TCP/IP address. Alternatively, when you specify an account, you can simply use the percent sign (%) in the Host portion of the account. The % character acts as a wildcard and allows any remote machine to access the database. For example, say that Alice needs access only from server1 and server2 and Bob needs access from anywhere. Table 1 shows the accounts you'd create to provide the required access.
For the NetworkData database, you need to allow the user access from anywhere, so you'll create two accounts: networkdata_user@localhost and networkdata_user@%. To create the accounts, right-click User Administration in the MySQLCC window and choose New User. Enter networkdata_user in the Username field, localhost in the Host field, and a password, as Figure 2 shows. To specify that networkdata_user has access to the NetworkData database, select the check box next to networkdata in the Allow access to pane. Finally, click Add to create the user, then click Close. Repeat these steps to create another account with the same username, but specify % in the Host field.
Connecting to MySQL from Windows Applications
The next step is to access the NetworkData database from a Windows application, namely Microsoft Access. Being able to use multiple methods to connect to MySQL is convenient when you're building interfaces for use by people (a situation in which Access or even Visual Basic—VB—shines) or shell scripts (for which the MySQL command-line tools play an important role).
From a client machine, download Connector/ODBC (formerly known as MyODBC), the open-source ODBC driver for MySQL (http://www.mysql.com/downloads/api-myodbc.html). Click the link to the current production release, page down to the Windows downloads section, and download Driver Installer. (I used Connector/ODBC Driver Installer 3.51.06 for this article.) Next, run the installation program and follow the instructions.
Then, configure a Data Source Name (DSN). A DSN is a way to abstract ODBC database connections from the applications that use them. For example, in Access you might use the Database Connection DSN to connect to a SQL Server database. Later, you might replace SQL Server with MySQL, which would require you to reconfigure the Database Connection DSN. If you retain the name Database Connection for the DSN, however, you won't need to reconfigure Access as well.
To configure a DSN, go to Administrative Tools and open the Data Sources (ODBC) tool. Choose either User DSN or System DSN. (System DSNs are systemwide, whereas User DSNs can be used only by the logged-in user who created the DSN.) Click Add, MySQL ODBC 3.51 Driver, Finish. The resulting MySQL Connector/ODBC configuration screen contains several fields that you need to complete. For Data Source Name, enter a descriptive name for the DSN, such as NetworkData. For Host/Server Name (or IP), enter the MySQL server's host name or TCP/IP address. Type NetworkData in the Database Name field, and enter networkdata_user and the appropriate password in the User and Password fields, respectively. Before you continue, click Test Data Source to ensure that the connection is working.
Now you can access your NetworkData database tables in Access 2000. To do so, follow these steps:
- Open Access.
- Create a new, blank database.
- In the Tables object, right-click in the Tables window and choose Link Tables.
- For Files of type, choose ODBC Databases.
- In the Select Data Source window that appears, choose Machine Data Source to specify a DSN.
- In the Machine Data Source window, choose the NetworkData DSN.
- When Access presents the Link Tables window, choose ComputerApps.
- In the Select Unique Record Identifier dialog box, press and hold Shift while you select the Computer and App fields.
You can now use Access to edit the information in the ComputerApps table by building an Access form, as Figure 3 shows, or by double-clicking the ComputerApps icon in the Tables window. Tools such as Business Objects' Crystal Reports or Access's built-in reporting functionality let you build reports based on the NetworkData database as you populate its tables.
Using MySQL from the Command Line
One of the most powerful benefits of MySQL is the Mysql command-line tool, which installs by default in C:\mysql\bin and doesn't require an ODBC connection. You can use Mysql to do everything from locking database tables to inserting rows into and deleting rows from a table, all from the command line. You can use Mysql interactively or noninteractively (e.g., in a batch file), depending on how you invoke the command.
To run Mysql interactively, simply type the command in the command line and specify the host name, account information, and database that you want to use, as I did in the sample command-line session that Figure 4, page 66, shows. In the sample command, the -h option specifies the MySQL server's TCP/IP address or host name (e.g., mysqlsvr). The server defaults to localhost, so if you run the Mysql command from the MySQL server, you can omit this option. The -u option specifies the MySQL username, and the -p option indicates that you want Mysql to prompt you for a password. (For information about anonymous connections, which don't require passwords, see the sidebar "Predefined MySQL Accounts.")
Using MySQL in a Script
To use Mysql noninteractively (e.g., in a shell script), you use the -e option followed by the SQL statement to be executed and the -p option followed by the account password. The SQL statement in the following command uses the asterisk (*) wildcard to select all columns in the NetworkData database's ComputerApps table and prints the output that Figure 5, page 66, shows.
C:\mysql\bin>mysql -h mysqlsvr -u networkdata_user -p<password> -e "SELECT * FROM ComputerApps" NetworkData
In shell scripts, use the -B and -skip-column-names options so that Mysql doesn't list column names and print table-like output such as Figure 5 shows. These options reduce Mysql's output to tab-separated fields that shell scripts can easily parse. To learn more about the available Mysql options, you can simply invoke the Mysql command and specify the —help parameter.
A Patch-Management Script
Imagine that you have a large network of servers, each of which must be diligently patched. You've decided to deploy a custom patch-management solution using MySQL, shell scripts, and Qchain. You create the ComputerApps table and populate it with the OS and applications that are running on each server. Then, you use the shell script that Listing 1 shows to access this table.
To match the local server name with a server name in the ComputerApps table and retrieve a list of the applications that are installed on that server, the script performs an SQL SELECT statement that contains a WHERE clause, as callout B in Listing 1 shows. Most of the script's logic is in the For loop, which callout C shows. This code parses the list and calls the appropriate subroutine for each application (e.g., subroutine :IIS for Microsoft IIS). The subroutine determines whether the application requires a patch and applies the appropriate patch when one is needed.
This script is a skeleton implementation that patches Win2K and IIS. You can easily adapt the script to your site and expand it to apply patches—and even hardening scripts—to other software. To adapt the script to your site, locate the code that callout A shows. Change the value of the SVR variable from mysqlsvr to your MySQL server host name, change the value of the DB variable to specify your MySQL database, set the USER variable to the username, specify the user's password for the PW variable, and replace the PATCH_UNC variable's value with the path to your patch files. After you configure the script to run in your network, you can use Scheduled Tasks to execute it automatically, or you can execute it manually during administrative downtime.
As another example of how powerful even the simple NetworkData database can be, imagine running a set of internally developed post-server-installation scripts on a newly installed Windows Server 2003 system. These scripts would determine the local server's application requirements based on the server's name and the records in NetworkData, install and configure the listed applications, then run lockdown scripts (e.g., for IIS). Automating these tasks plays directly from any disaster-recovery plan and can be a boon to harried administrators faced with day-to-day server deployments.
More Flexibility, Lower Cost
MySQL can lower your licensing and operating cost while increasing flexibility and letting you centralize vital company information. Whether you use MySQL to build an enterprisewide intranet or to provide a centralized information store for your scripts, you're well on your way to realizing the benefits of this powerful and cost-effective database server.
