Single-Machine multi-instance installation MySQL

1. In the data directory, create a new

/data/3306/data

/data/3307/data

2. Modify Permissions

Chown-r Mysql:mysql/data

3. Copying files

cd/data/3306/

Cp/usr/local/mysql/support-files/my-small.cnf.

MV My-small.cnf MY.CNF

4. Modify the My.cnf file

Cat 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 =/usr/local/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_rnd_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_oldboy3306.err

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

5. Modify MySQL File

VI MySQL

#!/bin/sh

port=3306

Mysql_user= "Root"

Mysql_pwd= ""

Cmdpath= "/usr/local/mysql/bin"

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

#startup function

Function_start_mysql ()

{

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

printf "Starting mariamysql...\n"

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

Else

printf "Mariamysql 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

6. Modify the permissions of MySQL

chmod MySQL

7. Initialize the database (note the datadir here)

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/3306/data/--user=mysql

8. Start the database

/data/3306/mysql start

Lsof-i: 3306

9. Log in to the database

/usr/local/mysql/bin/mysql-s/data/3306/mysql.sock

10. Configure a second instance

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

cp/data/3306/mysql/data/3307/

Modify a file

Sed-i ' S#3306#3307#g '/data/3307/my.cnf

Sed-i ' S#3306#3307#g '/data/3307/mysql

Chown-r mysql.mysql/data/3307/

chmod 700/data/3307/mysql

/usr/local/mysql/scripts/mysql_install_db--basedir=/usr/local/mysql--datadir=/data/3307/data/--user=mysql

/data/3307/mysql start

NETSTAT-LNT |grep 3307

/usr/local/mysql/bin/mysql-s/data/3307/mysql.sock

11. Check

NETSTAT-LNT |grep 330

Single-Machine multi-instance installation MySQL