db2 主从复制 sql主从复制

文章目录

• 一.概述
• 二.什么叫高可性
• 三.MySQL设计思路
• 三.主从复制原理

• 3.1 mysql复制类型
• 3.2mysql主从复制的工作过程

• 3.2.1 日志的工作过程

• 3.3 二进制日志的保存方式
• 3.4 主从复制策略

• 四.搭建MySQL主从复制

• 4.1 实验准备（三台虚拟机）
• 4.2 主从服务器时间同步
• 4.3 服务器安装mysql
• 4.4 msater服务器开启二进制日志
• 4.5 master配置：给从服务器提权grant；用于主从对接
• 4.6 slave1：开启从服务器功能
• 4.7 通用的方法开启slave2的从服务功能
• 4.8 测试主从复制

• 五、总结

• 5.1 主从同步原理
• 5.2 如何查看主从同步状态是否成功
• 5.3 如果I/0和SQL不是yes呢, 如何排查的
• 5.4 showslave.status能看到哪些信总(比较重要的)
• 5.5 主从复制慢(延迟)有哪些可能

一.概述

1. 在企业应用中，成熟的业务通常数据量都比较大
2. 单台mysql在安全性、高可用性和高并发方面都无法满足实际需求
3. 配置多台主从数据库服务器以实现读写分离

二.什么叫高可性

高可用性:
指的是冗余备份包含性能指标（五大负载+内核优化方向)
安全性指标:网络部分安全性、通讯加密，用户登陆、管理权限安全性

三.MySQL设计思路

1. 设计思路
   ①MySQL主从复制：让slave服务器中的中继日志同步master二进制日志
   ②MySQL读写分离：基于主从复制以功能性拆分成读和写，缓解MySQL读写的压力，解决读写比例失恒，单台读写瓶颈的问题。
   ③MHA高可用：对master服务器做一个冗余备份工作。
2. 三种方式：
   ①纵向扩展：强化自己服务器（五大资源，内核优化 加数据库内的优化，索引，存储过程，select查询等等
   ②横向扩展：增加服务器数量
   ③借助于内存/缓存数据库：Redis,来帮助MySQL缓存一些高热数据，减少MySQL压力
   除此之外可以考虑是否需要做一些架构，服务上的改动

三.主从复制原理

3.1 mysql复制类型

1. 基于语句的复制（sql)
2. 基于行的复制(记录）
3. 混合类型的复制

3.2mysql主从复制的工作过程

两个日志文件 三个线程

三个线程如何工作

master：二进制日志文件
 dump线程

 slave:中继日志文件
 io线程
 sql线程

3.2.1 日志的工作过程

1. 主从复制主要是通过日志文件进行恢复 master上的dump线程，把数据同步到二进制日志中
2. slave上的sql线程，把自己中继日志中的文件，同步到slave数据库中
3. slave上的sql线程，把自己中继日志中的文件，同步到slave数据库中

过程：

1. dump线程会监听二进制日志的更新，如果有更新则会通知slave IO ① 首先slave上的IO线程向master上申请同步二进制日志的更新内容
   ② dump线程会把同步的sql日志内容与slave服务器，slave的IO线程会把写入自己的中继日志
   ③ slave sql线程把日志中的更新语句同步执行到数据库内部，以达到和master数据库趋近一致

3.3 二进制日志的保存方式

1. STATEMENT（基于SQL语句）
   ① 每一条涉及到被修改的sql 都会记录在binlog中
   ② 缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题
2. ROW(基于行)
   ① 只记录变动的记录，不记录sql的上下文环境
   ② 缺点:如果遇到update…set…where true 那么binlog的数据量会越来越大
3. MIXED 推荐使用
   ①一般的语句使用statement，函数使用ROW方式存储

3.4 主从复制策略

1. 全同步：master请求服务时，等待slave全部同步之后才会回应client端
2. 半同步：master请求服务时，只要任意一个slave同步
3. 异步：master请求服务时，只要master同步即回应请求，不管slave的状态；默认的状态
4. 因为主从复制的默认策略是异步，导致的结果就是主从二进制日志和中继日志的保存内存会越来越大

四.搭建MySQL主从复制

4.1 实验准备（三台虚拟机）

虚拟机	IP地址
mysql-master	192.168.10.131
mysql-slave1	192.168.10.132
mysql-slave2	192.168.10.136

4.2 主从服务器时间同步

三台服务器配置

1. 修改主机名为master服务器

hostnamectl set-hostname master  #将主机名改成master
 su

2. 同样把两台从服务器改名

hostnamectl set-hostname slave1
hostnamectl set-hostname slave2
su

yum install -y ntp		##安装ntp软件
ntpdate ntp.aliyun.com	##时间同步
ntpdate 192.168.10.131	##把三台服务器同步同一时间，ip为master
crontab -e
	*/10 * * * * /usr/sbin/ntpdate 192.168.10.131		##master和slave1、slave2同步阿里云时钟服务

4.3 服务器安装mysql

MySQL安装完成确认下是否有mysql用户

4.4 msater服务器开启二进制日志

vim /etc/my.cnf
	[mysqld]
	server-id = 1		  ##主服务器id不可重复；标识服务器的角色
	log-bin=master-bin	    	##开启二进制日志文件
	log_slave_updates=ture	  	##开启从服务器日志同步，允许从从服务器的更新节点过来
systemctl restart mysqld		##重启服务**

4.5 master配置：给从服务器提权grant；用于主从对接

mysql -u root -p
grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123';##给从服务器提权，允许使用slave的身份复制msater所有数据库的所有表，并制定密码为123
flush privileges;
show master status;		##查询数据库状态

cd /usr/local/mysql/data

1. 以上可见产生了master- bin.000002日志文件，定位为603
   从服务器需要定位到此处进行复制
2. 查看二进制日志

mysqlbinlog --no-defaults --base64-output=decode-rows -v master-bin.000001

4.6 slave1：开启从服务器功能

vim /etc/my.cnf
		server-id = 2				##设置server_id为2，slave2为3
        log-bin=master-bin			##开启二进制日志文件
        relay-log=relay-log-bin		##从主服务器上同步日志文件记录到本地
        relay-log-index=slave-relay-bin.index	##定义relay_log的位置和名称(index索引)
	--->wq保存
systemctl restart mysqld
--------------------->slave1数据库操作<---------------------
mysql -u root -p 
change master to master_host='192.168.10.131',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=603;
start slave;
show slave status\G		##查看主从服务是否成功

4.7 通用的方法开启slave2的从服务功能

vim /etc/my.cnf
server-id = 3				##设置server_id为3，slave1为2
log-bin=master-bin			##开启二进制日志文件
relay-log=relay-log-bin		##从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index	##定义relay_log的位置和名称(index索引)
systemctl restart mysqld
--->wq保存
mysql -u root -p
change master to master_host='192.168.10.131',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=603;	
mysql> start slave;
mysql> show slave status\G			##查看主从服务是否成功

1. 若查询主从是否同步时显示：connecting，可以稍等一会再重新查看

4.8 测试主从复制

主服务器上创建一个数据库

mysql> create database qq;
Query OK, 1 row affected (0.04 sec)

slave1 同步

若是在slave1服务器上面更改，master主服务器与slave2并没有同步更改

五、总结

5.1 主从同步原理

1. 通过amoeba代理服务器，实现只在主服务器上写，只在从服务.上读;
2. 主数据库处理事务性查询，从数据库处理select 查询:
3. 数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库

5.2 如何查看主从同步状态是否成功

• 在从服务器内输入命令show slave
• status\G, 查看主从信息进行查看，里面有Io线程的状态信息，还有master服务器的IP地址、端口事务开始号
• slave_ io_ running 和slave_ sq1 running都显示为yes时，表示主从同步状态成功

5.3 如果I/0和SQL不是yes呢, 如何排查的

• 首先排除网络问题，使用ping命令查看从服务是否能与主服务器通信

• 再者查看防火墙和核心防护是否关闭
• 接着查看从服务器内的slave是否开启
• 两个从服务器的server-id 是否相同导致只能连上一-台
• naster_ 1og_ file和master_ 1og. _pos 的值要是否与Master查询的一 -致

5.4 showslave.status能看到哪些信总(比较重要的)

1. I0线程的状态信息
2. naster服务器的IP地址、端口、事务开始位置
3. 最近一次的报错信息和报错位置等

5.5 主从复制慢(延迟)有哪些可能

• 主服务器的负载过大，被多个睡眠或者僵尸线程占用，导致系统负载过大
• 从库硬件比主库差，导致复制延迟
• 主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。
• 慢sQL语句过多
• 网络延迟