Single-machine multi-instance deployment for MySQL

Centos6.6 Installing and configuring a single-machine multi-instance MySQL database

This article describes the environment for installing a single-machine multi-instance MySQL database as follows:

System platform Environment: Centos6.6

Mysql Package: mysql-5.5.32.tar.gz

One: Install the required dependencies and compile software for MySQL

#yum Install Ncurses-devel libaio-devel-y

II: Install the software required to compile MySQL (mysql5.2 version will need to use CMake to compile and install MySQL)

#rpm-IVH cmake-2.8.12.2-4.el6.x86_64 (CD-ROM will be self-contained or self-compiled installation)

If you are compiling your own installation cmake you can #which CMake to see the full path for later compilation and installation of MySQL

Three: Start installing MySQL Database

1: Create MySQL user account

# useradd-s/sbin/nologin-m mysql && ID mysql

2: Get the source code package for MySQL (if the connection fails, you can find the link yourself)

# wget Http://dev.mysql.com/downloads/mysql

3: Install MySQL using compile mode

# Tar XF mysql-5.5.32.tar.gz

# CD mysql-5.5.32

# CMake. -dcmake_install_prefix=/application/mysql-5.5.32-dmysql_datadir=/application/mysql-5.5.32/data-dmysql_unix_ Addr=/application/mysql-5.5.32/tmp/mysql.sock-ddefault_charset=utf8-ddefault_collation=utf8_general_ci-dextra_ Charsets=gbk,gb2312,utf8,ascii-denabled_local_infile=on-dwith_innobase_storage_engine=1-dwith_federated_ Storage_engine=1-dwith_blackhole_storage_engine=1-dwithout_example_storage_engine=1-dwithout_partition_storage _engine=1-dwith_fast_mutexes=1-dwith_zlib=bundled-denabled_local_infile=1-dwith_readline=1-dwith_embedded_ Server=1-dwith_debug=0

# Make && make install

# ln-s/application/mysql-5.5.32//application/mysql

At this point the MYSQL-5.5.32 source code package uses the CMake way installs even if succeeds!!

Since the configuration installs the single-machine multi-instance database, the next thing we need to do is to create the necessary data files, configuration files, and boot files for each instance.

Four: the instance creates the necessary data files, configuration files, and startup files.

1: Create data directory for each instance

#mkdir-P/data/{3306,3307}/data

2: Create profile for each instance (multiple profile templates provided by default for Ls-l/APPLICATION/MYSQL/SUPORT-FILES/MY*.CNF database, users can select the appropriate template based on server hardware configuration)

#vim/DATA/3306/MY.CNF (The configuration example is as follows #为注释行)

[Client]

Port = 3306

Socket =/data/3306/mysql.sock

[MySQL]

No-auto-rehash

[Mysqld]

Port = 3306

user = MySQL

Socket =/data/3306/mysql.sock

Basedir =/application/mysql

DataDir =/data/3306/data

Back_log = 100

Max_connections = 800

max_connect_errors = 100

Table_open_cache = 2048

external-locking = FALSE

Binlog_cache_size = 1M

Max_heap_table_size = 2M

Read_buffer_size = 2M

Read_rnd_buffer_size = 2M

Sort_buffer_size = 2M

Join_buffer_size = 1M

thread_cache_size = 100

Thread_concurrency = 8

Query_cache_size = 2M

Query_cache_limit = 2M

#ft_min_word_len = 4

#default-storage-engine = InnoDB

Thread_stack = 192K

#transaction_isolation = Repeatable-read

Tmp_table_size = 2M

#binlog_format =mixed

#slow_query_log

Long_query_time = 2

Pid-file =/data/3306/mysqld.pid

Relay-log =/data/3306/relay-bin

Relay-log-info-file =/data/3306/relay-log.info

Max_binlog_cache_size = 1M

Max_binlog_size = 2M

Key_buffer_size =16m

Read_buffer_size = 1M

Read_rnd_buffer_size = 1M

Lower_case_table_names = 1

Skip-name-resolve

Slave-skip-errors =1032,1062

replicate-ignore-db = MySQL

Bulk_insert_buffer_size = 1M

Server-id = 1

#log-bin =/data/3306/logbin/mysql-bin

#log-bin-index =/data/3306/logbin/log-bin.index

#myisam_sort_buffer_size = 128M

#myisam_max_sort_file_size = 10G

#myisam_repair_threads = 1

#myisam_recover

#skip-innodb

Innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size = 32M

Innodb_data_file_path = Ibdata1:128m:autoextend

Innodb_file_io_threads = 8

#innodb_read_io_threads = 8

#innodb_force_recovery =1

Innodb_thread_concurrency = 16

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 2M

Innodb_log_file_size = 24M

Innodb_log_files_in_group = 3

#innodb_log_group_home_dir

innodb_max_dirty_pages_pct = 90

#innodb_flush_method =o_dsync

Innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[Mysqldump]

Quick

Max_allowed_packet = 5M

[Mysqld_safe]

Log-error=/data/3306/mysql_niutianwen3306.err

Pid-file=/data/3306/mysqld.pid

#cp/data/3306/my.cnf/data/3307/my.cnf

#sed ' s/3306/3307/g '/data/3307/my.cnf

3: Create a MySQL multi-instance startup file

#vim/data/3306/mysqld (Boot script configuration example as follows, for reference)

#!/bin/bash

#this scripts is created by Niutianwen at 2016-10-20

#site: www.niutianwen.com

##############################

port=3306

Mysql_user= "Root"

Mysql_pwd= "Niutianwen" #<== to be consistent with database password >

Cmdpath= "/application/mysql/bin"

mysql_sock= "/data/${port}/mysql.sock"

Txtfile= "/data/${port}/txt"

Declare-i value

#startup function

Function_start_mysql ()

{

if [!-e "$mysql _sock"];then

printf "Starting mysql...\n"

/bin/sh ${cmdpath}/mysqld_safe--defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &

echo > ${txtfile}

#echo ${txtfile}

#netstat-TNLP | grep ": $port" > $txtfile

#value = ' Cat $txtfile |awk ' {print $4} ' |awk-f ': ' {print $} '

Sleep 10

Value= ' NETSTAT-TNLP | grep ": $port" | awk ' {print $4} ' | Awk-f ': ' {print $NF} '

#echo $value

if ["${value}"-eq "${port}"];then

printf "MYSQL is up...! \ n "

Else

printf "MYSQL is not startup...! \ n "

Fi

Else

printf "MYSQL is runing...\n"

Exit

Fi

}

#stop function

Function_stop_mysql ()

{

if [!-e "$mysql _sock"];then

printf "MYSQL is stopped...\n"

Exit

Else

printf "stoping mysql...\n"

${cmdpath}/mysqladmin-u${mysql_user}-p${mysql_pwd}-s/data/${port}/mysql.sock shutdown && echo "MySQL is down ...!"

Fi

}

#restart function

Function_restart_mysql ()

{

printf "Restarting nysql...\n"

Function_stop_mysql

Sleep 2

Function_start_mysql

}

Case $ in

Start

Function_start_mysql

;;

Stop

Function_stop_mysql

;;

Restart

Function_restart_mysql

;;

*)

printf "Usage:/data/${port}/mysql {start|stop|restart}\n"

Esac

# Cp/data/3306/mysqld/data/3307/mysqld

# sed ' s/3306/3307/g '/data/3307/mysqld

At this point two instances of data files, configuration files, startup files are all configured.

V: Configure file permissions for MySQL multi-instance

1: Authorize MySQL users to manage the entire multi-instance root directory/data

#chown-R Mysql.mysql/data

2: Because the startup file has password information for the database, you should set 700 permissions best

#find/data-name mysqld | Xargs chmod 700

Six: The MySQL command is added to the global path for ease of use.

# echo ' Export path=/application/mysql/bin: $PATH ' >>/etc/profile

# Source/etc/profile (direct entry into force)

# echo $PATH (see if the configuration was successful)

Seven: Initialize the MySQL multi-instance database file (the essence of the initialization database is the library file that creates the underlying database)

# cd/application/mysql/scripts

#./mysql_install_db--basedir=/application/mysql--datadir=/data/3306/data--user=mysql

#./mysql_install_db--basedir=/application/mysql--datadir=/data/3307/data--user=mysql

Eight: Start MySQL multi-instance database

#/data/3306/mysqld Start (Stop|restart)

Nine: Configure MySQL database to boot from

# echo "#mysql multi Instances" >>/etc/rc.local

# echo "/data/3306/mysql start" >>/etc/rc.local

# echo "/data/3307/mysql start" >>/etc/rc.local

Ten: Login Test and Security configuration (login for MySQL setting password)

# mysql-s/data/3306/mysql.sock

# mysqladmin-uroot-s/data/3306/mysql.sock password ' Niutianwen '

This is the single MySQL dual instance configuration has been completed! If you want to add MySQL instance again just repeat the above process! Thank you. Focus on the Linux road so that everyone is not alone!

This article is from the "Focus on Linux Technology" blog, please be sure to keep this source http://niutianwen.blog.51cto.com/10716187/1865228

Single-machine multi-instance deployment for MySQL