3.Mysql主从复制

最新推荐文章于 2024-04-11 17:16:59 发布

Threeboys33

最新推荐文章于 2024-04-11 17:16:59 发布

阅读量434

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/q1193751379/article/details/113759452

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、Replication

这种是官网提供的一种把master节点binlog文件，复制到slave节点，通过在从节点重建的方式来达到指定格式的数据追平，默认为全部重建。master和slave根据server_id来区分各自的身份，slave节点配置master节点的主机名。

数据一致性依据：相同文件包含相同的标志位

1.1 Pos方式

1.1.1 master设置

以下两个参数需要校对，其中innodb_flush_log_at_trx_commit控制事物的ACID特性，有0/1/2这几个值，其中0标示1s刷新一次，2表示事物提交后，再刷新，为0/2的时候，有可能在数据库宕机的时候，事物被擦除，数据丢失。sync_binglog参数更新操作什么阶段记录到binlog文件中，当存储引擎为innodb时确保以下两个参数为1

innodb_flush_log_at_trx_commit=1

sync_binlog=1

主节点开启binlog的配置，并设置全局唯一的server_id，可在/etc/my.cnf中进行配置，重启生效

[mysqld]
basedir=/root/mysql/mysql-5.7.31-el7-x86_64
datadir=/opt/mysql/data #数据目录
socket=/var/lib/mysql/mysql.sock
symbolic-links=0 
secure_file_priv= #数据导入导出安全参数设置，为空不限制
log_bin=/opt/mysql/data/binlogs/host-bin #开启logbin，配置logbin的文件存放位置，以及前缀
server_id=1 #指定master的id标示
binlog_format=row # 指定master对格式为row的更新语句进行记录，默认值
binlog_row_image=full # 指定对更新的操作，记录按照所有字段，包含未发生改变的字段进行记录
expire_logs_days=7 # 过期时间，0为不限制，99为最大值
max_binlog_size=536870912 # 每个logbin文件的大小设置，最大为1G，设置为512m
sync_binlog=1 # 更新操作记录logbin时的效率选择，0为commit后更新，1记录具体的事物操作，N为多少个事物后提交到logbin
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log # mysql的运行日志
pid-file=/opt/mysql/data/mysqld_safe.pid

如上所示，slaves将会对master上/opt/mysql/data/binlogs文件夹下以host-bin为前缀的的logbin文件进行监听和复制。上述的server_id可以在会话中动态的设置，如下

SET GLOBAL server_id = 1;

1.1.2 创建同步账户

slave节点通过具有REPLICATION权限的账户密码连接到master。可以通过 CHAGE MASTER TO USER切换到指定slave。也可以给每个slave创建不通的账户。

mysql> CREATE USER 's1'@'%' IDENTIFIED BY 'abc123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 's1'@'%';

1.1.3 获取master的binlog

1）开启会话,执行如下命令

mysql > FLUSH TABLES WITH READ LOCK;

2）开启另一个会话，执行

mysql > SHOW MASTER STATUS;

1.1.4 binlog准备

对于innode引擎推荐使用mysqldump将整个数据进行全备份并将其复制到各个slave节点；如果是选取的是增量备份的话，可以将其基准文件和增量binlog文件复制到每个slave节点。相比mysqldump方式，因为跳过了insert语句的索引更新操作，所以效率较高，但其如果是inndob引擎的话，不是很推荐

1.4.1 mysqldump

# --master-data 将自动添加CHANGE MASTER TO语句
shell> mysqldump -pabc123 --all-databases --master-data > dbdump.db
# 主节点
mysql> UNLOCK TABLES;

1.4.2 Raw files

1）innodb 引擎

# 关机
shell> mysqladmin shutdown
# 打包
shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

2）非innodb引擎

# 不需要关机，需要获取全局可读锁
# 打包
shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata

# 释放全局锁
mysql> UNLOCK TABLES;

1.1.5 配置slave

slave需要通过TCP/IP连接master

元信息的存储通过master_info_repository=FILE参数来控制，当为FILE时，数据目录下会有master.info和relay-log.info两个文件，当为TABLE时，数据库中会存在master_slave_info这张表

# 1）安装依赖解压
rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
tar -zxvf mysql-5.7.31-el7-x86_64.tar.gz
mkdir mysql 
mv mysql-5.7.31-el7-x86_64 mysql/
ln -s /root/mysql/mysql-5.7.31-el7-x86_64 /usr/local/mysql

# 2）准备导入导出的文件目录
#secure_file_priv系统变量 导入导出准备参数设置
cd /root/mysql/mysql-5.7.31-el7-x86_64
mkdir -p mysql-files /opt/mysql/data
chown mysql:mysql mysql-files
chown mysql:mysql /opt/mysql -R
chmod 750 mysql-files

# 3) 配置slave节点
# slave节点
vim /etc/my.cnf
[mysqld]
basedir=/root/mysql/mysql-5.7.31-el7-x86_64
datadir=/opt/mysql/data #数据目录
server_id=2 #指定slave的id标示
user=mysql

# 3）初始化数据库配置
#bin/mysqld --initialize --datadir=/opt/mysql/data --basedir=/root/mysql/mysql-5.7.31-el7-x86_64 --user=mysql
bin/mysqld --defaults-file=/etc/my.cnf --initialize
vim ~/.bash_profile
export PATH=$PATH:/usr/local/mysql/bin
source ~/.bash_profile

# 4) 加入开启自启动
cp /root/mysql/mysql-5.7.31-el7-x86_64/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql

1.1.6 slave启动

1.1.6.1 master无数据启动

启动数据库

# 此参数通过-defaults-file=/etc/my.cnf提供
bin/mysqld_safe --defaults-file=/etc/my.cnf &

添加slave配置

mysql> CHANGE MASTER TO MASTER_HOST='10.1.74.215',
MASTER_USER='s1',
MASTER_PASSWORD='abc123',
MASTER_LOG_FILE='host-bin.000003',
MASTER_LOG_POS=591;

启动slave

start slave;
show slave status;
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

测试数据同步

# master节点
insert into db1.tb1 values(3),(4);
# slave节点
select * from db1.tb1;

1.1.6.2 master有数据启动

mysqldump方式

启动

# 1) 添加--skip-slave-start命令启动，slave后边手动启动
bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-slave-start &

通信

# 2) 和master进行通信
mysql> CHANGE MASTER TO MASTER_HOST='10.1.74.215',
MASTER_USER='s1',
MASTER_PASSWORD='abc123',
MASTER_LOG_FILE='host-bin.000003',
MASTER_LOG_POS=591;

导入启动

# 3）导入文件
mysql -pabc123 < dbdump.db
# 4) 启动slave
start slave;
show slave status;
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

测试

# 测试数据同步
# master节点
insert into db1.tb1 values(3),(4);
# slave节点
select * from db1.tb1;

raw files方式

# 1) 将raw files文件放置slave的数据文件夹下
# 2）解压数据文件
tar xvf dbdump.tar
# 3）添加--skip-slave-start命令启动slave
# 4）启动slave
mysql> START SLAVE;

1.1.7 添加slave至已有集群

1.1.7.1 安装

按照1.5步骤进行，需将server_id修改为3,并添加relay_log参数后续跟的格式为existing_replica_hostname-relay-bin,如下

[mysqld]
basedir=/root/mysql/mysql-5.7.31-el7-x86_64
datadir=/opt/mysql/data #数据目录
server_id=3 #指定slave的id标示
user=mysql
relay_log=iz3ic059y51h0ld5mb6zovz-relay-bin #iz3ic059y51h0ld5mb6zovz为slave1的参数

bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'abc123';
CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'abc123';
CREATE USER 'root'@'::1' IDENTIFIED BY 'abc123';
mysqladmin shutdown

1.1.7.2 同步

停止slave1

mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS\G
shell> mysqladmin shutdown

copy数据文件

需要将relay-logs、master.info、relay-info.info、binlogs以及数据库的表空间、redo日志、undo日志全部复制到新的slave节点

scp -r db1 binlogs master.info relay-log.info iz3ic059y51h0ld5mb6zovz-relay* root@10.1.74.212:/opt/mysql/data
chown mysql:mysql db1 binlogs master.info relay-log.info iz3ic059y51h0ld5mb6zovz-relay* -R

启动slave2

bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-slave-start &
show slave status
start slave;
# 启动slave1
bin/mysqld_safe --defaults-file=/etc/my.cnf &

测试同步

# 1) master上创建表，插入数据
create table db1.tb3(a int);
insert into db1.tb3 values(666);
# 2) slave1/2节点上查看数据
select * from db1.tb3;

1.2 GTID方式

1.2.1理解

在这里插入图片描述

1.2.2 启动

主从配置只读

等待所有的事务包含副本数据的追赶，提交或者回滚完成
```
SET @@GLOBAL.read_only = ON;
```
停止主从
```
mysqladmin -pabc shutdown
```

配置主从

gtid_mode=ON
enforce-gtid-consistency=ON

启动主从

# 主节点
mysqld_safe --defaults-file=/etc/my.cnf &
# 从节点,如果从节点关闭binlog需加参，--skip-log-bin,--log-slave-update=off,前提是使用GTID
mysqld_safe --defaults-file=/etc/my.cnf --skip-slave-start &

从连接主

change master to master_host='10.1.74.215',
master_port=3306,
master_user='s1',
master_password='abc123',
master_auto_position=1;

备份

因为同步模式更改后，之前的备份已经无法使用，需要重新备份

启动副本

# 从节点
start slave;
# 每个节点
SET @@GLOBAL.read_only = OFF;

1.2.3 水平扩展故障转移

1.2.3.1 数据集

mysqldump方式

mysqldump -A --master-data --set-gtid-purged=on > dbdump_gtid.sql

rawfiles方式

复制rawfiles至新的服务器，用mysqlbackup工具进行恢复
directory方式

目标服务器有以GTID方式的主从服务器配置，gtid_mode=ON。关闭master和目标服务器，拷贝整个master的数据目录至目标服务器。

1.2.3.2 历史数据

mysqlbinlog方式

 # 未测试
 mysqlbinlog --read-from-remote-server --read-from-remote-master

直接导入

shell> mysqlbinlog copied-binlog.000001 copied-binlog.000002 | mysql -u root -p

空事务注入

# 每个在主节点上执行过的事务,通过注入next环境变量后，数据会记录到gtid_executed变量中去
SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';
FLUSH LOGS;
PURGE BINARY LOGS TO 'source-bin.00000N';

通过gtid_purged变量注入

# 1) 查看master节点的gtid
show master status
# 2) 在新的服务器上执行,如果导入数据后 
# 通过show global variables like '%gtid%'查看参数gtid_purged和主库的没有差剧，可忽略set这个操作
# set是@@global.gtid_executed这个变量为空的
set GLOBAL gtid_purged=UUID:SEQ
# 3）配置通信
change master to master_host='10.1.74.215',master_user='s1',master_password='abc123',master_auto_position=1;

1.2.4 模式切换

主从模式切换

1.3 半同步

1.3.1 配置

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
       FROM INFORMATION_SCHEMA.PLUGINS
       WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
SET GLOBAL rpl_semi_sync_master_enabled = {0|1};
SET GLOBAL rpl_semi_sync_master_timeout = N;
SET GLOBAL rpl_semi_sync_slave_enabled = {0|1};


STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
Or from MySQL 8.0.22:
STOP REPLICA IO_THREAD;
START REPLICA IO_THREAD;

[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000 # 1 second
[mysqld]
rpl_semi_sync_slave_enabled=1

SHOW VARIABLES LIKE 'rpl_semi_sync%';
SHOW STATUS LIKE 'Rpl_semi_sync%';

1.3.2 理解

1) mysql slave 决定连接客户端的时候是否使用半同步
2）如果master开启半同步，且至少有一个slave也开启半同步。在slave收到事务事件发出ack之前，master一直处于阻塞状态
3）master将操作刷新binlog后，才会去手机slave的ack
4）如果master没有收到任何slave的ack，或者等待超时，将自动转化为异步模式，当slave刷新成功后，又会转化为半同步模式
5）master如果没有开启半同步，或者开启半同步，但是没有副本的开启的话，就会自动变为异步模式
6）可以通过参数`rpl_semi_sync_master_wait_for_slave_count`这个参数控制master端等待slave的响应个数
7）自动提交的事务，如果配置了半同步，master也会等待ack响应
8）`rpl_semi_sync_master_wait_point`
	 `AFTER_SYNC`:master操作更新到binlog后，等待slave返回ack再提交，最后响应客户端
	 `AFTER_COMMIT`:master操作更新到binlog后，先提交，等slave返回ack后，再响应客户端

1.4 切换source

参照官方方案

1.5 优化

修改复制逻辑架构

source1和source2开启binlog记录，slave从source2读binlog，有助于减小网络网络压力

在这里插入图片描述

relay_log和data分别至于不同的挂载路径，提升io性能
适当的增大rpl_read_size参数的大小，提升读取relay_log和bin_log的速度
将master的不同库表，按照路由规则--replicate-wild-do-table和--replicate-do-db分配给不同的slave
在slave节点上关闭log_slave_updates系统参数变量

1.6 监控row模式下的主从复制

查询语句

1.7 状态解读

Replica_IO_State 当前slave的状态
Replica_IO_Running slave的io线程状态
Replica_SQL_Running slave的sql线程状态
Last_IO_Error/Last_SQL_Error slave处理relaylog的错误记录
Seconds_Behind_Source slave 处理进度和master的对比情况，越小越好，0最好
Master_Log_file/Read_Master_Log_Pos io线程处理读取binlog的进度
Relay_Master_Log_File/Exec_Master_Log_Pos sql线程处理relaylog的进度

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: source1
                  Source_User: root
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000004
          Read_Source_Log_Pos: 931
               Relay_Log_File: replica1-relay-bin.000056
                Relay_Log_Pos: 950
        Relay_Source_Log_File: mysql-bin.000004
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              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_Source_Log_Pos: 931
              Relay_Log_Space: 1365
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids: 0

# 客户端的名字可以由--report-host参数设置
mysql> SHOW REPLICAS;
+-----------+----------+------+-------------------+-----------+
| Server_id | Host     | Port | Rpl_recovery_rank | Source_id |
+-----------+----------+------+-------------------+-----------+
|        10 | replica1 | 3306 |                 0 |         1 |
+-----------+----------+------+-------------------+-----------+
1 row in set (0.00 sec)

Threeboys33

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
3.Mysql主从复制

1、Replication这种是官网提供的一种把master节点binlog文件，复制到slave节点，通过在从节点重建的方式来达到指定格式的数据追平，默认为全部重建。master和slave根据server_id来区分各自的身份，slave节点配置master节点的主机名。数据一致性依据：相同文件包含相同的标志位1.1 Pos方式1.1.1 master设置以下两个参数需要校对，其中innodb_flush_log_at_trx_commit控制事物的ACID特性，有0/1/2这几个值，其
复制链接

扫一扫