MySQL standalone multi-instance deployment

One, MySQL multi-instance deployment

Version: 5.7.18

1. Software Installation

# tar XF mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-c/usr/local# cd/usr/local# chown-r root.root MYSQL-5.7.18-LINUX-GL ibc2.5-x86_64# ln-sv mysql-5.7.18-linux-glibc2.5-x86_64 mysql5.7.18# mkdir/data/{mydata3307,mydata3308}# chown-r mysql.mysql/data/mydata33*

2. Provide multi-instance service startup scripts

# cd/usr/local/mysql5.7.18# CP support-files/mysqld_multi.server/etc/init.d/mysqld_multi# chmod +x/etc/init.d/ mysqld_multi# chkconfig--add mysqld_multi# vi/etc/init.d/mysqld_multiexport path= $PATH:/usr/local/mysql5.7.18/ Binbasedir=/usr/local/mysql5.7.18bindir=/usr/local/mysql5.7.18/bin

3. Provide configuration files

# cat /etc/my.cnf[mysql] #password  = 123456#prompt = [\\[email protected]\\h ][\\d]>\\_socket          = /tmp/mysql5.7.18.sock[ Client] #password  = 123456#prompt = [\\[email protected]\\h][\\d]>\\_socket           = /tmp/mysql5.7.18.sock[mysqld_multi]mysqld =  /usr/local/mysql5.7.18/bin/mysqld_safemysqladmin =/usr/local/mysql5.7.18/bin/mysqladminlog =/ data/mydata3307/mysqld_multi.log#  set a unified administrative password for each instance, which makes it easy to stop instances using service scripts user = rootpass = 123456# initialization requires [MYSQLD] segment configuration, otherwise initialization is not loaded into [mysqld3307] and [mysqld3308] segments about setting the independent undo tablespace and shared tablespace size [mysqld]innodb_buffer_pool_size  = 4096m#innodb_buffer_pool_size = 16384minnodb_undo_log_truncate=oninnodb_undo_ tablespaces = 2innodb_data_file_path=ibdata1:1g:autoextend[mysqld3307]innodb_buffer_pool_size =  4096m#innodb_buffer_pool_size = 16384mport = 3307socket           = /data/mydata3307/mysql5.7.18.sockskip-external-lockingkey_buffer_size = 256mmax_allowed _packet = 10mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size =  1mread_rnd_buffer_size = 4mmyisam_sort_buffer_size = 16mthread_cache_size =  4max_connections=1500character_set_server=utf8group_concat_max_len=65535log_bin_trust_function_creators= 1log_queries_not_using_indexes = onlog_throttle_queries_not_using_indexes = 2interactive_ timeout = 600wait_timeout = 600connect_timeout = 10expire_logs_days =  30replicate-ignore-db=information_schemareplicate-ignore-db=performance_schemareplicate-ignore-db= mysqlreplicate-ignore-db=syslog_timestamps=systeminnodb_print_all_deadlocks=1basedir=/usr/local/ Mysql5.7.18datadir=/data/mydata3307innodb_undO_log_truncate=oninnodb_undo_tablespaces = 2innodb_data_file_path=ibdata1:1g:autoextendcore_filesync _binlog = 0innodb_flush_log_at_trx_commit = 2# #Master #log-bin=mysql-binlog-bin=/data/ mydata3307/mysql-bin#binlog_format=mixedbinlog_format=rowserver-id=3307lower_case_table_names =  1skip-name-resolveinnodb_file_per_table=1long_query_time=2slow_query_log=1slow_query_log_file=/data/mydata3307 /slow-query.logsql_mode= ' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_ Create_user,no_engine_substitution ' #slaveslave-parallel-type=logical_clock#slave-parallel-workers= 16slave-parallel-workers=4master_info_repository=tablerelay_log_info_repository=tablerelay_log_recovery= onslave_preserve_commit_order=1log-slave-updates=trueslave_skip_errors= ' 1032,1062 ' relay_log=/data/mydata3307/ localhost-relay-bin### #gtid ##### #gtid_mode  = onenforce_gtid_consistency = onmaster_verify_ Checksum = 1slave_sql_verify_checksum = 1[mysqld3308]innodb_buffer_pool_size = 2048m#innodb_buffer_pool_size =  16384mport = 3308socket          = / data/mydata3308/mysql5.7.18.sockskip-external-lockingkey_buffer_size = 256mmax_allowed_packet =  10mtable_open_cache = 256sort_buffer_size = 1mread_buffer_size = 1mread_ Rnd_buffer_size = 4mmyisam_sort_buffer_size = 16mthread_cache_size = 4max_ Connections=1500character_set_server=utf8group_concat_max_len=65535log_bin_trust_function_creators=1log_ Queries_not_using_indexes = onlog_throttle_queries_not_using_indexes = 2interactive_ timeout = 600wait_timeout = 600connect_timeout = 10expire_logs_days =  30replicate-ignore-db=information_schemareplicate-ignore-db=performance_schemareplicate-ignore-db= Mysqlreplicate-ignore-db=syslog_timestamps=systemInnodb_print_all_deadlocks=1basedir=/usr/local/mysql5.7.18datadir=/data/mydata3308innodb_undo_log_truncate= oninnodb_undo_tablespaces = 2innodb_data_file_path=ibdata1:1g:autoextendcore_filesync_binlog =  0innodb_flush_log_at_trx_commit = 2# #Master #log-bin=mysql-binlog-bin=/data/mydata3308/ mysql-bin#binlog_format=mixedbinlog_format=rowserver-id=3308lower_case_table_names =  1skip-name-resolveinnodb_file_per_table=1long_query_time=2slow_query_log=1slow_query_log_file=/data/mydata3308 /slow-query.logsql_mode= ' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_ Create_user,no_engine_substitution ' #slaveslave-parallel-type=logical_clock#slave-parallel-workers= 16slave-parallel-workers=4master_info_repository=tablerelay_log_info_repository=tablerelay_log_recovery= onslave_preserve_commit_order=1log-slave-updates=trueslave_skip_errors= ' 1032,1062 ' relay_log=/data/mydata3308/ localhost-relay-bin### #gtid ##### #gtid_mode  = onenforce_gtid_consistency = onmaster_verify_checksum = 1slave_sql_verify_checksum =  1

4. Initializing instances

Example 3307# cd/usr/local/mysql5.7.18/bin#./mysqld--user=mysql--basedir=/usr/local/mysql5.7.18/--datadir=/data/ mydata3307/--initialize--initialize-insecure instance 3308#./mysqld--user=mysql--basedir=/usr/local/mysql5.7.18/-- datadir=/data/mydata3308/--initialize--initialize-insecure

5. Start the service

# service mysqld_multi start# service mysqld_multi reportreporting mysql  serversmysql server from group: mysqld3307 is runningmysql server  from group: mysqld3308 is running# ss -ntpl | grep  mysqldlisten     0      128                       :: :3307                     :::*       users: (("mysqld", 8004,29)) listen      0      128                       :::3308                     :::*       users: ((" Mysqld ", 8003,29)) multiple instances start successfully

6. Set the admin account password

Default initialization password is empty, prompt enter password, direct carriage return # mysqladmin-uroot-p password 123456-s/data/mydata3307/mysql5.7.18.sock# mysqladmin-uroot-p p Assword 123456-s/data/mydata3308/mysql5.7.18.sock

7. Stopping an instance

# Service Mysqld_multi Stop 3307# service mysqld_multi reportreporting MySQL serversmysql server from group:mysqld3307 are Not runningmysql server from group:mysqld3308 is running

Attention:

Multiple Instance service startup script start error

[[Email protected] mysql5.7.18]# service Mysqld_multi start

Warning:my_print_defaults command not found.

Please do sure you has this command available and

In your path. The command is available from the latest

MySQL distribution.

Abort:can ' t find command ' my_print_defaults '.

This command was available from the latest MySQL

Distribution. Please do sure you have the command

In your PATH.

Modify/etc/init.d/mysqld_multi

Export path= $PATH:/usr/local/mysql5.7.18/bin

This article is from the "Linux Road" blog, make sure to keep this source http://hnr520.blog.51cto.com/4484939/1923890

MySQL standalone multi-instance deployment