Step 1. Preparations

Close the firewall; install MySQL dependent on the libaio library; download, unzip, rename the MySQL executable; and prepare for new user groups and users will not be covered here.

The executable directory is/data/mysql57

Instructions to add users and groups

groupadd mysql

useradd mysql -g mysql

Step 2. Add environment variables

In the / etc/profile file, append PATH=${PATH}:/data/mysql57/bin/

After saving, source/etc/profile can be executed if it needs to take effect immediately.

step 3. Create directory and authorize

Execute under the data root directory

mkdir -p mysql3306/data mysql3306/mysql_log mysql3306/tmp mysql3307/data  mysql3307/mysql_log  mysql3307/tmp mysqld_multi/log

This step is necessary to create a file that records the log.

touch /data/mysql3306/mysql_log/mysql3306.err

touch /data/mysql3307/mysql_log/mysql3307.err

Give directory and file permissions

chown -R mysql.mysql mysql3* mysqld_multi

In this test case, mysql57 was copied from another service, not downloaded and decompressed directly, so there are two more authorization steps below.

chmod -R 755 /data/mysql57/bin

chmod -R 755 /data/mysql57/support-files

step 4. Edit my.cnf

[client]
host=localhost
socket = /tmp/mysql.sock 
default-character-set=utf8mb4
#loose-local-infile=0

[mysqld]
user=mysql
log_bin_trust_function_creators=1
secure_file_priv='/tmp'

########server setting#######
sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
character-set-server=utf8mb4
collation_server=utf8mb4_unicode_ci
lower_case_table_names = 0
skip_name_resolve = 1
#max_connect_errors = 1000
max_connections = 2000
thread_cache_size=256
#thread_stack= 262144
#back_log=80
max_allowed_packet = 134217728
event_scheduler = 1 
local-infile=0
#lower_case_table_names = 1
explicit_defaults_for_timestamp = 1
expire_logs_days = 7
log_bin_trust_function_creators =1

####.frm/.ibd files qty related
open_files_limit=65535
innodb_open_files=65535
table_open_cache=65535
table_definition_cache=65535

#### seesion buffer related
read_buffer_size = 262144
read_rnd_buffer_size = 524288
sort_buffer_size = 8388608
join_buffer_size = 8388608

####memory table size 
tmp_table_size =67108864
max_heap_table_size=67108864

####timeout
interactive_timeout = 1800
wait_timeout = 1800
# connect_timeout=10

########slow query ########
slow_query_log = 1
log_slow_slave_statements = 1
#log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
long_query_time = 1
#min_examined_row_limit = 10000

########innodb settings########
innodb_buffer_pool_size = 10737418240
innodb_buffer_pool_instances = 16 
innodb_buffer_pool_dump_pct = 40
innodb_lru_scan_depth = 2048
innodb_page_cleaners = 16
#innodb_purge_threads = 4
innodb_sort_buffer_size = 67108864
#innodb_file_per_table = 1
#innodb_flush_log_at_trx_commit = 1

innodb_undo_log_truncate = 1
innodb_undo_tablespaces = 3 
innodb_max_undo_log_size = 2147483648
innodb_purge_rseg_truncate_frequency = 128

innodb_log_file_size = 1073741824
innodb_log_files_in_group = 3
innodb_log_buffer_size = 16777216
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_print_all_deadlocks = 1

innodb_strict_mode = 1
#innodb_lock_wait_timeout = 50

innodb_io_capacity = 32768
innodb_io_capacity_max = 65536
innodb_thread_concurrency = 32
innodb_write_io_threads = 8
innodb_read_io_threads = 8

########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE

gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery=1
relay_log_recovery = 1

slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
slave_transaction_retries=128
slave_preserve_commit_order=1

log_slave_updates=1
binlog_format = ROW
log_timestamps=system

binlog_rows_query_log_events = 1
binlog_row_image='full' 
slave_skip_errors = ddl_exist_errors

########semi sync replication settings########
##plugin_dir=/data/mysql/plugin/
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 5000
#rpl_semi_sync_slave_enabled = 1

[mysqld_multi]
mysqld = /data/mysql57/bin/mysqld_safe
mysqladmin = /data/mysql57/bin/mysqladmin
log = /data/mysqld_multi/log/mysqld_multi.log

[mysqld3306]  
basedir = /data/mysql57
mysqladmin=mysqladmin
datadir=/data/mysql3306/data
port=3306  
server_id=102473306
socket= /tmp/mysql_3306.sock
tmpdir = /data/mysql3306/tmp
pid-file = /data/mysql3306/mysql_log/mysql3306.pid
slow_query_log_file = /data/mysql3306/mysql_log/mysql3306_slow_new.log  
log-error = /data/mysql3306/mysql_log/mysql3306.err
general_log_file= /data/mysql3306/mysql_log/mysql3306.genlog
log-bin = /data/mysql3306/mysql_log/mysql3306_bin
relay_log = /data/mysql3306/mysql_log/relay3306.log

innodb_buffer_pool_size = 90G
innodb_buffer_pool_instances = 8

[mysqld3307]
basedir = /data/mysql57
mysqladmin=mysqladmin
datadir=/data/mysql3307/data
port=3307
server_id=102473307
socket= /tmp/mysql_3307.sock
tmpdir = /data/mysql3307/tmp
pid-file = /data/mysql3307/mysql_log/mysql3307.pid
slow_query_log_file = /data/mysql3307/mysql_log/mysql3307_slow_new.log
log-error = /data/mysql3307/mysql_log/mysql3307.err
general_log_file= /data/mysql3307/mysql_log/mysql3307.genlog
log-bin = /data/mysql3307/mysql_log/mysql3307_bin
relay_log = /data/mysql3307/mysql_log/relay3307.log

innodb_buffer_pool_size = 90G
innodb_buffer_pool_instances = 8

[mysqldump]
quick

step 5. Initialize Instance

Initialize the instance of port 3306, note the temporary password generated.

/data/mysql57/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/data/mysql57  --datadir=/data/mysql3306/data

Initialize the instance of port 3307, noting the temporary password generated.

/data/mysql57/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --basedir=/data/mysql57  --datadir=/data/mysql3307/data

step 6. Copy to generate mysqld_multi and add to boot

cp /data/mysql57/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

chkconfig  --add mysqld_multi

step 7. Opening of multi-instance Mysqld

Open all instances

mysqld_multi start

View openings (see the status of all instances)

mysqld_multi report

Open the specified instance

On/etc/my.cnf [mysqld3306] where the number following mysqld is a label, such as 3306 label

mysqld_multi start 3306

Open 3307 Label Instance as well

mysqld_multi start 3307

(Note: Closing instances with the mysqld_multi stop command is not mentioned here, so why not because the command is invalid.Step 9 will make it valid)

step 8. Log in to the instance for the first time and change the root account password

Multiple instance login requires socket parameters to be specified

This test 3306 instance logged in as:

mysql  -S /tmp/mysql_3306.sock -uroot --port 3306 -p

 

step 9. Gives the right to close instances through mysqld_multi stop.

Closing the instance requires configuring the root user and password, modifying the / etc/my.cnf file.

Add in [client] location

user=root
password=Password

There is a security risk because the account password needs to be kept in the file.In the real world, this setup will depend on the specific situation and security requirements.

 

After the above steps, the server successfully installed two MySQL instances, one Port 3306 and the other Port 3307

 

Thank you: The above documents are based on colleague Fly Chen.