MYSQL service maintenance notebook paper from: http: //www.chedong.com/tech Author: Cha Dong (chedong@bigfoot.com) (2002-12-24 06:02:00)
Last updated: 2002-08-23 17:56:46 Copyright Notice: You can reprint anything, please indicate the original source and author information http://www.chedong.com/tech/mysql.html to use MySQL services Experience, the MYSQL service planning design, which is mainly considered in the following aspects. 1 MYSQL service install / configuration versatility; 2 system upgrades and data migration convenience; 3 backup and system quick recovery; mysql server planning ================ 为 for In order to maintain, upgrade the convenient and data security of the backup, it is best to install the mysql program files and data on "different hardware", respectively. / / Usr <== operating system} ==> Hard disk 1 / home / mysql <== mysql application ... / data / app_1 / <== application data and script} ==> hard disk 2 / data / app_2 / / DATA / App_3 / MySQL Service Installation and Service Startup: MySQL generally uses the current Stable version, try not to use --with-charset = option, I feel that with-charSet is only useful when sorting alphabetics, these options There will be 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 launch script stop_mysql.sh service Stop script mysql.pid service SOCK VAR / Data Area mysql / user library APP_1_DB_1 / App Library APP_2_DB_2 / ... / DATA / App_2 / ... View all application process ID: cat /data/*/mysql.pid View Error Log for All Database: CAT / DATA / * /var/*.err Personal Recommendation: MySQL's main bottleneck is on Port's connections, so after optimizing the table structure, the CPU occupied by the corresponding single mysql service is still more than 10%, and it is necessary to consider split the service to Multiple ports are running. 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 ===============
