MySQL multi-instance introduction and configuration

In a previous article, there was already an introduction to how to install and configure MySQL (http://blog.51cto.com/superpcm/2092357). Next, the configuration of multiple instances in MySQL.

1. What is MySQL multi-instance

In short, MySQL multi-instance is to open several different service ports on one server at the same time, run multiple MySQL service processes simultaneously, these service processes through different socket listens different server port to provide the service. These MySQL multiple instances share a set of MySQL installers that use different my.cnf (and can also be the same) configuration file, starting programs (or the same) and data files. When providing services, multi-instance MySQL appears to be independent logically, and it obtains the corresponding number of hardware resources of the server according to the corresponding setting value of the configuration file.

The advantage of multi-instance is that it can effectively utilize the resources of the server, save the server resources, and the disadvantage will have the problem of mutual preemption of resources. When a database instance is in high concurrency or has a SQL slow query, the entire instance consumes a lot of resources such as system CPU, disk I/O, and so on, causing the quality of other DB instance services on the server to fall together. The resources obtained from the different instances are relatively independent and cannot be completely isolated as virtualization.

2. Configure MySQL Multi-instance

MySQL's multi-instance configuration can be a single file, a single boot program deployment scenario, but not recommended, the risk is too high. Deployed here is a multi-configuration file, multi-boot deployment scenario. In the front, we have built the MySQL service, that is, there is a default instance, the port is 3306. Here, we add an additional instance with a port of 3307.

(1) Create a multi-instance data file directory, the directory itself is OK.

Mkdir-p/data/3307/data

(2) Create a MySQL multi-instance configuration file, Vim/data/3307/my.cnf, the content is as follows

[client]port= 3307socket= /data/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user= mysqlport=  3307socket= /data/3307/mysql.sockbasedir= /usr/local/mysqldatadir= /data/3307/dataopen_ Files_limit= 1024read-onlyback_log= 600max_connections= 800max_connect_errors= 3000#table_ Cache= 614external-locking= falsemax_allowed_packet= 8msort_buffer_size= 1mjoin_buffer_ size= 1mthread_cache_size= 100thread_concurrency= 2query_cache_size= 2mquery_cache_limit=  1mquery_cache_min_res_unit= 2k#default_table_type= innodbthread_stack= 192k#transaction_ isolation= read-committedtmp_table_size= 2mmax_heap_table_size= 2mlong_query_time=1  pid-file= /data/3307/mysql.pidrelay-log= /data/3307/relay-binrelay-log-info-file = /data/ 3307/relay-log.infobinlog_cache_size= 1mmax_binlog_cache_size= 1mmax_binlog_size= 2mkey_buffer_ Size= 16mread_rnd_buffer_size= 1mbulk_insert_buffer_size= 1mlower_case_table_names=  1skip-name-resolveslave-skip-errors= 1032,1062replicate-ignore-db=mysqlserver-id= 3          #不同实例的server-id Not the same, 3306-Port server_id I set to 52, this cannot be 52#innodb_additional_mem_ pool_size= 4minnodb_buffer_pool_size= 32minnodb_data_file_path = ibdata1:128m: autoextendinnodb_file_io_threads= 4#innodb_threads_concurrency= 8innodb_flush_log_at_trx_commit=  2innodb_log_buffer_size= 2Minnodb_log_file_size= 4Minnodb_log_files_in_group= 3  Innodb_max_dirty_pages_pct= 90innodb_lock_wait_timeout= 120innodb_file_per_table= 0[mysqldump] quickmax_allowed_packet= 2m[mysql_safe]log-error=/data/3307/mysql_pcm3307.errpid-file=/data/3307/ Mysqld.pid

     (3) Create a MySQL multi-instance startup file, Vim/data/3307/mysql, with the following

#!/bin/bash#initport=3307mysql_user= "root" mysql_pwd= "pcm123456" cmdpath= "/usr/local/mysql/bin" mysql_sock= "/data /${port}/mysql.sock "#startup  functionfunction_start_mysql () {if [ ! -e " $mysql _sock "  ];thenprintf  "starting mysql...\n"/bin/sh${cmdpath}/mysqld_safe --defaults-file=/data/${ port}/my.cnf 2>&1 >/dev/null &     #启动命令elseprintf   "Mysql  is running...\n "Exitfi} #stop  functionfunction_stop_mysql () {if [ ! -e " $ Mysql_sock " ];thenprintf " mysql is stopped...\n "exit elseprintf " stoping  mysql...\n "${cmdpath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -s /data/${port}/ mysql.sock shutdown     #停止命令fi} #restart  functionfunction_restart_mysql () {printf   "restart mysql...\n" Function_stop_mysqlsleep 2function_start_mysql}case $1 instart) Function_start_mysql;; Stop) Function_stop_mysql;; restart) Function_restart_mysql;; *) printf  "usage:/data/${port}/mysql.sh {start|stop|restart}\n" Esac

(4) Configuring MySQL multi-instance file permissions

Chown-r mysql:mysql/data/find/data-name mysql.sh |xargs chmod #修改权限为700, because there is a database password, high security requirements

(5) Initializing a MySQL multi-instance database file

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

See the Data directory in the 3307 directory, you can see that there are many more files

(6) Start the MySQL multi-instance database and check to see that 3307 of the ports have been started

/data/3307/mysql.sh start

PS: Initially my startup was failed, and then the data directory of the IBDATA1,IB_LOGFILE0,IB_LOGFILE0 three files deleted after the normal start. It should have been initialized once when I built the MySQL service, and again this initialization of the generated files and subsequent configurations did not match.

3. Login to manage MySQL multi-instance database

(1) Log in to MySQL database

Mysql-s/data/3307/mysql.sock #需要指定sock文件, otherwise use the default 3306

(2) See the root of the MySQL password is still empty, we need to modify.

mysqladmin-u root password ' pcm123456 '-s/data/3307/mysql.sock

Here, MySQL's multi-instance configuration is complete. If you want to remotely log on to multiple instances of MySQL, remember to add the port number.

MySQL multi-instance introduction and configuration