MySQL主从复制（上）

为什么要做主从复制

1.为了做高可用
2.为了解决MySQL的单点故障
3.分摊主库的压力
4.备份（延时从库）

change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000001',
master_log_pos=446,
master_port=3306;

主从复制前提

Master 和 Slave 时间同步
至少 2 台以上实例，需要有角色划分的标识，server_id
Master 需要开启 Binlog
Master 建立专用复制用户
Slave 提前录入Master 的大部分数据
Slave 确认复制起点
开启专用复制线程（主库 Dump 线程；从库 IO 线程，SQL 线程）

主从复制原理

#### 文字描述
主库配置：
#  主库配置 server_id ，开启 bin-log （/etc/my.cnf）
#  主库创建授权主从复制用户（grant replication slave）
#  获取主库的 binlog 信息（file，position）
#  导出主库数据（mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql）


从库配置：
#  从库配置 server_id 
#  确认使用主库的主从复制用户可以连接主库
#  导入主库数据（mysql < full.sql）
#  配置主从（change master to），需要 Master节点 IP，用户，端口，binlog-file，binlog-pos
#  binlog 配置信息可以从 full.sql 中 22 行获得


工作原理：
#  1. Start slave 之后，从库开启 IO线程 和 SQL线程
#  2. 主库开启 Dump线程
#  3. IO线程 向 Dump线程 发起询问，询问是否有新的数据
#  4. Dump线程 被询问，查找是否有新数据，若有返回给 IO线程
#  5. IO线程 拿到数据，写入TCP缓存
#  6. TCP缓存拿到数据，写入relay-log，并返回给 IO线程一个 ACK报文
#  7. IO线程 收到 ACK报文，会记录当前位置到 master.info（binlog 位置点）
#  8. SQL线程 会主动读取 relay-log，执行主库执行的sql语句
#  9. 执行后，将执行到的位置点，记录到 relay-log.info（relay-log 位置点）

涉及的文件和线程

########### 线程 #########
三个线程  dump  I/O  SQL
#主库  dump线程
Binlog_Dump 线程：用来接收从库的请求，并投递 Binlog 给从库
localhost:(none) >show processlist\G
*************************** 2. row ***************************
     Id: 73
   User: rep
   Host: 172.16.1.54:54424
     db: NULL
Command: Binlog Dump
   Time: 115
  State: Master has sent all binlog to slave; waiting for more update
        # Master已经将所有binlog发送给slave;等待更多更新
   Info: NULL
----------------------------------------------------------------------------
# 从库 I/O线程  SQL线程
I/O 线程：连接主库，请求 Binlog，接收 Binlog，等待主库发送 Binlog 事件
SQL 线程：读取执行 Relaylog，等待 I/O 线程更新 Relaylog，实质上就是执行从主库接收的 Binlog 事件（Relaylog 中记录着 Binlog 事件）

mysql> show processlist\G
*************************** 2. row ***************************
     Id: 19
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 295
  State: Waiting for master to send event
  		# 等待master发送事件
   Info: NULL
*************************** 3. row ***************************
     Id: 20
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 13484
  State: Slave has read all relay log; waiting for more updates
  		# Slave已读取所有中继日志;等待更多更新
   Info: NULL

##### 文件
四个文件 ： bin-log
# master
Binlog：主库执行的 SQL语句

# slave
Relay-log：中继日志，记录从主库接收的 binlog
master.info：连接主库的信息，以及记录主库 binlog 信息，会随着同步进行更新
relay-log.info：记录sql线程执行到了那里，下次从哪里开始执行

relay-log 会不定时自己清除 存储binlog中新增的数据（不是一直存在的）复制SQL线程在执行完文件中的所有事件并且不再需要它后，会自动删除每个中继日志文件 
# 官网解释
复制SQL线程在执行完文件中的所有事件并且不再需要它后，会自动删除每个中继日志文件。没有明确的机制来删除中继日志，因为复制SQL线程会负责这样做。但是，FLUSH logs会轮换中继日志，这会影响复制SQL线程何时删除它们。

主从复制实战

######## master（主库）#############
# 1.修改主库配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=1
log-bin=mysql-bin
skip_name_resolve

# 2.重启
systemctl restart mysqld

# 3.创建主从复制用户
grant replication slave on *.* to rep@'172.16.1.5%' identified by '123';
#-----# replication slave 只能给*.* 。

# 4.查看bin-log 记录数据位置点
show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 12450
  
# 5.进行一次打点全备
mysqldump -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz

############ slave(从库) #######
# 1.修改从库配置文件
vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=2
skip_name_resolve

# 2.重启数据库
systemctl restart mysqld

# 3.导入数据
zcat full.sql.gz | mysql

# 4.执行change master
change master to
master_host='172.16.1.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000002',
master_log_pos=446;

# 5.开启主从辅助
start slave;

ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /app/mysql/bin/mysql /usr/bin/mysql

# 6.查询主从状态
show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主从复制的故障处理

############# I/O ############
show slave status\G
      Slave_IO_Running: No
      Slave_SQL_Running: Yes
            
show slave status\G
      Slave_IO_Running: Connecting
      Slave_SQL_Running: Yes
      
 # 排查思路
1.网络
	[root@db02 ~]#  ping 172.16.1.53
2.端口
	[root@db02 ~]#  telnet 172.16.1.53 3306
	端口不通：
		- 防火墙 selinux
		- 服务没起
		- 没有创建远程连接的用户
3.主从授权的用户,端口错误
	change master to 错误
4.反向解析
	skip-name-resolve
5.UUID或server_id相同
	$datadir/auto.cnf
	my.cnf

################# SQL #############
show slave status\G
            Slave_IO_Running: Yes
            Slave_SQL_Running: No

# 主从之间的数据库不一致

## 解决方法

1）# 不推荐 临时停止同步
		mysql> stop slave;
		# 将同步指针向下移动一个（可重复操作）
		mysql> set global sql_slave_skip_counter=1;
		# 开启同步
		mysql> start slave;
		
2）# 不推荐 
[root@db01 ~]# vim /etc/my.cnf
#在[mysqld]标签下添加以下参数
slave-skip-errors=1032,1062,1007,1146


3）# ## 但是以上操作都是有风险存在的,导致从库数据缺失
# 数据不一致的原因：
1.在生产中运行已有数据的主库，新加从库直接从当前位置点开始复制
解决方案：给主库全备，导入到新从库中，保证数据的一致性
2.直接在从库中操作数据库
解决方案：从库设置为只读（读写分离）
# 在命令行临时设置
set global read_only=1;
# 在配置文件中永久生效
read_only=1