mysql5.7 创建简单的复制关系

天道酬勤-明天会更好

已于 2022-05-16 22:26:39 修改

阅读量1.2k

点赞数

文章标签： mysql

于 2022-03-26 16:04:32 首次发布

本文链接：https://blog.csdn.net/u010648194/article/details/123757327

版权

复制流程

mysql的commit分为3个阶段：

A) prepare阶段 B)写二进制日志 C)最后commit

A和C都是在innoDB层，B是在mysql层。三个步骤都是通过group commit提交的，所以性能比较好。

复制流程：当一个事务写入二进制日志后，mysql dump 线程就会把二进制日志发送给从机，从机中的I/O线程会接收二进制日志，并写入到relay log日志中继日志中，从机sql线程就会把relay log中的日志进行回放（数据写入到从机）。从mysql5.6后可以有多个回放线程，并行执行。

实战操作准备工作：

一台主机安装了两台mysql实例， 3307端口用-S /tmp/mysql.sock1连接，3308端口用-S /tmp/mysql.sock2连接。 3307为主机，3308为复制机。

一、主机上备份数据库

 mysqldump --single-transaction --master-data=1 -A -S /tmp/mysql.sock1 > fullbackup.sql

二、在从机上面恢复数据

mysql -S /tmp/mysql.sock2 -u root -p123456 < fullbackup.sql

把数据导入到3308的mysql中

三、主机上面创建复制的用户并加权限

create user rpl@'%' identified by '123456'; ##创建一个rpl复制用户
grant replication slave on *.* to rpl@'%';  ##授权复制权限给rpl用户

四、从机 change master to

① 从主机备份的文件fullbackup.sql文件中找到二进制日志名称和pos偏移量

vim fullbackup.sql

位置一般都在开头，记下master_log_file名称bin.000001 和 log_pos=154

② 在从机进行change master to

change master to master_log_file='bin.000001',master_log_pos=154,master_host='127.0.0.1',master_port=3307,master_user='rpl', master_password='123456';

master_log_file: 就是上面主机的master_log_file二进制文件

master_log_pos: 也是主机的pos

master_host: 主机的ip

master_port: 主机的端口

master_user: 主机的用户名

master_password: 主机的密码

③ show slave status\G 查看状态，下面就表示绑定成功了

④ 启动

start slave;

上面的yes表示成功，然后在主机上面进行操作后，从机可以看看有没有数据更新

⑤ show slave status; 的参数说明

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event ##IO线程的状态
                  Master_Host: 127.0.0.1  ##主机的ip
                  Master_User: rpl        ##主机的用户
                  Master_Port: 3307       ##主机的端口号
                Connect_Retry: 60         ##连接不上，重试多少次
              Master_Log_File: bin.000001 ##同步主机的二进制文件
          Read_Master_Log_Pos: 1534       ##同步主机的pos位置是多少 IO线程读取的位置
               Relay_Log_File: iZ2zedy98vjvmjmejqf2ogZ-relay-bin.000002 ##中介二进制文件名
                Relay_Log_Pos: 1694       ##中介pos位置
        Relay_Master_Log_File: bin.000001 ##中介文件对应master主机的文件名
             Slave_IO_Running: Yes        ##slave io线程的状态
            Slave_SQL_Running: Yes        ##slave sql线程的状态
              Replicate_Do_DB:       
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0         ##最近一次的错误日志编号
                   Last_Error:           ##最近一次的错误信息
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1534      ##表示当前sql线程执行的位置
              Relay_Log_Space: 1919      ##中介日志的大小
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0        ##从机落后主机的秒数
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 12
                  Master_UUID: dcd2ddb4-9877-11ec-98bb-00163e350e12
             Master_Info_File: /mdata/data2/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

⑥ show slave hosts;

在主机查看，可以看到连接的从机信息

五、高可用的配置

① master 主机配置 mysql5.7都是默认的

在搭建主从复制时，最好是检查一遍。

binlog-do-db = 需要复制的数据库名 ##一般不用设置

binlog-ignore-db = 不需要复制的数据库名##一般不用设置

max_binlog_size = 1024M ##二进制日志最大的值 5.7版本默认是1G ##一般不用设置

binlog_format = ROW ## 二进制日志的格式需要设置row，意思是事务的操作记录的日志都是以行来记录的，例如删除 a<10的， row记录的话则是记录 1-9每行都删除一次

transaction-isolation = READ-COMMITTED; ##事务设置RC级别，其实设置RR级别也可以，具体看项目需求

expire_logs_days = 0; 二进制日志自动删除/过期的天数最好还是设置0

server-id = 1; 表示数据库实例的id标识，跟从机的不要设置一样

binlog_cache_size = 3K; ##二进制内存缓冲大小 ##使用默认值就行了

sync_binlog = 1; ## 必须设置=1 表示事务commit后二进制日志立马刷新到磁盘，不使用操作系统的缓存。

innodb_flush_log_at_trx_commit = 1; ##日志在每次事务提交时写入并刷新到磁盘默认=1

innodb_support_xa = 1; 多线程并发执行提交事务，按照事务的先后顺序写入binlog,如果关闭则binlog记录事务的顺序可能与实际不符，造成slave不一致

② slave 从机配置参数

log_slave_updates = 1; 如果log_slave_updates=0时，从机根据主机的二进制文件改变后，从机不会写二进制文件，log_slave_updates=1，则表示从机在slave 状态下的更新数据也会写二进制文件。强烈建议设置=1

relay_log_recovery = 1; 在从库中将relay_log_recovery设置为on，如果从库发送宕机，从库会自动放弃所有未执行的relay log，重新生成一个relay log，并将从库的io线程的position重新指向新的relay log。并将sql线程的position退回到跟io线程的position保持一致，重新开始同步，这样在从库中事务不会丢失。这个参数建议开启

relay_log_info_repository= table; ## 默认值file, slave sql线程执行N个event，就会把二进制日志写入 relay-info.log文件里面，表示当前我们的master执行到哪里了。设置=table则表示日志写入到表中，mysql库中slave_relay_log_info表就是存储relay-info.log的表。

sync_relay_log_info变量就是设置N的参数，默认是1000。

设置table的好处是：sql线程执行event时和slave_relay_log_info使用事务执行，要么成功，要么不成功。这样就更好的确保event执行的位置和我们的 relay-info.log日志存储是一致的

read_only = 1; 从机只能读，不能写，防止主从不一致

replicate-do-db: #从机要slave控制的数据库名一般不用设置

replicate-ignore-db: #从机不要slave控制的数据库名一般不用设置

replicate-do-table: #从机要slave控制的表名一般不用设置

replicate-ignore-table:#从机不要slave控制的表名一般不用设置

server-id = 2； ##表示数据库实例的id标识，跟主机的不要设置一样

slave_parallel_workers = 4; ##从机回访event的线程数

slave_parallel_type = logical_clock; ## slave_parallel_workers设置>1后，并发的模式

5.7版本以前值 = DATABASE; 表示为每一个数据库分配一个线程

5.7版本可以设置 = logical_clock; 逻辑时钟的参数；这个参数主要是解决主从延迟，并行复制，原理是通过group commit（组提交）的方式实现，如果多个事务是在一组里面的，就可以放到不同的线程去执行，从而达到并行处理。一个组提交的事务都是可以并行回放（配合binary log group commit）

5.7.22 新的并行复制策略 WRITESET

新增一个新的参数控制 binlog-transaction-dependency-tracking

A) COMMIT_ORDER，根据事务同时进入prepare和commit来判断是否可以并行的策略。

B) WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的hash值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的writeset没有交集，就可以并行。

C) WRITESET_SESSION，是在WRITESET的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

## 这个值是有主从不一致，有sql error后解决用的

###sql_slave_skip_counter = N; N指的是跳过前一次正常运行的事务之后的N个event

③ msyql 5.7版本的my.cnf配置

## replication 复制参数
binlog_format = row
transaction_isolation = read-committed
sync_binlog = 1
relay_log_recovery = 1
relay_log_info_repository = TABLE
master_info_repository = table
log_slave_updates = 1

因为上面有很多都是默认对的，所以就只需要配置上面的参数.，在主从都可以配置一样的，达到对称。

注意：必须把上面的配置到mysql后重启再搭建主从，这样的主从复制才能高可用。

如果主从复制中，遇到一些从机主键冲突之类的错误，可以直接跳过一个事务，

mysql>stop slave ;

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳过一个事务

mysql>start slave ;

六、 GTID 配置

GTID即全局事务ID (global transaction identifier),

其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的事务ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。

mysql主从结构在一主一从情况下对于GTID来说就没有优势了，

而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。

使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。

① mysql5.7.6支持在线开启

mysql提供了两个额外选项off_permissive和on_permissive

off | off_permissive | on_permissive | on

上面四个状态变更必须是按照顺序变更，如不允许gtid_mode=off，直接变更为on_permissive;

当设置为off_permissive,不产生GTID事务, Slave接受不带GTID的事务(匿名事务)也接受带GTID的事务

当设置为on_permissive时，新事务为gtid事务，slave接受GTID事务也接受不带GTID事务

gtid值master 与slave 兼容性列表

A) 在每一台服务器上执行。err log没有任何warning产生，这是非常重要的一步，确保没有error log产生继续step 2；主要验证是否可以开启gtid，如create table select* from table 不支持事件

set @@global.enforce_gtid_consistency=warn;

B) 在每一台server上执行

set @@global.enforce_gtid_consistency=on;

C) 每一台server 执行，在那一台服务器执行没有先后之分

set @@global.gtid_mode=off_permissive;

D) 每一台server 执行,执行顺序没有先后之分，要保证下一步操作之前，上面的操作都已在所有server上执行过

set @@global.gtid_mode=on_permissive;

E) 保证每一台ongoing_anonymous_transaction_count状态值为零

show status like 'ongoing_anonymous_transaction_count';

F)、等待步骤E)生成的所有事务复制到所有服务器，此时不需要停止服务器更新，要保证所有的匿名事务都已经复制

G)、步骤F)完成，基本上可以进行步骤H).（此处没有考虑备份和restore情况）

H)、设置 gtid_mode=on

set @@global.gtid_mode=on;

② 持久化

在mysqld配置

log_bin = ON
gtid_mode = ON
log_slave_updates = ON
enforce_gtid_consistency = ON

③ 如果主机宕机了，从机1切换到主机，从机2还是从机

则需要从机2执行 change master to master_host = '127.0.0.1',MASTER_AUTO_POSITION=1;

MASTER_AUTO_POSITION：
该参数在mysql5.6.5版本引入，如果进行change master to时使用MASTER_AUTO_POSITION = 1，slave连接master将使用基于GTID的复制协议。
使用基于GTID协议的复制，slave会告诉master它已经接收到或执行了哪些事务。计算这个集，slave需要读取全局参数gtid_executed以及通过show slave status获取的参数Retrieved_gtid_set。

③ 主从数据少量的不一致，可以用gtid_next把冲突的行过滤掉

例如A是主机 B是备机， A和B里面的test表 id = 1冲突了，现在同步有问题，则可以在B库执行

set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10';
begin;
commit;

set gtid_next=automatic;
start slave;

上面的gtid_next是A主机的冲突的gtid,建立一个空的事务，表示冲突的gtid已经执行了。

上面解决冲突了，一定要执行set gtid_next=automatic; 不然会出错，表示下一个gtid又是自动生成了。

④ mysql 上面一定要谨慎使用

RESET MASTER;
RESET SLAVE;

因为RESET MASTER; 会删除master主机的二进制日志

reset slave会删除slave主机的二进制日志；

天道酬勤-明天会更好

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql5.7 创建简单的复制关系

准备工作：一台主机安装了两台mysql实例， 3307端口用-S /tmp/mysql.sock1连接，3308端口用-S /tmp/mysql.sock2连接。 3307为主机，3308为复制机。一、主机上备份数据库mysqldump --single-transaction --master-data=1 -A -S /tmp/mysql.sock1 > fullbackup.sql二、在从机上面恢复数据mysql -S /tmp/mysql.sock2 -u.
复制链接

扫一扫