Multiple instances of MySQL on a single server

Use different configuration files, start files, data files, log files.

The general usage is to configure multiple instances by port in the/data directory:/data/{3306,3307}/data

Vim/data/3306/my.cnf

<code>

[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

Log_long_format

Log-error =/data/3306/error.log

Log-slow-queries =/data/3306/slow.log

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

Log-bin =/data/3306/mysql-bin

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

Expire_logs_days = 7

Key_buffer_size = 16M

Read_buffer_size = 1M

Read_md_buffer_size = 1M

Bulk_insert_buffer_size = 1M

Myisam_sort_buffer_size = 1M

Myisam_max_sort_file_size = 10G

Myisam_max_extra_sort_file_size = 10G

Myisam_repair_threads = 1

Myisam_recover

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

</code>

Vim/data/3307/my.cnf

<code>

[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

Log_long_format

#log-error =/data/3307/error.log

#log-slow-queries =/data/3307/slow.log

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

#log-bin =/data/3307/mysql-bin

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

Expire_logs_days = 7

Key_buffer_size = 16M

Read_buffer_size = 1M

Read_md_buffer_size = 1M

Bulk_insert_buffer_size = 1M

Myisam_sort_buffer_size = 1M

Myisam_max_sort_file_size = 10G

Myisam_max_extra_sort_file_size = 10G

Myisam_repair_threads = 1

Myisam_recover

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

</code>

Vim/data/3306/mysql

<code>

#!/bin/bash

# This scripts was created by Swolice

#init

port=3306

Mysql_user= "Root"

mysql_pwd= "Root"

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 Funciton

Function_restart_mysql ()

{

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

</code>

The essence of starting MySQL is:

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

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

Chown-r Mysql:mysql/data

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

Find/data-name MySQL | Xargs chmod 700

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

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

The primary purpose of initializing the database is to create the underlying database I see, generate MySQL database tables, etc...

Initialize Database error Handling:

/usr/libexec/mysqld:unknown variable ' table_open_cache=4 '

Installaton of system tables failed!

Using the wrong initialization database command, the system comes with a/usr/bin/mysql_install_db error.

A multi-instance MySQL login for a host can only be logged on with sock:

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

Mysqladmin-uroot-s/data/3306/mysql.sock password ' new password '

How to connect to MySQL remotely using:

Mysql-uroot-proot-p3308-h 10.0.55.61

Multiple instances of MySQL on a single server