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.
End of Article
Register
