多实例介绍与搭建

多实例介绍与搭建

1.MySQL 多实例介绍

MySQL 多实例:简单理解就是在一台服务器上,MySQL 通过开启多个不同的端口(3306、3307、3308)来运行多个服务进程。这些 MySQL 服务进程通过不同的 socket 来监听不同的实例端口,进而实现互不干扰的提供各自的服务。

在同一台服务器上的 MySQL 多实例是共用一套 MySQL 应用程序,因此我们在部署 MySQL 的时候只需要部署一次 MySQL 程序即可,只是 MySQL 多实例之间会使用各自不同的 my.cnf 配置文件、启动程序和数据文件。在提供服务方面,MySQL 多实例在逻辑上看起来各自是独立的、互不干涉的,并且多个实例之间是根据配置文件的设定值来获取相关服务器的硬件资源。

当然像云数据库(RDS)都是每个实例单独部署一个 MySQL 程序,以便做的各种操作都不会互相影响;

2.MySQL 多实例优缺点

  • 优点(1)更充分利用服务器资源
    当物理机配置比较高,单个实例无法充分使用服务器资源时,导致服务器资源过剩时,可以充分利用剩余的资源来提供更多的服务,或者是不同的业务错高峰运行;
    (2)节约服务器资源
    当公司预算不足,但是我们又需要使用主从同步技术,这时多实例是最好的选择;
    (3)提高 MySQL 服务性能
    MySQL 数据库随着连接数的上升,性能会出现下降。所以我们可以使用 MySQL 多实例来分担 MySQL 数据库的连接数;
  • 缺点(1)多实例资源互相抢占的问题
    当某个服务实例并发很高或者有慢查询时,整个实例会消耗更多的硬件资源,例如内存、CPU 和 IO 资源。这样必将导致服务器上的其他实例服务质量的下降,会对服务造成一定的影响。

3.MySQL 多实例实现方式

mysql 多实例一般来讲,有两种方案可以实现,两种方案各有利弊

(1) 基于多配置文件

可以通过使用过个配置文件来启动不同的 MySQL 进程,以此来实现多实例的创建;

  • 优点: 配置简单,逻辑也比较简单
  • 缺点: 如果实例太多,管理起来可能不是太方便

(2) 基于 mysqld_multi

通过官方自带的 mysqld_multi 工具来创建多实例,使用单独配置文件来实现多实例的管理

  • 优点: 便于集中管理
  • 缺点: 不方便针对每个实例的配置进行定制

4、MySQL多实例部署

配置条件

port socket Conf
3306 /data/mysql/mysql3306/tmp/mysql.sock /data/mysql/my3306.cnf
3307 /data/mysql/mysql3307/tmp/mysql.sock /data/mysql/my3307.cnf
3308 /data/mysql/mysql3308/tmp/mysql.sock  /data/mysql/my3308.cnf

 

5、安装相关依赖包

yum -y install libaio

6、创建多个配置文件路径

touch -p /data/mysql/my33{6..8}.cnf

7、创建数据目录

[root@rhzy1 mysql]# mkdir -p /data/mysql/mysql3307/{data,tmp,logs}

 

8、授权UID与GID

 

chown -R mysql.mysql /data/

 

9、配置文件路径更改

[client]
port            = 3306

[mysql]
auto-rehash
prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log

[mysqld]
####: for global
user                                =mysql                          #   mysql
basedir                             =/usr/local/mysql/              #   /usr/local/mysql/
datadir                             =/data/mysql/mysql3306/data     #   /usr/local/mysql/data
server_id                           =1003306                        #   0
port                                =3306                           #   3306
character_set_server                =utf8                           #   latin1
explicit_defaults_for_timestamp     =off                            #    off
log_timestamps                      =system                         #   utc
socket                              =/data/mysql/mysql3306/tmp/mysql.sock                #   /tmp/mysql.sock
read_only                           =0                              #   off
skip_name_resolve                   =1                              #   0
auto_increment_increment            =1                              #   1
auto_increment_offset               =1                              #   1
lower_case_table_names              =1                              #   0
secure_file_priv                    =                               #   null
open_files_limit                    =65536                          #   1024
max_connections                     =1000                           #   151
thread_cache_size                   =64                             #   9
table_open_cache                    =81920                          #   2000
table_definition_cache              =4096                           #   1400
table_open_cache_instances          =64                             #   16
max_prepared_stmt_count             =1048576                        #

####: for binlog
binlog_format                       =row                          #     row
log_bin                             =/data/mysql/mysql3306/logs/mysql-bin                      #        off
binlog_rows_query_log_events        =on                             #   off
log_slave_updates                   =on                             #   off
expire_logs_days                    =7                              #   0
binlog_cache_size                   =65536                          #   65536(64k)
#binlog_checksum                     =none                           #  CRC32
sync_binlog                         =1                              #   1
slave-preserve-commit-order         =ON                             #

####: for error-log
log_error                           =/data/mysql/mysql3306/logs/error.log                        #      /usr/local/mysql/data/localhost.localdomain.err

general_log                         =off                            #   off
general_log_file                    =general.log                    #   hostname.log

####: for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
#log_queries_not_using_indexes       =on                             #    off
long_query_time                     =1.000000                       #    10.000000

####: for gtid
#gtid_executed_compression_period    =1000                          #   1000
gtid_mode                           =on                            #    off
enforce_gtid_consistency            =on                            #    off


####: for replication
skip_slave_start                     =1                              #
#master_info_repository              =table                         #   file
#relay_log_info_repository           =table                         #   file
slave_parallel_type                  =logical_clock                 #    database | LOGICAL_CLOCK
slave_parallel_workers               =4                             #    0
#rpl_semi_sync_master_enabled        =1                             #    0
#rpl_semi_sync_slave_enabled         =1                             #    0
#rpl_semi_sync_master_timeout        =1000                          #    1000(1 second)
#plugin_load_add                     =semisync_master.so            #
#plugin_load_add                     =semisync_slave.so             #
binlog_group_commit_sync_delay       =100                           #    500(0.05%秒)、默认值0
binlog_group_commit_sync_no_delay_count = 10                       #    0


####: for innodb
default_storage_engine                          =innodb                     #   innodb
default_tmp_storage_engine                      =innodb                     #   innodb
innodb_data_file_path                           =ibdata1:100M:autoextend    #   ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #   ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #   ib_buffer_pool
innodb_log_group_home_dir                       =./                         #   ./
innodb_log_files_in_group                       =3                          #   2
innodb_log_file_size                            =100M                       #   50331648(48M)
innodb_file_per_table                           =on                         #   on
innodb_online_alter_log_max_size                =128M                       #   134217728(128M)
innodb_open_files                               =65535                      #   2000
innodb_page_size                                =16k                        #   16384(16k)
innodb_thread_concurrency                       =0                          #   0
innodb_read_io_threads                          =4                          #   4
innodb_write_io_threads                         =4                          #   4
innodb_purge_threads                            =4                          #   4(垃圾回收)
innodb_page_cleaners                            =4                          #   4(刷新lru脏页)
innodb_print_all_deadlocks                      =on                         #   off
innodb_deadlock_detect                          =on                         #   on
innodb_lock_wait_timeout                        =20                         #   50
innodb_spin_wait_delay                          =128                          # 6
innodb_autoinc_lock_mode                        =2                          #   1
innodb_io_capacity                              =200                        #   200
innodb_io_capacity_max                          =2000                       #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                         #   on
innodb_stats_persistent                         =on                         #   on
innodb_stats_persistent_sample_pages            =20                         #   20

innodb_adaptive_hash_index                      =on                         #   on
innodb_change_buffering                         =all                        #   all
innodb_change_buffer_max_size                   =25                         #   25
innodb_flush_neighbors                          =1                          #   1
#innodb_flush_method                             =                           #
innodb_doublewrite                              =on                         #   on
innodb_log_buffer_size                          =128M                        #  16777216(16M)
innodb_flush_log_at_timeout                     =1                          #   1
innodb_flush_log_at_trx_commit                  =1                          #   1
innodb_buffer_pool_size                         =100M                  #        134217728(128M)
innodb_buffer_pool_instances                    =4
autocommit                                      =1                          #   1
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =OFF                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                         #    25
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON

####  for performance_schema
performance_schema                                                      =off   #    on
performance_schema_consumer_global_instrumentation                      =on    #    on
performance_schema_consumer_thread_instrumentation                      =on    #    on
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_statements_digest                           =on    #    on
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =on    #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance-schema-instrument                                           ='memory/%=COUNTED'

 

8、初始化MySQL多实例

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/my3306.cnf --initalize

9、多实例启动

 

/usr/local/mysql/bin/mysql -S -uroot -p /data/mysql/my3308.cnf &

10、多实例关闭

/usr/local/mysql/bin/mysqladmin -uroot -pzstzst3 -S /data/mysql/mysql3308/tmp/mysql.sock shutdown

 

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
百度已收录
Linux

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: