MySQL：MySQL复制

最新推荐文章于 2023-06-06 08:56:43 发布

Hskds

最新推荐文章于 2023-06-06 08:56:43 发布

阅读量840

点赞数

分类专栏： mysql 文章标签： mysql复制

本文链接：https://blog.csdn.net/wdirdo/article/details/101374015

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

MySQL：（十三）MySQL复制

（一）关系型数据库基础

（二）MySQL安装

（三）管理数据库和表

（四）用户和权限管理

（五）函数，存储过程和触发器

（六）MySQL架构

（七）存储引擎

（八）MySQL服务器选项，系统和状态变量

（九）优化查询和索引管理

（十）锁和事务管理

（十一）日志管理

（十二）备份还原

（十三）MySQL集群

壹.MySQL复制

为什么要mysql复制

1.对于系统架构的性能而言，mysql容易成为其性能瓶颈，其中提升系统性能的方法有两种：Scale Up ，Scale Out

Scale Up：（向上扩展）使用性能更好的服务器，扩展一个点的能力支撑更大的请求。==>不可取
Scale Out：（向外扩展）为达到性能使用更多的节点。简单来说一台不行，就两台。
一般采用向外扩展的方式以提升性能，但是这样将产生新的问题：以两个节点的mysql为例，问题①数据如何同步？②用户访问哪个主机？

解决上述问题的办法：前端设计一个调度器实现。复制将用户的请求分配给不同的节点。这样导致又一个新问题：调度器的单点故障。。。

2.先解决调度问题：

调度器：负责接收用户请求，实现读写分离
用户的写操作：主服务器处理用户的增、改、删操作（INSERT、UPDATE、DELETE），即调度器将对数据库的增删改发送给主服务器。

用户的读操作，调度器均分发给slave主机，但是如果一个用户刚执行完insert操作，立马查询刚才插入的记录，由于读写分离的，将必然涉及到主从同步问题。

2.MySQL的主从复制

每个节点都有相同的数据集
向外扩展
二进制日志 master --> slave是基于二进制日志的
单向：master --> slave
slave应该设为只读

3.复制的功用

数据分布
负载均衡读
备份
高可用和故障切换
MySQL升级测试

数据库的拆分，减轻master服务负载

1.数据库拆分

当master服务器的压力过大时，可以考虑将数据库进行拆分
将数据进行查分即将数据分别存放于不同的服务器
拆分的对象是同一数据库中的多张表
一般一个服务器只创建一个数据库，不同数据库应放于不同的服务器

2.MySQL垂直分区

各类数据的表之间若是没有关联的数据，即不会涉及查询的join时，可考虑垂直拆分
从业务上来说，各个表之间本身没有任何关系

3.MySQL水平分片（Sharding）

各表之间有联系，有时也会因为一些原因将其拆分，比如按地区拆分，或者按照用户的级别拆分等等
水平拆分带来的问题：需要对用户进行判断，哪个地区？什么级别之类的判断。

4.对应shard中查询相关数据

引入一个分片服务，保存分类类型与对应服务器的信息

主从复制原理

1.主从复制原理※※※※※

主从复制是基于二进制日志实现的，因此要求主服务器必须启用二进制日志
从节点对二进制日志的启用，没有要求

基于二进制日志如何实现主从复制：

master服务器：启动一个Dump线程，Dump线程负责将二进制日日志中生成新的二进制日志发送给slave服务器
slave服务器：开启io Thread的线程负责从master传来的二进制日志，然后专门生成一个从master接收的二进制日志的日志relay log（中继日志）。最后开启SQL thread线程将中继日志记录的SQL语句进行执行，来更新slave数据库。

MySQL复制的实现

1.主从复制线程：

主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放

2.跟复制功能相关的文件：

master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系

3.主从复制特点：

异步复制|同步复制|半同步复制
主从数据不一致比较常见

4.复制架构：

Master/Slave, Master/Master, 环状复制
一主多从
从服务器还可以再有从服务器
一从多主：适用于多个不同数据库

5.复制需要考虑二进制日志事件记录格式

STATEMENT（5.0之前）
ROW（5.1之后，推荐）
MIXED

6.MySQL复制模型

基本复制模型
一主一从
一主多从

7.主从配置过程：参看官网

https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

8.主节点配置：※※※※※

(1) 启用二进制日志
[mysqld]
log_bin
(2) 为当前节点设置一个全局惟一的ID号 [mysqld]
server_id=#
log-basename=master 可选项，设置datadir中日志名称，确保不依赖主机名
(3) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON *.* TO ‘repluser’@‘HOST’ IDENTIFIED BY ‘password’;

9.从节点配置：※※※※※

(1) 启动中继日志
[mysqld]
server_id=# 为当前节点设置一个全局惟的ID号
read_only=ON 设置数据库只读
root用户还是可以写的哈~~
relay_log=relay-log relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index 默认值hostname-relay-bin.index
(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
①mysql> CHANGE MASTER TO MASTER_HOST=‘host’, MASTER_USER=‘repluser’, MASTER_PASSWORD=‘replpass’, MASTER_LOG_FILE=’ mariadb-bin.xxxxxx’, MASTER_LOG_POS=#;
表示slave节点以哪一个用户从哪个master服务器的二进制日志的哪个位置进行同步。

②启动复制线程：mysql> START SLAVE [IO_THREAD|SQL_THREAD];

贰.MySQL主从实验※※※※※※

主从场景：

1.MySQL主从的场景：

①企业一开始搭建数据库时就直接是搭建的主从架构。
②企业刚开始就一个主服务器，但是由于后面的业务需求需要搭建主从架构。

主从场景之直接搭建主从架构

【1.实验规划：】

直接搭建主从由于刚开始master服务器数据库也没有任何的数据，二进制的同步位置为245（最开始的默认位置mariadb-bin.000001）
master ip：192.168.38.17
slave ip：192.168.38.27

【2.实验准备】

master：# yum install -y mariadb-server
slave：# yum install -y mariadb-server

【3.master 配置】

1）配置文件配置
# vim /etc/my.cnf
[mysqld]
# Master-slave replication configuration
innodb-file-per-table
log-bin
server-id=17

2）启动服务并执行安全加固脚本
# systemctl start mariadb
# mysql_secure_installation

3）master数据库中创建同步账户：
# mysql -p123456
> grant replication slave on *.*  to repluser@'192.168.38.%' identified by 'centos';

【4.slave 配置】

1）配置文件配置
# vim /etc/my.cnf
[mysqld]
# Master-slave replication configuration
server-id=27
    以下选项被注释，即不需要强制配置，建议配置，强制配置的只有server-id
innodb-file-per-table
# log-bin （如果考虑master的负载，要对slave进行备份的话，二进制日志需要开启）
# read-only
# relay_log=relay-log
# relay_log_index=relay-log.index

2）启动服务
# systemctl start mariadb

3）slave使用有复制权限的用户账号连接至主服务器，并启动复制线程
> help change master to （命令帮助获取或者说记不住命令）
>CHANGE MASTER TO
  MASTER_HOST='192.168.38.17',  masterip
  MASTER_USER='repluser',       master创建的复制用户
  MASTER_PASSWORD='centos',     复制用户的密码
  MASTER_PORT=3306,              master端口号
  MASTER_LOG_FILE='mariadb-bin.000001',  master二进制文件名
  MASTER_LOG_POS=245,             从二进制文件的神马位置同步 
  MASTER_CONNECT_RETRY=10;

方便以后复制使用：
> CHANGE MASTER TO   MASTER_HOST='192.168.38.17',   MASTER_USER='repluser',   MASTER_PASSWORD='centos',   MASTER_PORT=3306,   MASTER_LOG_FILE='mariadb-bin.000001',   MASTER_LOG_POS=245,   MASTER_CONNECT_RETRY=10;

执行change master to 之后datadir目录下出现中继日志相关的文件relay-log.000001，relay-log.index，relay-log.info
# ll /var/lib/mysql/relay-log.*

②启动复制线程：
> START SLAVE [IO_THREAD|SQL_THREAD];

【4.主从复制测试】

直接测试master的root密码是否同步
对master 数据库进行修改，slave是否同步

在master 数据库中建库建表建记录
> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8;
> use testdb
> create table students ( id tinyint(5) unsigned not null auto_increment, name varchar(20) not null , age tinyint(5) unsigned not null, gender enum('F','M') not null default 'M', primary key (id) );
> insert  students values (1,'Shi Zhongyu',22,'M'),(2,'Shi Potian',22,'M'),(3,'Xie Yanke',53,'M'),(4,'Ding Dian',32,'M'),(5,'Yu Yutong',26,'M');

在slave 中查询是否同步
> select * from testdb.students;

【5.主从复制的相关查询命令】

Seconds_Behind_Master: 0 主从同步的关键指标
表示slave与master之间存在多长时间的时间差

> show processlist;
> show slave status\G

主从场景之先只有一个主服务后面需要搭建从服务器

【1.实验规划：】

此实验可以利用上述实验，因为master 上数据库已经发生改变了，只需要将hostB快照还原，然后将A数据库中已经有的数据进行完全备份，发送至hostB，当然此过程中A仍然在提供服务，需要结合二进制日志位置进行同步。
master ip：192.168.38.17
slave ip：192.168.38.27

【2.实验准备】

slave hostB：# yum install -y mariadb-server

【3.将A进行完全备份】

生产环境的话，就这种应该是每天做的完全备份，可以直接将完全备份的数据发生至B主机即可
实验手动实现完全备份

# mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 > fullbak.sql
将备份文件传给hostB：
# scp fullbak.sql 192.168.38.27:/data/

【4.将B还原至备份状态】

hostB首先应该完成配置文件的修改，保证安装系统的有些数据选项一致

①安装配置：
# vim /etc/my.cnf
[mysqld]
server-id=27
innodb-file-per-table
log-bin
# systemctl start mariadb
# mysql_secure_installation 

②在hostB主机实现还原
# mysql -p123456
由于的开启了二进制日志，但是还原时记录二进制日志一般是没有必要的
> show variables like 'sql_log_bin';
> set sql_log_bin=0;临时关闭二进制日志

> source /data/fullbak.sql 恢复备份

> set sql_log_bin=1; 恢复备份后开启二进制日志

【在B主机还原期间A主机数据在进行修改】

> insert students(name,age)values('Tom',18);
则hostB指定CHANGE MASTER TO 的二进制日志的位置需要以备份文件的位置为准

【host B 设置 change master to】

CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000004', MASTER_LOG_POS=245;

可见备份文件的二进制日志和当时的位置（文件大小）

>CHANGE MASTER TO
  MASTER_HOST='192.168.38.17',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=245,
  MASTER_CONNECT_RETRY=10;
> start slave;
> show slave status\G 数据已同步

【hostB另外一种在命令行直接完成的步骤】

直接完成同步和change master to

直接在命令恢复并直接指定change master to的方式：
#vim /data/fullbak.sql
CHANGE MASTER TO 
....插入对应的信息
.....
MASTER_LOG_FILE='mariadb-bin.000004', 
MASTER_LOG_POS=245;

# mysql < /data/fullbak.sql

最后在hostB主机开启线程：
> start slave;

叁.MySQL复制架构中应该注意的问题

1.限制从服务器为只读

在从服务器上设置read_only=ON
注意：此限制对拥有SUPER权限的用户均无效
阻止所有用户, 包括主服务器复制的更新
mysql> FLUSH TABLES WITH READ LOCK;

2.从服务器清楚主服务器信息

RESET SLAVE：从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL：清除所有从服务器上设置的主服务器同步信息，如PORT, HOST, USER和 PASSWORD等
**注意：**以上都需要先STOP SLAVE

3.遇到同步错误的采取措施

若是不严重的同步错误，可以选择跳过当前错误然后同步。sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件，global变量
若是比较严重的错误只能采取将从服务器的信息清除，重新搭建。

4.如何保证主从的事务安全

参看https://mariadb.com/kb/en/library/server-system-variables/
在master节点启用参数：
sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
sync_master_info=# #次事件后master.info同步到磁盘
在slave节点启用服务器选项：
skip-slave-start=ON 不自动启动slave
在slave节点启用参数：
sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

肆.MySQL主从的server-id

1.由于对于server-id这个选项学习时比较在意，老师解释server-id的含义使我感觉到有些许困惑，因此自己查询了一些server-id的作用和server-id在实际中别人遇到过的坑。

2.server-id的作用

①mysql同步的数据中是包含server-id的，用于标识语句最初是从哪个server写入的，因此server-id必须存在和不同。
②每个同步中的slave在maser上均对应一个master线程，该线程就是通过slave的server-id来标识的，每个slave在master端最多有一个master线程，如果两个server-id相同，则后一个连接成功，前一个将被踢掉。这里至少有这么一种考虑：
slave主动连接master之后，如果slave在上面执行了slave stop；则连接断开，但是master上对应的线程并没有退出，当slave start之后，master不能再创建一个线程而是会保留原来的线程，那样的话同步可能会出现问题。
③在mysql做主主同步时，多个主需要构成一个环状，但是同步的时候需要保证一条数据不会陷入死循环，即依靠server-id区分。

3.MySQL Server-id相关的坑

无意间看到别人踩过的坑，记录一下。
线上两台服务器，一主一从即masterA–>slaveB，每天使用xtrabackup在B服务器上进行完全备份，当masterA突然宕机后，后来由于某些原因无法进入系统，无奈对masterA进行重装，此时先恢复A服务器mysql部署，然后利用备份恢复数据，再根据POS点change到B服务器，让A服务器与B服务器同步，此时架构变为masterB–>slaveA。

在A服务器从新从B服务器同步完成以后，确认没有延时以后，此时把A重新恢复成了原来的角色，也就是主库即masterA–>slaveB。但是恢复之后，发现A的数据比B的数据少，masterA–>slaveB数据不同步。

4.模拟关于server-id的坑

实验实际是承接上文的主从场景之直接搭建主从架构实验的，因此前面的主从同步就不再累述了
实验规划：
masterA：192.168.38.17
slaveB：192.168.38.27
【同步验证】
masterA：

> use testdb
> insert students(name,age)values('Xi Gua',18);

slaveB:

> select * from testdb.students;
+----+-------------+-----+--------+
| id | name        | age | gender |
+----+-------------+-----+--------+
|  1 | Shi Zhongyu |  22 | M      |
|  2 | Shi Potian  |  22 | M      |
|  3 | Xie Yanke   |  53 | M      |
|  4 | Ding Dian   |  32 | M      |
|  5 | Yu Yutong   |  26 | M      |
|  6 | Xi Gua      |  18 | M      |
+----+-------------+-----+--------+

masterA与slaveB同步ok

【对slaveB进行完备】

mysqldump -uroot -p123456 -A -F --single-transaction --master-data=1 > fullbak.sql

【masterA宕机重装系统，此时架构变为masterB–>slaveA】

注意：此过程中A、B的ip我未曾改，配置文件未曾改，server-id保证不变
B主机：清除同步信息

> stop slave;
> reset slave all;

A主机：故障的话直接还原系统啦–>重新搭建–>还原数据库–>变为slave

# yum install -y mariadb-server
# systemctl start mariadb
# mysql <fullbak.sql
# mysql_secure_installation

mysql -p123456 < fullbak.sql

因为上面同步时复制账户也同步了，在B主机上也就并未创建复制用户
A数据库数据恢复，此时保证A和B数据库的数据是相同的。


将A主机作为B主机的从：masterB-->slaveA
在B主机上为避免二进制日志产生问题：刷新一下二进制日志滚动
> flush logs
然后执行
>  show master status;
 mariadb-bin.000002 |724<如果刷新的话，Position=245>
保证下面命令的二进制文件和位置与此处对应。

 CHANGE MASTER TO
  MASTER_HOST='192.168.38.27',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000002',注意对应关系
  MASTER_LOG_POS=724,注意对应关系
  MASTER_CONNECT_RETRY=10;

> start slave;
此时主机A称为了主机B的从服务器

【测试】

在B主机上更改数据，然后在A主机上测试。

【个人总结】

我的实验并未发生server-id的更改，因此并未出现上述的坑。
server-id比较重要，不能出现重复，谨防掉坑吧~~
发现对从slave备份导致–master-data选项要使用必须开启二进制日志，因此备份slave的话，slave必须开启二进制日志！
而且slave通过中继日志执行的SQL语句要记录在二进制日志文件中的话，还有添加log_slave_updates选项。

伍.MySQL复制的线程、监控和维护

线程的启动情况

1.默认io thread和SQL thread会伴随着mysql/mariadb服务的启动而启动的

2.在slave节点启用不自动启动io thread 和SQL thread线程的方式

skip-slave-start=on

3.io thread和seq thread线程的相关SQL语句

show slave status\G 查看
start slave 启动
stop slave 关闭
reset slave all 清理slave相关信息

复制的监控和维护

(1).清理日志

PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE
datetime_expr }
RESET MASTER
RESET SLAVE

(2).复制监控

SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

(3).从服务器是否落后于主服务

Seconds_Behind_Master：0

(4).如何确定主从节点数据是否一致

percona-tools

(5).数据不一致如何修复

删除从数据库，重新复制

陆.MySQL复制的其他架构

级联复制

1.级联复制架构图

2.slave1的配置关键选项

[mysqld]
log_bin
log_slave_updates
注：二进制日志默认只会记录在本机更新数据库才会记录二进制日志，通过master主机同步过来中继日志对slave数据库的更新将不会记录二进制日志，即执行中继日志中的SQL语句默认不会记录在二进制日志中。因此需要让执行中继日志的SQL语句也记录在二进制日志中，则需要添加log_slave_updates选项。

3.级联复制的实现此处省略。

主主复制

1.主主复制：互为主从

容易产生的问题：数据不一致
生产中直接单纯的使用主主是不推荐使用的，
解决一张表的数据冲突考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 开始点
auto_increment_increment=2 增长幅度
另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2

注：以上解决方案并不能完全解决主主的数据不一致问题，比如主主同时创建一个同名的表或者库…届时同步将产生错误.
主主复制的实现：Galera Cluster （后面阐述）

2.此处简单的主主复制的配置步骤：

(1) 各节点使用一个惟一server_id
(2) 都启动binary log和relay log
(3) 创建拥有复制权限的用户账号
(4) 定义自动增长id字段的数值范围各为奇偶
(5) 均把对方指定为主节点，并启动复制线程

3.主主复制的实现

实现规划：
master1-ip：192.168.38.17
master2-ip：192.168.38.27

【实现①：准备工作：】

master1和master2安装mariadb软件
说明：为了实验的简易性，mysql_secure_installation脚本将不执行了，即使用默认root等空口令实现

【实现②：master1配置】

master1-ip：192.168.38.17

[mysqld]
server-id=17
log-bin
innodb-file-per-table
auto_increment_offset=1
auto_increment_increment=2

# systemctl start mariadb
# mysql
> grant replication slave on *.*  to repluser@'192.168.38.%' identified by 'centos';

【实现③：master2配置】

master2-ip：192.168.38.27

[mysqld]
server-id=27
log-bin
innodb-file-per-table
auto_increment_offset=2
auto_increment_increment=2

> CHANGE MASTER TO
  MASTER_HOST='192.168.38.17',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=245,
  MASTER_CONNECT_RETRY=10;

> start slave;

【实现④：master1配置】

>CHANGE MASTER TO
  MASTER_HOST='192.168.38.27',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=245,
  MASTER_CONNECT_RETRY=10;

> start slave;

master1<–>master2互为主从

【实现⑤：主主复制验证】

master1:建库
> create database testdb;
master2：可以查询，master1-->master2同步成功

master2:建库
> create database testdb2;
master1：可以查询，master2-->master1同步成功

创建一张表，然后利用xshell向其中同时添加记录
> use testdb
> create table tab1( id tinyint unsigned auto_increment not null, name char(10) not null,primary key(id));

xshell 菜单栏 --> 查看 --> 撰写栏 
在撰写栏中：
insert tab1(name)values('qingwa');撰写栏中向master1/2同时发送
select * from tab1;
+----+--------+
| id | name   |
+----+--------+
|  1 | qingwa |
|  2 | qingwa |
+----+--------+

==> 由于设置了主主自动增长id，可以避免id冲突现象

但是如果是利用撰写栏同时创建同一张表呢？
在撰写栏中：
create table tab2( id tinyint unsigned auto_increment not null, name char(10) not null,primary key(id));

发现master1和master2同时创建同一张表均成功啦~
然后查看同步状态：
show slave status\G
Error 'Table 'tab2' already exists' on query. Default database: 'testdb'. Query: 'create table tab2( id tinyint unsigned auto_increment not null, name char(10) not null,primary key(id))'
这样将导致同步失败！！

【实现⑥：主主复制总结】

单纯的直接使用主主可能产生数据不一致，而且同步出现问题，因此一般不会直接使用主主同步
主主同步一般会配合别的应用混合使用，比如可以通过keepalive，配置是主主，但是使用相当于主从，当其中一个主宕机时，另一个服务器可以不用配置直接顶上。
后续的主主实现：Galera Cluster

柒.mysql的半同步复制

mysql的同步机制

同步|半同步|异步
系统默认的同步复制机制是异步的，异步可能导致数据丢失~~
半同步：用户更新数据库时，master需要等待至少一个slave节点同步成功，才会返回用户更新成功。或者等待slave同步超时再返回用户数据更新成功。
但是如果因为网络问题半同步时可能比较慢，此时解决的方案是设计一个等待时间，等待时间一到，如果slave还未同步，也返回用户更新成功。
**注：**生成环境中一般采用半同步机制，既能提升用户体验又能保证数据至少有一个备份

mysql半同步复制

要实现半同步复制，需要在master和slave节点分别安装对应的插件，插件默认在安装数据库服务文件时已经安装

# rpm -ql mariadb-server|grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

mysql半同步复制的实现

【1.半同步复制实验规划】

master ip：192.168.38.17
slave1 ip：192.168.38.27
slave2 ip：192.168.38.47
说明：为简化实验mysql的root口令为空

【2.半同步复制实验准备】

master slave1 slave2安装mariadb-server

【3.半同步复制实验之先搭建主从架构】

主从架构不详细写，直接写实现的关键步骤

1、master
[mysqld]
innodb-file-per-table
server-id=17
log-bin
rpl_semi_sync_master_enabled master启用半同步插件
rpl_semi_sync_master_timeout=3000 等待延迟时间为3s

>grant replication slave on *.* to repluser@'192.168.38.%' identified by 'centos';

slave1:
[mysqld]
innodb-file-per-table
server-id=27
log-bin
read-only
rpl_semi_sync_slave_enabled  slave启用半同步插件

> CHANGE MASTER TO
  MASTER_HOST='192.168.38.17',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=245,
  MASTER_CONNECT_RETRY=10;

> start slave;

slave2:
[mysqld]
innodb-file-per-table
server-id=47
log-bin
read-only
rpl_semi_sync_slave_enabled slave启用半同步插件

> CHANGE MASTER TO
  MASTER_HOST='192.168.38.17',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=245,
  MASTER_CONNECT_RETRY=10;

> start slave;

【4.半同步复制实验之安装各种对应的插件】

master
安装semisync_master.so插件
> install plugin rpl_semi_sync_master soname 'semisync_master.so';

临时生效刚安装的插件：
> set global rpl_semi_sync_master_enabled=1;

slave
安装semisync_slave.so插件
> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

临时生效刚安装的插件：
> set global rpl_semi_sync_slave_enabled=1;

插件管理命令

1.show plugins;

show plugins;
查看系统中的插件

2.install plugin

install plugin 插件取名 soname ‘安装插件的.so文件’
安装插件

半同步相关的变量

1.服务变量

master 服务器变量
> show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

rpl_semi_sync_master_enabled：默认安装插件后不启动
永久启用需要写入对应的配置文件
rpl_semi_sync_master_timeout：超时时长 10000ms=10s 以ms为单位的

slave 服务器变量
> show global variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

2.状态变量

> show global status like '%semi%'; 
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+

mysql半同步复制总结

1.可以确保用户对数据库的更新操作有至少一个备份
2.可以在一定程度上防止异步复制时master宕机导致数据丢失的问题
3.但是在网络异常至超时时长slave还未同步master数据时，master宕机还是可能造成数据丢失
4.半同步在生产环境中一般都会启用

mysql复制过滤器

1.使用场景：mysql数据库在有多个数据库，但是并不是每个数据库都希望进行复制时，比如说master节点有一个数据库数据量也不大，平时更新也很少，也没有进行复制的必要。此时可以使用复制过滤去选择特点的库进行复制。

2.复制过滤器：

让从节点仅复制指定的数据库，或指定数据库的指定表

3.复制过滤器的实现方式

(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
注意：此项和binlog_format相关
参看：https://mariadb.com/kb/en/library/mysqld-options/#-binlogignore-db

binlog-do-db = 数据库白名单列表，多个数据库需多行实现
binlog-ignore-db = 数据库黑名单列表
问题：基于二进制还原将无法实现；不建议使用
(2) 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本地
问题：会造成网络及磁盘IO浪费

4.从服务器上的复制过滤器相关变量

replicate_do_db= 指定复制库的白名单
replicate_ignore_db= 指定复制库黑名单
replicate_do_table= 指定复制表的白名单
replicate_ignore_table= 指定复制表的黑名单
replicate_wild_do_table= foo%.bar% 支持通配符
replicate_wild_ignore_table=
在从节点上实现复制过滤是可以采取的

【实验：master的配置】

[mysqld]  
binlog-do-db=db1 增添此行
    表示只要db1才生产二进制日志，不推荐在master上设置此选项

> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000004 |      245 | db1          |                  |
+--------------------+----------+--------------+------------------+

然后此时仅有db1能在master传输二进制日志，当然仅只要db1才能实现同步咯

【实验：slave的配置】

[mysqld]  
binlog-do-db=db1 增添此行

捌.mysql复制加密

mysql复制的加密性

1.mysql客户端通过网络连接远程的服务器中间的信息并未加密，mysql的主从复制默认同样未加密。若主从复制需要经过公网时，则可能需要实现mysql的加密。

2.mysql+ssl

在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或则复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性

3.配置实现：

参看：https://mariadb.com/kb/en/library/replication-with-secureconnections/
主服务器开启SSL，配置证书和私钥路径
并且创建一个要求必须使用SSL连接的复制账号
mysql>GRANT REPLICATION SLAVE ON . TO ‘repluser’@‘192.168.38.%’ IDENTIFIED BY ‘centos’ REQUIRE SSL;
EQUIRE SSL：强制用户以加密方式连接
从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项

4.Master服务器配置

[mysqld]
log-bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key

5.Slave服务器配置

mysql>
CHANGE MASTER TO 
MASTER_HOST='MASTERIP', 
MASTER_USER='rep', 
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1, 
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', 
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', 
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';

mysql加密的实现实验

【1.实验规划】

master：192.168.38.17
slave：192.168.38.27
master即为CA，然后签署给master和slave颁发证书

【2.实现ssl】

计划将所有的证书信息全部放在/etc/my.cnf.s/ssl/目录
将slave证书和master证书都在此处一并生成
然后将目录直接拷贝给slave

# mkdir /etc/my.cnf.d/ssl
# cd /etc/my.cnf.d/ssl

生成CA的私钥文件
# (umask 066;openssl genrsa 1024 > cakey.pem;)

生成CA自签证书
# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650

生成master的私钥和证书请求
# openssl req -newkey rsa:1024 -nodes -keyout master.key>master.csr

生成slave的私钥和证书请求
# openssl req -newkey rsa:1024 -nodes -keyout slave.key>slave.csr

给master颁发证书
# openssl x509 -req -in master.csr -CA cacert.pem  -CAkey cakey.pem -set_serial 01 > master.crt

给slave颁发证书
# openssl x509 -req -in slave.csr -CA cacert.pem  -CAkey cakey.pem -set_serial 02 > slave.crt

【3.master主机配置】

[mysqld]
innodb-file-per-table
server-id=17
log-bin
read-only
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key

> show variables like '%ssl%';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| have_openssl  | YES                          |
| have_ssl      | YES                          |
| ssl_ca        | /etc/my.cnf.d/ssl/cacert.pem |
| ssl_capath    |                              |
| ssl_cert      | /etc/my.cnf.d/ssl/master.crt |
| ssl_cipher    |                              |
| ssl_key       | /etc/my.cnf.d/ssl/master.key |
+---------------+------------------------------+

==>master已经启用的ssl功能，但是仅是启用，并未强制。

创建用户时：使用REQUIRE SSL关键字，强制要求此用户使用ssl连接进行复制
 > grant replication slave on *.*  to repluser@'192.168.38.%' identified by 'centos' REQUIRE SSL;

拷贝过去之后在slave进行连接测试。注意目录
ssl]# mysql -urepluser -pcentos -h192.168.38.17 --ssl-cert=slave.crt --ssl-key=slave.key --ssl-ca=cacert.pem

【4.slave主机配置】

方式一：
[mysqld]
innodb-file-per-table
server-id=27
log-bin
read-only
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key

mysql>
  CHANGE MASTER TO
  MASTER_HOST='192.168.38.17',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=569,
  MASTER_ssl=1;

方式二：
mysql>
CHANGE MASTER TO 
MASTER_HOST='192.168.38.17', 
MASTER_USER='repluser', 
MASTER_PASSWORD='centos',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245,
MASTER_SSL=1, 
MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem', 
MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt', 
MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';

玖.二进制安装mysql5.7

注：mysql5.7的编译安装与其前面的二进制安装mariadb有所不同

【1.准备阶段】

获取mysql5.7二进制软件

# tar xf mysql-5.7.27-el7-x86_64.tar.gz  -C /usr/local/
# cd /usr/local/
# ln -s ./mysql-5.7.27-el7-x86_64/  mysql
# cd /usr/local/mysql
    查看文件属性，文件的属主属组均为root正确

【2.创建mysql用户】

# groupadd -g 666 -o -r mysql
# useradd -s /sbin/nologin -r -d /data/mysql -g 666 -u 666 -c "mysql server" mysql
# getent passwd mysql

【3.修改PATH变量】

# echo 'PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
# . /etc/profile.d/mysql.sh

【4.创建datadir目录】

# mkdir /data/mysql/
# chown mysql.mysql /data/mysql/

【5.生成数据库文件】

#  yum install -y libaio
# mysqld --initialize --user=mysql --datadir=/data/mysql
mysql@local用户的初始密码：frsdkWii<9W/

【6.准备配置文件】

目前将所有文件均放置在datadir目录的
放置其他对应目录时，注意文件属性。

[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

【7.准备启动脚本】

# cp support-files/mysql.server /etc/init.d/mysqld
# chkconfig --add mysqld

【8.启动服务连接测试】

# service mysqld start
# mysql -p'frsdkWii<9W/'
mysql> 必须重新更改密码才能对数据库进行操作
mysql> set password for root@'localhost'=password('123456');
这样可以修改，但是有一个warning，这种更改密码的方式将在以后版本被淘汰
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

【9.mysql5.7的mysql.user表】

mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *8DDA22DAC660080A5FDE0FF4B8D0C392B98AD12C |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

更改root@localhost 用户密码
mysql> update user set authentication_string=password('123456') where user='root';

==>存放口令的字段变为了authentication_string
==>password()函数将在以后版本被淘汰

拾.GTID复制

GTID复制简介

1.GTID复制

GTID复制：（global transaction id 全局事务标识符） MySQL5.6版本开始支持，GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找同步

2.GTID 架构

GTID = server_uuid:transaction_id
在一组复制中，全局唯一server_uuid 来源于 /datadir/auto.cnf

3.GTID服务器相关选项

gtid_mode gtid模式
enforce_gtid_consistency 保证GTID安全的参数
GTID复制这两项必须启用

4.GTID在binlog中的结构和GTID event 结构

GTID配置示例

1.主服务器

vim /etc/my.cnf
server-id=1 
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency
mysql> grant replication slave on *.* to 'repluser'@'192.168.8.%' 
identified by 'P@ssw0rd!';

2.从服务器

vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency
mysql>CHANGE MASTER TO MASTER_HOST='192.168.8.100',
MASTER_USER='repluser',
MASTER_PASSWORD='P@ssw0rd!',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
mysql>start slave;

基于上述二进制安装的mysql5.7实现GTID

【1.实验规划】

mmaster ip：192.168.38.17
slave ip：192.168.38.27

【2.master配置】

[mysqld]
server-id=17
log-bin=mysql-bin
gtid_mode=ON
enforce_gtid_consistency

mysql> grant replication slave on *.*  to repluser@'192.168.38.%' identified by 'centos';

【3.slave配置】

vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency

mysql>CHANGE MASTER TO MASTER_HOST='192.168.38.17',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
    不需要指定二进制日志的位置

mysql>start slave;

Hskds

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL：MySQL复制

MySQL：（十三）MySQL集群其一（一）关系型数据库基础（二）MySQL安装（三）管理数据库和表（四）用户和权限管理（五）函数，存储过程和触发器（六）MySQL架构（七）存储引擎（八）MySQL服务器选项，系统和状态变量（九）优化查询和索引管理（十）锁和事务管理（十一）日志管理（十二）备份还原（十三）MySQL集群壹.MySQL复制为什么要mysql复制1....
复制链接

扫一扫