Taking Care of Business with MySQL Administrator 335

The mysqlhotcopy utility copies only MyISAM tables, not InnoDB or BDB tables. Using mysqlhotcopy is less intrusive than taking your database offline, but it can slow things down if you’re backing up a busy database. (mysqlhotcopy has to wait for the READ lock on all tables before it proceeds and blocks all table modifications until it finishes.)

Archiving a Replication Slave

Another very useful alternative to mysqlhotcopy is replication. When you replicate a database (or a table within a database), the replication slave maintains an exact copy of the data stored in the replication master.

To back up your data, simply shut down the slave and use a file system backup tool to archive the slave database. While your slave database is offline, users can still read and modify the master. Then when the slave comes back online, the replication mechanism automatically applies any changes to the slave database. When you back up a replication slave, you must archive the $MYSQL/master.info and $MYSQL/relay-log.info files, too.

Taking Care of Business with MySQL Administrator

MySQL Administrator is a graphical interface that helps you manage your MySQL server. It’s a very young tool (in fact, it’s still in alpha-release land), complete with all the bugs you would expect from a program at this stage of maturity. It does show a ton of promise though, and it’s worth getting familiar with. We think it has a great future as it grows and develops.

The official voices at MySQL say that this tool will not work well with the release-3 servers — but only a few of the features don’t work.

Fedora is currently distributed with release-3

servers but should be stepping up to release-4 servers any day now. SuSE and Mandrake ship with MySQL release 4.

You can run release-4 MySQL servers on Fedora, but they’re not standard issue. However, they should become standard equipment soon.

This is alpha-release software. It seems pretty stable, but it shouldn’t be used in a production environment yet (that is, with really, really important data). It’s a good product to get familiar with though — just keep lots of backups of any data dear to your heart or business.

Installing MySQL Administrator

To install MySQL Administrator, follow these steps:

1. Open a browser window and surf to

www.mysql.com/downloads/

2. Scroll down the page to the Graphical Clients section and click the MySQL Administrator link.

3. Scroll down the MySQL Administrator page and click the Pick a Mirror link next to the Linux download labeled Linux (x86, glibc 2.3, stripped).

The nonstripped version contains debug information and other stuff you most likely don’t need, and it will just slow down the download.

4. Scroll through the mirror sites to find a download location geographically near you. Click the HTTP link to start the download.

5. Save the download to your home directory

(or /tmp — you won’t need the tarball after you’ve installed it).

6. Close the download manager and the browser.

7. Open a terminal window and give yourself superuser privileges with the su command.

8. Unpack the tarball with the following command:

# tar -C / -zxvf mysql-administrator-

1.0.2b-alpha-linux-stripped.tar.gz

336 Technique 44: Making a MySQL Server Your SQL Server

The installation instructions on the MySQL Administrator Web page are wrong at the time we’re writing this. If you’ve downloaded version 1.0.2b-alpha, use the preceding command to install the program in the correct location.

Starting MySQL Administrator

After you install MySQL Administrator, you’re ready to start the program and begin using its tools. To start the program, follow these steps:

1. Move to the directory containing the program:

#cd /opt/mysql-administrator/bin

2.Start the program with the following command:

#./mysql-administrator

Add /opt/mysql-administrator/bin to your shell’s search $PATH, and you’ll be able to start the program from anywhere on your system with just the program name. See Technique 7 for details about updating $PATH.

The MySQL Administrator connection dialog opens, as shown in Figure 44-1.

• Figure 44-1: The MySQL Administrator connection dialog.

3. Enter the connection information for your

MySQL server — a user name, password, and host name — and then click the Connect button.

The MySQL Administrator window opens to the Server Information page, displaying information about the connection, the server version, and the client (see Figure 44-2).

• Figure 44-2: The MySQL Administrator opening window.

Exploring MySQL Administrator’s tools

The panel on the left side of the MySQL Administrator window contains menu names for the different features that MySQL Administrator can help control. Highlight a menu name to view (and change) a set of features. Here’s an overview of the tools you’ll find:

Service Control: Click Service Control in the menu panel to open the Service Control menu. From this menu, you can monitor the startup log messages. You can also start and stop the server by clicking the Stop the Server (or Start the Server) button.

Startup Variables: Click Startup Variables to change the configuration parameters for your

Taking Care of Business with MySQL Administrator 337

MySQL server. The Startup Variables menu has a series of tab controls that group the configuration parameters into manageable chunks, as shown in Figure 44-3.

• Figure 44-3: The Startup Variables menu.

With the tabs on this menu, you can adjust many configuration parameters, including the following:

General Parameters: On this tab, check the Disable Networking box to restrict access to your MySQL server to those clients on the same machine as the server.

Log Files: On this tab, click the Slow Queries Log box to generate a log file that displays “slow” queries. If you find that performance is lagging, this tells you which queries are consuming the most resources.

Security: On this tab, check the Make All Tables Read-Only box for your replication slave servers. You shouldn’t modify replicated tables from slave servers.

Check out all the tabs and the options they include to find new ways to customize and optimize your MySQL server.

User Administration: Click User Administration in the menu listing to open the User Administration menu. MySQL user accounts are listed in the lower-left frame of the window, as shown in Figure 44-4.

• Figure 44-4: The User Administration menu.

To create a new MySQL user account, click the New User button. Complete the user information and then click Apply Changes. Use the Schema Privileges and Resource Limits tabs to view

(or modify) user privileges.

If you’re running a version of MySQL younger than version 4.0, you’ll get an error message when you modify user information. Ignore the message and consider upgrading to a more recent version of MySQL.

Server Connections: Click Server Connections to view all active connections and see what users are up to. The User Connections tab displays a detailed list of user sessions. Highlight a user name to display the active threads for that user, as shown in Figure 44-5.

If you have runaway (or really slow) queries, you can kill them off on the User Connections tab with the Kill Thread button, or terminate all threads for a single user with the Kill User button. Just highlight the thread or user name and click the appropriate button.

Health: Click Health in the left panel to access graphs of performance and server statistics for your MySQL server. The Health menu opens, displaying a trio of Connection Health graphs, as shown in Figure 44-6.

338 Technique 44: Making a MySQL Server Your SQL Server

• Figure 44-5: User and thread information on the User Connections tab.

• Figure 44-6: Server Health graphs.

Every MySQL server maintains a set of status variables that track performance and usage statistics. Click the Status Variables tab to see the server status variables. Highlight a group in the Status Variables column to view the variables within that group, as shown in Figure 44-7.

Click the Server Variables tab to view the configuration parameters for your server. Highlight a group in the Server Variables column to see the configuration parameters in that group, as shown in Figure 44-8.

•Figure 44-7: Status variables for a MySQL server.

Server Logs: Click Server Logs in the menu panel to open the log viewer (the log viewer does not appear to work with a version-3 MySQL server). This feature needs a bit of help right now, but we’re sure the problems will be worked out.

• Figure 44-8: Configuration parameters for the MySQL server.

Backup: The Backup menu lets you create (and execute) backup profiles. With this menu, you can define jobs that archive all or part of your server data. Click the New Profile button and enter a name for the backup profile in the Project Name field. Highlight a database name in the

Taking Care of Business with MySQL Administrator 339

Databases column and click the right-arrow button to include that database in the Backup Content pane. Expand the database into a tree control form by clicking the arrow next to the database name. Check the boxes next to each table name to include (or uncheck the boxes to exclude) the tables in the backup (see Figure 44-9).

• Figure 44-9: Creating a new backup profile.

When your profile is complete, click the Save Project button. To run the backup, click the Start Backup button.

In this technique, we’ve given you other, dependable ways to back up. They’re not graphical, but dependability counts when you’re making backups. Until this product matures a bit, we recommend sticking to tried and true backups for data you treasure.

Restore Backup: The Restore Backup menu selection opens a dialog designed to restore from archives created with the Backup menu. For now, we recommend that you use more dependable methods to back up and restore.

Replication Status: The Replication Status menu selection opens a dialog displaying information about the state of your replication topology. (We cover MySQL replication features earlier in this technique.)

Catalogs: The Catalogs menu selection displays information about the databases and tables defined on your MySQL server.

The creators of MySQL Administrator are a bit loose with their labels. The Catalogs and Schemata labels both refer to what are normally called databases.

Highlight a database name in the Schemata frame to see the tables stored in that database. Highlight a table name in the top panel to display the columns within that table, as shown in Figure 44-10.

Use the tab controls in the lower panel to view information about the Columns, Indices, Table Status, and Row Status.

• Figure 44-10: Table information displayed with MySQL Administrator.

MySQL Administrator should grow up to be a nifty timesaving tool. If you’re just starting to use MySQL, you may want to avoid the frustration (and slowdowns) caused by the bugs and skip the installation of the version distributed with Fedora (3.x). You can download

the RPM packages that you need to install MySQL version 4.0 or better from www. mysql.com/downloads.

45	Safeguarding Your
	Apache Server with
Technique	SSL Certificates

Save Time By

Generating your own SSL certificate

Signing your own SSL certificates for private use

Viewing the CAs that your browser currently trusts

SSL (Secure Sockets Layer) is a security protocol that assures users visiting your Web site that you are who you say you are. OpenSSL is a Linux software package that implements the SSL protocol. If you’re

running a Web server that handles financial transactions or sensitive documents, an SSL certificate tells your customers or colleagues that their information is really in your hands, and not in the hands of some troll who has intercepted the transaction.

To use SSL security at your Web site, you need a signed SSL certificate. Your Web server (typically Apache) sends your certificate whenever a browser requests a secure connection. You can purchase a signature from a trusted authority, or you can obtain a signature from an intermediate authority to generate a certificate that’s part of a chain of trust. In a chain of trust, a high-level certification authority entrusts an intermediary who in turn signs your certificate, guaranteeing that you’re reputable.

You can also sign your own certificates by becoming your own certificate authority without a chain of trust. Your certificate may not convey enough trust for consumers to send in their credit card numbers, but within a circle of colleagues, having an active certificate is an assurance that their transactions are being handled with respect.

In this technique, we explain the trust levels associated with each type of certificate. We also show you how to request a signed certificate from a trusted certificate authority, how to create a self-signed certificate that you can use to test your Web server configuration, and how to create your own certificate authority. You’ll save time and assure your colleagues and customers that they really are in contact with your computer and not an interceptor.

Understanding the Basics

of How Certificates Work

In geek-speak, a certificate is a document that proves your identity. Certificates are like passports in that they contain information about