2台主机极致实现双主复制架构及MMM

• 简介

• MySQL复制中较常见的复制架构有“一主一从”、“一主多从”、“双主”、“多级复制”和“多主环形机构”等，见下图；

  

  最常用，也最灵活的就要数“一主多从”复制架构了，其能满足多种需求，如：

  为不同的角色使用不同的备库(例如添加不同的索引或使用不同的存储引擎)；

  把一台备库当做待用的主库，除了复制没有其它数据传输；

  将一台备库放在远程数据中心，用作灾难恢复；

  延迟一个或多个备库，以备灾难恢复；

  使用其中一个备库，作为备份、培训、开发或者测试使用服务器；

  而“双主”复制架构则用于特殊的场景下，如两个处于不同地理位置的办公室，且都需要一份可写的数据拷贝；

  这种架构最大的问题是如何解决数据冲突和不一致，尤其当两台服务器同时修改同一行记录，或同时在两台服务器上向一个包含auto_increment列的表里插入数据时；

  而通过将一台服务器设置为只读的被动服务器，则可以很好的避免数据写入冲突的问题，这种主动-被动模式下的主-主复制架构使得反复切换主动和被动服务器非常方便，可以实现在不关闭服务器的情况下执行维护、优化表、升级操作系统或其他任务；

  配置主动-被动模式的主-主复制架构的一般流程:

  • 确保两台服务器上有相同的数据；

    启用二进制日志，选择唯一的服务器ID，并创建复制账号；

    启用备库更新的日志记录，这是故障转移和故障恢复的关键；

    把被动服务器配置成只读，防止可能与主动服务器上的更新产生冲突；

    启动每个服务器的MySQL实例；

    将每个主库设置为对方的备库，使用新创建的二进制日志开始工作；

    同时为了消除不同地理位置的站点单点故障问题，可以为每个主库增加冗余，即为每一个主库增加一个从库;

    而MMM(=Master-Master Replication Manager for MySQL)则是一套脚本集合，用以监控、管理双主复制架构，通过设置一个可写的VIP和多个只读的VIP，完成故障自动转移、读负载分摊等功能；

     

    架构设计

    

    服务器规划

    

    虚IP规划

    

     

    配置部署

    双主复制架构部署

    MySQL或MariaDB的安装初始化可详见博客“MySQL初识-架构-安装-初始化-连接-管理工具-数据文件”

    利用mysqld_multi在一台主机上启动多个mysqld实例

    数据库初始化

     

    1.# 在主机Host1和Host2上

    2.cd /usr/local/mysql

    3.scripts/mysql_install_db --user=mysql --datadir=/data/mariadb_data_3406/

    4.scripts/mysql_install_db --user=mysql --datadir=/data/mariadb_data_3506/

    数据库配置

     

    001.# 在主机Host1上

    002.vi /etc/my.cnf

    003.[mysqld_multi]

    004.mysqld = /usr/local/mysql/bin/mysqld_safe

    005.mysqladmin = /usr/local/mysql/bin/mysqladmin

    006.[mysqld1]

    007.port = 3406

    008.socket = /tmp/mysql3406.sock

    009.skip-external-locking

    010.key_buffer_size = 256M

    011.max_allowed_packet = 1M

    012.table_open_cache = 256

    013.sort_buffer_size = 1M

    014.read_buffer_size = 1M

    015.read_rnd_buffer_size = 4M

    016.myisam_sort_buffer_size = 64M

    017.thread_cache_size = 8

    018.query_cache_size= 16M

    019.thread_concurrency = 2

    020.datadir = /data/mariadb_data_3406

    021.innodb_file_per_table = 1

    022.default_storage_engine = InnoDB

    023.log-bin=mysql-bin

    024.relay-log=/data/relaylogs_3406/relay-bin # 指定中继日志路径

    025.log_slave_updates=1 # 开启从库更新操作写入二进制日志功能

    026.auto_increment_increment=2 # 双主复制中自增长字段的步长

    027.auto_increment_offset=1 # 双主复制中自增长字段的起始值，此为1

    028.sync_binlog = 1 # 可保证事务日志及时写入磁盘文件

    029.binlog_format=row

    030.server-id = 11  # 注意server-id的唯一性

    031.[mysqld2]

    032.port = 3506

    033.socket = /tmp/mysql3506.sock

    034.skip-external-locking

    035.key_buffer_size = 256M

    036.max_allowed_packet = 1M

    037.table_open_cache = 256

    038.sort_buffer_size = 1M

    039.read_buffer_size = 1M

    040.read_rnd_buffer_size = 4M

    041.myisam_sort_buffer_size = 64M

    042.thread_cache_size = 8

    043.query_cache_size= 16M

    044.thread_concurrency = 2

    045.datadir = /data/mariadb_data_3506

    046.innodb_file_per_table = 1

    047.default_storage_engine = InnoDB

    048.log-bin=mysql-bin

    049.relay-log=/data/relaylogs_3506/relay-bin

    050.log_slave_updates=1

    051.sync_binlog = 1

    052.binlog_format=row

    053.server-id = 12

    054.# 在主机Host2上

    055.vi /etc/my.cnf

    056.[mysqld_multi]

    057.mysqld = /usr/local/mysql/bin/mysqld_safe

    058.mysqladmin = /usr/local/mysql/bin/mysqladmin

    059.[mysqld1]

    060.port = 3406

    061.socket = /tmp/mysql3406.sock

    062.skip-external-locking

    063.key_buffer_size = 256M

    064.max_allowed_packet = 1M

    065.table_open_cache = 256

    066.sort_buffer_size = 1M

    067.read_buffer_size = 1M

    068.read_rnd_buffer_size = 4M

    069.myisam_sort_buffer_size = 64M

    070.thread_cache_size = 8

    071.query_cache_size= 16M

    072.thread_concurrency = 2

    073.datadir = /data/mariadb_data_3406

    074.innodb_file_per_table = 1

    075.default_storage_engine = InnoDB

    076.log-bin=mysql-bin

    077.relay-log=/data/relaylogs_3406/relay-bin

    078.log_slave_updates=1

    079.auto_increment_increment=2 # # 双主复制中自增长字段的步长

    080.auto_increment_offset=2 # 双主复制中自增长字段的起始值，此为2

    081.sync_binlog = 1

    082.binlog_format=row

    083.server-id = 21

    084.[mysqld2]

    085.port = 3506

    086.socket = /tmp/mysql3506.sock

    087.skip-external-locking

    088.key_buffer_size = 256M

    089.max_allowed_packet = 1M

    090.table_open_cache = 256

    091.sort_buffer_size = 1M

    092.read_buffer_size = 1M

    093.read_rnd_buffer_size = 4M

    094.myisam_sort_buffer_size = 64M

    095.thread_cache_size = 8

    096.query_cache_size= 16M

    097.thread_concurrency = 2

    098.datadir = /data/mariadb_data_3506

    099.innodb_file_per_table = 1

    100.default_storage_engine = InnoDB

    101.log-bin=mysql-bin

    102.relay-log=/data/relaylogs_3506/relay-bin

    103.log_slave_updates=1

    104.sync_binlog = 1

    105.binlog_format=row

    106.server-id = 22

    启动数据库实例

    1.# 在主机Host1和Host2上

    2./etc/init.d/mysqld_multi start 1 # 停止服务操作是/etc/init.d/mysqld_multi stop 1

    3./etc/init.d/mysqld_multi start 2 # 停止服务操作是/etc/init.d/mysqld_multi stop 2

    登录数据库

    1.# 在主机Host1和Host2上

    2.mysql -S /tmp/mysql3406.sock # 登录master1或master2

    3.mysql -S /tmp/mysql3506.sock # 登录slave1或slave2

    创建所需账户(在Master1实例上)

    1.grant replication client on *.* to '3m_moni'@'192.168.0.%' identified by '3m_12345'; # 创建MMM的监控账户

    2.grant super,replication client,process on *.* to '3m_agen'@'192.168.0.%' identified by '3m_12345'; # 创建MMM的代理账户

    3.grant replication slave on *.* to '3m_repl'@'192.168.0.%' identified by '3m_12345'; # 创建复制账户

    配置数据同步

    01.# 每次从库连接主库前，需先查询对应主库的二进制日志文件及其事件位置，即在主库上执行show master status即可，据此决定从库连接时的master_log_file和master_log_pos参数；

    02.# slave1实例上

    03.change master to master_host='192.168.0.45',master_port=3406,master_user='3m_repl',master_pass<a href="http://www.it165.net/edu/ebg/" target="_blank"class="keylink">word</a>='3m_12345',master_log_file='mysql-bin.000001',master_log_pos=2448;

    04.# master2实例上

    05.change master to master_host='192.168.0.45',master_port=3406,master_user='3m_repl',master_pass<a href="http://www.it165.net/edu/ebg/" target="_blank"class="keylink">word</a>='3m_12345',master_log_file='mysql-bin.000002',master_log_pos=365;

    06.# slave2实例上

    07.change master to master_host='192.168.0.46',master_port=3406,master_user='3m_repl',master_password='3m_12345',master_log_file='mysql-bin.000004',master_log_pos=342;

    08.# master1实例上

    09.change master to master_host='192.168.0.46',master_port=3406,master_user='3m_repl',master_password='3m_12345',master_log_file='mysql-bin.000004',master_log_pos=342;

    查看同步状态

    01.# 重点检查Slave_IO_Running、Slave_SQL_Running和Master_Server_Id等参数

    02.MariaDB [(none)]> show slave status\G

    03.*************************** 1. row ***************************

    04.Slave_IO_State: Waiting for master to send event

    05.Master_Host: 192.168.0.45

    06.Master_User: 3m_repl

    07.Master_Port: 3406

    08.Connect_Retry: 60

    09.Master_Log_File: mysql-bin.000005

    10.Read_Master_Log_Pos: 326

    11.Relay_Log_File: relay-bin.000010

    12.Relay_Log_Pos: 613

    13.Relay_Master_Log_File: mysql-bin.000005

    14.Slave_IO_Running: Yes

    15.Slave_SQL_Running: Yes

    16.Master_Server_Id: 11

     

    MMM安装部署

    Host1主机上：部署agent和monitor

     

     

    01.yum -y install mysql-mmm-*

    02.# 配置公共设置

    03.vi /etc/mysql-mmm/mmm_common.conf

    04.active_master_role      writer

    05.<host default>

    06.cluster_interface       eth0

    07.pid_path                /var/run/mysql-mmm/mmm_agentd.pid

    08.bin_path                /usr/libexec/mysql-mmm/

    09.replication_user        3m_repl# 复制账户

    10.replication_password    3m_12345# 复制账户密码

    11.agent_user              3m_agen# agent账户

    12.agent_password          3m_12345# agent账户密码

    13.</host>

    14.<host db1>

    15.ip      192.168.0.45

    16.mysql_port 3406# 可指定需连接的mysqld的端口

    17.mode    master

    18.peer    db2# peer表示db1、db2是同等级别的

    19.</host>

    20.<host db2>

    21.ip      192.168.0.46

    22.mysql_port 3406

    23.mode    master

    24.peer    db1

    25.</host>

    26.<host db3>

    27.ip      192.168.0.45

    28.mysql_port 3506

    29.mode    slave

    30.</host>

    31.<host db4>

    32.ip      192.168.0.46

    33.mysql_port 3506

    34.mode    slave

    35.</host>

    36.<role writer>

    37.hosts   db1, db2

    38.ips     192.168.0.11# 可写VIP只配置一个

    39.mode    exclusive# 表示排它

    40.</role>

    41.<role reader>

    42.hosts   db1, db2,db3,db4

    43.ips     192.168.0.12,192.168.0.13,192.168.0.14,192.168.0.15 # 只读VIP可配置多个

    44.mode    balanced# 表示可以共用

    45.</role>

    46.==========

    47.scp mmm_common.conf 192.168.0.46:/etc/mysql-mmm/ # 将公共配置文件拷贝至其它主机

    48.==========

    49.# 配置监控设置

    50.vi /etc/mysql-mmm/mmm_mon.conf

    51.include mmm_common.conf

    52.<monitor>

    53.ip                  127.0.0.1

    54.pid_path            /var/run/mysql-mmm/mmm_mond.pid

    55.bin_path            /usr/libexec/mysql-mmm

    56.status_path         /var/lib/mysql-mmm/mmm_mond.status

    57.ping_ips            192.168.0.45,192.168.0.46# 健康监测时需ping的主机IP，不是VIP哦

    58.auto_set_online     60

    59.</monitor>

    60.<host default>

    61.monitor_user        3m_moni# 监控账户

    62.monitor_password    3m_12345 # 监控账户密码

    63.</host>

    64.debug 0

    65.# 配置agent设置

    66.vi /etc/mysql-mmm/mmm_agent.conf

    67.include mmm_common.conf

    68.this db1# 因为在一台主机上启用了2个mysqld实例，故可配置2个this参数哦

    69.this db3

    Host2主机上：只需部署agent

    1.yum -y install mysql-mmm-agent

    2.# 配置agent设置

    3.vi /etc/mysql-mmm/mmm_agent.conf

    4.include mmm_common.conf

    5.this db2

    6.this db4

    服务启动

    1.# Host2主机上

    2.service mysql-mmm-agent start # 启动agent代理程序

    3.# Host1主机上

    4.service mysql-mmm-agent start

    5.service mysql-mmm-monitor start # 启动监控程序

     

    测试验证

    查看双主复制架构中基于MMM实现的状态信息：

    1.# 在主机Host1上

    2.[root@mysql mysql-mmm]# mmm_control show

    3.db1(192.168.0.45) master/ONLINE. Roles: reader(192.168.0.14), writer(192.168.0.11)

    4.db2(192.168.0.46) master/ONLINE. Roles: reader(192.168.0.13)

    5.db3(192.168.0.45) slave/ONLINE. Roles: reader(192.168.0.15)

    6.db4(192.168.0.46) slave/ONLINE. Roles: reader(192.168.0.12)

    手动进行各节点的健康监测

    01.# 在主机Host1上

    02.[root@mysql mysql-mmm]# mmm_control checks

    03.db4  ping         [last change: 2014/05/06 22:38:27]  OK

    04.db4  mysql        [last change: 2014/05/06 22:38:27]  OK

    05.db4  rep_threads  [last change: 2014/05/06 22:38:27]  OK

    06.db4  rep_backlog  [last change: 2014/05/06 22:38:27]  OK: Backlog is null

    07.db2  ping         [last change: 2014/05/06 22:38:27]  OK

    08.db2  mysql        [last change: 2014/05/06 22:38:27]  OK

    09.db2  rep_threads  [last change: 2014/05/06 22:38:27]  OK

    10.db2  rep_backlog  [last change: 2014/05/06 22:38:27]  OK: Backlog is null

    11.db3  ping         [last change: 2014/05/06 22:38:27]  OK

    12.db3  mysql        [last change: 2014/05/06 22:38:27]  OK

    13.db3  rep_threads  [last change: 2014/05/06 22:38:27]  OK

    14.db3  rep_backlog  [last change: 2014/05/06 22:38:27]  OK: Backlog is null

    15.db1  ping         [last change: 2014/05/06 22:38:27]  OK

    16.db1  mysql        [last change: 2014/05/06 22:38:27]  OK

    17.db1  rep_threads  [last change: 2014/05/06 22:38:27]  OK

    18.db1  rep_backlog  [last change: 2014/05/06 22:38:27]  OK: Backlog is null

     

    补充说明

    • 在本篇的演示案例中，前端程序若要与MySQL通信，则写库需连接192.168.0.11:3406，读库可连接192.168.0.12-15中的一个或多个，端口可能是3406或3506；

      在只读VIP漂移时，会导致前端程序连接的mysqld端口发生变化，所以生产环境下还是统一使用3306端口为宜；

      利用MMM实现了双主复制架构中的故障自动转移后，mysql并非直接与前端程序通信，还需配合使用读写分离器(如Ameoba)，以统一对外的连接地址，由读写分离器负责读写的向下分配；

    • 本博客作者：杨乐平   QQ：[e-mail]

    • 原文转自：http://www.it165.net/database/html/201405/6340.html  版权所属