MYSQL service maintenance NOTE _ MySQL

Some experience in using MYSQL services, mainly from the following aspects: MYSQL service planning and design. 1. the versatility of the installation and configuration of the MYSQL service; 2. convenient system upgrade and data migration; 3. fast backup and system recovery; and the planning of the MYSQL server for future maintenance, to upgrade the backup convenience and data security, it is best to use the MYSQL service experience of MYSQL, mainly from the following aspects to consider the MYSQL service planning and design.
1. versatility of MYSQL service installation/configuration;
2. easy system upgrade and data migration;
3. fast backup and system recovery;
　　
　　 MYSQL server planning
========================
For future maintenance and convenience of upgrading backup and data security, it is best to install MYSQL program files and data on "different hardware" separately.
/
/Usr <= operating system }=> hard disk 1
/Home/mysql <= mysql application
...
/Data/app_1/<= application data and script} => Hard Disk 2
/Data/app_2/
/Data/app_3/
　　
Install the mysql service and start the service:
MYSQL generally uses the current STABLE version, and tries not to use the -- with-charset = option. I feel that with-charset is only useful in alphabetical order, these options will cause a lot of trouble for data migration.
Configure -- prefix =/home/mysql
Make
Make install
　　
　　 Service start and stop
======================
1. copy the default mysql/var/mysql to the/data/app_1/directory,
2 MYSQLD startup script: start_mysql.sh
#! /Bin/sh
Rundir = 'dirname "$0 "'
Echo "$ rundir"
/Home/mysql/bin/safe_mysqld -- user = mysql -- pid-file = "$ rundir"/mysql. pid -- datadir = "$ rundir"/var "$ @"-O max_connections = 500-O wait_timeout = 600-O key_buffer = 32 M -- port = 3402 -- socket = "$ rundir "/mysql. sock &
　　
Note:
-- Pid-file = "$ rundir"/mysql. pid -- socket = "$ rundir"/mysql. sock -- datadir = "$ rundir"/var
The purpose is to put the corresponding data and temporary application files together;
-O is generally followed by the global variable optimization parameter for server startup, and sometimes needs to be adjusted according to the specific application;
-- Port: different applications use PORT parameters to distribute to different services. The number of connections that a service can provide is generally the main bottleneck of the MYSQL service;
　　
After modifying different services to different ports, add the following to the rc. local file:
/Data/app_1/start_mysql.sh
/Data/app_2/start_mysql.sh
/Data/app_3/start_mysql.sh
Note: the full path must be written.
　　
3. MYSQLD stop script: stop_mysql.sh
#! /Bin/sh
Rundir = 'dirname "$0 "'
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, '%' must 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.
　　
　　 Data recovery and system upgrade
======================================
Routine maintenance and data migration: when the data disk is not damaged
Hard disks are 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 data is damaged
Determine the time point of destruction, and then recover from the backup data.
　　
　　 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 bottlenecks of database services: number of connections of a single service
For an application, if the database table structure can be designed according to the database principle paradigm, and the latest MYSQL version has been used and runs in a relatively optimized manner, the main bottleneck at the end is generally the number of connections to a single service. even if a database supports 500 concurrent connections, it is best not to use the application, 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.