Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of multi-instance configuration and management in MySQL, which is very detailed and has a certain reference value. Interested friends must read it!

Multiple instances of mysql can be implemented in two ways, each of which has its own advantages and disadvantages.

The first is to use multiple configuration files to start different processes to achieve multiple instances, which has the advantages of simple logic and simple configuration, but the disadvantage is that it is not easy to manage.

The second is to implement multiple instances through the official mysqld_multi using separate configuration files. This way of customizing the configuration of each instance is not quite good. The advantage is that it is easy to manage and centrally manage.

Let's practice the installation and management of these two kinds of multi-instances.

Let's start with the first case of using multiple profiles to start multiple different processes:

Environment introduction:

Mysql version: 5.1.50

Operating system: SUSE 11

Number of mysql instances: 3

The ports occupied by the instance are 3306, 3307 and 3308, respectively.

To create a mysql user:

/ usr/sbin/groupadd mysql/usr/sbin/useradd-g mysql mysql

Compile and install mysql:

Tar xzvf mysql-5.1.50.tar.gzcd mysql-5.1.50./configure'--prefix=/usr/local/mysql''--with-charset=utf8''--with-extra-charsets=complex''--with-pthread''--enable-thread-safe-client''--with-ssl''--with-client-ldflags=-all-static''--with-mysqld-ldflags=-all-static''--with-plugins=partition,innobase,blackhole,myisam,innodb_plugin,heap Archive''- enable-shared''- enable-assembler'makemake install

Initialize the database:

/ usr/local/mysql/bin/mysql_install_db-basedir=/usr/local/mysql-datadir=/data/dbdata_3306-user=mysql/usr/local/mysql/bin/mysql_install_db-basedir=/usr/local/mysql-datadir=/data/dbdata_3307-user=mysql/usr/local/mysql/bin/mysql_install_db-basedir=/usr/local/mysql-datadir=/data/dbdata_3308-user=mysql

Create a profile

Vim / data/dbdata_3306/my.cnf

The configuration file for 3306 is as follows:

[client] port = 3306socket = / data/dbdata_3306/ mysql.sock [mysqld] datadir=/data/dbdata_3306/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3306socket = / data/dbdata_3306/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query _ cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem _ pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120 [mysqldump] quickmax_allowed_packet = 256m [MySQL] no-auto-rehashprompt=\\ u@\ D\ R:\ m > [myisamchk] key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8m [mysqlhotcopy] interactive- timeout [mysqld _ safe] open-files-limit = 8192

Vim / data/dbdata_3307/my.cnf

The configuration file for 3307 is as follows:

[client] port = 3307socket = / data/dbdata_3307/ mysql.sock [mysqld] datadir=/data/dbdata_3307/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3307socket = / data/dbdata_3307/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query _ cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem _ pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120 [mysqldump] quickmax_allowed_packet = 256m [MySQL] no-auto-rehashprompt=\\ u@\ D\ R:\ m > [myisamchk] key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8m [mysqlhotcopy] interactive- timeout [mysqld _ safe] open-files-limit = 8192

Vim / data/dbdata_3308/my.cnf

The configuration file for 3308 is as follows:

[client] port = 3308socket = / data/dbdata_3308/ mysql.sock [mysqld] datadir=/data/dbdata_3308/skip-name-resolvelower_case_table_names=1innodb_file_per_table=1port = 3308socket = / data/dbdata_3308/mysql.sockback_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query _ cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem _ pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120 [mysqldump] quickmax_allowed_packet = 256m [MySQL] no-auto-rehashprompt=\\ u@\ D\ R:\ m > [myisamchk] key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8m [mysqlhotcopy] interactive- timeout [mysqld _ safe] open-files-limit = 8192

Create an automatic startup file

Vim / data/dbdata_3306/mysqld

The startup file for 3306 is as follows:

#! / bin/bashmysql_port=3306mysql_username= "admin" mysql_password= "password" function_start_mysql () {printf "Starting MySQL...\ n" / bin/sh / usr/local/mysql/bin/mysqld_safe-- defaults-file=/data/dbdata_$ {mysql_port} / my.cnf 2 > & 1 > / dev/null &} function_stop_mysql () {printf "Stoping MySQL...\ n" / usr/local/mysql/bin/mysqladmin-u ${mysql_username} -p$ {mysql_password}-S / data/dbdata_$ {mysql_port} / mysql.sock shutdown} function_restart_mysql () {printf "Restarting MySQL...\ n" function_stop_mysqlfunction_start_mysql} function_kill_mysql () {kill-9 $(ps-ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk' {printf $2}') kill-9 $(ps-ef | grep 'libexec/mysqld' | grep ${mysql_port}) | | awk'{printf $2}')} case $1 instart) function_start_mysql | ; stop) function_stop_mysql;;kill) function_stop_mysqlfunction_start_mysql;;*) echo "Usage: / data/dbdata_$ {mysql_port} / mysqld {start | stop | restart | kill}";; esac

Vim / data/dbdata_3307/mysqld

The startup file for 3307 is as follows:

#! / bin/bashmysql_port=3307mysql_username= "admin" mysql_password= "password" function_start_mysql () {printf "Starting MySQL...\ n" / bin/sh / usr/local/mysql/bin/mysqld_safe-- defaults-file=/data/dbdata_$ {mysql_port} / my.cnf 2 > & 1 > / dev/null &} function_stop_mysql () {printf "Stoping MySQL...\ n" / usr/local/mysql/bin/mysqladmin-u ${mysql_username} -p$ {mysql_password}-S / data/dbdata_$ {mysql_port} / mysql.sock shutdown} function_restart_mysql () {printf "Restarting MySQL...\ n" function_stop_mysqlfunction_start_mysql} function_kill_mysql () {kill-9 $(ps-ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk' {printf $2}') kill-9 $(ps-ef | grep 'libexec/mysqld' | grep ${mysql_port}) | | awk'{printf $2}')} case $1 instart) function_start_mysql | ; stop) function_stop_mysql;;kill) function_stop_mysqlfunction_start_mysql;;*) echo "Usage: / data/dbdata_$ {mysql_port} / mysqld {start | stop | restart | kill}";; esac

Vim / data/dbdata_3308/mysqld

The startup file for 3308 is as follows:

#! / bin/bashmysql_port=3308mysql_username= "admin" mysql_password= "password" function_start_mysql () {printf "Starting MySQL...\ n" / bin/sh / usr/local/mysql/bin/mysqld_safe-- defaults-file=/data/dbdata_$ {mysql_port} / my.cnf 2 > & 1 > / dev/null &} function_stop_mysql () {printf "Stoping MySQL...\ n" / usr/local/mysql/bin/mysqladmin-u ${mysql_username} -p$ {mysql_password}-S / data/dbdata_$ {mysql_port} / mysql.sock shutdown} function_restart_mysql () {printf "Restarting MySQL...\ n" function_stop_mysqlfunction_start_mysql} function_kill_mysql () {kill-9 $(ps-ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk' {printf $2}') kill-9 $(ps-ef | grep 'libexec/mysqld' | grep ${mysql_port}) | | awk'{printf $2}')} case $1 instart) function_start_mysql | ; stop) function_stop_mysql;;kill) function_stop_mysqlfunction_start_mysql;;*) echo "Usage: / data/dbdata_$ {mysql_port} / mysqld {start | stop | restart | kill}";; esac

Start mysql for 3306, 3307, 3308

/ data/dbdata_3306/mysqld start/data/dbdata_3307/mysqld start/data/dbdata_3308/mysqld start

Change the original password (for security reasons, you also need to delete accounts that do not have a password in the system, which is omitted here):

/ usr/local/mysql/bin/mysqladmin-uroot password' password'-S / data/dbdata_3306/mysql.sock/usr/local/mysql/bin/mysqladmin-uroot password' password'-S / data/dbdata_3307/mysql.sock/usr/local/mysql/bin/mysqladmin-uroot password' password'-S / data/dbdata_3308/mysql.sock

Log in to the test and create an account to close mysql, which is needed for mysqld scripts!

/ usr/local/mysql/bin/mysql-uroot-ppassword-S / data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *. * TO 'admin'@'localhost' IDENTIFIED BY' password';flush privileges;/usr/local/mysql/bin/mysql-uroot-ppassword-S / data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *. * TO 'admin'@'localhost' IDENTIFIED BY' password';flush privileges / usr/local/mysql/bin/mysql-uroot-ppassword-S / data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *. * TO 'admin'@'localhost' IDENTIFIED BY' password';flush privileges

After creating an admin account, the stop function and restart function of the script will be normal!

Change environment variables

Vim / etc/profile add the following line PATH=$ {PATH}: / usr/local/mysql/bin/source / etc/profile

Add to Auto start

Vim / etc/init.d/boot.local/data/dbdata_3306/mysqld start/data/dbdata_3307/mysqld start/data/dbdata_3308/mysqld start

Self-boot file / etc/rc.local if it is a rhel or centos system

Management, in the local use of-S / data/dbdata_3308/mysql.sock, if remote through different ports can be connected to sit on the management operation. Other and single instance management is no different!

Let's take a look at the second way to achieve multi-instance combat through the official mysqld_multi:

The mysql installation and database initialization here are the same as the previous steps, so I won't repeat them.

Configuration of mysqld_multi

Vim / etc/my.cnf

[mysqld_multi] mysqld = / usr/local/mysql/bin/mysqld_safemysqladmin = / usr/local/mysql/bin/mysqladminuser = adminpassword = password [mysqld1] socket = / data/dbdata_3306/mysql.sockport = 3306pid-file = / data/dbdata_3306/3306.piddatadir = / data/dbdata_3306user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb _ Lock_wait_timeout = 120 [mysqld2] socket = / data/dbdata_3307/mysql.sockport = 3307pid-file = / data/dbdata_3307/3307.piddatadir = / data/dbdata_3307user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread _ concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb _ additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120 [mysqld3] socket = / data/dbdata_3308/mysql.sockport = 3308pid-file = / data/dbdata_3308/3308.piddatadir = / data/dbdata_3308user = mysqlskip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4defaultMurray- Engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1: 10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120 [mysqldump] quickmax_allowed_packet = 256m [MySQL] no-auto-rehashprompt=\\ u @\\ d\ R:\ m > [myisamchk] key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8m [mysqlhotcopy] interactive- timeout [mysqld _ safe] open-files-limit = 8192

Mysqld_multi start

/ usr/local/mysql/bin/mysqld_multi start 1/usr/local/mysql/bin/mysqld_multi start 2/usr/local/mysql/bin/mysqld_multi start 3

Or in the form of a command:

/ usr/local/mysql/bin/mysqld_multi start 1-3

Change the original password (for security reasons, you also need to delete accounts that do not have a password in the system, which is omitted here):

/ usr/local/mysql/bin/mysqladmin-uroot password' password'-S / data/dbdata_3306/mysql.sock/usr/local/mysql/bin/mysqladmin-uroot password' password'-S / data/dbdata_3307/mysql.sock/usr/local/mysql/bin/mysqladmin-uroot password' password'-S / data/dbdata_3308/mysql.sock

Log in to the test and create an admin password (required when stopping mysql)

/ usr/local/mysql/bin/mysql-uroot-ppassword-S / data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *. * TO 'admin'@'localhost' IDENTIFIED BY' password';flush privileges;/usr/local/mysql/bin/mysql-uroot-ppassword-S / data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *. * TO 'admin'@'localhost' IDENTIFIED BY' password';flush privileges / usr/local/mysql/bin/mysql-uroot-ppassword-S / data/dbdata_3308/mysql.sockGRANT SHUTDOWN ON *. * TO 'admin'@'localhost' IDENTIFIED BY' password';flush privileges

Change environment variables

Vim / etc/profilePATH=$ {PATH}: / usr/local/mysql/bin/source / etc/profile

Add to Auto start

Vim / etc/init.d/boot.local/usr/local/mysql/bin/mysqld_multi start 1-3

Self-boot file / etc/rc.local if it is a rhel or centos system

Management, in the local use of-S / data/dbdata_3308/mysql.sock, if remote through different ports can be connected to sit on the management operation. Other and single instance management is no different!

These are all the contents of the article "sample Analysis of Multi-instance configuration and Management in MySQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.