General Administration

General administration deals primarily with the operation of mysqld, the MySQL server, and with providing your users access to the server. The following duties are most important in carrying out this responsibility:

  • Server startup and shutdown. You should know how to start and stop the server manually from the command line and how to arrange for automatic startup and shutdown when your system starts up and shuts down. It's also important to know what to do to get the server going again if it crashes or will not start properly.

  • User account maintenance. You should understand the difference between MySQL user accounts and UNIX or Windows login accounts. You should know how to set up MySQL accounts by specifying which users can connect to the server and from where they can connect. New users should also be advised on the proper connection parameters that they will need to use to connect to the server successfully. It's not their job to figure out how you've set up their accounts! You'll also need to know how to reset forgotten passwords.

  • Log file maintenance. You should understand what types of log files you can maintain, as well as when and how to perform log file maintenance. Log rotation and expiration are essential to prevent the logs from filling up your file system.

  • Database backup and copying. Database backups are of crucial importance in the event of a severe system crash. You want to be able to restore your databases to the state they were in at the time of the crash with as little data loss as possible. Note that backing up your databases is not the same thing as performing general system backups (as is done, for example, by using the UNIX dump program). The files corresponding to your database tables may be in flux due to server activity when system backups take place, so restoring those files will not give you internally consistent tables. The mysqldump program generates backup files that are more useful for database restoration, and it allows you to create backups without taking down the server. You may also need to move databases in the event of a full disk.

    If you decide to run a database on a faster host, you'll need to copy its contents to a different machine. You should understand the procedure for doing this, should the need arise. Database files may be system dependent, so you can't necessarily just copy the files.

  • Database replication. Making a backup or a copy of a database takes a snapshot of its state at one point in time. Another option available to you is to use replication, which involves setting up two servers in cooperative fashion such that changes to databases managed by one server are propagated on a continuing basis to the corresponding databases managed by the other server.

  • Server configuration and tuning. Your users want the server to perform at its best. The quick-and-dirty method for improving how well your server runs is to buy more memory or to get faster disks. But those brute-force techniques are no substitute for understanding how the server works. You should know what parameters are available for tuning the server's operation and how they apply to your situation. At some sites, queries tend to be mostly retrievals. At others, inserts and updates dominate. The choice of which parameters to change will be influenced by the query mix that you observe at your own site.

    Configuration issues also include localizing the server (for example, to make sure that it uses the proper character set and time zone).

  • Multiple servers. It's useful to run multiple servers under some circumstances. You can test a new MySQL release while leaving your current production installation in place, or provide better privacy for different groups of users by giving each group its own server. (The latter scenario is particularly relevant to ISPs.) For such situations, you should know how to set up multiple simultaneous installations.

  • MySQL software updates. New MySQL releases appear frequently. You should know how to keep up to date with these releases to take advantage of bug fixes and new features. Understand the circumstances under which it's more reasonable to hold off on upgrading, and know how to choose between the stable and development releases.