MySQL service maintenance and application design notes (2) _ MySQL

MySQL service maintenance and application design Note (2) 3. MYSQLD stop script: stop_mysql.sh
#! /Bin/sh
Rundir = 'dirname "{GetProperty (Content )}"'
Echo "$ rundir"
/Home/mysql/bin/mysqladmin-u mysql-S "$ rundir"/mysql. sock shutdown

The advantages of using this script are:

1. start multiple services: you only need to modify the -- port = parameter in the script. Data and service scripts in a single directory can be packaged independently.

2. all service files are located in the/data/app_1/directory, for example, mysql. pid mysql. sock: when multiple services are started on one server, multiple services will not affect each other. However, if both are put under the default/tmp/, it may be accidentally deleted by other applications.

3. when hard disk 1 fails, you can directly put hard disk 2 on a server with MYSQL installed to immediately restore the service (if it is placed in my. cnf, you also need to back up the corresponding configuration file ).

After the service is started, the corresponding files and directories under/data/app_1/are distributed as follows:
/Data/app_1/
Start_mysql.sh service startup script
Stop_mysql.sh service stop script
Mysql. pid service process ID
Sock of mysql. SOCK service
Var/Data Zone
Mysql/user Library
App_mongodb_1/application library
App_2_db_2/
...
/Data/app_2/
...

View all application process IDs:
Cat/data/*/mysql. pid

View error logs for all databases:
Cat/data/*/var/*. err

Personal suggestion: the main bottleneck of MYSQL is the number of PORT connections. Therefore, after the table structure is optimized, the CPU usage of a single MYSQL service is still above 10%, we need to consider splitting the service to multiple ports for running.

Service backup

Try to use mysql dump instead of backing up data files directly. The following is a script that backs up data on weekday: the backup interval and cycle can be determined based on the backup requirements.

/Home/mysql/bin/mysqldump-S/data/app_1/mysql. sock-umysql db_name | gzip-f>/path/to/backup/db_name. 'data cannot exceed w'.dump.gz
　　
Therefore, it is generally written in CRONTAB as follows:
* 6 ***/home/mysql/bin/mysqldump-S/data/app_1/mysql. sock-umysql db_name | gzip-f>/path/to/backup/db_name. 'data +/mongow'.dump.gz

Note:

1 in crontab, '%' needs to be converted to '/%'

2. according To log statistics, the minimum application load is generally at A.M..

Back up data locally and then upload it to a remote backup server, or directly create a database backup account to back up data directly on the remote server, for remote backup, you only need to replace-S/path/to/msyql in the preceding script. change sock to-h IP. ADDRESS. Routine maintenance and data migration: when the data disk is not damaged, the hard disk is generally the hardware with the lowest service life in the system. However, system (including the operating system and MYSQL applications) upgrades and hardware upgrades will encounter data migration problems. As long as the data remains unchanged, first install the server, and then directly install the data disk (hard disk 2), just add the startup script to rc again. in the local file, the system is restored.

Disaster Recovery: when the data is damaged, determine the time point of the destruction and restore the data from the backup.

Key points of application design

1. is a non-use database unavailable?
Databases can indeed simplify the structure design of many applications, but they are also an application that consumes a large amount of system resources. Therefore, if many applications do not have high real-time statistics requirements, they can be recorded in file logs and imported to the database periodically for subsequent statistical analysis. If you still need to record the 2-dimensional table structure, you can use the DBM structure if the structure is simple enough. Even if you need to use a database, if the application does not have too complex data integrity requirements, you can simply not use commercial databases that support foreign keys.

2. major bottleneck of database services: for an application, if the database table structure can be designed according to the database principle paradigm, and the latest MYSQL version is used, and run in a relatively optimized way, the main bottleneck is generally the number of connections of a single service. even if a database supports 500 concurrent connections, it is best not to use the application to this point, because the number of concurrent connections is too large, the overhead of the database service itself for scheduling threads will also be very large. So if the application permits: let one machine run a few more MYSQL services to share. Plan the service balance to multiple MYSQL service ports, for example, app_1 ==> 3301 app_2 ==> 3302... app_9 ==> 3309. It is normal for a machine with 1 GB memory to run 10 MYSQL instances. It is much more efficient to enable 10 MYSQLD to handle 1000 concurrent connections than to enable 2 MYSQLD to handle 1000 connections. Of course, this will also bring some application programming complexity;

3. Use a separate database server (do not compete with the front-end WEB service for memory). MYSQL may effectively cache the result set if it has more memory;

4. the application tries its best to use the PCONNECT and polling mechanisms to save the connection overhead of the MYSQL service;

5. horizontal split of tables: place 10% of the most frequently accessed data in a small table, and 90% of historical data in an archive table, data is saved through regular "migration" and regular deletion of invalid data. In this way, the application always selects 10% of the data, which is more conducive to data caching. do not expect MYSQL to have a high efficiency when the number of records in a single table is above 0.1 million.

6. vertical split of a table (transition normalization): put all fixed-length fields (char, int, etc.) in a table, all variable-length fields (varchar, text, blob, etc) in another table, the two tables are associated with the primary key, so that the fixed-length field table can be greatly optimized (or even the HEAP table type can be used, data is fully accessed in the memory). here we also illustrate another principle. for us, we should try our best to use a fixed-length field to improve the access efficiency through space loss. MYSQL supports multiple table types and provides different optimization methods for different applications;

7. carefully check the index design of the application, and even add -- log-slow-queries [= file] to the service startup to track and analyze the application bottleneck.