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.
Previous
Register
