MySQL service maintenance notes

MySQL service maintenance notes Summary: 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: 1. the versatility of MySQL service installation/configuration; 2. system upgrades and data migration convenience; 3. backup and quick system recovery; 4. key Points of database application design; 5. one application optimization practice; Plan for www.2cto.com MySQL Server ================== for future maintenance, upgrade backup convenience 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 Hard 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/ install app_3/MySQL and start the service: mySQL generally uses the current STABLE version: Do not use the -- with-charset = option as far as possible. I feel that with-charset is only useful in alphabetical order, these options will cause a lot of trouble for data migration. Www.2cto.com
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-innodbmakemake install start and stop the service ======================= 1 copy the default mysql /var/mysql to the/data/app_1/directory, 2 MySQLD Startup Script: start_mysql.sh #! /Bin/sh www.2cto.com 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 is used to put the corresponding data together with the temporary application file.-O is followed by the global variable optimization Parameter for server startup. Application adjustment; -- 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 www.2cto.com modifies different services to different ports. add/data/app_1/start_mysql.sh/data/app_2/start_mysql.sh/data/app_3/start_mysql.sh to the local file. Note: The stop script of full path 3 MySQLD must be written: stop_mysql.sh #! /Bin/shrundir = 'dirname "$0" 'echo "$ rundir"/home/mysql/bin/mysqladmin-u mysql-S "$ rundir"/mysql. sock shutdown uses this script to enable 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. All service files of www.2cto.com 2 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 mysql. sock var/mysql/user database app_mongodb_1/application database app_mongodb_2 /... www.2cto.com/data/app_2 /... view All application process IDs: cat/data/*/mysql. pid to view error logs of 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 ===========use MySQL DUMP instead of backing up data files as much as possible. Below is a script to back up data on weekday: the backup interval and cycle can be determined based on the backup requirements www.2cto.com/home/mysql/bin/mysqldump-S/data/app_1/mysql. sock-umysql db_name | gzip-f>/path/to/backup/db_name. 'date should contain w'.dump.gz so it is generally written in CRONTAB: 15 4 */home/mysql/bin/mysqldump-S/data/app_1/mysql. sock-umysql db_name | gzip-f>/path/to/backup/db_name. 'date + \ mongow'.dump.gz Note: 1 in crontab, '%' needs to be converted to '\ %' 2 based on log statistics, When the application load is the lowest, it is generally backed up locally at AM and then uploaded to a remote backup server, or a database backup account is directly created and backed up directly on a 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 at www.2cto.com ============================== 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 of the damage and restore the data from the backup.