MySQL service maintenance notes page 1/2

Content Abstract: some experience in using MySQL services, mainly from the following aspects of MySQL service planning and design. For high-load sites, PHP and MySQL running together (or any application and database running together planning) is the biggest performance bottleneck. Such a design is like making people draw a picture in one hand, in this way, the work efficiency of two people is definitely not as high as making one person draw a circle and one person draw a picture, running applications and databases on one high-performance server may not be as fast as running on two normal servers.


The following are the optimization suggestions for MySQL as a dedicated database server:

The versatility of MySQL service installation/configuration;
Convenient system upgrades and data migration;
Fast backup and system recovery;
Key Points of database application design;
One application optimization practice;
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 |/home/mysql <= mysql main directory, this is just a link to the latest version of the directory Disk 1 ==>|/ home/mysql-3.23.54/<== latest version of mysql/home/mysql link here/home/mysql-old/< = previous versions of mysql/data/app_1/<= hard disks such as application data and startup scripts 2 ==>|/data/app_2/data/app_3/
Install the MySQL service and start the service:
MySQL generally uses the current STABLE version:
Try 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.
Try not to use innodb. innodb is mainly used for enterprise-level support such as foreign keys and transactions, at the cost of reducing the speed by an order of magnitude than MYISAM.
./Configure -- prefix =/home/mysql -- without-innodb
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: for different services, you only need to modify the -- port [= port number] 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_mongodb_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:
15 4 */home/mysql/bin/mysqldump-S/data/app_1/mysql. sock-umysql db_name | gzip-f>/path/to/backup/db_name. 'Data has been downloaded w'.dump.gz
Note:
1. In crontab, '%' must be converted to '%'
2. According to log statistics, the minimum application load is usually between 4 and 6 in the morning.

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.