一、MySQL多实例部署

版本：5.7.18

1.软件安装

#tarxfmysql-5.7.18-linux-glibc2.5-x86_64.tar.gz-C/usr/local#cd/usr/local#chown-Rroot.rootmysql-5.7.18-linux-glibc2.5-x86_64#ln-svmysql-5.7.18-linux-glibc2.5-x86_64mysql5.7.18#mkdir/data/{mydata3307,mydata3308}#chown-Rmysql.mysql/data/mydata33*

2.提供多实例服务启动脚本

#cd/usr/local/mysql5.7.18#cpsupport-files/mysqld_multi.server/etc/init.d/mysqld_multi#chmod+x/etc/init.d/mysqld_multi#chkconfig--addmysqld_multi#vi/etc/init.d/mysqld_multiexportPATH=$PATH:/usr/local/mysql5.7.18/binbasedir=/usr/local/mysql5.7.18bindir=/usr/local/mysql5.7.18/bin

3.提供配置文件

#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#每个实例都设置统一管理密码，方便使用服务脚本停止实例user=rootpass=123456#初始化需要[mysqld]段配置，否则初始化的时候加载不到[mysqld3307]及[mysqld3308]段中关于设置独立undo表空间及共享表空间大小[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.初始化实例

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

5.启动服务

#servicemysqld_multistart#servicemysqld_multireportReportingMySQLserversMySQLserverfromgroup:mysqld3307isrunningMySQLserverfromgroup:mysqld3308isrunning#ss-ntpl|grepmysqldLISTEN0128:::3307:::*users:(("mysqld",8004,29))LISTEN0128:::3308:::*users:(("mysqld",8003,29))多实例启动成功

6.设置管理账号密码

默认初始化密码为空，提示输入密码时，直接回车#mysqladmin-uroot-ppassword123456-S/data/mydata3307/mysql5.7.18.sock#mysqladmin-uroot-ppassword123456-S/data/mydata3308/mysql5.7.18.sock

7.停止实例

#servicemysqld_multistop3307#servicemysqld_multireportReportingMySQLserversMySQLserverfromgroup:mysqld3307isnotrunningMySQLserverfromgroup:mysqld3308isrunning

注意：

多实例服务启动脚本启动报错

[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.

修改/etc/init.d/mysqld_multi

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