Multiple instances of MySQL on a single server

Source: Internet
Author: User
Tags rehash mysql login

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.