MySQL5.5 Multi-instance compilation installation-Multiple configuration files

One, what is MySQL multi-instance?
MySQL multi-instance simple is to install a set of MySQL program on a server, through different ports to provide external access, multi-instance not only save the cost of physical host, but also effectively improve the single physical host CPU, disk I/O efficiency, but also can be deployed between multiple instances of the database ha scheme.
Second, how to configure MySQL multi-instance?
There are two ways to configure multiple instances of MySQL
1, according to the official provision is through the mysqld_multi using a separate configuration file to achieve multi-instance, this way to customize each instance configuration is not too, the advantage is easy to manage, centralized management.
2, the use of multiple configuration files and startup files, the difference between the configuration file: Server-id, Socket file location, configuration path and data storage location is different. Initializing the database with only a different configuration file, starting with a different startup file, this method of logic and configuration is simple, but not easy to manage.
Let's configure it with a second multi-instance approach
Three, multi-instance configuration
MySQL installation is mysql5.5.52 version, installation method see MySQL5.5.52 compiled installation
1. Stop Single Instance MySQL database

[[email protected] ~]#/etc/init.d/mysqld stopshutting down MySQL. success!

2. No boot from boot

[[email protected] ~]# chkconfig mysqld off[[email protected] ~]# chkconfig--list mysqldmysqld          0: Off  1: Off  2: Close  3: Close  4: Close  5: Close 6: Off

3. Create a multi-instance root directory/data/Directory

[Email protected] ~]# mkdir-p/data/{3306,3307}/data

In particular, in a multi-instance boot file, there is a difference between the commands that are required to launch the MySQL service for different forces, for example, the launch of the 3306 instance command as follows

Mysql_safe--defaults-file=/data/3306/mysql &>/dev/null

The command to launch the 3307 instance is as follows:

Mysql_safe--defaults-file=/data/3307/mysql &>/dev/null

Take a look at the multi-instance boot file, stop MySQL different instance service of the substantive command
The command to stop the 3306 instance is as follows:

Mysqladmin-uroot-p123456-s/data/3306/mysql.sock shutdown

The command to stop the 3307 instance is as follows:

Mysqladmin-uroot-p123456-s/data/3307/mysql.sock shutdown

4. Create a MySQL multi-instance configuration file and boot file
1) 3306mysql instance configuration file

[[email protected] ~]# vim/data/3306/my.cnf[client]port = 3306socket =/data/3306/mysql.sock[mys  Ql]no-auto-rehash[mysqld]user = Mysqlport = 3306socket =/data/3306/mysql.sockbasedir =/application/mysqldatadir = /data/3306/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614ext ernal-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#log_long_format#log-error =/data/3306/error.log#log-slow-queries =/data/3306/slow.logpid-file =/data/3306/ Mysql.pidlog-bin =/data/3306/mysql-binrelay-log =/data/3306/relay-binrelay-log-info-file =/data/3306/ Relay-log.infobinlog_cache_size = 1mmax_binlog_cache_size = 1mmax_binlOg_size = 2mexpire_logs_days = 7key_buffer_size = 16mread_buffer_size = 1mread_rnd_buffer_size = 1Mbulk_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_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 1innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32minnodb_data_file_path = Ibdata1:128m:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_ Flush_log_at_trx_commit = 2innodb_log_buffer_size = 2minnodb_log_file_size = 4minnodb_log_files_in_group = 3innodb_max _dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[ Mysqld_safe]log-error=/data/3306/mysql_3306.errpid-file=/data/3306/mysqld.pid

2) 3307mysql instance configuration file

[[email protected] ~]# cp/data/3306/my.cnf/data/3307/my.cnf[[email protected] ~]# sed-i ' s#3306#3307#g '/ Data/3307/my.cnf[[email protected] ~]# sed-n/server-id/p/data/3307/my.cnfserver-id = 1[[email protected] ~]# sed-i ' S#server-id = 1#server-id = 2#g '/data/3307/my.cnf [[email protected] ~]# Cat/data /3307/my.cnf[client]port = 3307socket =/data/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user = My  Sqlport = 3307socket =/data/3307/mysql.sockbasedir =/application/mysqldatadir =/data/3307/dataopen_files_limit = 1024back_log = 600max_connections = 800max_connect_errors = 3000table_cache = 614external-locking = FALSEmax_allowed_pac Ket =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#log_long_format#log-error =/data/3307/error.log#log-slow-queries =/data/3307/ Slow.logpid-file =/data/3307/mysql.pidlog-bin =/data/3307/mysql-binrelay-log =/data/3307/ Relay-binrelay-log-info-file =/data/3307/relay-log.infobinlog_cache_size = 1mmax_binlog_cache_size = 1Mmax_binlog_  Size = 2mexpire_logs_days = 7key_buffer_size = 16mread_buffer_size = 1mread_rnd_buffer_size = 1Mbulk_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_recoverlower_case_table_names = 1skip-name-resolveslave-skip-errors = 1032,1062replicate-ignore-db=mysqlserver-id = 2innodb_additional_mem_pool_size = 4Minnodb_buffer_pool_size = 32minnodb_data_file_path = Ibdata1:128m:autoextendinnodb_file_io_threads = 4innodb_thread_concurrency = 8innodb_ Flush_log_at_trx_commit = 2innodb_log_buffer_size = 2minnodb_log_file_size = 4minnodb_log_files_in_group = 3innodb_max _dirty_pages_pct = 90innodb_lock_wait_timeout = 120innodb_file_per_table = 0[mysqldump]quickmax_allowed_packet = 2M[mysqld_safe] Log-error=/data/3307/mysql_3307.errpid-file=/data/3307/mysqld.pid

5, MySQL multi-instance startup file creation and configuration file creation is almost the same, you can also use the VIM command to add the following:
1) 3306mysql instance startup file

[[email protected] ~]# vim/data/3306/mysql#!/bin/bash################################################# filename:mysql# Description:start MySQL Multi instance script# version:1.0# date:2016/12/10# author:xuanwiei# Email:[ email protected]################################################ #initport =3306mysql_user= "root" mysql_pwd= "123456" #这里将来是要修改为和数据库密码一致CmdPath = "/application/mysql/bin" mysql_sock= "/data/${port}/mysql.sock" #startup Functionfunction_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 Functionfunction_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/d Ata/${port}/mysql.sock shutdown fi} #restart functionfunction_resTart_mysql () {printf "restarting mysql...\n" Function_stop_mysql sleep 2 function_start_mysql}case $ instart) Function_start_mysql;; stop) Function_stop_mysql;; restart) Function_restart_mysql;; *) printf "Usage:/data/${port}/mysql {start|stop|restart}\n" Esac

2) 3307mysql instance startup file

[[email protected] ~]# cp/data/3306/mysql/data/3307/mysql[[email protected] ~]# sed-i ' s#3306#3307#g '/ Data/3307/mysql[[email protected] ~]# cat/data/3307/mysql#!/bin/bash####################################### ########## filename:mysql# Description:start MySQL Multi instance script# version:1.0# date:2016/12/10# author:xuanwiei# Email: [email protected]################################################ #initport =3307my sql_user= "root" mysql_pwd= "123456" cmdpath= "/application/mysql/bin" mysql_sock= "/data/${port}/mysql.sock" #startup Functionfunction_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 Functionfunction_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 Functionfunction_restart_mysql () {printf "restarting mysql...\n" Function_stop_mysql sleep 2 function_start_my Sql}case $ instart) function_start_mysql;; stop) Function_stop_mysql;; restart) Function_restart_mysql;; *) printf "Usage:/data/${port}/mysql {start|stop|restart}\n" Esac

6. Configure MySQL Multi-instance file permissions
(1) Authorize MySQL users and user groups to manage the entire multi-instance root directory by following the command below/data

[Email protected] ~]# chown-r mysql.mysql/data

(2) through the following MySQL multi-instance all startup file MySQL executable, set 700 permissions best, note Do not use 755 permissions, because the file has the database administrator password, will be read to.

[Email protected] scripts]# find/data/-type f-name "MySQL"/data/3306/mysql/data/3307/mysql[[email protected] Scripts ]# find/data/-type f-name "MySQL" |xargs chmod 700[[email protected] scripts]# find/data/-type f-name "MySQL" |xargs l s-l-rwx------1 root root 1359 December 16:20/data/3306/mysql-rwx------1 root root 1359 December 16:22/data/3307/mysql

7. Initializing MySQL multi-instance database file
(1) Initializing MySQL database

cd/application/mysql/scripts/<== Note that unlike the MySQL5.1 path, MySQL5.1 does not have a 3306 instance of the MySQL bin path/application/mysql/scripts/ mysql_install_db--basedir=/application/mysql--datadir=/data/3306/data--user=mysql3307 instance/application/mysql/ scripts/mysql_install_db--basedir=/application/mysql--datadir=/data/3307/data--user=mysql

Tip:--basedir=/application/mysql is the installation path for MySQL,--datadir for different instance data directories
Operation Process:

[[email protected] ~]# cd/application/mysql/scripts/[[email  Protected] scripts]#/application/mysql/scripts/mysql_install_db >--basedir=/application/mysql >--datadir=/ Data/3306/data >--user=mysqlwarning:the host ' Db01 ' could not being looked up with RESOLVEIP. This probably means that your libc libraries is not the Compatiblewith this binary MySQL version. The MySQL daemon, mysqld, should worknormally with the exception that host name resolving won't work. This means the should use IP addresses instead of Hostnameswhen specifying MySQL privileges! Installing MySQL system tables ... 161117 14:14:14 [Note]/application/mysql/bin/mysqld (mysqld 5.5.52) starting as Process 46676 ... Okfilling Help Tables ... 161117 14:14:15 [Note]/application/mysql/bin/mysqld (mysqld 5.5.52) starting as Process 46683 ... OK 

If there are two ok, it means the initialization is successful.
where Warning:the host ' db01 ' could not is looked up with RESOLVEIP.
The reason is because DB01 did not parse in the Hosts file
Solution: Echo "172.16.1.52 db01" >>/etc/hosts

[Email protected] scripts]#/application/mysql/scripts/mysql_install_db >--basedir=/application/mysql >-- Datadir=/data/3307/data >--user=mysqlinstalling MySQL system tables ... 161117 14:18:20 [Note]/application/mysql/bin/mysqld (mysqld 5.5.52) starting as Process 46733 ... Okfilling Help Tables ... 161117 14:18:21 [Note]/application/mysql/bin/mysqld (mysqld 5.5.52) starting as Process 46740 ... Ok

If there are two ok, it means the initialization is successful.
This time no use appears warning:the host ' db01 ' could not being looked up with RESOLVEIP.
(2) Principle and result of initializing database

[Email protected] scripts]# tree/data/data├──3306│   ├──data│   │   ├──mysql│   │   │   ├──columns_ priv.frm│   │   │   ├──columns_priv. myd│   │   │   ├──columns_priv. myi│ │ │   ├──db.frm│   │   │   ├──db. myd│   │   │   ├──db. myi│ │ │   ├──event.frm│   │   │   ├──event. myd│   │   │   ├──event. myi│ │ │   ├──func.frm│   │   │   ├──func. myd│   │   │   ├──func. MYI ........ ........ The omitted part ..... ....... .................

(3) Initialization failure
Example 1: The warning message "warning:the host ' db01 ' could not being looked up with RESOLVEIP is given."
This warning message can be ignored, and if it is not resolved, the host name resolution needs to be modified

echo "172.16.1.52     db01" >>/etc/hosts

8. Start MySQL multi-instance database
Start command for first instance 3306

/data/3306/mysql start

Start command for second instance 3307

/data/3307/mysql start

Now check if MySQL multi-instance database starts successfully

Netstat-lntup|grep 330

Operation Process:

[Email protected] scripts]#/data/3306/mysql    Usage:/data/3306/mysql {start|stop|restart}[[email protected] scripts]#/data/3306/mysql startstarting MySQL ... [Email protected] scripts]#/data/3307/mysql startstarting MySQL ...

View ports

[Email protected] scripts]# ss-nlutp|grep 330tcp    LISTEN     0                    *:3306                  *:*      Users: (("Mysqld" , 48766,12)) TCP    LISTEN     0                    *:3307                  *:*      Users: (("mysqld", 49510,12))

9. Configure and manage MySQL multi-instance database
(1) Configure MySQL multi-instance database to boot from
Service start-up and key, MySQL multi-instance boot is no exception, the MySQL multi-instance start command to join the/etc/rc.local, to achieve power-on self-boot:

Cat >>/etc/rc.local<<eof#mysql Multi Instances/data/3306/mysql start/data/3307/mysql startEOFtail-3/etc /rc.local

Tip: To ensure that MySQL scripts have execute permissions
(2) Login MySQL Test
To specify the sock file at logon
The test commands are as follows:

Mysql-s/data/3306/mysql.sock    <== directly knocked in, and identity or root, but more-s/data/3306/mysql.sock, the user difference between the login different instances

Operation Demo

[[email protected] scripts]# mysql-s/data/3306/mysql.sockwelcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 1Server version:5.5.52-log Source distributioncopyright (c), Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>[[email protected] 3306]# mysql-s/DATA/3307/MYSQL.SOCKWELC  ome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 2Server version:5.5.52-log Source distributioncopyright (c), Oracle and/or I TS affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

Here MySQL multi-instance is configured to complete O (∩_∩) o~~!!!

MySQL5.5 Multi-instance compilation installation-Multiple configuration files