Critical Skill 6.1 Use MySQL Administrator


MySQL Administrator is a visual console that allows you easy access to a variety of administrative tasks , as well as performance and data structure information. It provides a point-and-click environment for speedy and simple command access, as well as a real-time visual interpretation of your database s performance.

Note  

This section refers to MySQL Administrator version 1.0.3 Alpha. Because of the rapid development in the open -source environment, you may download a later version.

The Alpha designation means that this is a new product from MySQL AB that has only been tested on a limited number of machines. The likelihood of undiscovered bugs in the code means that you should back up all important data before using MySQL Administrator in a production environment. If you find a bug in the program, you can participate in the open source development cycle by reporting it to MySQL AB as described on their Report a Bug page at http:// bugs .mysql.com/.

As shown in Figure 6-1, MySQL Administrator has three components in its basic layout: a menu bar, a sidebar list, and a main window display. As you would expect, the menu bar hosts the program s drop-down menus . The sidebar lists the available information displays, and the main window to its right shows the information for the item selected in the sidebar.


Figure 6-1: The MySQL Administrator opening window

Choosing MySQL Administrator Menu Options

Five drop-down menus are available from the MySQL Administrator menu bar:

  • File This menu has five options, which may or may not be available, depending on the information shown in the main window. The first three options allow you to open a new connection to a database, manage your database connections, and save the current database connection. Choosing the Manage Connection or Save Connection option opens the Connections page of the Options dialog box, which is discussed in the next section. The final two options are Export Page, which is only available when applicable and Close, which shuts down the MySQL Administrator tool.

  • Edit This menu has an item to open the Options dialog box, which is discussed in the next section. It also offers the standard Cut, Copy, and Paste functions.

  • View This menu provides options for opening the same information displays listed in the sidebar, which are discussed in the Viewing Information in MySQL Administrator section.

  • Tools This menu has two options: one to access the MySQL command-line and another to access the standard command-line window for the OS on which MySQL Administrator is installed (as you have been using in previous modules to gain access to you MySQL database). The MySQL Command Line Client option opens a command-line window where the mysql command has already been input. The current connection operating in MySQL Administrator, for instance root , is entered as user , allowing you to simply type in the corresponding password and gain access to the MySQL Client.

  • Help This menu has four options. You can open a searchable Help document in a separate window, report a MySQL Administrator bug should you find one, and launch a new browser window to go to the MySQL AB web site. In addition, of course, you ll find the About option, which gives you version and proprietary information about the MySQL Administrator program you are using.

Setting MySQL Administrator Options

MySQL Administrator s Options dialog box offers options in three categories: Administrator, General Options, and Connections. This dialog box appears with the Connections category options when you choose either File Manage Connection or File Save Connection, as shown in Figure 6-2. You can also open the Options dialog box by selecting Edit Options. You can adjust the settings in the Options dialog box to control some aspects of MySQL Administrator. The buttons at the bottom of the dialog box allow you to apply or discard changes you ve made, and to close the dialog box.


Figure 6-2: The Connections category of the Options dialog box

Administrator Options

The Administrator Options category of the Options dialog box allows you to make two choices: whether or not to show the global privileges and the table/row privileges within the displays in the MySQL Administrator program. Any changes you make can be either applied or discarded, so you can experiment without making actual changes to your database.

General Options

The General Options category of the Options dialog box has four option areas:

  • Applications Startup This section allows you to choose whether to show the tip of the day and whether to store window positions .

  • Language This section theoretically allows you to choose the language MySQL Administrator runs in; at this time, it lists only English.

  • Connection Passwords Storage This section has a check box to indicate if you want the connection passwords stored and a drop-down list that allows you to choose either plain text or obscured formatting for those stored passwords.

  • Font This section allows you to choose another font and font size in place of the default MS Sans Serif 8 used by MySQL Administrator, and also to choose a different character set from the ones installed on the server.

Connection Options

The Connections category of the Options dialog box (see Figure 6-2) shows all the stored connections and allows you to access and edit them, as well as view their histories. You can also add and save new connections from this dialog box.

Note  

Connections are automatically added to the History file, but you must intentionally save them by clicking Apply Changes in order for them to remain in the Connections file.

You can edit the connection in both the Connections and History file by using the fields in the Options dialog box: Connection (the name of the connection), Username, Password, Hostname, Port, Type (connection protocol), Schema, and Notes. In the Notes field, you can add notes to clarify the intended use of a specific connection. On a database with more than one administrator, adding notes can take the guesswork out of the reasoning or purpose behind a connection or connections. This can also be helpful for clarifying your intentions for future administrators.

Note  

MySQL Administrator ignores the Schema field because other MySQL GUI programs use that field.

The Advanced Parameters tab in the Connections category of the Options dialog box allows you to choose a compression protocol, return the number of found rows instead of affected rows, ignore spaces after function names (which effectively makes them reserved words), enable LOAD DATA LOCAL handling (which allows a user to access files on the local machine that the server has read access to, raising obvious security issues), and set the number of seconds a connection can be inactive before it is disabled.

Some of the early versions of MySQL Administrator do not have the Advanced Parameters tab enabled. To access these settings in those versions, go to File New Instance Connection , and then click the Details button in the Connect to MySQL Server Instance dialog box, as shown in Figure 6-3.


Figure 6-3: The Connect to MySQL Server Instance dialog box with details displayed

The following three paragraphs give you a frame of reference for the Toolbar links. Take a moment to familiarize yourself with their location and dropdown menu contents.

The Toolbar Edit dropdown, as mentioned previously, has a link to the Options window. It also has links to the standard Cut, Copy and Paste functions. The View dropdown has text links to all of the Icons listed in the Sidebar Index and those displays of information will be discussed in the Basic Areas of Information section immediately following this one.

The Tools dropdown has two links: MySQL Command Line Client and a standard Command Line Window for the OS that MySQL Administrator is installed on. The MySQL Command Line Client link opens a command line window where the mysql command has already been input. The current connection operating in MySQL Administrator, for instance root , is entered as user, allowing you to simply type in the corresponding password and gain access to the MySQL Client. The other link opens a normal Command Line Window such as you have been using to gain access to your MySQL database previously in these lessons.

The final dropdown link on the Toolbar is Help. Help has four links allowing you to pull up a searchable Help document in a separate window, report a My SQL Administrator Bug should you find one, launch a new browser window to go to the MySQL AB web site, and of course, the About link, which gives you version and proprietary information about the MySQL Administrator program you are using.

Viewing Information in MySQL Administrator

To view information in MySQL Administrator, select the area of interest in the sidebar, and the information will appear in the main window on the right. Most of the areas of information have more than one tab in the main window. The following sections describe each area of information.

Server Information

The Server Information display (see Figure 6-1), provides four areas of information:

  • Server Status Whether the MySQL server is running or disconnected.

  • Connected to MySQL Server Instance The username, host name, and connecting port by which the active instance of connection to the server had been made.

  • Server Information The version of MySQL running on the server, along with its network name and IP address.

  • Client Information The client version, network name, and IP address, as well as the OS it is running on and the hardware s CPU information.

Service Control Information

The Service Control information display has two tabs: Stop/Start Service and Configure Service. For security reasons, Service Control functions work only if you are connected to MySQL via the localhost. That is, you must be physically using the computer on which your MySQL Server and MySQL Administrator are installed; you cannot be logged in from another computer on a network or from a remote location. This means that only someone logged in to the server that the database is hosted on can change these functions, or start or stop the service.

Start/Stop Service Tab The Start/Stop Service tab, shown in Figure 6-4, has an icon and text indicating whether the service is running or stopped . It also has a single button, which either starts or stop the service, depending on the current condition. An animated progress bar indicates that the starting or stopping process is occurring, and it disappears when the desired state has been reached. The bottom part of this tab contains a Log Messages window, where all the messages sent during startup or shutdown are displayed.


Figure 6-4: The Service Control display with the Start/Stop Service tab selected

Configure Service Tab The Configuration Service tab, shown in Figure 6-5, is divided into three areas:


Figure 6-5: The Service Control display with the Configure Service tab selected
  • Service Settings This area has a check box that, if checked, allows MySQL to start up automatically whenever the server it is running on starts. It also allows you to edit the name that the computer uses to refer to the program and add a description of that service if desired.

  • Configuration Files This area allows you to see and edit the path to the MySQL configuration file, and edit the name of the configuration file section used for the current instance of the server. Unless you are running multiple servers, it is doubtful you will need to change the Section Name setting here.

  • Server Features This area consists of four check boxes and an editable display of the path to the binary executable file that starts the service. The four options the check boxes allow you to choose are support for InnoDB, support for BDB, allowing named pipes, and enabling debug information. The use of named pipes on anything other than a server making connections on a localhost is not recommended.

Caution  

Using Debug Information causes the server to slow down and should only be used when debugging is really needed. If you enable Named Pipes, the Debug Information option is not available.

When you select the Apply Changes button to save changes you ve made on the Configure Service tab, the changes do not take effect until the next time the server is shut down and restarted.

Startup Variables Information

The Startup Variables information display, shown in Figure 6-6, allows you to view and alter a number of the variables that MySQL reads at startup from the configuration file (my.ini or my.cnf). The variables are presented on nine tabs. You can make changes to the configuration file here, instead of opening the file in a word processing program and changing it by hand, which can cut down on typos. For security reasons, the Startup Variables functions can be accessed only if you are connected via the localhost. This way, only someone logged in to the server on which the database is hosted can change the startup variables.


Figure 6-6: The Startup Variables display with the General Parameters tab selected

As with the Configure Service tab in the Server Control information display, any changes you make here do not take effect until the server is stopped and started again (because the configuration file is read only when the server is started).

General Parameters Tab The General Parameters tab (see Figure 6-6) has three areas:

  • Networking This section allows you to disable networking, which means that the server will no longer accept TCP/IP connections. It also allows you to set the TCP port to a different number than the default.

  • Directories This area allows you to specify or edit the path for three directories: Base, Data, and Temp. The Base directory is where the installation directory for MySQL is located. The Data directory is where the data for MySQL is located. The Temp directory is where MySQL stores temporary data. This option can be used to store data on a drive separate from the executable program to increase storage space or spread the hardware failure risk factor.

  • Memory Usage This area is used to set the key buffer and sort buffer sizes. Both of these values affect how indexes are handled. Altering them in keeping with your computer s parameters can enhance index handling, as well as enhance the REPAIR command and index creation when using the CREATE INDEX or ALTER TABLE command.

Note  

For advice on when to change buffer sizes, see the Ask the Expert section in this module.

MyISAM Parameters Tab The MyISAM Parameters tab has three areas:

  • General This area has two options. One allows concurrent inserts in MyISAM tables. The other allows external locking, which would enable you to test (but not repair) MyISAM tables on a running MySQL Server, by using the myisamchk script (discussed in Module 8).

  • Fulltext Search This area allows you to change four parameters related to fulltext searches: minimum word size, maximum word size, query expansion, and the path for a Stopword file that is created rather than using the built-in one.

  • Advanced Settings This area has four options. The Block Size setting refers to MyISAM index pages. The Extra Sort File Size setting refers to the point where MySQL decides to use the slower but safer key cache index create method. The Max Sort File Size setting refers to the maximum size that a temporary file made while creating an index can reach before MySQL decides not to use the faster sort index method to create an index. The Number of Repairs setting refers to the number of threads that MySQL can use when repairing a MyISAM table.

Ask the Expert

When should I change the size of my key and index buffers?

One of the reasons MySQL is so popular is that its default settings are adequate for most users and applications. The power and speed of MySQL make up for systems with scarce resources; however, if you encounter performance issues, you might consider changing the key_buffer_size setting.

By default, 8MB of RAM is allocated for key and index buffers. MyISAM tables use this setting; InnoDB uses the innodb_buffer_pool_size for its indexes. You should note that if you set both sizes, the system will allocate both, whether you use it to create indexes or not. So, by default key_buffer_size and innodb_buffer_ pool_size are set to 8MB. The two settings take 16MB of RAM from the system and reserve that space for their own use, even if it is never used. This may not be a problem for newer computers, as most have enough memory that 8 to 16MB will probably not be missed. The larger the setting, the larger the indexes you can keep in memory, and this makes searching the tables to which those indexes refer faster.

However, as your application grows, so does your need for large buffers. MySQL recommends 25 percent of physical RAM as a rule of thumb for key buffer allocation. If you have 1GB of RAM, it is not unheard of to allocate 256MB for the key buffer setting. Setting your buffer size too large has consequences though. Performance may decrease because the computer is expending too many resources on performing memory management.

This is where the MySQL Administrator health monitors come in. Use these monitors to check resource usage during your database's most stressful periods. If your resources are not regularly approaching 80 percent, then leave the default settings unaltered. Simply monitor your system on a regular basis (weekly, monthly, or even quarterly, depending on how quickly the size and demands on your database changes). Don't be afraid to experiment with adding more memory to your buffers and then monitoring how significantly, if at all, your system is impacted

 
Note  

Setting the value of Number of Repairs to 1 disables parallel thread repair, which may result in a slower repair process.

InnoDB Parameters Tab The InnoDB Parameters tab has a variety of areas dealing with the options available when using InnoDB tables. The check box at the top of the tab allows you to deactivate InnoDB tables. As mentioned in Module 2, if you have decided that you are not going to use InnoDB tables, deactivating them saves space, frees allocated memory, and speeds up the startup process.

Performance Tab The Performance tab allows you to alter four options dealing with query cache:

  • Query Cache Limit This option sets an upper limit to the size of result that is cached. Any result larger than the limit is not cached.

  • Minimal Size of Result This option sets the smallest unit into which results are cached.

  • Cache Size This option refers to the amount of allocated memory set aside to hold results from old queries.

  • Cache Type This option has a dropdown menu that allows you to choose from several query cache types or no result caching at all.

Log Files Tab The Log Files tab has four areas:

  • Activate Logging This section allows you to enter a log name for any or all of the six types of logs listed: Binary, Query, Error, Slow Queries, Update, and ISAM. If the logging functions are turned on and there are no names entered, MySQL will use default names for each of these log types.

  • Binlog Options This section allows you to name the file where the last binary log files are stored and to set the maximum binary log size, which signals MySQL to rotate to a new log file when this size is reached. Rotating a log means that the current full log is saved under a different name, and the current log file is then emptied and ready to fill again.

  • Slow Query Log Options This section allows you to set the number of seconds MySQL uses to determine whether a query has taken too long and is deemed to be slow. An entry is written into the Slow Query log every time a query takes longer to execute than the number of seconds set.

  • Advanced Log Options This section allows you to specify if you want MySQL to log queries that are executed without using indexes, to log noncritical warnings, or to log only in short format, which filters out extra information sent to the Update and Slow Query logs. It also allows you to set the number of days after which MySQL will rotate expired logs.

Replication Tab Replication is an automated process where a main server, called the master , replicates specified data onto another server or servers, called slaves . You can set up replication in order to maintain more than one copy of the data on a running server, to protect against catastrophic hardware or software failure. The Replication tab offers a wide variety of settings for both the master and slave computers that control the replication process.

Networking Tab The Networking tab allows you to set and/or edit variables that deal with accessing MySQL over a network. The five areas it covers are General, Memory, Timeout Settings, Advanced, and Naming. Fine-tuning these settings on a busy network can remarkably enhance the performance of your MySQL database. If you are logged in to your database as root, then you are by default the system administrator of the MySQL server; however, if your database exists in a corporate network environment, then it is advisable to consult with the network s system administrator before altering any of these settings. For the vast majority of users, the MySQL default settings will be the best choice unless slow performance issues arise.

Security Tab The Security tab allows you to turn on or off seven security-related functions:

  • Safe User Create This option prohibits a user who does not have write access to the mysql.user table from creating new users.

  • Disable Grant Tables This option allows MySQL to start without accessing the grant tables, which results in all users being given full access to all tables (see Module 8).

    Tip  

    If you forget the root password to a MySQL database, restarting it without the grant tables ( --skip-grant-tables ) allows you to establish a new root password. See Module 8 for details.

  • Make all tables read-only This option allows visual access to the tables without endangering data integrity. The only exceptions are replication threads and users with SUPER privileges.

  • Deactivate SHOW DATABASE Commands This option prevents users from accessing any database they do not already know the name of.

  • Use Old Passwords This option switches the password encryption to the old, pre-version 4.0 methods , which is sometimes necessary when importing data from an older version of MySQL.

  • Secure Authentication This option prevents access to any user whose password is encrypted in the preversion 4.1 methods.

  • Enable LOAD DATA LOCAL INFILE This option allows you to turn on or off the permission to load data from a file in this manner.

Advanced Tab The Advanced tab has five areas:

  • Localization These options allow you to set the language, the default character set, the default collation, and a path to a character set directory.

  • Thread Specific Settings These options allow you to fine-tune threads to improve database performance.

  • Insert Delayed Settings These options allow you to fine-tune insert operations to improve database performance.

  • General These options cover a range of advanced settings. You can print a symbolic stack trace on a failure to flush tables, or set a specified interval for MySQL to flush all tables. You can set up a signal to aid in debugging. You can set a lower priority for INSERT , DELETE , and UPDATE commands than for SELECT commands. You can specify a memory lock, which rules out swapping. You can use the old LOAD DATA command in the binary log, which means the data already in the file is not saved. You can disable the stack trace. You can also set a default week format, set an open files limit, and set a maximum error count to limit the amount of errors and/or warnings that can be saved for a particular statement.

  • Various As you might guess from the name, these are a mixture of settings that do not fit under any of the other tab headings but still are useful startup variables to access through the GUI.

User Administration

The User Administration display allows you to add new users, delete existing users, and view and change the privileges of users. When you click the User Administration icon in the sidebar, an additional sidebar panel appears beneath the main one, as shown in Figure 6-7. Labeled User Accounts, this sidebar displays all users. When you double-click a user name, any subcategories for that user (for instance, host names the user may connect from) are displayed. If subcategories appear for a user, it means that he or she has restricted privileges that are related directly to the specified hosts. Above the list of users is a text box with a magnifying glass icon. To search for a specific user, type the beginning letters or full name of a user in the text box.


Figure 6-7: The User Administration display with the User Information tab selected

As shown in Figure 6-7, the User Administration display has three tabs: User Information, Schema Privileges, and Resources. If you ve set the MySQL Administrator options to show global privileges and table/column privileges (in the Administrator Options category of the Options dialog box, accessed by choosing Edit Options), the display will also include two additional tabs: Global Privileges and Table/Column Privileges.

User Information Tab When none of the users in the User Accounts sidebar are highlighted, all of the input fields in the User Information tab (see Figure 6-7) are grayed out and unavailable. Choosing a user or clicking the New User button at the bottom of the window activates all the fields. The display is divided into two areas: User Information and Additional Information. The User Information area allows you to add or change the user name and password.

Caution  

Although MySQL allows you to add users without supplying a password, it is not recommended to do so for security reasons.

The Additional Information fields are Full Name, Description, Email, Contact Information, and Icon. None of this information is required, but the more users your database has, the more useful the additional information becomes, especially when future administrators take over maintenance duties .

Note  

If you click the Load Icon From Disk button next to the Icon field, you can assign your own icon for the user. The file you choose must be 48 by 48 pixels and in the PNG (Portable Network Graphics) format.

When editing an existing user account or adding a new user, the Apply Changes and Discard Changes buttons at the bottom of the window allow you to save or abandon any changes you have made. If you made changes to a user account, and then click another user without applying or discarding the changes, a MySQL Administrator pop-up window will prompt you to save or abandon the changes.

Global Privileges Tab As noted earlier, the Global Privileges tab is available in the User Administration display only if you have selected the Show Global Privileges check box in the Administrator Options tab of the MySQL Administrator Options dialog box. The Global Privileges tab will show the selected users privileges in an Assigned Privileges list and the other privileges in an Available Privileges list. You can alter the user s privileges by moving them between these lists by means of arrow buttons. You can move single or multiple privileges by highlighting them and then clicking the single arrow button that points in the direction of the appropriate list, or move all of the privileges by clicking the desired double-arrow button.

Schema Privileges Tab The Schema Privileges tab is always visible in the User Administration display. It is similar to the Global Privileges tab described in the previous section, except for an additional list labeled Schema, which shows all of the individual databases in your MySQL database. Once you select a specific database, the privileges in the Available Privileges list can be transferred into or out of the Assigned Privileges list, using the arrow buttons.

Table/Column Privileges Tab Like the Global Privileges tab, the Table/Column Privileges tab is only available when the Show Table/Column Privileges check box is selected in the Administrator Options tab of the Options dialog box. It looks and functions like the Schema Privileges tab, except its leftmost list box is labeled Schemata and contains the individual databases, which you can double-click to see the tables within the databases and the columns within the tables. The available privileges differ on each of these levels, and you can set assigned privileges at any of these levels.

Resources Tab The Resources tab allows you to manage MySQL s resources by limiting the number of user resources in three specific areas: questions, updates, and connections. By entering a number into the max_questions, max_updates, or max_connections fields, you limit the amount of drain each user can put on the system within one hour .

Server Connections Information

The Server Connections display allows you to view your connections, or threads, to the server or, if you have the privileges, to see all the connections to the server. It has two tabs: Threads and User Connections, which show the same information but in different formats. Figure 6-8 shows the Threads tab.


Figure 6-8: The Server Connections display with the Threads tab selected

Threads Tab The Threads tab shows all the threads attached to the server. This means multiple threads from the same user may be visible, depending on his or her interaction with the database. At the bottom of the Threads tab display there are three buttons: Kill Thread, Kill User, and Refresh. The Refresh button is always available, and it updates the display of the threads at the moment it is clicked. The Kill Thread button remains grayed out and inoperative until a thread is highlighted in the display by clicking it. You can stop the highlighted thread by clicking the Kill Thread button or by right-clicking the highlighted thread and choosing Kill Thread from the pop-up menu. You are allowed to select multiple threads. The Kill User button is inactive on this tab s display.

Note  

You need SUPER privileges to view and kill other users threads, and you cannot kill your own threads.

User Connections Tab The User Connections tab also displays the threads connected to the server; however, instead of only displaying each thread separately, it shows both the number of threads that the selected user has at any given time and a list of each separate thread of that user. Like the Threads tab, the User Connections tab can be updated by clicking the Refresh button. When a user is highlighted in the upper window, the Kill User button becomes active and using it will stop all the threads of that user with a single click. When a single thread or multiple threads are highlighted in the list of user threads, the Kill Thread button becomes active and using it will stop any threads that are selected.

Health Information

The Health display, shown in Figure 6-9, provides graphical and statistical views of MySQL s performance and status. The Health information is divided among four tabs: Connection Health, Memory Health, Status Variables, and System Variables.


Figure 6-9: The Health display with the Connection Health tab selected

Connection Health Tab The Connections Health tab shows information about MySQL connections in two different ways. There is a vertical bar graphic, which displays percentages, and an X,Y axis chart, which scrolls from right to left with time. The tab has three sections:

  • Connection Usage This refers to the maximum number of simultaneous client connections allowed.

    Note  

    If the Connections Usage gets close to 100% on a regular basis, you should consider raising the maximum number of connections allowed. You can do this from the Startup Variables display s Networking tab, in the Max Connections field.

  • Traffic This refers to the amount of usage at a given moment.

  • Number of SQL Queries This refers to the number of SQL queries running at any given moment.

Current, Minimum, Maximum, and Average statistics are listed under each display.

Memory Health Tab The Memory Health tab includes two sections, which use the same graphical and statistical format as the Connection Health tab:

  • Query Cache Hitrate This shows the ratio between the number of SELECT queries that were and were not cached. The higher the hit rate percentage, the better the performance of the server.

  • Key Efficiency This shows information about the key buffer.

Status Variables Tab The Status Variables tab shows a wide range of statistics concerning the status of the MySQL Server. The sidebar lists a variety of variables grouped in five sections: General, Performance, Networking, Commands Executed, and Miscellaneous. Each section has subsections you can select to see the corresponding information in the main display. There is a Refresh button at the bottom of the window to gather up-to-the-moment information.

System Variables Tab The System Variables tab shows a wide range of statistics and information concerning the system operation of the MySQL Server. The sidebar lists the variables grouped in five sections: General, Connections, SQL, Memory, and Table Types. Select a section in the sidebar to see the related information in the main display. If the icon for a variable is a page with a yellow pencil poised over it, the variable is editable, and double-clicking it opens a pop-up window that allows you to change the value. Set and Abort buttons allow you to save or abandon any changes to the variable made in the pop-up window. This tab also has a Refresh button to gather the most current information.

Server Logs

The Server Logs display contains information about three logs, as shown in Figure 6-10. The display format for each log includes a left pane showing events and a right pane showing page content. You can use the Pages bar at the top of the display as a rough navigation method for longer logs, by clicking at the beginning, middle, or end of the log. The Search and Save Log Page buttons at the bottom of the window open pop-up windows allowing you to search the log file and save the currently displayed page, respectively. The Refresh button allows you to gather an up-to-the-moment image of the log file.


Figure 6-10: The Server Logs display with the Error Log tab selected

There is a tab for each log:

  • Error Log This tab displays the MySQL Server s Error log, which contains start and stop information, as well as the critical errors that occur while running.

  • Slow Log This tab displays the log containing all the commands that took longer than a specified amount of time to execute. For this log to exist, you must enable it, using the Startup Variables display s Log Files tab, which contains the Slow Queries Log field in the Activate Logging area.

  • General Log This tab displays the Query log file containing general query information. Like the Slow log, this file must first be enabled for this tab to be functional. You can enable the Query log file using the Startup Variables display s Log Files tab, which contains the Query Logfile Name field in the Activate Logging area.

    Note  

    If one of the Server Logs tabs is disabled, the log file may not be enabled on the MySQL Server, or the log file may exist on a different computer than the one on which MySQL Administrator is running. The Alpha version of MySQL Administrator allows you to read only the log files stored on the computer on which it is installed.

Replication Status Information

If you have a replication process set up for your MySQL Server, the Replication Status display will give you an overview of the replication status.

Backup

The Backup display, shown in Figure 6-11, allows you to create and execute backup projects. It is a graphic interface for the mysqldump command that we will cover in Module 8, with the added advantage of saving the projects.


Figure 6-11: The Backup display with the Backup Project tab selected

Backup Project Tab When you select the Backup Projects tab, another sidebar panel appears below the main sidebar on the left. This sidebar lists the saved backup projects. The main display area has two panes: on the left, the pane labeled Schemata lists the individual databases, and the pane on the right shows the contents of the backup. You can move data into the Backup Content list by using the arrow buttons between the two panes.

The New Project button at the bottom of the window clears the display so a new project can be specified. The Save Project button is enabled when the New Project button is selected or when data is altered in an existing project. The Execute Backup Now button is enabled any time a project is selected in the sidebar list.

Advanced Options Tab The Advanced Options tab allows you to specify how your backup should be executed. It is arranged into two areas: Backup Execution and Output File Options. The Backup Execution area offers four choices:

  • Lock All Tables This option is crucial to getting a consistent picture of your MyISAM tables.

  • Single Transaction This option is crucial to getting a consistent picture of your InnoDB tables.

  • Normal Backup This option causes each table to be backed up independently, which can lead to inconsistent backups and should be avoided.

  • Backup Selected Schemata This option completely overrides your Backup Content specifications and backs up all of the tables of the selected schemata. This should be used on schemas where tables are often added.

The Output File Options area has options that allow you to select the format of the output file and to choose to include or exclude certain commands and information.

Schedule Tab The Schedule tab will eventually allow you to perform an automatic, scheduled backup at a specific interval of time, but this feature is not yet available in the current Alpha version. If the feature uses the crontab command to achieve its end result, this will be a Linux-specific feature, but by the time the tool reaches the Production version, the Schedule tab may be available on Windows systems as well.

Restore

The Restore display, shown in Figure 6-12, allows you to restore backups you ve made. Click the Open Backup File button at the bottom of the window and select a .sql backup file to restore. Click the Start Restore to begin the restoration process.


Figure 6-12: The Restore window with General tab displayed

The Restore display has two tabs:

  • General This tab contains options for how the backup will be restored. When you select a backup file, MySQL Administrator automatically fills in the File to Restore field. In the Alpha version of MySQL Administrator, the Target Schema and Backup File Type are also selected automatically, because there is only one option available for each field.

  • Restore Content This tab displays the contents of the backup file that has been selected for restoration.

Catalogs Information

The Catalogs display allows you to select a database (schema) and view its tables (schemata), as well as the details of the table s construction, as shown in Figure 6-13. It has three tabs: Schema Tables, Indices, and Users. All three tabs have a Refresh button at the bottom of the window.


Figure 6-13: The Catalogs display with the Schema Tables tab selected

Schema Tables Tab The Schema Tables tab allows you to view a database s tables and columns. When a database is selected in the Schemata sidebar that appears beneath the main one on the left, the list of tables for that database is displayed in the upper pane of the main display. When you select a table from that list, the details about that table are displayed in the lower pane s four tabs:

  • Columns This tab shows the description of the selected table.

  • Indices This tab shows any index fields the table may have.

  • Table Status This tab shows the table type, the row format, the next available auto-increment number, any CREATE options specified, and comments (if any exist for the table).

  • Row Status This tab shows both data and time information. The Data section shows Number of Rows, Average Row Length, Data Length, Max Data Length, Index Length, and Data Free fields. The Time section shows the Create, Update, and Check Times fields.

The buttons at the bottom of the window allow you to perform certain tasks related to the tables: optimizing, checking, and repairing tables.

Indices Tab The Indices tab is similar but more comprehensive than the Indices tab in the Schema Tables tab. Where the Indices tab in the Schema Tables tab lists the index field or fields for a specific table, the Indices tab of the Catalog display lists all of the index fields for the entire selected database. Double-click a table name to see the column name of the index.

Note  

The Alpha version lists all the indices for all the databases on the server, regardless of which database is selected.

Users Tab The Users tab lists all the users that have access privileges to the selected database.

Progress Check

1.  

Why shouldn t you always leave the Debug Information option on?

2.  

If the Connections Usage percentage gets close to 100%, what is the first thing you should you do?

3.  

How do you view the various areas of information in MySQL Administrator?

4.  

When should you use MySQL Administrator?

Answers

1.  

Leaving the Debug Information on can affect the performance of the server.

2.  

You should increase the Max Connections number.

3.  

In the sidebar to the left, you select the area your interested in and that information will appear in the main window on the right.

4.  

You should use it if you prefer a GUI interface over the command line for administration tasks like setting buffer sizes, monitoring system health, and maintaining user accounts and privileges.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net