MYSQL service maintenance and Application Design Notes

The following are some experiences in using the MYSQL service, mainly from the following aspects: 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, and immediately restore the service. If it is placed in my. cnf, you also need to back up the corresponding configuration files ).
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, the hard disk is generally the hardware with the lowest service life in the system. However, the system includes the operating system and MYSQL applications) 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 Disk 2), you only need to re-Add the startup script to the rc. local file, and the system will be 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 and do not compete for memory with the front-end WEB Service). MYSQL may cache the result set effectively 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. transition normalization for Vertical Split of a table): Place all fixed-length fields such as char and int in one table, and place all variable-length fields such as varchar, text, and blob in another table, the two tables are associated with the primary key. In this way, the fixed-length field table can be greatly optimized and even the HEAP table type can be used to completely access the data in the memory ), another principle is also described here. For us, we should try 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.