MySQL service maintenance note (above) _ MySQL

MySQL service maintenance notes (I) The following are the optimization suggestions for MySQL as a dedicated database server:

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 hard disk 1 ==>| is a link to the latest version of the directory |/home/mysql-3.23.54/<== the latest version of mysql/home/mysql link here // home/mysql- old/<= The old mysql version that was running earlier than/data/app_1/<= hard disk such as application data and startup script 2 ==>|/data/app_2 /// data/app_3/

MySQL service installation and service startup

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 "{GetProperty (Content )}"'
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 "{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: 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 +/mongow'.dump.gz

Note:

1 in crontab, '%' needs to 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.

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, 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 database data is damaged, determine the time point for the destruction and restore the data from the backup.