• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    mysql 复制原理与实践应用详解

    本文实例讲述了mysql 复制原理与实践应用。分享给大家供大家参考,具体如下:

    复制功能是将一个mysql数据库上的数据复到一个或多个mysql从数据库上。

    复制的原理:在主服务器上执行的所有DDL和DML语句都会被记录到二进制日志中,这些日志由连接到它的从服务器获取,并复制到从库,并保存为中继日志,

    这个过程由一个称为 IO线程 的线程负责,还有一个称为 SQL线程 的则按顺序执行中继日志中的语句。

    复制有多种拓扑形式:

    1、传统复制,一主多从,一个主服务器多个从服务器。

    2、链式复制,一台服务器从主库复制,而另一台服务器又从这台复制,中间服务器又叫中继主库。

    3、主主复制,两个主库互相接受写入和复制。

    4、多源复制,一个从库,从多个主库复制。

    一、复制如何操作

    1、在主库上启用二进制日志记录。

    2、在主库上创建一个复制用户。

    3、在从训上设置唯一的 server_id。

    4、从主库中备份数据。

    5、在从库上恢复主库备份的数据。

    6、执行CHANGE MASTER TO命令。

    7、开始复制。

    二、具体的操作步骤如下:

    1、在 主库 上,启用二进制日志并设置server_id。

    #设置server_id
    server_id = 1
    #开启binlog日志
    log-bin = mysql-bin
    

    2、在主库上创建复制用户

    create user '用户名'@'%' identified by '密码';
    grant replication slave on *.* to '用户名'@'%';
    

    3、在从库上设置server_id

    #设置server_id
    server_id = 10
    

    4、备份主库上的数据

    mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --master-data > 导出路径
    

    5、在从库上恢复主库导出的数据

    mysql -u root -p -f  主库备份文件.sql
    

    6、在从库上执行 CHANGE MASTER TO 命令

    CHANGE MASTER TO 
    MASTER_HOST='主库IP',
    MASTER_USER='主库复制用户',
    MASTER_PASSWORD='密码',
    MASTER_LOG_FILE='二进制日志名称',
    MASTER_LOG_POS=二进制日志位置;
    

    二进制日志名称和二进制日志位置,已经在备份主库文件中包含了,类似如下所示:

    CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=47845;
    

    7、从库上运行 start slave,然后 show slave status\G; 查看复制状态;

    三、设置主主复制

    假设主库分别是master1和master2。

    1、设置master2为只读

    set @@global.read_only = on;
    

    2、在master2上创建复制用户,如果存在,则不用创建了

    create user '用户名'@'%' identified by '密码';
    grant replication slave on *.* to '用户名'@'%';

    3、确保master2已开启二进制日志,检查master2上的二进制日志的坐标

    show master status;
    

    4、根据第2步的信息,在master1上执行 CHANGE MASTER TO 命令

    CHANGE MASTER TO 
    MASTER_HOST='MASTER2主机IP',
    MASTER_USER='MASTER2复制用户',
    MASTER_PASSWORD='密码',
    MASTER_LOG_FILE='二进制日志名称',
    MASTER_LOG_POS=二进制日志位置;
    

    5、在master1上开启 slave 模式

    start slave;
    

    6、设置master2为可读写

    set @@global.read_only = off;
    

    四、设置多源复制

    设置 server3 为 server1 和 server2 的从库。

    1、设置 server1 和 server2 的二进制日志和server_id,具体操作可参考上面。

    2、在 server1 和 server2 上创建复制用户,具体操作可参考上面。

    3、在 server3 上设置 server_id。

    4、备份 server1 和 server2 的数据。

    5、在 server3 上恢复 server1 和 server2 上备份的数据。

    6、在 server3 上,将复制存储库从 FILE 改为 TABLE,

    stop slave;
    set global master_info_repository = 'TABLE';
    set global relay_log_info_repository = 'TABLE';

     还需要在配置文件中修改:

    [mysqld]
    master-info-repository = TABLE
    relay-log-info-repository = TABLE
    

    7、在 server3 上,执行 CHANGE MASTER TO 命令,并命名通道名

    CHANGE MASTER TO 
    MASTER_HOST='server1主机IP',
    MASTER_USER='server1复制用户',
    MASTER_PASSWORD='密码',
    MASTER_LOG_FILE='server1二进制日志名称',
    MASTER_LOG_POS=server1二进制日志位置 FOR CHANNEL 'server1';
    
    CHANGE MASTER TO 
    MASTER_HOST='server2主机IP',
    MASTER_USER='server2复制用户',
    MASTER_PASSWORD='密码',
    MASTER_LOG_FILE='server2二进制日志名称',
    MASTER_LOG_POS=server2二进制日志位置 FOR CHANNEL 'server2';
    

    8 、在 server3 上,为每个通道执行 START SLAVE FOR CHANNEL 语句

    start slave for channel 'server1';
    start slave for channel 'server2';
    

    9、查看同步状态,show slave status\G;

    要获取指定通道的从库状态,show slave status for channel '通道名称'\G;

    五、设置复制筛选器

    可以选择要复制哪些表或数据库,在主库上,可以使用--binlog-do-db 和 --binlog-ignore-db 选项来选择要记录变更的数据库,以控制二进制日志。更好的方法是控制从库。

    1、复制指定数据库

    CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);
    

    2、复制指定表

    CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = ('db1.table1');
    

    3、如果想使用通配符来选择表

    CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.tb_%');
    

    4、忽略数据库

    CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2);
    

    5、忽略指定表

    CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = ('db1.table1');
    

    六、将从库由主从复制切换到链式复制

    比如现在服务器A为主库,服务器B和服务器C为从库,复制于服务器A。现在想把服务器C作为服务器B的从库。

    1、在服务器C上停止从库运行

    stop slave;
    show slave status\G;

    记录下Relay_Master_Log_File和Exec_Master_Log_Pos的值

    2、在服务器B上停止从库运行

    stop slave;
    show slave status\G;
    

    记录下Relay_Master_Log_File和Exec_Master_Log_Pos的值

    3、将服务器B的日志位置与服务器C的进行比较,找出哪一个是服务器A最新同步,通常,服务器C先停止从库运行,服务器B的日志会更靠前。

    4、在服务器C上,使用 START SLAVE UNTIL 语句将其同步到服务器B的日志位置:

    START SLAVE UNTIL MASTER_LOG_FILE='上一步中服务器B日志名称', MASTER_LOG_POS=上一步中服务器B日志位置;
    

    5、在服务器C上,检查 show slave status 中 Exec_Master_Log_Pos 和 Until_Log_Pos 两者应该相同。

    6、在服务器B上,查看主库状态,启动从库。

    show master status;
    start slave;
    show slave status\G;
    

    7、在服务器C上,停止从库运行,执行 CHANGE MASTER TO 命令。

    stop slave;
    CHANGE MASTER TO
    MASTER_HOST='服务器B的IP',
    MASTER_USER='服务器B复制用户',
    MASTER_PASSWORD='密码',
    MASTER_LOG_FILE='上一步中通过show master status获取日志名称',
    MASTER_LOG_POS=上一步中通过show master status获取日志位置;
    

    8、在服务器C上,启动复制并查看状态

    start slave;
    show slave status\G;
    

    七、将链式复制切换到主从复制

    服务器A->服务器B->服务器C,如果想让服务器C直接作为服务器A的从库,该怎么做?

    1、在服务器B上,停止从库运行,并记录主库状态

    stop slave;
    show master status\G;
    

    2、服务器C上,确保从库的延迟已被追上,Relay_Master_Log_File和Exec_Master_Log_Pos应该等于服务器B上主库状态。

    一旦延迟被追上,就停止从库的运行。

    stop slave;
    

    3、在服务器B上,从 show slave status 中获取服务器A的日志坐标值(Relay_Master_Log_File和Exec_Master_Log_Pos),并启动从库

    show slave status\G;
    start slave;
    

    4、在服务器C上,停止从库运行,并执行 CHANGE MASTER TO 命令,指向服务器A

    stop slave;
    CHANGE MASTER TO 
    MASTER_HOST='服务器A的IP',
    MASTER_USER='服务器A的复制用户',
    MASTER_PASSWORD='密码',
    MASTER_LOG_FILE='上一步中获取的日志',
    MASTER_LOG_POS=上一步中获取的日志位置;
    

    5、在服务器C上,开启从库,并查看状态。

    start slave;
    show slave status\G;
    

    八、设置延迟复制

    为什么需要延迟复制,有可能主库上执行了一条灾难性语句,你必须通过备份中的时间点恢复,如果数据库大小过大,这将导致长时间停机。

    为了避免出现这种情况,可以使用一个延迟的从库,如果发生了灾难,并且延迟的从库还没有执行这条灾难性语句,则可以先停止复制,让从库跳过该灾难语句,最后把从库提升为主库。

    1、停止从库运行

    stop slave;
    

    2、设置延迟时间,以秒为单位

    CHANGE MASTER TO MASTER_DELAY = 3600;
    start slave;
    

    3、检查从库状态

    show slave status\G;

    SQL_Delay: 从库延迟于主库的秒数。

    SQL_Remaining_Delay:延迟还剩余的秒数,当保持延迟时,这个值是NULL。

    Slave_SQL_Running_State:SQL线程的状态

    九、设置 GTID 复制

    全局事务标识符 GTID 是在程序中创建的唯一标识符,并与主库上提交的每个事务相关联。该标识符是唯一的,不仅在主库上,在其他从库上,它都唯一。

    上面描述的所有复制,都需要指明二进制文件和复制起点的位置,如果将一个从库的主库切换到另一个,就必须重新获取二进制文件位置,这会很麻烦。

    为了避免,可以使用基于 GTID 的复制,mysql 使用 GTID 自动检测二进制日志的位置。

    1、在所有数据库中 my.cnf 中启动 GTID

    [mysqld]
    gtid_mode = ON
    enforce-gtid-consistency = 1
    skip_slave_start
    

    2、将主库设置为只读,确保主库与从库数据一致。

    set @@global.read_only = on;
    

    3、重新启动所有从库,使 GTID 生效。

    4、重新启动主库。

    5、在从库上执行 CHANGE MASTER TO 命令来设置 GTID 复制

    CHANGE MASTER TO
    MASTER_HOST='主库IP',
    MASTER_PORT=3306,
    MASTER_USER='复制用户',
    MASTER_PASSWORD='密码',
    MASTER_AUTO_POSITION=1;
    

    6、在所有从库上执行 start slave; 并查看状态。

    十、设置半同步复制

    默认情况下,复制是异步的,主库不知道写入操作是否到达从库,如果主库与从库间存在延迟,主库崩了,尚未到达从库的那些数据就会丢失。

    为了解决这种问题,半同步复制,主库会一直等待,直到至少有一个从库接收到写入的数据。

    1、在主库上,安装 rpl_semi_sync_master 插件

    install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
    

    windows下请使用如下:

    install plugin rpl_semi_sync_master SONAME 'semisync_master.dll';
    

    2、确认插件已激活

    select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
    

    3、开启半同步复制并调整超时时间

    set @@global.rpl_semi_sync_master_enabled=1;
    set @@global.rpl_semi_sync_master_timeout=100;
    

    4、在从库上,安装 rpl_semi_sync_slave 插件

    install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
    

    windows下请使用如下:

    install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll';
    

    5、确认插件已激活

    select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
    

    6、在从库上,启用半同步复制,并重新启动从库IO线程

    set global rpl_semi_sync_slave_enabld = 1;
    STOP SLAVE IO_THREAD;
    START SLAVE IO_THREAD;
    

    7、通过如下方式查看半同步状态

    show status like 'rpl_semi_sync_master_clients';

    查看以半同步连接到主库的客户端数量

    show status like 'rpl_semi_sync_master_status';

    主库在异步和半同步复制之间切换,on表示半同步,off表示异步。

    更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

    希望本文所述对大家MySQL数据库计有所帮助。

    您可能感兴趣的文章:
    • MySQL主从复制的原理及配置方法(比较详细)
    • mysql中复制表结构的方法小结
    • MySQL复制表结构和内容到另一张表中的SQL语句
    • MySQL快速复制数据库数据表的方法
    • Mysql主从复制(master-slave)实际操作案例
    • mysql主从同步复制错误解决一例
    • mysql跨数据库复制表(在同一IP地址中)示例
    • mysql同步复制搭建方法指南详细步骤
    • 简单讲解MySQL的数据库复制方法
    • 详解MySQL双活同步复制四种解决方案
    • MySQL 数据库双向镜像、循环镜像(复制)
    上一篇:mysql 数据库备份的多种实现方式总结
    下一篇:mysql 悲观锁与乐观锁的理解及应用分析
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    mysql 复制原理与实践应用详解 mysql,复制,原理,与,实践,