MySQL stand-alone multi-instance deployment

I. 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-glibc2.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*

two。 Provide multi-instance service startup script

# 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 fil

# cat / etc/my.cnf [mysql] # password = 123456#prompt = [\\ u@\\ h] [\\ d] >\\ _ socket = / tmp/mysql5.7.18.sock [client] # password = 123456#prompt = [\\ u@\\ 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 Easy to use service script to stop instance user = rootpass = 12345 initialization requires [mysqld] section configuration Otherwise, the [mysqld3307] and [mysqld3308] paragraphs on setting independent undo table space and shared table space 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 cannot be loaded during initialization. .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. Initialize an instance

Instance 330clients cd / usr/local/mysql5.7.18/bin#. / mysqld-- user=mysql-- basedir=/usr/local/mysql5.7.18/-- datadir=/data/mydata3307/-- initialize--initialize-insecure instance 330clients. / 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 Magi 29)) LISTEN 0 128:: 3308:: * users: (("mysqld") 8003GMT 29) multiple instances were launched successfully

6. Set the administrative account password

The default initialization password is empty. When prompted for a password, enter # mysqladmin-uroot-p password 123456-S / data/mydata3307/mysql5.7.18.sock# mysqladmin-uroot-p password 123456-S / data/mydata3308/mysql5.7.18.sock directly.

7. Stop the instance

# service mysqld_multi stop 3307# service mysqld_multi reportReporting MySQL serversMySQL server from group: mysqld3307 is not runningMySQL server from group: mysqld3308 is running

Note:

Multi-instance service startup script starts an error

[root@localhost mysql5.7.18] # service mysqld_multi start

WARNING: my_print_defaults command not found.

Please make sure you have 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 is available from the latest MySQL

Distribution. Please make sure you have the command

In your PATH.

Modify / etc/init.d/mysqld_multi

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