Introduction to the general management of MySQL database system

MySQL is fairly simple to use when running a database system, and there is little work required to install and use MySQL. However, no matter what level of expertise you are, MySQL's installer does not run automatically. Someone has to monitor it to make sure it runs smoothly and efficiently, and sometimes it has to know what to do when the problem arises. And MySQL's management has its own unique requirements, this article will help you familiarize yourself with these content.

Management Responsibilities Overview

The MySQL database system consists of several parts. You should familiarize yourself with the content of these components and the purpose of each section. This requires you to understand the characteristics of the system you are managing and the tools available to help you manage it. If you take the time to learn what to monitor, the work will be very easy. To do so, you should familiarize yourself with the following aspects of MySQL:

MySQL server. Server MySQL performs all of the database and table operations. SAFE_MYSQLD is a related program that starts the server, monitors the server, and restarts the server.

MySQL client and utilities. There are several MySQL programs that can be used to help you communicate with the server and perform administrative tasks. Some of the most important are:

MySQL, an interactive program that allows you to publish SQL statements to the server and browse the results.

Mysqladmin, a management program that allows you to perform work such as shutting down a server and creating or deleting a database. If the server is not functioning properly, you can also use Mysqladmin to check the status of the server.

Isamchk and Myisamchk, these utilities help you complete table analysis and optimization, and crash recovery when tables are corrupted.

Mysqldump, a tool for backing up a database or copying a database to another server.

The language of the server, SQL. Some administrative responsibilities can only be done with mysqladmin command-line utilities, but it's even better if you can talk to the server in your own language. As a simple example, you might want to find out why user privileges do not work the way you want them to. No alternatives can participate and communicate directly with the server. This can be done by using the MySQL client program to publish SQL queries that verify the authorization table. If your MySQL version has not yet introduced the GRANT statement, you will need to use MySQL to set permissions for each user first.

If you don't know anything about SQL, you must have at least a basic understanding of SQL. The lack of familiarity with SQL can only confuse you, and the time spent on learning SQL will be rewarded exponentially. It takes some time to really master SQL, but it's very quick to master basic skills. If you need to know something about SQL and MySQL command-line clients, see Chapter 1th, "MySQL and SQL introduction."

MySQL Data directory. The data directory is where the server stores its database and state files. It is important to understand the structure and content of the data directory, and you can see how the server uses the file system to represent databases and tables, as well as the location and content of files such as logs. You should also understand the options for managing disk space allocation in the file system, which can be adjusted when the file system that places the data directory is found to be full.

General Administration

General management refers primarily to the handling of mysqld, MySQL servers, and access servers provided to users. In discharging this responsibility, the following tasks are of paramount importance:

Startup and shutdown of the server. You should be able to start and terminate the server manually from the command line, and know how to start and shut down automatically when the system starts and shuts down. It is also important to know how to make the server run again if the server crashes or is not booting properly.

User account maintenance. You should know the difference between a MySQL user and a UNIX or Windows user. You should know how to create a MySQL user account by specifying which users can connect to the server and where to connect. New users should also be advised of appropriate connection parameters to enable them to successfully connect to the server. Figuring out how to create an account is not a job for users.

Log file maintenance. You should understand the types of log files that can be maintained, and when and how to complete the maintenance of the log files. Looping and terminating logs are necessary to prevent the log from filling the file system.

Database backup and copy. Database backups are critical to server system crashes. You should be able to restore the database to its state of collapse to minimize the loss of data. Note that database backups are different from regular system backups, for example, by using a UNIX dump program. Files that correspond to database tables change with server activity when a system backup occurs, so restoring those files will cause inconsistencies within your tables. The mysqldump program produces backup files that are more useful for recovery databases, and allows you to create backups without shutting down the server.

If you decide to run the database on a faster host, or if you want to copy the database, you need to copy its contents to another machine. If you need to, you should understand the process of doing this. Database files are system-dependent, so you cannot copy only these files.

Server optimization. The user wants the server to run in the best state. The easiest way to improve server performance is to buy more memory or make the disk faster. However, this straightforward technology does not replace the understanding of server work. You should understand the parameters used to optimize server operations and how to apply these parameters to your environment. In some sites, most queries are retrieved. In other sites, insert and update operations occupy an advantage. Choosing which parameters to modify will be affected by the site query.

multiple servers. It is useful to run multiple servers in some environments. You can test the new version of MySQL if you keep your current production installer in the right place, or if you provide better confidentiality for different groups of users (which is especially relevant to your ISP). For these scenarios, you should understand how to create multiple simultaneous installations.

MySQL update. Since the new version of MySQL is frequently available, you should know how to keep up with these versions to take advantage of fault fixes and new features. You need to understand the reasons for not upgrading and how to choose between a stable version and a developer version.

Security

When running the MySQL installer, it is important to ensure that the data stored by the user is secure. The MySQL administrator is responsible for controlling access to the data directories and servers, and should be aware of the following issues:

The security of the file system. UNIX machines may make several user accounts a host account, and these accounts do not have the management responsibilities associated with MySQL. It is important to ensure that these accounts do not have access to the data directory. This prevents them from damaging file system-level data by copying database tables or moving database tables, or by being able to read log files that contain sensitive information. You should know how to set up a UNIX user account for the MySQL server, how to set up the data directory that the user owns, and how to start the server to run with that user's privileges.

The security of the server. You must understand how MySQL's security system works so that you can grant the appropriate permissions when creating a user account. Users who connect to the server over the network are only allowed to do what they are supposed to do. You should not grant superuser access to an anonymous user because of a faulty understanding of the security system.

Database Repair and Maintenance

All MySQL administrators want to avoid handling corrupted or corrupted database tables. But a wish is not a substitute for reality. Here are a few steps you can take to reduce your risk and learn how to handle problems when they occur:

Crash recovery. If, despite your best efforts, disaster has befallen you, you should know how to repair or recover a table. Crash recovery is rarely used, but when used, it is a nasty, intense task (especially when you are hustling to fix something, the phone rings or someone knocks). However, you must know how to handle it, otherwise users will be very unhappy. Be familiar with the ISAMCHK and MYISAMCHK table inspection and repair capabilities, know how to recover from backup files as much as possible, and know how to use the update log to restore changes that occur after a backup.

Preventive maintenance. Regular procedures for preventive maintenance should be properly placed to minimize the likelihood of destruction and destruction of the database. Of course, you also need to make backups, but preventative maintenance will reduce the chances of using those backups.

The above comprehensive summary of the responsibilities as a MySQL administrator should assume.