MySQL单机多实例部署

一、MySQL多实例部署

版本：5.7.18

1.软件安装

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

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

# 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.提供配置文件

# 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.启动服务

# 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))多实例启动成功

6.设置管理账号密码

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

7.停止实例

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

注意：

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

[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