MySQL数据库安装实践 Part 2：多实例部署

最新推荐文章于 2022-07-29 02:49:53 发布

吥輕誩放棄

最新推荐文章于 2022-07-29 02:49:53 发布

阅读量232

点赞数 1

分类专栏： MySQL 5.7 文章标签： MySQL Multinstanse installation

本文链接：https://blog.csdn.net/sandy9919/article/details/88919592

版权

MySQL 5.7 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

1 MySQL多实例介绍

MySQL多实例是在一台服务器同时开启多个不同服务端口（例如 3306、3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。

MySQL多实例公用一套安装程序，使用不同的my.cnf配置文件、启动程序和数据文件。提供服务时，多个实例是相互独立的，根据配置文件的对应配置获取服务器的资源。

1.1 MySQL多实例优势

1.2 MySQL多实例问题

存在资源争抢，当某实例并发很高或者SQL慢查询时，会消耗大量CPU、磁盘I/O等资源，此时服务器整体性能下降，导致其他实例资源紧张。实例获取资源是相对独立的，无法与虚拟化一样完全隔离。

2 MySQL多实例的应用场景

有效利用服务器资源，单个服务器资源剩余时，可以充分利用剩余资源提供更多服务，且实现资源逻辑隔离
节约服务器资源，资金紧张，但数据库要求独立地提供服务，而且需要用到主从复制技术，那么多实例是最好的选择

2.1 资金紧张

企业资金紧张，业务访问量较少，但数据库服务各自独立地提供服务。例如，4台服务器，部署约16个实例，交叉主从复制、数据备份及读写分离。

2.2 并发访问量小

业务并发访问量小，服务器资源过剩，此时建议部署多实例。

3 MySQL多实例常见的配置方案

3.1 集中配置、统一启停

MySQL官方文档介绍的单一配置文件、单一启动程序多实例部署方案。配置文件如下：

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = mysql

[mysqld]
socket = /var/lib/mysql/mysql.sock
port = 3306
pid-file = /var/lib/mysql/mysql.pid
datadir = /var/lib/mysql
user = mysql

[mysqld2]
socket = /app/mysql/db1/mysql.sock
port = 3307
pid-file = /app/mysql/db1/mysql.pid
datadir = /app/mysql/db1
user = mysql

skip-name-resolve
server-id = 10
default-storage-engine = innodb
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool = 10M
default_character_set = utf8
character_set_server = utf8
relay-log-space-limit = 2048M
expire_logs_day = 30

启动命令：

mysqld_multi --config-file=/app/mysql/my_multi.cnf start 1,2

注：此方案耦合度高，单一配置文件，不易管理。

3.2 分散配置、独立启停

多配置文件、独立启动程序部署

[root@node1 ~]# tree /app
/app
|-- 3306
|   |-- data
|   |-- my.cnf
|   `-- mysql
`-- 3307
    |-- data
    |-- my.cnf
    `-- mysql
4 directories, 4 files

注：配置文件、启动程序和数据文件目录都是独立的

4 MySQL多实例部署

4.1 安装多实例

4.1.1 安装依赖包

--- 查看依赖包

rpm -qa | grep ncurses-devel libaio-devel

--- 安装依赖包

yum install ncurses-devel libaio-devel -y

4.1.2 安装编译软件

--- 查看cmake包

rpm -qa | grep cmake

--- 安装cmake

yum install cmake

4.1.3 创建mysql用户

useradd -s /sbin/nologin -M mysql

4.1.4 获取mysql软件包

MySQL下载地址：https://downloads.mysql.com/archives/community/

MySQL 源码包：mysql-5.7.24.tar.gz

4.1.5 编译安装mysql

参考《MySQL数据库安装实践》2.1.2章节

4.2 创建多实例数据目录

/app 作为多实例的根目录，以服务端口号命名各个实例的数据目录

--- 创建多级目录

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

--- 查看目录结构

tree /multinst

4.3 配置多实例my.cnf

--- 备份my.cnf

mv /etc/my.cnf /etc/my.cnf.bak

--- 配置my.cnf

vi /multinst/3306/my.cnf

[client]

port = 3306
socket = /multinst/3306/tmp/mysql.sock

[mysqld]

basedir = /multinst/3306
datadir = /multinst/3306/data
user = mysql
port = 3306
socket = /multinst/3306/tmp/mysql.sock
log-bin = /multinst/3306/log/mysql-bin
server-id = 6

[mysqld_safe]

log-error = /multinst/3306/log/my_3306.err
pid-file = /multinst/3306/mysqld.pid

vi /multinst/3307/my.cnf

[client]

port = 3307
socket = /app/3307/tmp/mysql.sock

[mysqld]

basedir = /multinst/3307
datadir = /multinst/3307/data
user = mysql
port = 3307
socket = /multinst/3307/tmp/mysql.sock
log-bin = /multinst/3307/log/mysql-bin
server-id = 7

[mysqld_safe]

log-error = /multinst/3307/log/my_3307.err
pid-file = /multinst/3307/mysqld.pid

4.4 配置多实例启动文件

启动文件与配置文件一样，可以通过vi命令配置

--- 配置启动文件

cp /app/mysql5.7/support-files/mysql.server /multinst/3306/mysql

vi /multinst/3306/mysql


cp /app/mysql5.7/support-files/mysql.server /multinst/3307/mysql

vi /multinst/3307/mysql

注：唯一需要修改的配置项"port"

多实例启动时，需指定配置文件

--- 启动3306实例

mysqld_safe --defaults-file=/multinst/3306/my.cnf > /dev/null 2>&1&

--- 启动3307实例

mysqld_safe --defaults-file=/multinst/3307/my.cnf > /dev/null 2>&1&

多实例停止服务可以通过kill方式和mysqladmin方式来完成

--- kill方式

vi stop.sh

printf "Stoping MySQL...\n"
mysqld_pid = `cat "$mysqld_pid_file_path"`
if ( kill -0 $mysqld_pid 2 > /dev/null )
    then
        kill $mysqld_pid
        sleep 2
fi

--- mysqladmin方式

mysqladmin -u root -proot123456 -S /multinst/3306/tmp/mysql.sock shutdown

mysqladmin -u root -proot123456 -S /multinst/3307/tmp/mysql.sock shutdown

4.5 多实例文件权限

--- MySQL目录授权用户、组

chown -R mysql.mysql /multinst

find /multinst -name mysql | xargs ls -l 

--- 配置mysql启动文件权限

find /multinst -name mysql | xargs chmod 700

--- 检查权限

find /multinst -name mysql exec ls -l {} \;

4.6 MySQL命令添加到全局路径

之前配置启动和停止mysql都使用的绝对路径‘/app/mysql/bin/mysql’，很繁琐。添加全局路径后可直接敲mysql。下面来看看配置方法

--- profile中添加mysql路径

echo 'export PATH=/app/mysql/bin:$PATH' >> /etc/profile

--- 查看profile配置

tail -l /etc/profile

--- 生效变更的配置

source /etc/profile

--- 查看PATH

echo $PATH

软连接方式

--- mysql所有命令软连接到全局路径下

ln -s /app/mysql/bin/* /usr/local/sbin

4.7 初始化MySQL数据库文件

--- 初始化数据库

cd /app/mysql5.7/bin

/app/mysql/bin/mysqld --no-defaults --initialize --basedir=/multinst/3306 --datadir=/multinst/3306/data --lc-messages-dir=/app/mysql/share --lc-messages=utf8mb4 --user=mysql

/app/mysql/bin/mysqld --no-defaults --initialize --basedir=/multinst/3307 --datadir=/multinst/3307/data --lc-messages-dir=/app/mysql/share --lc-messages=utf8mb4 --user=mysql

--- 核验数据文件

tree /multinst

4.8 启动多实例

--- 创建tmp目录

mkdir -p /multinst/3306/tmp /multinst/3307/tmp

chown -R mysql.mysql /multinst

--- 停止系统所有mysql服务

/etc/init.d/mysqld stop

chkconfig mysqld off

chkconfig --list | grep mysqld

--- 启动3306实例

/app/3306/mysql start

/app/mysql5.7/bin/mysqld_safe --defaults-file=/multinst/3306/my.cnf > /dev/null 2>&1&

--- 启动3307实例

/app/3307/mysql start

/app/mysql5.7/bin/mysqld_safe --defaults-file=/multinst/3307/my.cnf > /dev/null 2>&1&

--- 检查多实例启动情况

netstat -lntup | grep 330

5 多实例数据库的管理与配置

5.1 多实例数据库自启动配置

vi /etc/rc.d/rc.local

#mysql multi instances

mysqld_safe --defaults-file=/multinst/3306/my.cnf > /dev/null 2>&1&

mysqld_safe --defaults-file=/multinst/3307/my.cnf > /dev/null 2>&1&

5.2 多实例数据库管理

MySQL5.7多实例登录时，需要指定实例的sock路径及mysql.sock文件并且用到初始化的临时密码，通过"-S"参数设置sock文件

--- 使用临时密码登录

mysql -uroot -p'eiSjqq<u5jGX' -S /multinst/3306/tmp/mysql.sock

mysql -uroot -p'bXtBd9Fgy#u5' -S /multinst/3307/tmp/mysql.sock

--- 首次登录后需更改密码才能查询数据

ALTER USER USER() IDENTIFIED BY 'root1234';

5.3 多实例数据库安全配置

MySQL5.6及以前版本的root密码默认为空，可以通过mysqladmin配置实例的管理员密码。MySQL5.7初始化

--- MySQL 5.6 及以前版本，多实例初次配置密码

mysqladmin -u root -S /multinst/3306/tmp/mysql.sock password 'root123456'

mysqladmin -u root -S /multinst/3307/tmp/mysql.sock password 'root123456'

--- MySQL 5.7版本，多实例初次配置密码

使用临时密码登录mysql后，使用alter user配置密码

--- 登录实例

mysql -uroot -p -S /multinst/3306/tmp/mysql.sock

mysql -uroot -p'root1234' -S /multinst/3306/tmp/mysql.sock

mysql -uroot -p -S /multinst/3307/tmp/mysql.sock

mysql -uroot -p'root1234' -S /multinst/3307/tmp/mysql.sock

--- MySQL 5.7 修改密码，需登录mysql后使用"alter user"或"update"进行操作

UPDATE user set password=password('root123') where user='root' and host='localhost';

FLUSH PRIVILEGES;

附件

my.cnf参考配置

[client]
port = 3307
socket = /multinst/3307/tmp/mysql3307.sock

[mysql]
auto-rehash

[mysqld]
user = mysql
port = 3307
socket= /multinst/3307/tmp/mysql3307.sock
pid-file = /multinst/3307/mysql3307.pid
datadir = /multinst/3307/data
basedir = /multinst/3307
log-error = /multinst/3307/log/my3307.err

skip_name_resolve = 1 
skip-external-locking 
max_connections = 3000
max_connect_errors = 10 
transaction_isolation = READ-COMMITTED
interactive_timeout=86400
wait_timeout=86400
back_log=600

####cache######  
table_open_cache=2000 
thread_cache_size=500
query_cache_size=128M
query_cache_min_res_unit=128k
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=2M
bulk_insert_buffer_size=16M
max_heap_table_size=64M
tmp_table_size=64M

###MyISAM####
key_buffer_size=64M
key_cache_block_size=4k
myisam_sort_buffer_size=2M

########innodb settings########
#innodb_page_size = 4k
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 50
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_log_group_home_dir = /multinst/3307/redolog/
innodb_undo_directory = /multinst/3307/undolog/
innodb_undo_logs = 128
#innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 4M

######mysqld-5.7########
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
show_compatibility_56=on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

#skip-grant-tables