MySQL service maintenance and application design Note (1) _ MySQL

MySQL service maintenance and application design notes (1) The following are some experiences using MYSQL services, mainly from the following aspects: MYSQL service planning and design.

1. versatility of MYSQL service installation/configuration;

2. easy system upgrade and data migration;

3. fast backup and system recovery;

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 }=> hard disk 1
/Home/mysql <= mysql application
...
/Data/app_1/<= application data and script} => Hard Disk 2
/Data/app_2/
/Data/app_3/

Install the mysql service and start the service:
MYSQL generally uses the current STABLE version, and tries 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.

Configure -- prefix =/home/mysql
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