linux安装单机mysql_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_multi

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

basedir=/usr/local/mysql5.7.18

bindir=/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_safe

mysqladmin =/usr/local/mysql5.7.18/bin/mysqladmin

log =/data/mydata3307/mysqld_multi.log

# 每个实例都设置统一管理密码，方便使用服务脚本停止实例

user = root

pass = 123456

#初始化需要[mysqld]段配置，否则初始化的时候加载不到[mysqld3307]及[mysqld3308]段中关于设置独立undo表空间及共享表空间大小

[mysqld]

innodb_buffer_pool_size = 4096M

#innodb_buffer_pool_size = 16384M

innodb_undo_log_truncate=ON

innodb_undo_tablespaces = 2

innodb_data_file_path=ibdata1:1G:autoextend

[mysqld3307]

innodb_buffer_pool_size = 4096M

#innodb_buffer_pool_size = 16384M

port = 3307

socket          = /data/mydata3307/mysql5.7.18.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 10M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 16M

thread_cache_size = 4

max_connections=1500

character_set_server=utf8

group_concat_max_len=65535

log_bin_trust_function_creators=1

log_queries_not_using_indexes = ON

log_throttle_queries_not_using_indexes = 2

interactive_timeout = 600

wait_timeout = 600

connect_timeout = 10

expire_logs_days = 30

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-ignore-db=mysql

replicate-ignore-db=sys

log_timestamps=SYSTEM

innodb_print_all_deadlocks=1

basedir=/usr/local/mysql5.7.18

datadir=/data/mydata3307

innodb_undo_log_truncate=ON

innodb_undo_tablespaces = 2

innodb_data_file_path=ibdata1:1G:autoextend

core_file

sync_binlog = 0

innodb_flush_log_at_trx_commit = 2

##Master

#log-bin=mysql-bin

log-bin=/data/mydata3307/mysql-bin

#binlog_format=mixed

binlog_format=row

server-id=3307

lower_case_table_names = 1

skip-name-resolve

innodb_file_per_table=1

long_query_time=2

slow_query_log=1

slow_query_log_file=/data/mydata3307/slow-query.log

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

#slave

slave-parallel-type=LOGICAL_CLOCK

#slave-parallel-workers=16

slave-parallel-workers=4

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

slave_preserve_commit_order=1

log-slave-updates=true

slave_skip_errors='1032,1062'

relay_log=/data/mydata3307/localhost-relay-bin

####gtid######

gtid_mode = ON

enforce_gtid_consistency = ON

master_verify_checksum = 1

slave_sql_verify_checksum = 1

[mysqld3308]

innodb_buffer_pool_size = 2048M

#innodb_buffer_pool_size = 16384M

port = 3308

socket          = /data/mydata3308/mysql5.7.18.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 10M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 16M

thread_cache_size = 4

max_connections=1500

character_set_server=utf8

group_concat_max_len=65535

log_bin_trust_function_creators=1

log_queries_not_using_indexes = ON

log_throttle_queries_not_using_indexes = 2

interactive_timeout = 600

wait_timeout = 600

connect_timeout = 10

expire_logs_days = 30

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-ignore-db=mysql

replicate-ignore-db=sys

log_timestamps=SYSTEM

innodb_print_all_deadlocks=1

basedir=/usr/local/mysql5.7.18

datadir=/data/mydata3308

innodb_undo_log_truncate=ON

innodb_undo_tablespaces = 2

innodb_data_file_path=ibdata1:1G:autoextend

core_file

sync_binlog = 0

innodb_flush_log_at_trx_commit = 2

##Master

#log-bin=mysql-bin

log-bin=/data/mydata3308/mysql-bin

#binlog_format=mixed

binlog_format=row

server-id=3308

lower_case_table_names = 1

skip-name-resolve

innodb_file_per_table=1

long_query_time=2

slow_query_log=1

slow_query_log_file=/data/mydata3308/slow-query.log

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

#slave

slave-parallel-type=LOGICAL_CLOCK

#slave-parallel-workers=16

slave-parallel-workers=4

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

slave_preserve_commit_order=1

log-slave-updates=true

slave_skip_errors='1032,1062'

relay_log=/data/mydata3308/localhost-relay-bin

####gtid######

gtid_mode = ON

enforce_gtid_consistency = ON

master_verify_checksum = 1

slave_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 report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

# ss -ntpl | grep mysqld

LISTEN     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 report

Reporting MySQL servers

MySQL server from group: mysqld3307 is not running

MySQL 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