Multi-mysql instance configuration for single system in Centos6.3

Note: This document has two MySQL instances, multiple instance methods and so on

Linux operating system: centOS6.3 64bit (Installation of the system default development package)

Example one:

MySQL version: mysql-5.0.56

port:3306

System catalog:/usr/local/mysql3306

Example two:

MySQL version: mysql-5.1.72

port:3307

System catalog:/usr/local/mysql3307

I. Installing the development pack (using the default CentOS update source):

# yum-y Install wget gcc-c++ ncurses ncurses-devel cmake make Perl bison OpenSSL openssl-devel gcc* libxml2 Curl-devel libjpeg* libpng* freetype*

Two. Close Iptables and SELinux

# Service Iptables Stop

# Setenforce 0

# Vi/etc/sysconfig/selinux

---------------

Selinux=disabled

---------------

Three. Install MySQL database instance

1. Download Compilation Package:

# Su-

# mkdir ~/SRC

# CD SRC

# wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-5.1/mysql-5.1.73.tar.gz

# wget http://down1.chinaunix.net/distfiles/mysql-5.0.56.tar.gz

2. Pre-installation Initial configuration work:

1). Create a MySQL user

# useradd MySQL

2). New MySQL under Data and log subdirectories

# Mkdir/usr/local/mysql{3306,3307}/data

# Mkdir/usr/local/mysql{3306,3307}/log

3). Modify the owner of the directory and the permissions of the group to which it belongs

# Chown-r mysql:mysql/usr/local/mysql{3306,3307}/data/

# Chown-r mysql:mysql/usr/local/mysql{3306,3307}/log/

# chmod 750/usr/local/mysql{3306,3307}/data

# chmod 750/usr/local/mysql{3306,3307}/log

4. Create MySQL-related directories and configure permissions

# mkdir-p/usr/local/mysql{3306,3307}/etc

# Chown-r Mysql.mysql/usr/local/mysql{3306,3307}/etc

# Mkdir-p/var/run/mysqld{3306,3307}

# Chown-r mysql.mysql/var/run/mysqld{3306,3307}

# Mkdir-p/var/lib/mysqld{3306,3307}

# Chown-r mysql.mysql/var/lib/mysqld{3306,3307}

# CP/ETC/MY.CNF/USR/LOCAL/MYSQL{3306,3307}/ETC

3. Unpack and compile the installation

Compile instance one:

# CD ~/SRC

# TAR-ZXVF Mysql-5.0.56.tar.gz

# CD mysql-5.0.56

./configure--prefix=/usr/local/mysql3306--with-mysqld-user=mysql--sysconfdir=/usr/local/mysql3306/etc-- Localstatedir=/usr/local/mysql3306/data--with-tcp-port=3306-enable-assembler--with-mysqld-ldflags=-all-static- -with-charset=utf8--with-extra-charsets=gbk-with-extra-charsets=all--with-plugins=csv,innobase,myisam,heap-- With-unix-socket-path=/tmp/mysql3306.sock

# make

# make Install

Compile instance two:

# CD ~/SRC

# TAR-ZXVF Mysql-5.1.71.tar.gz

# CD mysql-5.1.71

./configure--prefix=/usr/local/mysql3307--with-mysqld-user=mysql--sysconfdir=/usr/local/mysql3307/etc-- Localstatedir=/usr/local/mysql3307/data--with-tcp-port=3307-enable-assembler--with-mysqld-ldflags=-all-static- -with-charset=utf8--with-extra-charsets=gbk-with-extra-charsets=all--with-plugins=csv,innobase,myisam,heap-- With-unix-socket-path=/tmp/mysql3307.sock

# make

# make Install

4. Write MySQL configuration entries:

Example one:

# VI/USR/LOCAL/MYSQL3306/ETC/MY.CNF

-------------------------------------------------------

[Mysqld]

Datadir=/usr/local/mysql3306/data

Socket=/tmp/mysql3306.sock

User=mysql

port=3306

Pid-file=/var/lib/mysqld3306/mysql.pid

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

max_connections= 16384

Skip-name-resolve

Skip-locking

Key_buffer = 256M

Max_allowed_packet = 32M

Table_cache = 3072

Thread_cache_size = 256

Sort_buffer_size = 16M

Read_buffer_size = 4M

Read_rnd_buffer_size = 16M

Net_buffer_length = 256M

Thread_stack = 8M

Query_cache_size = 128M

Query_cache_limit = 2M

wait_timeout=7200

interactive_timeout=7200

#log

Log-error=/usr/local/mysql3306/log/error.log

Log=/usr/local/mysql3306/log/mysql.log

long_query_time=2

log-slow-queries=/usr/local/mysql3306/log/slowquery.log

log-bin=/usr/local/mysql3306/log/bin.log

Expire_logs_days = 15

Sync_binlog = 1

Max_binlog_cache_size = 4294967295

Local-infile=0

[Mysqld_safe]

Log-error=/var/log/mysqld3306.log

Pid-file=/var/run/mysqld3306/mysqld.pid

-------------------------------------------------------

Example two: www.111cn.nEt

# VI/USR/LOCAL/MYSQL3307/ETC/MY.CNF

-------------------------------------------------------

[Mysqld]

Datadir=/usr/local/mysql3307/data

Socket=/tmp/mysql3307.sock

User=mysql

port=3307

Pid-file=/var/lib/mysqld3307/mysql.pid

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

max_connections= 16384

Skip-name-resolve

Skip-locking

Key_buffer = 256M

Max_allowed_packet = 32M

Table_cache = 3072

Thread_cache_size = 256

Sort_buffer_size = 16M

Read_buffer_size = 4M

Read_rnd_buffer_size = 16M

Net_buffer_length = 256M

Thread_stack = 8M

Query_cache_size = 128M

Query_cache_limit = 2M

wait_timeout=7200

interactive_timeout=7200

#log

Log-error=/usr/local/mysql3307/log/error.log

Log=/usr/local/mysql3307/log/mysql.log

long_query_time=2

log-slow-queries=/usr/local/mysql3307/log/slowquery.log

log-bin=/usr/local/mysql3307/log/bin.log

Expire_logs_days = 15

Sync_binlog = 1

Max_binlog_cache_size = 4294967295

Local-infile=0

[Mysqld_safe]

Log-error=/var/log/mysqld3307.log

Pid-file=/var/run/mysqld3307/mysqld.pid

-------------------------------------------------------

5. Add MySQL library file path to the system's library file search path

Method One: Make soft link directly

# ln-s/usr/local/mysql3306/lib/mysql/usr/lib/mysql

Method Two: Use Ldconfig to import system library

# echo "/usr/local/mysql3306/lib" >>/etc/ld.so.conf.d/mysql.conf

# Ldconfig

6. Output MySQL header file to system header file

# ln-s/usr/local/mysql3306/include/mysql/usr/include/mysql

Note: Only one MySQL instance's library file can be added to the system library here without having to add multiple

7. Enter the installation path of the corresponding instance, initialize the respective configuration script

Example one:

# cd/usr/local/mysql3306

# scripts/mysql_install_db--user=mysql--datadir=/usr/local/mysql3306/data

Example two:

# cd/usr/local/mysql3307

# scripts/mysql_install_db--user=mysql--datadir=/usr/local/mysql3307/data

8. Copy the MySQL startup script to the System services directory and change the script configuration

# cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld3306

# cp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld3307

Example one:

# vi/etc/init.d/mysqld3006

Search for the following line with the added parameters in red:

---------------------------------------------

basedir=/usr/local/mysql3306

Datadir=/usr/local/mysql3306/data

Conf=/usr/local/mysql3306/etc/my.cnf

$bindir/mysqld_safe--defaults-file= $conf--datadir= $datadir--pid-file= $server _pid_file $other _args >/dev/null 2 >&1 &

--------------------------------------------

Example two:

# vi/etc/init.d/mysqld3007

Search for the following line with the added parameters in red:

---------------------------------------------

basedir=/usr/local/mysql3307

Datadir=/usr/local/mysql3307/data

Conf=/usr/local/mysql3307/etc/my.cnf

$bindir/mysqld_safe--defaults-file= $conf--datadir= $datadir--pid-file= $server _pid_file $other _args >/dev/null 2 >&1 &

--------------------------------------------

9. System Startup Item related configuration

Example one:

# chkconfig--add mysqld3306 #添加开机启动服务

# chkconfig--level mysqld3306 on #设置mysql启动

Example two:

# chkconfig--add mysqld3307

# chkconfig--level mysqld3307 on

10. Start MySQL

Example one:

# service mysqld3306 Start

Example two: www.111cn.net

# service mysqld3307 Start

11 add MySQL command set to system global variable

Note: If the MySQL client is not installed before the system, you can import the compiled MySQL command set into the system global variable

You can then use the MySQL command set directly, without the need to use absolute path access.

# echo "path= $PATH:/usr/local/mysql3306/bin;export PATH" >>/etc/profile

# Source/etc/profile

12. Set up the initial account, and login backstage:

Example one:

#/usr/local/mysql3306/bin/mysqladmin-u root password 123456 #设置超级管理员密码

#/usr/local/mysql3306/bin/mysql-p3306-s/tmp/mysql3306.sock-uroot-p123456 #连接数据库

Example two:

#/usr/local/mysql3307/bin/mysqladmin-u root password 123456 #设置超级管理员密码

#/usr/local/mysql3307/bin/mysql-p3307-s/tmp/mysql3307.sock-uroot-p123456 #连接数据库

Note: Because of the MySQL environment variable, the system can be used directly in the background of the MySQL command login, where the absolute path is used to standardize the operation

# mysql-p3307-s/tmp/mysql3307.sock-uroot-p123456

13. Port test:

Done..... O (∩_∩) o~