Routine Management of the MySQL database system

The main responsibility of daily management is to manage the running status of MySQL server program mysqld, so that database users can smoothly access the MySQL server. The following are the main responsibilities of this job:


Server startup and shutdown. The specific responsibilities include: 1) manually start and close the MySQL server from the command line; 2) automatically start and close the MySQL server during system startup and shutdown; 3) restore the MySQL server to a normal running state when it crashes or is not properly started.

Manage user accounts. The specific responsibilities include: 1) understanding the difference between a MySQL user account and a UNIX or Windows registered account; 2) Setting a MySQL user account, restrict the user to connect to the MySQL server only from the specified machine; 3) notify the new user of the correct connection parameters, so that they can smoothly connect to the MySQL server-their job is to use the database rather than set an account! 4) if the user (or yourself) forgets the password, you need to know how to reset it.

Manage log files. The specific responsibilities include: 1) know which types of log files can be managed; 2) when and how to manage; 3) develop and implement log loops and invalidation mechanisms to prevent log files from consuming up the available space of the file system.

Back up and migrate the database. When the system crashes, database backup will play a crucial role. You certainly want to restore the system to the State before the crash with as little data as possible. However, it should be noted that the database backup work is different from the general system backup work (such as the backup work with the UNIX tool program dump. The system administrator is usually responsible for system backup. Before the backup starts, the MySQL server may not be shut down. Therefore, in the process of system backup, some data tables may change because the MySQL server is still reading and writing them. Using such a backup to restore the system will lead to confusion in the data tables. The backup file generated by the mysqldump program is more suitable for database recovery operations, and it does not require you to disable the MySQL server before the backup starts. You may also need to move the database when the disk is full.
Database migration refers to transferring a database from one hard disk to another. When there is little available space on the disk or you want to move some databases to another host with a higher speed, you need to move the relevant database. Note that database files depend on specific operating systems. Therefore, database migration may not always be completed using simple file copy commands.

Create a database image. If you copy or back up a database as a database photo, creating a database image is equivalent to recording a database image. To create a database image, you need to run two database servers at the same time and make them form a master-slave relationship. In this way, modifications made to a database managed by the master server will be synchronized (with a slight delay) reflected in the database managed by the slave server.

Configure and optimize the server. Database users want the database server to run in the optimal state. The simplest way to improve server performance is to add more memory and faster hard disks. However, this cannot be the reason you do not study the working principles of the database-after being so "dry", you still need to configure and optimize the server. The specific content of this responsibility includes: 1) know which parameters can be used to optimize the server; 2) how to optimize the server according to the specific situation. Most queries on some sites are data retrieval operations, while others are data insertion and modification operations. You should select the most effective parameter based on the "hybrid proportion" observed in your site.
"Localization" (such as setting appropriate character sets and time zones) for database servers is also one of its configuration work.

Run multiple servers at the same time. In some cases, multiple servers must be run simultaneously. You may be testing a new version of MySQL software, but you must keep the existing server running, you may want to allow different user groups to use different servers to provide a better privacy protection mechanism for each group of users. (The latter case is especially suitable for ISP .) In either case, you must master the technology of installing and starting multiple MySQL servers at the same time.

Upgrade the MySQL software. Like other software products, MySQL is constantly being updated. To apply a new version with fewer vulnerabilities and more functions, you must master the software upgrade technology. The specific content of this responsibility includes: 1) Know how to upgrade MySQL software; 2) When not to upgrade is more reasonable; 3) how to select between a stable version and a test version.