MySQL Multi-instance installation

Multi-instance installation with multiple different server ports open at the same time 3306 3307

Large company installation database is generally compiled and installed, and will be made into RPM packages after optimization for uniform use

Install MySQL, you can use, and then do a long instance installation, here MySQL installation directory/application/mysql

Mkdir-p/data/{3306,3307}/data

tree/data/

ll/application/mysql/support-files/#mysql默认为用户提供的多个配置文件模板my *.conf

Vim/data/3306/my.cnf

[Client]

Port = 3306

Socket =/data/3306/mysql.sock

[MySQL]

No-auto-rehash

[Mysqld]

user = MySQL

Port = 3306

Socket =/data/3306/mysql.sock

Basedir =/application/mysql

DataDir =/data/3306/data

Open_files_limit = 1024

Back_log = 600

Max_connections = 800

Max_connect_errors = 3000

Table_cache = 614

external-locking = FALSE

Max_allowed_packet =8m

Sort_buffer_size = 1M

Join_buffer_size = 1M

thread_cache_size = 100

Thread_concurrency = 2

Query_cache_size = 2M

Query_cache_limit = 1M

Query_cache_min_res_unit = 2k

Default_table_type = InnoDB

Thread_stack = 192K

Transaction_isolation = read-committed

Tmp_table_size = 2M

Max_heap_table_size = 2M

Long_query_time = 1

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

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

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

Binlog_cache_size = 1M

Max_binlog_cache_size = 1M

Max_binlog_size = 2M

Key_buffer_size = 16M

Read_buffer_size = 1M

Read_rnd_buffer_size = 1M

Bulk_insert_buffer_size = 1M

Lower_case_table_names = 1

Skip-name-resolve

Slave-skip-errors = 1032,1062

Replicate-ignore-db=mysql

Server-id = 1

Innodb_additional_mem_pool_size = 4M

Innodb_buffer_pool_size = 32M

Innodb_data_file_path = Ibdata1:128m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 2M

Innodb_log_file_size = 4M

Innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[Mysqldump]

Quick

Max_allowed_packet = 2M

[Mysqld_safe]

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

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

Modify the directory and change it to your MySQL installation directory

Sed-i "S#/application/mysql#/usr/local/mysql#g"/data/3306/my.cnf

Vim/data/3307/my.cnf

[Client]

Port = 3307

Socket =/data/3307/mysql.sock

[MySQL]

No-auto-rehash

[Mysqld]

user = MySQL

Port = 3307

Socket =/data/3307/mysql.sock

Basedir =/application/mysql

DataDir =/data/3307/data

Open_files_limit = 1024

Back_log = 600

Max_connections = 800

Max_connect_errors = 3000

Table_cache = 614

external-locking = FALSE

Max_allowed_packet =8m

Sort_buffer_size = 1M

Join_buffer_size = 1M

thread_cache_size = 100

Thread_concurrency = 2

Query_cache_size = 2M

Query_cache_limit = 1M

Query_cache_min_res_unit = 2k

Default_table_type = InnoDB

Thread_stack = 192K

Transaction_isolation = read-committed

Tmp_table_size = 2M

Max_heap_table_size = 2M

Long_query_time = 1

Pid-file =/data/3307/mysql.pid

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

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

Binlog_cache_size = 1M

Max_binlog_cache_size = 1M

Max_binlog_size = 2M

Key_buffer_size = 16M

Read_buffer_size = 1M

Read_rnd_buffer_size = 1M

Bulk_insert_buffer_size = 1M

Lower_case_table_names = 1

Skip-name-resolve

Slave-skip-errors = 1032,1062

Replicate-ignore-db=mysql

Server-id = 3

Innodb_additional_mem_pool_size = 4M

Innodb_buffer_pool_size = 32M

Innodb_data_file_path = Ibdata1:128m:autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 2M

Innodb_log_file_size = 4M

Innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

[Mysqldump]

Quick

Max_allowed_packet = 2M

[Mysqld_safe]

Log-error=/data/3307/mysql_3307.err

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

Modify the directory and change it to your MySQL installation directory

Sed-i "S#/application/mysql#/usr/local/mysql#g"/data/3307/my.cnf

Edit MySQL startup script

Vim/data/3306/mysql

#!/bin/sh

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

#this scripts is created by Jie at 2017-06-20

#init

port=3306

Mysql_user= "Root"

Mysql_pwd= "Qwe123" #<== here and Database password consistent

Cmdpath= "/application/mysql/bin"

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

#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 &

Else

printf "MySQL is running...\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

Fi

}

#restart function

Function_restart_mysql ()

{

printf "Restarting mysql...\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

Modify Directory Sed-i "S#/application/mysql#/usr/local/mysql#g"/data/3306/mysql

Vim/data/3307/mysql

#!/bin/sh

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

#this scripts is created by Jie at 2017-06-20

#init

port=3307

Mysql_user= "Root"

Mysql_pwd= "Qwe123" #<== here and Database password consistent

Cmdpath= "/application/mysql/bin"

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

#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 &

Else

printf "MySQL is running...\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

Fi

}

#restart function

Function_restart_mysql ()

{

printf "Restarting mysql...\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

Modify Directory Sed-i "S#/application/mysql#/usr/local/mysql#g"/data/3307/mysql

[Email protected] data]# tree/data/

/data/

├──3306

│├──data

│├──my.cnf

│└──mysql

└──3307

├──data

├──my.cnf

└──mysql

Multi-instance startup file, start the MySQL different instance service, the execution of the command in essence there is a difference

Mysqld_safe--defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &

Mysqld_safe--defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &

Stop MySQL different instance service in real order

Mysqladmin-u root-pqwe123-s/data/3306/mysql.sock shutdown

Mysqladmin-u root-pqwe123-s/data/3307/mysql.sock shutdown

Authorized Chown-r Mysql.mysql/data

Find/data-name Mysql|xargs Ls-l

Find/data-name Mysql|xargs chmod 700

Find/data-name mysql-exec ls-l {} \; the same effect on the root.

Ls/application/mysql/bin/mysql

Echo ' Export path=/application/mysql/bin: $PATH ' >>/etc/profile #把mysql安装目录bin定义到环境变量

[Email protected] ~]# tail-1/etc/profile

Export Path=/application/mysql/bin: $PATH

Source/etc/profile

[Email protected] ~]# echo $PATH

/application/mysql/bin:/application/mysql/bin:/application/mysql/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin :/usr/sbin:/usr/bin:/root/bin

You can also set a soft connection without setting environment variables

Ln-s/application/mysql/bin/*/usr/local/sbin/

Cd/application/mysql/scripts

Initializing the database

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

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

See the following display success

Installing MySQL system tables ...

170620 12:43:43 [Note]/usr/local/mysql/bin/mysqld (mysqld 5.5.48-log) starting as Process 29870 ...

Ok

Filling Help Tables ...

170620 12:43:43 [Note]/usr/local/mysql/bin/mysqld (mysqld 5.5.48-log) starting as Process 29877 ...

Ok

View Catalog Tree/data

Start a multi-instance database

/data/3306/mysql start

/data/3307/mysql start

echo "/data/3306/mysql start" >>/etc/rc.local #加入开机启动

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

Mysql-s/data/3306/mysql.sock #登入数据库无密码

Mysql-s/data/3307/mysql.sock

Come in as direct root.

Set Password

Mysqladmin-u root-s/data/3306/mysql.sock password ' qwe123 '

Mysql-uroot-pqwe123-s/data/3306/mysql.sock

Mysqladmin-u root-s/data/3307/mysql.sock password ' qwe123 '

Mysql-uroot-pqwe123-s/data/3307/mysql.sock

Tip: Because MySQL has a password, it is not safe to be seen by other users, so change the permissions to 700, group root

Find/data-type f-name "MySQL"-exec chmod 700 {} \;

Find/data-type f-name "MySQL"-exec chown root.root {} \;

Disable the use of Pkill, kill-9, killall-9, and other commands to force the database to be killed, which can cause a failure to start.

MySQL Multi-instance installation