Content Summary: The use of MySQL service experience, mainly from the following aspects of the MySQL service planning and design considerations. For a high load site, PHP and MySQL run together (or any application that runs together with the database) are the most performance bottlenecks, such a design is like a hand drawing square, so that the efficiency of 2 of people is not as good as let a person dedicated to draw a person specialized painting side of the high efficiency, Letting applications and databases run on a high-performance server might as well run on 2 of ordinary servers faster.
The following are the best recommendations for MySQL as a dedicated database server:
The commonality of installation/configuration of MySQL service;
system upgrades and data migration convenience;
Backup and system rapid recovery;
Design Essentials of database application;
One application optimization actual combat;
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 | /home/ Mysql <== mysql Home Directory, For easy upgrades, this is just a link to the latest version directory HDD 1==>| /home/mysql-3.23.54/ <== Latest version of Mysql /home/mysql link to here /home/mysql-old/ <== Previous versions of MySQL that ran previously / /data/app_1/ <== Application Data and startup scripts, such as hard disk 2==>| /data/app_2/ /data/app_3/
Installation of MySQL services and startup of services:
MySQL typically uses the current stable version:
Try not to use the--with-charset= option, I feel that with-charset is only useful when sorting alphabetically, and these options can cause a lot of trouble with migrating data.
Try not to use INNODB,INNODB primarily for enterprise-class support that requires foreign keys, transactions, and so on, at the expense of a drop in order of magnitude over MyISAM.
./configure--prefix=/home/mysql--without-innodb
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 &
Comments:
--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[= port number parameters in the script need to be modified for different services. 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_1_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:
4 * * */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 4-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.
Current 1/2 page
12 Next read the full text
