MySQL Service maintenance notes

Some experience with MySQL service, mainly from the following aspects of the MySQL service planning and design considerations.

1 MySQL Service installation/configuration versatility;

2 system upgrades and data migration convenience;

3 Backup and system rapid recovery;

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         
            /    /opt/mysql              
Hard Drive 1==>|    /opt/mysql-3.23.54/     <== The latest version of the Mysql/home/mysql link to here
            \   /opt/mysql-old/         <== old version of MySQL previously run
             \  /etc/my.multi.cnf      <== multiple daemon configuration file

             /   /data/app_3301/             <== Application data, etc.,
Hard Drive 2==>|    /data/app_3302/
             \   /data/app_3303/

Installation of MySQL services and startup of services:

MySQL generally uses the current stable version, try not to use the--with-charset= option, I feel that with-charset only in alphabetical order when the useful, these options will bring a lot of trouble with the data migration.

Configure--prefix=/home/mysql

Make

Make install

Start and stop of services

================

1 Copy the default Mysql/var/mysql to the/database/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:

/database/app_1/start_mysql.sh

/database/app_2/start_mysql.sh

/database/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