MySQL Multi-instance installation

There are two ways to install MySQL multi-instance:

• One is that each instance uses its own my.cnf file;
• One is that multiple instances use the same my.cnf file, placed under the/etc/path, and managed using MySQL's own mysqld_multi tools

The first approach is described here, where each instance uses its own my.cnf file:

Upload files and unzip them

[Email protected] ~]# cd/usr/local/ls mysql*mysql-5.7. 9-LINUX-GLIBC2. 5-x86_64. Tar  tar zxvf mysql-5.7. 9-LINUX-GLIBC2. 5-x86_64. tar. gz

Create a soft connection after the decompression is complete

[Email protected] local]#LN-S mysql-5.7.9-LINUX-GLIBC2.5-x86_64 mysql[[email protected] local]# CD Mysql[[email protected] mysql]# lltotal thedrwxr-xr-x2 7161Wheel4096Oct A  -bin-rw-r--r--1 7161Wheel17987Oct A  -COPYINGDRWXR-xr-x2 7161Wheel4096Oct A  -DOCSDRWXR-xr-x3 7161Wheel4096Oct A  -include-rw-r--r--1 7161Wheel108028Oct A  -install-BINARYDRWXR-xr-x5 7161Wheel4096Oct A  -LIBDRWXR-xr-x4 7161Wheel4096Oct A  - Mans-rw-r--r--1 7161Wheel2478Oct A  -READMEDRWXR-xr-x - 7161Wheel4096Oct A  -SHAREDRWXR-xr-x2 7161Wheel4096Oct A  -Support-files

Create users and Groups

-r-g MySQL MySQL

User and group modifications for files below the MySQL path

chown -chgrp -R MySQL.

Create a data file and log file path

mkdir /data/{3308,3307}/chown -R mysql:mysql/data

Create the My.cnf file, respectively, below the/data/3307 and/data/3308 paths: Take 3308 as an example

[[Email protected]3308]#pwd/data/3308[[Email protected]3308]#Catmy.cnf [Client]port=3308Socket=/data/3308/mysql.sock[mysqld]server_id=2Port=3308User=Mysqlcharacter-set-server =Utf8mb4default_storage_engine=Innodblog_timestamps=Systemsocket=/data/3308/Mysql.sockbasedir=/usr/local/Mysqldatadir=/data/3308/Datapid-file=/data/3308/mysql.pidmax_connections= +max_connect_errors= +Table_open_cache=1024x768Max_allowed_packet=128mopen_files_limit=65535#####====================================[innodb]==============================innodb_buffer_pool_size=1024minnodb_file_per_table=1innodb_write_io_threads=4innodb_read_io_threads=4innodb_purge_threads=2Innodb_flush_log_at_trx_commit=1innodb_log_file_size=512minnodb_log_files_in_group=2innodb_log_buffer_size=16minnodb_max_dirty_pages_pct= theInnodb_lock_wait_timeout= -Innodb_data_file_path=ibdata1:1024m:autoextend#####====================================[log]==============================Log_error=/data/3308/log/mysql-Error.log Slow_query_log=1Long_query_time=1Slow_query_log_file=/data/3308/log/mysql-Slow.logsql_mode=no_engine_substitution,strict_trans_tables

Initialize the database:

mysql5.7.14 version initialization time has discarded the defaults-file parameter file, so at initialization time to specify the configuration file error, and must ensure that the DataDir is empty, here is 5.7.9, so you can specify defaults-file to initialize

[Email protected] mysql]# bin/mysqld--defaults-file=/data/3308/my.cnf--user=mysql-- Initialize-insecure--datadir=/data/3308/data--basedir=/usr/local/mysq[[email protected] mysql]# bin /mysqld--defaults-file=/data/3307/my.cnf--user=mysql--initialize-insecure--datadir=/ data/3307/data--basedir=/usr/local/MySQL
Set up an encrypted connection [[email protected] mysql]# bin/mysql_ssl_rsa_setup--datadir=/data/3307/Data[[email Protected] mysql]# bin/mysql_ssl_rsa_setup--datadir=/data/3308/data

Database Startup:

[[email protected] mysql]# bin/mysqld_safe--defaults-file=/data/33072>&1 >/dev/null &[112140[roo[email protected] mysql]# bin/ Mysqld_safe--defaults-file=/data/33082>&1 >/dev/null & [212641

To close the database:

[Email protected] mysql]# mysqladmin-s/data/3307/mysql.sock-uroot-p shutdownenter Password: [1]-Done Bin/mysqld_safe--defaults-file=/data/3307/MY.CNF--user=mysql2>&1>/dev/NULL[email protected] mysql]# mysqladmin-s/data/3308/mysql.sock-uroot-p shutdown Enter Password: [2]+ done Bin/mysqld_safe--defaults-file=/data/3308/MY.CNF--user=mysql2>&1>/dev/NULL

Of course, the startup and shutdown here can be written as shell scripts, or the Mysql.server file will be modified, placed under the/ETC/INIT.D path, that becomes the service.

Multi-instance login, specify socket
Mysql-s/data/3306/mysql.sock-uroot-p

MySQL Remote connection mode:
[Email protected] bin]# mysql-uroot-p-p3308-h localhost

Add Auto Start
Shell> cat/etc/rc.local
shell> echo "/usr/local/mysql/bin/mysqld_multi--defaults-extra-file=/etc/my.cnf start 3306,3307" >>/etc/ Rc.local

Turn on the firewall
Shell> Vi/etc/sysconfig/iptables
-A input-p tcp-m state--state new-m TCP--dport 3306-j ACCEPT
-A input-p tcp-m state--state new-m TCP--dport 3307-j ACCEPT

Shell> Service iptables Restart


/* Firewall basic command:

0) View current rules
Iptables-l-N--line-number
Service Iptables Status

1) temporary entry into force, recovery after restart
Save: Service Iptables Save
Open: Service iptables start
Close: Service iptables stop
Restart: Service iptables restart

2) Permanent, no recovery after reboot
Open: Chkconfig iptables on
OFF: Chkconfig iptables off
*/

The second way:

This method only modifies the/etc/my.cnf file, configured as follows, configures multiple instances, initializes each instance individually, initializes no defaults-file, and defaults to/ETC/MY.CNF.

Multi-instance Boot mode:
Mysqld_multi start {port1,port2,...}

Database login and shutdown in the same way as the first

Multi-instance configuration file
[Email protected] log]# CAT/ETC/MY.CNF

[Mysqld_multi] mysqld=/usr/local/mysql/bin/Mysqld_safe mysqladmin=/usr/local/mysql/bin/mysqladmin #user=Root #password=rootpwd [mysqld3306] Port=3306server_id=3306Basedir=/usr/local/MySQL DataDir=/data/3306/Datalog-bin=/data/3306/log/mysql-Bin Socket=/data/3306/Mysql.sock Log-error =/data/3306/log/mysqld.log pid-file=/data/3306/mysqld.pid [mysqld3307] Port=3307server_id=3307Basedir=/usr/local/MySQL DataDir=/data/3307/Data Log-bin=/data/3307/log/mysql-Bin Socket=/data/3307/Mysql.sock Log-error =/data/3307/log/mysqld.log pid-file=/data/3307/mysqld.pid

Error encountered:

[Email protected] mysql]# bin/mysqld--initialize-insecure--defaults-file=/data/3306/my.cnf--user=mysql--datadir= /data/3306/data--basedir=/usr/local/mysql
-26t12:0'defaults-file=/data/3306/my.cnf  '-26t12:0 [ERROR] Aborting

Here is a bug, whether in the start or initialization, you must put defaults-file this parameter in the first place, otherwise it will error, correct as follows:

[Email protected] mysql]# bin/mysqld--defaults-file=/data/3306/MY.CNF--user=mysql--initialize-insecure--datadir=/data/3306/data--basedir=/usr/local/mysqlmysqld: [Warning] World-writable Configfile '/data/3306/my.cnf'is ignored. .- .-26T12: the: A.901074Z0[Warning] TIMESTAMP with implicit the DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for  Moredetails). .- .-26T12: the: -.520944Z0[Warning] Innodb:new log files created, lsn=45790 .- .-26T12: the: -.616526Z0[Warning] innodb:creating FOREIGN KEY constraint system tables. .- .-26T12: the: -.709685Z0[Warning] No existing UUID have been found, so we assume that the first TimeThat this server has been started. Generating a new uuid:c2f799c6-5a6b-11e7-b7ff-08002714955b. .- .-26T12: the: -.714696Z0[Warning] Gtid table is not a ready-to-be used. Table'mysql.gtid_executed'cannot be opened. .- .-26T12: the: -.716948Z1[Warning] [email protected] is created with an empty password! Please consider switching off the--initialize-insecure option.

MySQL Multi-instance installation