一台主机部署多个mysqld实例方案
【导读】
我们可能出于各种原因，而考虑在同一台物理服务器上部署多个实例，而多实例的部署方式简单，但是如何才能减少我们生产环 境的维护成本，如何减少我们出错的机会，如何方便我们后续的迁移和清理等工作，以及如何借助多实例绑定的方式提高服务器的CPU资源利用率，mysqld 实例绑定处理器的方法可参考文章NUMA处理器绑定多实例到固定核心。
 文章的开篇我们分析一下，什么情况下我们会考虑一台物理服务器上部署多个实例，大致有以下几种情况：
l         采用了数据伪分布式架构的原因，而项目启动初期又不一定有那多的用户量，为此先一组物理数据库服务器，但部署多个实例，方便后续迁移；
l         为规避mysql对SMP架构不支持的缺陷，使用多实例绑定处理器的办法，把不同的数据库分配到不同的实例上提供数据服务；
l         一台物理数据库服务器支撑多个数据库的数据服务，为提高mysql复制的从机的恢复效率，采用多实例部署；
l         已经为双主复制的mysql数据库服务器架构，想部分重要业务的数据多一份异地机房的热备份，而mysql复制暂不支持多主的复制模式，且不给用户提供服 务，为有效控制成本，会考虑异地机房部署一台性能超好的物理服务器，甚至外加磁盘柜的方式，为此也会部署多实例；
l         传统游戏行业的MMO/MMORPG，以及Web Game，每一个服都对应一个数据库，而可能要做很多数据查询和数据订正的工作，为减少维护而出错的概率，也可能采用多实例部署的方式，按区的概念分配数据库；
以上五种应用场景，其中四种是我在不同公司，根据公司业务和维护的实际情况，在生产环境中使用过。
多实例部署的争论点：采用多个my.cnf配置文件，还是使用一个。个人推荐使用一个配置文件的方式，这种方式维护成本 更低更加方便快捷，那我们将围绕使用一个服务器端参数配置文件讲述，以下篇幅分为三段讲述：mysqld_multi命令、配置文件中的节点作用及配置样 例、配置文件的部分参数介绍。
n         mysqld_multi 命令
命令执行语法：
mysqld_multi [OPTIONS] {start|stop|report} [GNR,GNR,GNR...]
或者
mysqld_multi [OPTIONS] {start|stop|report} [GNR-GNR,GNR,GNR-GNR,...]
参考示例：
mysqld_multi start 3306,3307,3308,3309
或者
mysqld_multi start 3306-3309
OPTIONS：
--no-defaults没有需要读取的默认文件；
–defaults-file=….  对于实例的启动、关闭和其他维护动作，只根据此配置文件来确定，
不接受其他方式给予的参数配置信息；
–defaults-extra-file=…  读取给予的参数配置信息，或标准的参数配置文件之外，还可以指定
一个额外的参数配置文件；
n         配置文件中的节点作用及参考配置样例
u       服务器端参数配置文件my.cnf中，有哪些节点呢？常用到需配置参数的节点名称如下：
l         mysqld_multi：配置用于传递给命令工具mysqld_multi的参数信息；
l         client：配置用于传递给每个客户端的参数信息；
l         mysqld实例需配置的参数信息，节点名称例如：mysqld3306，此节点名称可以自定义；
l         mysql：给命令行工具mysql配置的默认参数信息；
l         mysqld_safe：给mysqld服务启动工具mysqld_safe配置的默认参数；
l         mysqldump：给用于数据备份的命令工具mysqldump配置的默认参数；
l         myisamchk：给用于myisam类引擎检查、修复数据命令行工具myisamchk配置的默认参数；
l         mysqlhotcopy：给用于myisam类引擎的数据备份命令行工具mysqlhotcopy配置的默认参数信息；
u       my.cnf文件参考配置样例
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = admin
log = /data/multi.log
[client]
default-character-set = utf8
[mysqld3306]
user = mysql
port = 3306
socket = /data/mysqldata3306/sock/mysql.sock
pid-file = /data/mysqldata3306/sock/mysql.pid
datadir = /data/mysqldata3306/mydata
tmpdir = /data/mysqldata3306/tmpdir
big_tables
skip_external_locking
skip-locking
skip-name-resolve
lower_case_table_names = 1
back_log = 100
default-storage-engine = INNODB
default-character-set = utf8
collation = utf8_general_ci
max_connections = 800
max_connect_errors = 100000
interactive_timeout = 172800
connect_timeout = 10
max_allowed_packet = 4M
max_heap_table_size = 128M
tmp_table_size = 128M
max_length_for_sort_data = 4096
net_buffer_length = 8K
sort_buffer_size = 8M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
thread_concurrency = 8
query_cache_type = 0
#query_cache_size = 64M
query_cache_limit = 1M
#*******************************  Logs related settings ***************************
log-error = /data/mysqldata3306/log/error.log
log_warnings
long_query_time = 1
slow_query_log
slow_query_log_file = /data/mysqldata3306/log/slow-query.log
#log_slow_queries = /data/mysqldata3306/log/slow-query.log
log_queries_not_using_indexes
binlog_cache_size = 8M
max_binlog_size = 512M
log_long_format
log-bin = /data/mysqldata3306/binlog/mysql-bin3306
log-bin-index = /data/mysqldata3306/binlog/mysql-bin3306.index
expire_logs_days = 3
#*******************************  Replication related settings **********************
#master
server-id = 3306
bind-address = 10.10.1.157
report_host = 10.10.1.157
report_port = 3306
report_user = repl
slave_net_timeout = 60
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
binlog-format = mixed
transaction_isolation = REPEATABLE-READ
#******************************* MyISAM Specific options ****************************
key_buffer_size = 32M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
#***************************** INNODB Specific options ******************************
innodb_file_per_table
innodb_autoinc_lock_mode = 1
innodb_fast_shutdown = 2
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 1G
innodb_data_home_dir = /data/mysqldata3306/innodb_ts
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 10
innodb_log_group_home_dir = /data/mysqldata3306/innodb_log
innodb_max_dirty_pages_pct = 20
innodb_lock_wait_timeout = 120
innodb_flush_method=O_DIRECT
[mysqld3307]
user = mysql
port = 3307
socket = /data/mysqldata3307/sock/mysql.sock
pid-file = /data/mysqldata3307/sock/mysql.pid
datadir = /data/mysqldata3307/mydata
tmpdir = /data/mysqldata3307/tmpdir
big_tables
skip_external_locking
skip-locking
skip-name-resolve
lower_case_table_names = 1
back_log = 100
default-storage-engine = INNODB
default-character-set = utf8
collation = utf8_general_ci
max_connections = 800
max_connect_errors = 100000
interactive_timeout = 172800
connect_timeout = 10
max_allowed_packet = 4M
max_heap_table_size = 128M
tmp_table_size = 128M
max_length_for_sort_data = 4096
net_buffer_length = 8K
sort_buffer_size = 8M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
thread_concurrency = 8
query_cache_type = 0
#query_cache_size = 64M
query_cache_limit = 1M
#*******************************  Logs related settings ***************************
log-error = /data/mysqldata3307/log/error.log
log_warnings
long_query_time = 1
slow_query_log
slow_query_log_file = /data/mysqldata3307/log/slow-query.log
#log_slow_queries = /data/mysqldata3307/log/slow-query.log
log_queries_not_using_indexes
binlog_cache_size = 8M
max_binlog_size = 512M
log_long_format
log-bin = /data/mysqldata3307/binlog/mysql-bin3307
log-bin-index = /data/mysqldata3307/binlog/mysql-bin3307.index
expire_logs_days = 3
#*******************************  Replication related settings **********************
#master
server-id = 3307
bind-address = 10.10.1.157
report_host = 10.10.1.157
report_port = 3307
report_user = repl
slave_net_timeout = 60
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0
binlog-format = mixed
transaction_isolation = REPEATABLE-READ
#******************************* MyISAM Specific options ****************************
key_buffer_size = 32M
bulk_insert_buffer_size = 16M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
#***************************** INNODB Specific options ******************************
innodb_file_per_table
innodb_autoinc_lock_mode = 1
innodb_fast_shutdown = 2
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 5G
innodb_data_home_dir = /data/mysqldata3307/innodb_ts
innodb_data_file_path = ibdata1:256M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 10
innodb_log_group_home_dir = /data/mysqldata3307/innodb_log
innodb_max_dirty_pages_pct = 20
innodb_lock_wait_timeout = 120
innodb_flush_method=O_DIRECT
[mysql]
no-auto-rehash
prompt=”\\u@\\h : \\d \\r:\\m:\\s>”
#tee=”/tmp/query.log”
#pager=”less -i -n -S”
max_allowed_packet = 1G
[mysqldump]
quick
max_allowed_packet = 1G
[mysqld_safe]
open-files-limit = 8192
[myisamchk]
key_buffer = 512M
sort_buffer_size = 128M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
n         配置文件的部分参数介绍
u       mysqld_multi 节点
l         user = admin
user参数是为ssmysqld_multi命令配置一个统一默认的管理帐号，能够统一管理旗下所有mysqld服务节点的运行、管理、检查等相关信息，若在此设置的话，命令方式执行时没制定就使用此参数的值；
l         password
该参数是对应user的密码，但是密码信息我们一般都不写到配置文件中，以避免泄漏，而是执行命令的时候再输入；
l         log
该参数用于记录mysqld_multi执行命令的日志信息，以及出错信息，以便于我们查找问题的根源；
u       client 节点
l         default-character-set
若是客户端连接请求没有主动设置字符集，则使用该参数为所有连接mysqld服务器的客户端的默认字符集，但是使用mysqlbinlog命令行工具的时候，5.1系列版本会报错，使用时请暂时在配置文件中进行注释即可；
u       mysqld服务节点，以mysql3306节点内容为例
一台主机部署要多个实例，就涉及到各个实例各自的数据文件如何存放和隔离的问题，处于实例相关的目录和数据清理方便，以及维护成本更低，减少维护时的出错概率，我们采用上述样例配置文件中的目录结构，以及目录和数据文件命名方式，接下来我们主要阐述部分参数设置的意义：
l         user
该参数为mysqld服务启动后，mysqld使用何系统帐号运行mysqld服务的问题，不是指mysql授权表中创 建的帐号，而是指操作系统级别中的帐号。我们安装mysql软件的时候，一般都会创建一个mysql帐号及为其制定用户编号，那么就可以把帐号名称 mysql 或 mysql名称对应的用户编号，设置成参数user的值；
l         lower_case_table_names
类unix或Linux类中的文件系统一般都区分大小写，为减少程序员区分数据库对象名称的大小写问题，建议设置此参数，从而降低风险和降低开发成本；
l         default-character-set和collation
这一组参数是用于指定mysqld服务的字符集和校对规则，为减少字符集转换带来的开销，以及转换而可能带来的乱码问题，我们每个mysqld服务上的数据字符集和校对规则都统一，但是校对规则可以针对需要，设置表或某字段的校对规则不同，不会被全局的覆盖；
l         max_connect_errors
若某一客户端异常断开次数超过该参数设置的值，不重新启动mysqld服务或者执行SQL命令：FLUSH HOSTS 的话，将永远无法再成功连接到数据库服务器上，为此防止出现极端的情况，此参数的值至少要设置大于10W；
l         interactive_timeout
该参数用于设置客户端最长多少时间不发送任何命令给服务器端，除检查客户端是否活着的名另外，时间单位为秒。业务场景中可能存在使用长连接或连接池，但是某个时间段无业务运行或业务低谷时期，防止数据库连接被强制断开，一般设置为48小时；
l         query_cache_type
该参数是设置是否打开查询缓存和设置什么样的SQL可以加入到查询缓存中，特意把次参数列出的原因，是查询缓存有其特殊的场景：适合读为主的业务，且查询缓存大小设置要合理，毕竟查询缓存可能会增加修改类型处理的负荷，而导致性能下降；
l         expire_logs_days
可以借助设置此参数的值，不需要借助外部脚本或工具，就可让mysqld自动完成二进制日志文件的清理工作，该参数只能设置为整数n，表示的是保留n+1天的二进制日志文件，超过的则会在生成新的二进制日志文件时候，自动进行检测和删除掉；
l         binlog-format和transaction_isolation
二个参数分别是控制二进制日志登记模式、事务隔离方式，这2组参数组合在一起会共同决定最终登记二进制日志的格式，以及复制的模式，关于这方面信息，可参考文章解读MySQL事务的隔离级别和日志登记模式选择技巧；
l         innodb_file_per_table
对于InnoDB引擎的表，为减少维护表空间的大小，以及磁盘空间占用而不释放的问题，推荐使用单表表空间的模式，效率也更高，维护成本也更低；
n         后续
为帮助我们减少数据库结构和数据操作、维护或应用程序连接时出错的概率，mysql数据库系统中数据库访问的帐号名称及 密码上需要花一些心思，帮助我们减少失误的概率，而且维护成本合理。我们可以对用户名称进行一些特殊设置，比如采用：站点名称_也许代号_端口号的模式， 比如：xy_brmms_3306，帐号对应的密码我，们也可以在自动生成的基础上做一些对应的设置。

原创文章，转载请注明： 文章地址一台主机部署多个mysqld实例方案