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

2023-07-31 23:04:05 215

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

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

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

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

复制有多种拓扑形式：

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

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

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

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

一、复制如何操作

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

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

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

4、从主库中备份数据。

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

6、执行CHANGEMASTERTO命令。

7、开始复制。

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

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

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

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

createuser'用户名'@'%'identifiedby'密码';
grantreplicationslaveon*.*to'用户名'@'%';

3、在从库上设置server_id

#设置server_id
server_id=10

4、备份主库上的数据

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

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

mysql-uroot-p-f<主库备份文件.sql

6、在从库上执行CHANGEMASTERTO命令

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

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

CHANGEMASTERTOMASTER_LOG_FILE='mysql-bin.000016',MASTER_LOG_POS=47845;

7、从库上运行startslave，然后showslavestatus\G;查看复制状态;

三、设置主主复制

假设主库分别是master1和master2。

1、设置master2为只读

set@@global.read_only=on;

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

createuser'用户名'@'%'identifiedby'密码';
grantreplicationslaveon*.*to'用户名'@'%';

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

showmasterstatus;

4、根据第2步的信息，在master1上执行CHANGEMASTERTO命令

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

5、在master1上开启slave模式

startslave;

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，

stopslave;
setglobalmaster_info_repository='TABLE';
setglobalrelay_log_info_repository='TABLE';

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

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

7、在server3上，执行CHANGEMASTERTO命令，并命名通道名

CHANGEMASTERTO
MASTER_HOST='server1主机IP',
MASTER_USER='server1复制用户',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='server1二进制日志名称',
MASTER_LOG_POS=server1二进制日志位置FORCHANNEL'server1';

CHANGEMASTERTO
MASTER_HOST='server2主机IP',
MASTER_USER='server2复制用户',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='server2二进制日志名称',
MASTER_LOG_POS=server2二进制日志位置FORCHANNEL'server2';

8、在server3上，为每个通道执行STARTSLAVEFORCHANNEL语句

startslaveforchannel'server1';
startslaveforchannel'server2';

9、查看同步状态，showslavestatus\G;

要获取指定通道的从库状态，showslavestatusforchannel'通道名称'\G;

五、设置复制筛选器

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

1、复制指定数据库

CHANGEREPLICATIONFILTERREPLICATE_DO_DB=(db1,db2);

2、复制指定表

CHANGEREPLICATIONFILTERREPLICATE_DO_TABLE=('db1.table1');

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

CHANGEREPLICATIONFILTERREPLICATE_WILD_DO_TABLE=('db1.tb_%');

4、忽略数据库

CHANGEREPLICATIONFILTERREPLICATE_IGNORE_DB=(db1,db2);

5、忽略指定表

CHANGEREPLICATIONFILTERREPLICATE_IGNORE_TABLE=('db1.table1');

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

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

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

stopslave;
showslavestatus\G;

记录下Relay_Master_Log_File和Exec_Master_Log_Pos的值

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

stopslave;
showslavestatus\G;

记录下Relay_Master_Log_File和Exec_Master_Log_Pos的值

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

4、在服务器C上，使用STARTSLAVEUNTIL语句将其同步到服务器B的日志位置：

STARTSLAVEUNTILMASTER_LOG_FILE='上一步中服务器B日志名称',MASTER_LOG_POS=上一步中服务器B日志位置;

5、在服务器C上，检查showslavestatus中 Exec_Master_Log_Pos和 Until_Log_Pos两者应该相同。

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

showmasterstatus;
startslave;
showslavestatus\G;

7、在服务器C上，停止从库运行，执行CHANGEMASTERTO命令。

stopslave;
CHANGEMASTERTO
MASTER_HOST='服务器B的IP',
MASTER_USER='服务器B复制用户',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='上一步中通过showmasterstatus获取日志名称',
MASTER_LOG_POS=上一步中通过showmasterstatus获取日志位置;

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

startslave;
showslavestatus\G;

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

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

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

stopslave;
showmasterstatus\G;

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

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

stopslave;

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

showslavestatus\G;
startslave;

4、在服务器C上，停止从库运行，并执行CHANGEMASTERTO命令，指向服务器A

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

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

startslave;
showslavestatus\G;

八、设置延迟复制

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

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

1、停止从库运行

stopslave;

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

CHANGEMASTERTOMASTER_DELAY=3600;
startslave;

3、检查从库状态

showslavestatus\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、在从库上执行CHANGEMASTERTO命令来设置GTID复制

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

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

十、设置半同步复制

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

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

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

installpluginrpl_semi_sync_masterSONAME'semisync_master.so';

windows下请使用如下：

installpluginrpl_semi_sync_masterSONAME'semisync_master.dll';

2、确认插件已激活

selectplugin_name,plugin_statusfrominformation_schema.pluginswhereplugin_namelike'%semi%';

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

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

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

installpluginrpl_semi_sync_slaveSONAME'semisync_slave.so';

windows下请使用如下：

installpluginrpl_semi_sync_slaveSONAME'semisync_slave.dll';

5、确认插件已激活

selectplugin_name,plugin_statusfrominformation_schema.pluginswhereplugin_namelike'%semi%';

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

setglobalrpl_semi_sync_slave_enabld=1;
STOPSLAVEIO_THREAD;
STARTSLAVEIO_THREAD;

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

showstatuslike'rpl_semi_sync_master_clients';

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

showstatuslike'rpl_semi_sync_master_status';

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

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

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

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

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

热门推荐

随机推荐