The world's most popular open source database
Database administration serves a number of important functions from improving the performance of your database to avoiding a disaster that would result in lost data or costly system downtime. This article will describe some of the fundamental tools and practices programmers, as well as DBAs and SysAdmins, can use with the powerful new MySQL Administrator tool to ensure a healthy database environment.
The role of the administrative function will largely depend on the size of the organization and size of the IT department. In larger organizations there are DBA (Database Administrators), Data Analysts, and SysAdmins that would work alongside programmers to create a well designed database systems and then ultimately be responsible for managing the health and availability of the system on a daily basis. However, in smaller organization, or where IT departments are more resource limited, the duty of administering MySQL falls into the hands of the database programmer.
For programmers less familiar with the administration functions, their responsibility must also cover the following areas:
Optimizing the server for performance which includes, space management, monitoring memory usage, and diagnosing problems.
Administering the server on a daily basis to ensure server availability and the ongoing health of the system.
Disaster prevention and recovery including creating consistent backups and restoring databases in case of network, application, or hardware failure.
Until now, a powerful set of command line tools have been used to administer MySQL servers. However, they have been cumbersome and time-consuming to use for individuals not familiar or well-versed in the utilities. The result, in many cases, has been a general neglect of the administration function leaving the database system at risk to security breaches, data loss, and downtime.
To make administration easier and faster, MySQL AB recently announced MySQL Administrator, an integrated visual environment for managing MySQL servers. It is an ideal tool that not only greatly simplifies the life of DBAs but also empowers developers to effectively manage a networked environment of MySQL servers.
Below are 5 critical functions of database administration that must be addressed to ensure the level of availability your users will demand of your database system.
Designing a well structured, normalized database schema is just half of the
optimization task. The other half is building and fine tuning a server to run
MySQL. MySQL provides a wealth of information regarding the tuning of server
parameters. This information which was traditionally accessible in a
configuration file called
my.cnf, but can now be accessed and
tuned visually using the MySQL Administrator graphical interface.
MySQL default parameters are very small so it can run on almost any box as
an additional service without slowing things down. MySQL provides
my-small/medium/huge.cnf files that come with the standard binary
distribution as general parameter guidelines for small, medium, and huge
Many of the optimal parameter values will depend a lot on the load you put on the server, as well as the storage engines(s) you use. But, here are some generic guidelines to optimize performance regardless of which storage engine you are using. We will take a look at storage engine specific tuning options in a future article so please, continue to watch this space.
max_connections to the number of concurrent connections you
need. The default value is only 100 connections, which is very small.
Note: connections take memory and your OS might not be able to handle a lot of connections. MySQL binaries for Linux/x86 allow you to have up to 4096 concurrent connections, but self compiled binaries often have less of a limit.
table_cache to match the number of your open tables and
concurrent connections. Watch the
open_tables value and if it is
growing quickly you will need to increase its size.
Note: The 2 previous parameters may require a lot of open files.
20+max_connections+table_cache*2 is a good estimate for what you
need. MySQL on Linux has an open_file_limit option to set this limit.
If you have complex queries
tmp_table_size are likely to be very important. Values will
depend on the query complexity and available resources, but 4Mb and 32Mb,
respectively are recommended starting points.
Note: These are "per connection" values, among
read_rnd_buffer_size and some
others, meaning that this value might be needed for each connection. So,
consider your load and available resource when setting these parameters. For
sort_buffer_size is allocated only if MySQL nees to do a
sort. Note: be careful not to run out of memory.
If you have many connects established (i.e. a web site without persistent
connections) you might improve performance by setting
to a non-zero value. 16 is good value to start with. Increase the value until
threads_created do not grow very quickly.
Backing up your database is the single most important element to preventing a data loss disaster as a result of network, application, or hardware failure. Backing up your database is your first step towards ensuring recoverability and should be done on a regular basis. Since your MySQL tables are stored as actual files in the MySQL installation directory, making a backup may seem as easy making as backup of your personal files on your home computer. However this method does not apply to a live database system. If someone is accessing a table at the moment you try to copy the file, you may end up with a partially updated file.
Using the graphical environment, you can ensure consistent backups and that can be quickly restored data by simply selecting the schemas and tables you want to back/restore from a hierarchical data directory. The graphical environment gives you additional visibility into Tabletype, Rows, Data Length, and Update time. What's more, you can back up tables remotely, eliminating the need to log into each machine locally.
In larger organizations, DBAs would typically be responsible for implementing a complete security strategy and privilege system to prevent possible security breaches. Regardless of whose responsibility it is, adopting a proactive approach is crucial to preventing violations. The graphical interface of MySQL Administrator leverages the full power of MySQL's user management and privilege capabilities and significantly simplifies and reduces the time it takes to set up Access Control Lists and grant privileges. The graphical interface provides a simple method for adding users, assigning passwords and includes user profile information. Assigning and revoking global, database, and table/column privileges is as easy as adding and removing privilege items from an available list.
Our most important piece of advice on this topic is the learn and use the powerful authentication and privilege system. Some specific guidelines include:
Do not have an empty
root password. Otherwise anyone can connect as root without a password and be granted all privileges.
Always use passwords and make sure that passwords are not plain text passwords. If your computer becomes compromised, the intruder can take full advantage of passwords and use them.
Give permissions only as needed and use different logins for different applications.
Note: From a performance point of view it better to have table level permissions. Column level permissions tend to slow things down a bit.
Users are granted privileges on a "per host" basis. peter@myhost has nothing to do with peter@yourhost. You should avoid granting users privileges to all hosts unless absolutely necessary.
Note: Users, usernames, and passwords have nothing to do with OS users. You can specify any user name you want.
Avoid having MySQL Server open to the Internet. Remote exploits are uncommon, but why risk it when it can be easily avoided.
There are some basic, yet key variables you will want to keep your eye on
that will help better diagnose potential problems and ensure you system runs
smoothly in a production environment. Particularly, developers and DBAs will
want to monitor hitrate percentages, number of SQL queries, and connection
usage to ensure you system is not experiencing a bottleneck. The new health
monitoring facility gives you improved visibility from centralized location,
using dynamic graphs to chart usage. You can also use the visual health
monitoring feature to get a hierarchical view into status and server variables
such as the number of outstanding connections, number of bytes sent and
received by clients, buffer allocation size and more. There are a few Status
Variables for InnoDB and MyISAM that you will want to monitor. For example, IO
rate is a good one to monitor to see database activity. In addition,
threads_running are good
indicators to see how loaded the server is.
Typically databases grow and naturally accumulate information. It's important to make sure there is always enough free space to handle the day's expected growth. You should consider keeping at least 30% of your hard disk space free. Another good way to avoid running out of space is to keep the automatic extensions of the data files on and only set the maximum to 90% of the hard disk. Again, optimal values are going to depend largely on your specific implementation and usage of the database.
The Startup Parameters of the MySQL Administrator allows you to easily allocate the size of the memory buffer InnoDB users to cache data and indexes of its tables as well as allocate the size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
MySQL Administrator provides an easy to use facility to manage your database servers. This integrated graphical tool will enable to you harness all the power of the administrative functions, while reducing the learning curve and simplifying the overall administration task. Ultimately, MySQL Administrator will help you minimize problems and maximize database availability — which is one of the key goals of good database administration and user satisfaction.
The tips and guidelines in this article come directly from the MySQL support team which is made up of MySQL experts. For busy developers and tech staffs, the services they provide can save considerable time otherwise spent in research and experimentation. MySQL support team can help ensure that you get the level of support you need when you need it on a 24x7 basis worldwide. Contact MySQL support to get more information on the range of support and training services available.