mysql5.7 多实例部署,MySQL5.7版本多实例化

1.概述

现生产库中主要使用的是MySQL-5.7这个版本,而很多企业在测试环境或者生产环境,为了节省服务器的资源,便有了一服务器多实例的多情况,本文档主要讲述的是MySQL-5.7多实例部署,基础环境情况如下:

centos 7.4

mysql-5.7.32(二进制包)(直接解压放置相应的目录进行数据库初始化即可)

2.MySQL-5.7多实例部署

MySQL二进制包直接解压并且加入环境变量即可:

# tar -xf mysql-5.7.32-el7-x86_64.tar.gz  -C  /usr/local/mysql

# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

# source  /etc/profie

创建mysql用户,创建msyql工作目录并且目录授权

# groupadd -r mysql

# useradd -r -g mysql -s /bin/false -M mysql

# mkdir -p /data/mysql_{3306,3307}/{data,log,tmp}

# chown -R mysql.mysql /data/

修改数据库配置文件

# vim  /etc/my.cnf

[mysqld_multi]

mysqld    = /usr/local/mysql/bin/mysqld

mysqladmin = /usr/local/mysql/bin/mysqladmin

log        = /tmp/mysql_multi.log

[mysqld]

user=mysql

basedir = /usr/local/mysql

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

key_buffer_size = 32M

wait_timeout=3600

max_allowed_packet = 200M

table_open_cache = 128

sort_buffer_size = 768K

net_buffer_length = 8K

read_buffer_size = 768K

read_rnd_buffer_size = 512K

myisam_sort_buffer_size = 8M

thread_cache_size = 16

query_cache_size = 16M

tmp_table_size = 32M

performance_schema_max_table_instances = 500

max_connections = 1000

max_connect_errors = 100

open_files_limit = 65535

[mysqld3306]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql_3306/data

port=3306

server_id=3306

socket=/tmp/mysql_3306.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql_3306/log/slow.log

log-error = /data/mysql_3306/log/error.log

binlog_format = mixed

log-bin = /data/mysql_3306/log/mysql3306_bin

innodb_file_per_table = 1

innodb_data_file_path = ibdata1:10M:autoextend

innodb_buffer_pool_size = 128M

innodb_log_file_size = 32M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump3306]

quick

max_allowed_packet = 1024M

[mysql3306]

no-auto-rehash

[myisamchk3306]

key_buffer_size = 32M

sort_buffer_size = 768K

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy3306]

interactive-timeout

[mysqld3307]

mysqld=mysqld

mysqladmin=mysqladmin

datadir=/data/mysql_3307/data

port=3307

server_id=3307

socket=/tmp/mysql_3307.sock

log-output=file

slow_query_log = 1

long_query_time = 1

slow_query_log_file = /data/mysql_3307/log/slow.log

log-error = /data/mysql_3307/log/error.log

binlog_format = mixed

log-bin = /data/mysql_3307/log/mysql3307_bin

innodb_file_per_table = 1

innodb_data_file_path = ibdata1:10M:autoextend

innodb_buffer_pool_size = 128M

innodb_log_file_size = 32M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 50

[mysqldump3307]

quick

max_allowed_packet = 1024M

[mysql3307]

no-auto-rehash

[myisamchk3307]

key_buffer_size = 32M

sort_buffer_size = 768K

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy3307]

interactive-timeout

数据库初始化

# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_3306/data

17f924579971

# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_3307/data

17f924579971

# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

# chmod +x /etc/init.d/mysqld_multi

# /etc/init.d/mysqld_multi start #启动多实例

# /etc/init.d/mysqld_multi report #查看多实例状态

修改数据库初始化密码

# mysql -S /tmp/mysql_3306.sock -p'xa+jl+)M2f/e'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.32-log

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = 'Vvm!@#2020';

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

# mysql -S /tmp/mysql_3307.sock -p'I+e;jlw6B3qO'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.32-log

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = 'Vvm!@#2020';

Query OK, 0 rows affected (0.00 sec)

mysql> exit

总结:1.二进制包不需要进行安装部署,只需解压和设置环境变量,最后数据库初始化即可完成数据库部署。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值