美文网首页
Mysql 主从库搭建

Mysql 主从库搭建

作者: Js_Gavin | 来源:发表于2021-12-17 16:12 被阅读0次

    注意：确保主从库上安装了相同版本的数据库，因为主从库的角色可能会互换，同时减少出错的概率，所以在可能的情况下推荐安装最新的稳定版，本文使用版本为5.7.36

    搭建主库

    1、创建主库复制使用的账户

    create user slavauser@'ip' identified by 'password';

grant replication slave on *.* to slavauser@'ip';

    2、查看权限是否正确

    show grants for slavauser@'ip';
    image.png

    3、修改主数据库配置文件my.cnf，开启binlog，并设置server-id，这两个参数修改需要重启数据库服务

    [mysqld]
server-id=1
log-bin=/var/log/mysql/localhost-binlog

#需要同步的表，不配置默认同步所有数据库
binlog-do-db=db_a
binlog-do-db=db_b
binlog-do-db=db_c
......

    4、将主数据库的数据备份到从数据库，建议设置读锁有效，确保没有数据库写操作，以便获得一致性的快照

    flush tables with read lock

    主数据库备份完成后，可以恢复写操作

    unlock tables

    5、查询主库上当前二进制日志名和偏移量值，目的是为了在从数据库启动以后，从这个点开始进行数据的恢复

    show master status
    image.png

    6、启动或重启主数据库服务

    搭建从库

    1、修改my.cnf，增加server-id参数，注意值必须是唯一的，不能和主数据库的server-id相同：

    [mysqld]
server-id=2

#指定同步的数据库，如果不配置，默认同步所有数据库
replicate-do-db=sql_inform

skip-slave-start

    在从库上，使用--skip-slave-start选项启动从数据库，也可以在my.cnf中加入skip-slave-start配置，这样不会立即启动从数据库服务器上的复制进行，方便做进一步配置。

    2、对从数据库服务器做相应设置
    change master to
    master_host = '主数据库服务器IP',
    master_port = 数据库端口,
    master_user = '主数据复制用户',
    master_password = '主数据复制用户密码',
    master_log_file = '主数据当前二进制日志名',
    master_log_pos = 主数据当前二进制日志偏移量值;
    示列：

    change master to 
master_host = '192.168.1.102',
master_port = 9802,
master_user = 'slavauser',
master_password = '123456',
master_log_file = 'localhost-binlog.000009',
master_log_pos = 2329;

    3、在从库上，启动slave线程

    start slave

    这时在从库上执行show processlist命令将显示类似如下进程：

    image.png

    这表明slave已经连上master，并开始接受并执行日志。也可以在从库上执行 show slave status 命令，查看两个重要字段是否都为yes

    image.png

    还可以在主数据库上执行一个更新操作，观察是否同步到从数据库。

    如果以上操作之一没有成功，请查看从数据库上的错误日志，进行排查。

    多线程复制(扩展)

    mysql5.7，带来了全新的多线程复制技术，解决了master同一个schema下的数据发生了变更，从库不能并发应用的问题。

    1、在从库my.cnf上追加配置以下参数

    [mysqld]
#多线程复制（从库）
#复制依赖主库commit时刻的时间戳
slave_parallel_type=LOGICAL_CLOCK
#执行线程数量
slave_parallel_workers=4
#在复制过程中，保证和主库事务提交顺序的一致性
slave_preserve_commit_order=ON
relay_log_recovery=ON
master_info_repository=table
relay_log_info_repository=table

    增强半同步复制(扩展)

    前面介绍的复制是异步操作，主库和从库的数据之间难免会存在一定的延迟，这样存在一个隐患：当在主库中写入一个事务并提交，而从库未得到主库的binlog日志时，主库由于磁盘损坏，内存故障，断点等原因意外宕机，导致主库上该事务binlog丢失，此时从库就会损失这个事务，从而造成主从不一致。

    在传统的半同步复制中，主库写数据到BINLOG，且执行Commit操作后，会一直等待从库的ACK，即从库写入RelayLog(中继日志)后，并将数据落盘，返回给主库消息，通知主库可以返回前端应用操作成功，这样会出现一个问题，就是实际上主库已经将该事务Commit到了事务引擎层，应用已经可以看到数据发生了变化，只是在等返回而已，如果此时主库宕机，有可能从库还没能写入RelayLog,就会发生主从库数据不一致。

    增强半同步就是为了解决这个问题，做了微调，即主库写数据到BINLOG后，就开始等待从库的应答ACK，直到至少一个从库写入RelayLog后，并将数据落盘，然后返回给主库消息，通知主库可以执行Commit操作，然后主库开始提交到事务引擎层，应用此时可以看到数据发生了变化。增强半同步复制的大致流程如图30-9所示。

    image.png

    半同步复制模式下，假如在传送 BINLOG日志到从库时，从库宕机或者网络故障，导致BINLOG并没有及时地传送到从库上，此时主库上的事务会等待一段时间(时间长短由参数rpl_semi_sync_master_timeout设置的毫秒数决定)如果 BINLOG在这段时间内都无法成功发送到从库上，则MySQL自动调整复制为异步模式，事务正常返回提交结果给客户端。

    半同步复制很大程度上取决于主从库之间的网络情况，往返时延RTT越小决定了从库的实时性越好。通俗地说，主从库之间网络越快，从库越实时。

    注意:往返时延RTT(Round-TripTime)在计算机网络中是一个重要的性能指标，它表示从发送端发送数据开始到发送端接收到接收端的确认，总共经历的时长。

    1、在主库上安装semisync_master.so插件

    install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

    2、在从库上安装 semisync_slave.so插件

    install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

    查看安装的插件

    select * from mysql.plugin;
    image.png

    3、在主库my.cnf中配置参数打开半同步，默认是不打开的

    [mysqld]
#半同步配置（主库）
rpl_semi_sync_master_enabled=on
#主库上的事务等待从库响应时间
rpl_semi_sync_master_timeout=1000
#增强半同步
rpl_semi_sync_master_wait_point=AFTER_SYNC

    4、在从库my.cnf中配置参数，打开半同步

    [mysqld]
#打开半同步(从库)
rpl_semi_sync_slave_enabled=ON

    5、由于之前的配置，需要重启从库I/O线程(如果是全新配置的半同步复制，则不需要)

    stop slave
start slave

    到此半同步配置完成。主库上通过show status like '%semi_sync%'命令能够看到当前半同步复制的一些状态

    image.png

    着重关注以下3个状态值：
    Rpl_semi_sync_master_status：值为ON，表示半同步复制目前处于打开状态。

    Rpl_semi_sync_master_yest：值为6表示主库当前有6个事务是通过半同步复制到从库。

    Rpl_semi_sync_master_no：值为1表示在半同步模式下，从库没有及时响应的事务为1个。

    命令集合：

    show master status;
show processlist;
start slave;
stop slave;
reset slave all;  //重置从库连接主库所有配置内容

change master to 
master_host = '192.168.1.102',
master_port = 9802,
master_user = 'repl',
master_password = 'Re123456..',
master_log_file = 'localhost-binlog.000007',
master_log_pos = 5665;

select @@have_dynamic_loading; // 查看是否支持动态增加插件
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
show status like '%semi_sync%'

    主库my.cnf配置文件参考

    [mysqld]
server-id=1
#端口
port=9802
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
pid-file=/usr/local/mysql/mysql.pid
symbolic-links=0
log_timestamps=SYSTEM

#需要同步的表
binlog-do-db=sql_inform
binlog-do-db=test

#半同步配置（主库）
rpl_semi_sync_master_enabled=on
rpl_semi_sync_master_timeout=1000
#增强半同步
rpl_semi_sync_master_wait_point=AFTER_SYNC

#启动日志，错误日志
log-error=/var/log/mysql/localhost-error.log

#慢日志
slow_query_log=ON
slow_query_log_file=/var/log/mysql/localhost-slow.log
#超时时间
long_query_time=5

#二进制日志
log-bin=/var/log/mysql/localhost-binlog
log_bin_index=/var/log/mysql/localhost-binlog.index
#二进制日志保留天数
expire_logs_days=7

#查询日志
general_log=0
general_log_file=/var/log/mysql/localhost-query.log

#密码强度
plugin-load-add=validate_password.so
validate_password_length=8
validate_password_policy=MEDIUM

#模式
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.dlog
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/var/log/mysql/localhost-error.log
pid-file=/usr/local/mysql/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

    从库my.cnf配置文件参考

    [mysqld]
server-id=3
port=9803
datadir=/usr/local/mysql9803/data
socket=/usr/local/mysql9803/mysql.sock
pid-file=/usr/local/mysql9803/mysql.pid
log_timestamps=SYSTEM

#多线程复制（从库）
slave_parallel_type=LOGICAL_CLOCK  #复制依赖主库commit时刻的时间戳
slave_parallel_workers=4   #执行线程数量
slave_preserve_commit_order=ON  #在复制过程中，保证和主库事务提交顺序的一致性
relay_log_recovery=ON
master_info_repository=table
relay_log_info_repository=table

#打开半同步(从库)
rpl_semi_sync_slave_enabled=ON

#error日志
log-error=/var/log/mysql9803/localhost-error.log

#慢日志
slow_query_log=ON
slow_query_log_file=/var/log/mysql9803/localhost-slow.log
#超时时间
long_query_time=5

#二进制日志
log-bin=/var/log/mysql9803/localhost-binlog
log_bin_index=/var/log/mysql9803/localhost-binlog.index
#二进制日志保留天数
expire_logs_days=7

#中继日志
relay_log=/var/log/mysql9803/localhost-relay-bin
log_slave_updates=on

#查询日志
general_log=0
general_log_file=/var/log/mysql9803/localhost-query.log

#密码强度
#plugin-load-add=validate_password.so
#validate_password_length=8
#validate_password_policy=MEDIUM

#模式
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.dlog
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/var/log/mysql9803/localhost-error.log
pid-file=/usr/local/mysql9803/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

    相关文章

      网友评论

          本文标题：Mysql 主从库搭建

          本文链接：https://www.haomeiwen.com/subject/zysifrtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|Mysql 主从库搭建|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！