MySQL5.5 multi-instance compilation and installation-Multi-configuration file, mysql5.5 configuration file

MySQL5.5 multi-instance compilation and installation-Multi-configuration file, mysql5.5 configuration file

1. What is MySQL multi-instance?
MySQL multi-instance is simply to install a MySQL program on a server and provide external access through different ports. Multi-instance not only saves the cost of physical hosts, it also effectively improves the CPU and disk I/O usage efficiency of a single physical host, and can deploy the database HA solution between multiple instances.
2. How to configure multiple MySQL instances?
You can configure multiple mysql instances in either of the following ways:
1. According to the official provision, mysqld_multi uses a separate configuration file to implement multiple instances. In this way, the configuration of each instance is not very good, and the advantage is that it is easy to manage and can be centrally managed.
2. Use multiple configuration files and startup files. The configuration files are different: server-id, socket file location, configuration path, and data storage location. During initialization, only different configuration files are used to initialize the database, and different startup files are used to start the database. This method is simple in logic and configuration, but is not convenient to manage.
The second multi-instance method is used for configuration.
Iii. Multi-instance Configuration
MySQL is installed with MySQL 5.5.52. For the installation method, see mysql5.5.52 compilation and installation.
1. Stop a single-instance mysql database

[root@db01 ~]# /etc/init.d/mysqld stopShutting down MySQL. SUCCESS!

2. Disable auto-start upon startup

[Root @ db01 ~] # Chkconfig mysqld off [root @ db01 ~] # Chkconfig -- list mysqldmysqld 0: Close 1: Close 2: Close 3: Close 4: Close 5: Close 6: Close

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

[root@db01 ~]# mkdir -p /data/{3306,3307}/data

Note that in the Multi-instance Startup File, the commands that need to be executed to start the services of different forces in MySQL are actually different. For example, the command to start the 3306 instance is as follows:

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

Run the following command to start the 3307 instance:

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

Next let's take a look at the substantive commands for stopping different MySQL instance services in the Multi-instance Startup File.
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 Startup File
1) 3306mysql instance configuration file

[root@db01 ~]# vim /data/3306/my.cnf[client]port            = 3306socket          = /data/3306/mysql.sock[mysql]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 = 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#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

[root@db01 ~]# cp /data/3306/my.cnf /data/3307/my.cnf[root@db01 ~]# sed -i 's#3306#3307#g' /data/3307/my.cnf[root@db01 ~]# sed -n /server-id/p /data/3307/my.cnfserver-id = 1[root@db01 ~]# sed -i 's#server-id = 1#server-id = 2#g' /data/3307/my.cnf                       [root@db01 ~]# cat /data/3307/my.cnf[client]port            = 3307socket          = /data/3307/mysql.sock[mysql]no-auto-rehash[mysqld]user    = mysqlport    = 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_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/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. The creation of the MySQL multi-instance Startup File is almost the same as that of the configuration file. You can also use the vim command to add the following:
1) 3306mysql instance Startup File

[Root @ db01 ~] # Vim/data/3306/mysql #! /Bin/bash #################################### ############ Filename: mysql # Description: Start MySQL multi instance script # Version: 1.0 # Date: 2016/12/10 # Author: xuanwiei # Email: 1756112532@qq.com ####################################### ########## initport = 3306mysql_user = "root" mysql_pwd = "123456" # in the future, we will change it to the same path as the Database Password = "/application/mysql /bin "mysql_sock ="/data/$ {port}/mysql. sock "# startup functionfunctio N_start_mysql () {if [! -E "$ mysql_sock"]; then printf "Starting MySQL... \ n "/bin/sh $ {export path}/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 "$ {export path}/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_mysql} case $1 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

[root@db01 ~]# cp /data/3306/mysql /data/3307/mysql[root@db01 ~]# sed -i 's#3306#3307#g' /data/3307/mysql[root@db01 ~]# cat /data/3307/mysql#!/bin/bash################################################# Filename:    mysql# Description: Start MySQL multi instance script# Version:     1.0# Date:        2016/12/10# Author:      xuanwiei# Email:       1756112532@qq.com#################################################initport=3307mysql_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_mysql}case $1 instart)    function_start_mysql;;stop)    function_stop_mysql;;restart)    function_restart_mysql;;*)    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"esac

6. configure file permissions for multiple MySQL instances
(1) Use the following command to authorize mysql users and user groups to manage the root directory/data of the entire multi-instance

[root@db01 ~]# chown -R mysql.mysql /data

(2) Use the following mysql multi-instance Startup File to execute mysql. Set the 700 permission to the best. Do not use the 755 permission because the file contains the database administrator password, will be read.

[Root @ db01 scripts] # find/data/-type f-name "mysql"/data/3306/mysql/data/3307/mysql [root @ db01 scripts] # find/ data/-type f-name "mysql" | xargs chmod 700 [root @ db01 scripts] # find/data/-type f-name "mysql" | xargs ls-l-rwx ------ 1 root 1359 December 10 16:20/data/3306/mysql-rwx ------ 1 root 1359 December 10 16:22/data/3307/mysql

7. initialize the database files of multiple MySQL instances.
(1) initialize the MySQL database

Cd/application/mysql/scripts/<= Note that the path is different from that of MySQL5.1, mySQL5.1 is not in the MySQL bin path. The 3306 instance/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 of MySQL, and -- datadir is the data directory of different instances.
Procedure:

[root@db01 ~]# cd /application/mysql/scripts/[root@db01 scripts]# /application/mysql/scripts/mysql_install_db \> --basedir=/application/mysql \> --datadir=/data/3306/data \> --user=mysqlWARNING: The host 'db01' could not be looked up with resolveip.This probably means that your libc libraries are not 100 % compatiblewith this binary MySQL version. The MySQL daemon, mysqld, should worknormally with the exception that host name resolving will not work.This means that you 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 two OK columns exist, the initialization is successful.
Here, WARNING: The host 'db01' cocould not be looked up with resolveip.
The reason is that db01 is not parsed In the hosts file.
Solution: echo "172.16.1.52 db01">/etc/hosts

[root@db01 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 two OK columns exist, the initialization is successful.
WARNING: The host 'db01' cannot be looked up with resolveip.
(2) Principles and results of database Initialization

[Root @ db01 scripts] # tree/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 ..................... Omitted ....................................

(3) initialization failure
Example 1: The WARNING message "WARNING: The host 'db01' could not be looked up with resolveip ."
This warning message can be ignored. If you want to solve this problem, you need to modify the host name resolution.

echo "172.16.1.52     db01" >>/etc/hosts

8. Start MySQL multi-instance Database
Start command of instance 3306

/data/3306/mysql start

Startup command of instance 2 3307

/data/3307/mysql start

Check whether MySQL multi-instance database is successfully started

netstat -lntup|grep 330

Procedure:

[root@db01 scripts]# /data/3306/mysql    Usage: /data/3306/mysql {start|stop|restart}[root@db01 scripts]# /data/3306/mysql startStarting MySQL...[root@db01 scripts]# /data/3307/mysql startStarting MySQL...

View port

[root@db01 scripts]# ss -nlutp|grep 330tcp    LISTEN     0      600                    *:3306                  *:*      users:(("mysqld",48766,12))tcp    LISTEN     0      600                    *:3307                  *:*      users:(("mysqld",49510,12))

9. configure and manage MySQL multi-instance Databases
(1) Configure MySQL multi-instance database to start automatically
The service is automatically started and key, and MySQL multi-instance startup is no exception. Add the MySQL multi-instance startup command to/etc/rc. local to enable automatic startup:

cat >>/etc/rc.local<<EOF#mysql multi instances/data/3306/mysql start/data/3307/mysql startEOFtail -3 /etc/rc.local

Tip: Make sure that the MySQL script has the execution permission.
(2) log on to mysql for testing
Specify the sock file when logging on
The test command is as follows:

Mysql-S/data/3306/mysql. sock <= directly typed in, and the identity is still root, but-S/data/3306/mysql is added. sock. Different users log on to different instances.

Operation demonstration

[root@db01 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) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>[root@db01 3306]# mysql -S /data/3307/mysql.sockWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.52-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Here, the MySQL multi-instance configuration is complete ~~!!!