http://www.chedong.com/tech/mysql.html Author: Cha Dong chedong@bigfoot.com Disclaimer: can be reproduced, reprint, please be sure to indicate the original source and author information, reproduced Summary: Some experience with MYSQL services Mainly considering the MYSQL service planning design from the following aspects. MYSQL service install / configuration versatility; system upgrades and data migration convenience; backup and system quick recovery; database application design key; mysql server planning ============== == For future maintenance, upgrade the backup of the convenient and data security, it is best to install the mysql program files and data on "different hard disks, respectively. / / | / usr <== operating system | / home / mysql <== mysql home directory, in order to facilitate upgrade, this is just a link hard disk for a new version of the directory 1 ==> | /Home/mysql-3.23.54/ < == The latest version of MySQL / Home / MySQL link to here / / home / mysql-old / <== previously running old version of mysql / / data / app_1 / <== application data and startup script and other hard drives 2 == > | / data / app_2 // / data / app_3 / mysql service installation and service start: mysql generally uses the current Stable version, try not to use --with-charset = option, I feel with-charse only It is only useful when sorted, and these options bring a lot of trouble on the migration of the data.
CONFIGURE --PREFIX = / home / mysqlmake make install service boot and stop ================ 1 Copy default mysql / var / mysql to / data / app_1 / directory 2 MySQLD startup script: start_mysql.sh #! / Bin / shrundir = `Dirname" $ 0 "` echo "$ rundir" / home / mysql / bin / safe_mysqld --user = mysql - pid-file = "$ RUNDIR "/Mysql.pid --dataDir =" $ rundir "/ var" $ @ "/ - o max_connections = 500-t wait_timeout = 600 -o key_buffer = 32m --port = 3402 --socket =" $ rundir "/ mysql . Sock & Note: - pid-file = "$ rundir" /mysql.pid - Socket = "$ rundir" /mysql.sock --dadir = "$ rundir" / var purpose is to temporary data and application temporary The file is put together; -o is later usually the server starts global variable optimization parameters, sometimes needs to be adjusted according to specific application; - port: Different applications use port parameters to different services, one service can provide the number of connections It is the main bottleneck of MySQL services; after modifying different services to different ports, add: /DATA/APP_1/Start_Mysql.sh/data/App_2/start_mysql.sh/data/App_3/start_mysql.sh : Must write full path 3 mysqld stop scripts: stop_mysql.sh #! / Bin / shrundir = `Dirname" $ 0 "` echo "$ rundir" / home / mysql / bin / mysqladmin -u mysql -s "$ rundir" / MySQL.Sock Shutdown uses this script is the benefits of: 1 service start: just modify the --port = parameter in the script. The data and service scripts in a single directory can be packaged separately. 2 All services are located in / data / app_1 / directory: such as: mysql.pid mysql.sock, multiple services do not affect each other when you start multiple services on a server. However, it is possible to be deleted by other applications in the default / TMP / next. 3 When the hard disk 1 has a problem, the hard disk 2 can be placed directly on a MYSQL server, and the service can be immediately resumed (if you put it in my.cnf, you will need to back up the corresponding profile).
After the service is started, the corresponding file and directory distribution are as follows: /data/app_1/start_mysql.sh service start script stop_mysql.sh service Stop script mysql.pid service's SockVar / Data area mysql after Idmysql.Sock service / User Library APP_1_DB_1 / Application Library App_2_DB_2 /.../ DATA / App_2 / ... View all application process ID: cat /data/*/mysql.pid View All Database Error Logs: CAT / DATA / * / VAR /*.err Personal suggestion: MySQL's main bottleneck is on the port's connection, so after optimizing the table structure, the corresponding single mysql service CPU occupied is still more than 10%, and it is necessary to consider split the service to multiple Opened on Port. Service backup ========== Try to use mysql dump instead of direct backup data files, the following is a script that follows WeekDay: Backup interval and cycle can be determined according to the demand of 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 So Write in crontab usually : * 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 Note: 1 In crontab '%' needs to escape into '/%' 2 According to log statistics, the application load is the lowest in the morning, and then back up in the local backup server, or directly Create a database backup account, back up directly on a remote server, remote backup only need to change -s /path/to/msyql.sock in the above scripts to -h ip.address. Data recovery and system upgrade ====================== 日常 maintenance and data migration: The hard drive is generally the life of the system in the case where the data disk is not broken. Minimum hardware. The upgrade and hardware upgrade of the system (including operating system and mysql application) will encounter data migration. As long as the data is unchanged, first install the server, then install the data disk (hard disk 2) directly, just re-add the startup script to the rc.local file, the system is a good recovery. Disaster recovery: The data itself determines the time point of the damage in the case of being destroyed, and then recovered from backup data. Application design points ============== Non-use databases? Although the database can simplify the structure design of many applications, it is also an application that is relatively large in system resource consumption. So in some cases, the DBM is better than the database, such as: Many applications do not have a high real-time statistical demand, fully recorded in the file log, regular import to the database to do subsequent statistical analysis . If you still need to record 2-dimensional table structure, you can use the DBM structure if the structure is simple enough.
