MySQL service maintenance and application design notes

The following are some of the experience of using MySQL services, mainly from the following aspects of the MySQL service planning and design considerations.

1 MySQL Service installation/configuration versatility;
2 system upgrades and data migration convenience;
3 Backup and system rapid recovery;

Planning for MySQL Server
In order to maintain later, upgrade the convenience of backup and data security, it is best to install MySQL program files and data on "different hardware".

/
/usr <== operating system}==> hard drive 1
/home/mysql <== MySQL Application
...
/data/app_1/<== Application Data and scripts}==> hard drive 2
/data/app_2/
/data/app_3/

Installation of MySQL services and startup of services:
MySQL generally uses the current stable version, try not to use the--with-charset= option, I feel that with-charset only in alphabetical order when the useful, these options will bring a lot of trouble with the data migration.

Configure--prefix=/home/mysql
Make
Make install

start and stop of services

1 Copy the default Mysql/var/mysql to the/data/app_1/directory

2 mysqld Startup script:
start_mysql.sh
#!/bin/sh
Rundir= ' dirname ' "$"
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=32m--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 the application of temporary documents together;
-O is generally the server to start global variable optimization parameters, sometimes need to adjust according to the specific application;
--port: Different applications use port parameters to distribute to different services, a service can provide the number of connections is generally the main bottleneck of MySQL services;

after modifying different services to a different port, add the following in the Rc.local file:

/data/app_1/start_mysql.sh
/data/app_2/start_mysql.sh
/data/app_3/start_mysql.sh
Note: Full path must be written

3 mysqld Stop script: stop_mysql.sh
#!/bin/sh
Rundir= ' dirname ' "$"
echo "$rundir"
/home/mysql/bin/mysqladmin-u mysql-s "$rundir"/mysql.sock shutdown

The advantage of using this script is that:

More than 1 service launches: Only the--port= parameters in the script need to be modified. Data and service scripts in a single directory can be packaged independently.

2 All services corresponding files are located in the/data/app_1/directory: for example: Mysql.pid Mysql.sock, when multiple services are started on a single server, multiple services do not affect each other. However, it may be mistakenly deleted by other applications under the default/tmp/.

3 when the hard drive 1 out of the question, directly put hard disk 2 on a server installed MySQL can immediately restore services (if put into the my.cnf will also need to back up the corresponding configuration file).

After service startup, the corresponding files and directories are distributed as follows:/data/app_1/
/data/app_1/
Start_mysql.sh Service startup script
stop_mysql.sh Service Stop Script
Process ID of the MYSQL.PID service
The sock of Mysql.sock service
var/Data area
mysql/User Library
APP_1_DB_1/Application Library
app_2_db_2/
...
/data/app_2/
...

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

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

Personal suggestion: MySQL's main bottleneck in the port connection number, therefore, the table structure optimization, the corresponding single MySQL service CPU occupancy is still more than 10%, it is necessary to consider the service split to multiple port running.

Backup of services

Try to use MySQL dump instead of directly backing up your data files, here's a script that weekday the data back up: The interval and cycle of backups can be determined based on the needs of the backup

/home/mysql/bin/mysqldump-s/data/app_1/mysql.sock-umysql db_name | Gzip-f>/path/to/backup/db_name. ' Data +%w '. dump.gz
　　
Therefore written in the crontab is generally:
* 6 * * */home/mysql/bin/mysqldump-s/data/app_1/mysql.sock-umysql db_name | Gzip-f>/path/to/backup/db_name. ' Data +\%w '. dump.gz

Attention:

1 in crontab '% ' need to be escaped into ' \% '

2 According to the log statistics, the lowest application load is usually at 6 in the morning.

Back up locally and then to the remote backup server, or directly set up a database backup account, directly on the remote server backup, remote backup only need to change the-s/path/to/msyql.sock in the above script to H-IP. Address can be.

Data recovery and system upgrades

Daily maintenance and data migration: Hard drives are generally the lowest-life hardware in the system when the data disk is not compromised. and the system (including operating system and MySQL application) upgrades and hardware upgrades, will encounter the problem of data migration. As long as the data unchanged, first installed the server, and then directly to the data disk (hard disk 2) installation, only need to add the startup script to the Rc.local file, the system is a good recovery.

Disaster recovery: When the data itself is compromised, determine the point of time of the damage, and then recover from the backup data.

Design Essentials of Application

1. Is the database not available?
The database can simplify the structure design of many applications, but it is also a large application of system resource consumption. So many applications without very high real-time statistical requirements, can be recorded in the file log, regular import to the database for follow-up statistical analysis. If you still need to record a 2-dimensional table structure, the structure is simple enough to use the DBM structure. Even if you need to use a database, the application cannot use a commercial database that supports foreign keys without the complexity of data integrity requirements.

2. The main bottleneck of database services: The number of connections for a single service for an application, if the design of the database table structure can be designed according to the paradigm of the database principle, and the latest version of MySQL has been used, and is running in a more optimized way, The final major bottleneck is generally the number of connections to a single service, even if a database can support concurrent 500 connections, it is best not to use the application at this point because the number of concurrent connections is too large for the threads that the database service itself uses for scheduling. So if the application allows: let a machine run a few more MySQL services share. Balanced service planning on multiple MySQL service ports: for example App_1 ==> 3301 app_2 ==> 3302...app_9 ==> 3309. It is normal for a 1G memory machine to run 10 MySQL. It is more efficient to have 10 mysqld to assume 1000 concurrent connections than 2 mysqld to assume 1000. Of course, this will also bring some application programming complexity;

3. Using a separate database server (not to grab memory from the foreground Web service), MySQL has more memory and may be able to effectively cache the result set;

4. Use the Pconnect and polling mechanisms as much as possible to save the cost of establishing a connection to the MySQL service;

5. Horizontal split of the table: allow the most frequently accessed 10% of the data to be placed in a small list, 90% of the historical data in an archive, the middle of the data through regular "move" and periodically delete invalid data to save. This is always in the application of 10% data to choose, more conducive to data caching, do not expect a single table in MySQL in the number of records in more than 100,000 or more high efficiency.

6. Vertical split (transition Fan Hua): All fixed-length fields (char, int, etc.) are placed in one table, all variable-length fields (VARCHAR,TEXT,BLOB, etc.) are placed in another table, and 2 tables are associated with a primary key, so that The fixed-length field table can be greatly optimized (even with the heap table type, the data is fully accessible in memory), and here is another principle, for us, to use fixed-length fields as much as possible through the loss of space in exchange for improved access efficiency. MySQL supports a variety of table types, in fact, for different applications to provide a different optimization methods;

7. Carefully examine the application's indexing design and even add--log-slow-queries[=file to the service launch to track analysis application bottlenecks.