MySQL 主从复制基础

2021-02-16 | 129 浏览

MySQL主从复制基础

0.主从复制简介

基于二进制日志复制的

主库的修改操作会记录二进制日志

从库会请求新的二进制日志,并恢复,最终达到主从数据同步

1. 主从复制前提(

1.两台以上的mysql实例
2.主库开启二进制日志
3.保证主从开启之前的某个时间点，从库数据是和主库一致
4.专用的复制用户
5.告知从库，复制用户信息，IP Port，以及复制起点
6 线程(三个):Dump thread  IO thread  SQL thread 开启(start slave)

2.主从复制的原理 (Classic Replication)

2.1 主从中设置到的文件和线程
2.1.1 线程
主:
	DUMP THREAD

从:
	IO  THREAD
	SQL THREAD
2.1.2 文件
主:
	mysql-bin.000001
从: 
	db01-relay.000001
	master.info 
	relay-log.info
2.2 (图示说明)

3.1 简单描述

(1)Master将数据改变记录到二进制日志binary log中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件

(2)Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志的relay log

(3)Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据

3.2详细描述

1.Slave节点通过IO线程读取master.info的信息（主要是IP ,Port ,user ,passwd , file+position）

2.IO thread通过连接信息，连上主库，主库会生成DUMP Thread

3.IO通过master.info提供的file+position，找到主库请求比这个新的二进制日志事件

4.Master Dump Thread截取全新的二进制日志事件，按照事件为单元，串行传送给Slave IO Thread

5.Slave IO Therad收到发过来的二进制日志事件,缓存到 TCP/IP cache中,立即返回ACK 给MASTER.

6.Slave IO线程更新master.info中二进制日志信息，并且会将TCP IP缓存数据写入relay-log日志中

7.Slave SQL线程，读取relay-log.info,获取到上次已经执行过的relay log位置号

8.SQL线程根据relay log历史记录，往后继续串行执行relay log，执行完成后，再次更新relay-log.info信息

9.relay-log被应用完成后，会定时自动清理

3.主从复制环境准备

1.1 准备多实例环境（3307,3308,3309）
2.1.1 创建多实例需要的目录

[root@db01 ~]# rm -rf /data/330*
[root@db01 ~]# mkdir -p /data/330{7..9}/data

2.1.2 授权

[root@db01 ~]# chown -R mysql.mysql /data

2.1.3 创建3个配置文件

vim /data/3307/my.cnf 
[mysqld]
server_id=7
port=3307
basedir=/server/tools/mysql
datadir=/data/3307/data
log_bin=/data/3307/mysql-bin
log_error=/data/3307/mysql.log
socket=/data/3307/mysql.sock


vim /data/3308/my.cnf 
[mysqld]
server_id=8
port=3308
basedir=/server/tools/mysql
datadir=/data/3308/data
log_bin=/data/3308/mysql-bin
log_error=/data/3308/mysql.log
socket=/data/3308/mysql.sock


vim /data/3309/my.cnf 
[mysqld]
server_id=9
port=3309
basedir=/server/tools/mysql
datadir=/data/3309/data
log_bin=/data/3309/mysql-bin
log_error=/data/3309/mysql.log
socket=/data/3309/mysql.sock

2.1.4 初始化数据
[5.7初始化命令]

/server/tools/mysql/bin/mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/server/tools/mysql
/server/tools/mysql/bin/mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/server/tools/mysql
/server/tools/mysql/bin/mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/server/tools/mysql

[5.6初始化命令]

/server/tools/mysql/scripts/mysql_install_db --user=mysql --basedir=/server/tools/mysql --datadir=/data/3307/data 
/server/tools/mysql/scripts/mysql_install_db --user=mysql --basedir=/server/tools/mysql --datadir=/data/3308/data 
/server/tools/mysql/scripts/mysql_install_db --user=mysql --basedir=/server/tools/mysql --datadir=/data/3309/data

[重新初始化命令]

mysqld --initialize-insecure --user=mysql --basedir=/app/mysql --datadir=/data/3307/data

2.1.5 启动数据库

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

报错：

[root@db01 ~]# 190221 17:15:10 mysqld_safe error: log-error set to '/data/3309/mysql.log', however file don't exists. Create writable for user 'mysql'.
190221 17:15:10 mysqld_safe error: log-error set to '/data/3308/mysql.log', however file don't exists. Create writable for user 'mysql'.
190221 17:15:10 mysqld_safe error: log-error set to '/data/3307/mysql.log', however file don't exists. Create writable for user 'mysql'.

解决：

touch /data/330{7..9}/mysql.log 
chown -R mysql.mysql /data/*

2.1.6 查看端口号

[root@db01 3307]# netstat -lnp|grep 330

4.主从搭建过程

2.0 规划

主（master）：10.0.0.51:3307

从（slave）：10.0.0.51:3308

2.1 主库中进行创建复制用户（权限是replication slave）

mysql -S /data/3307/mysql.sock 
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

2.2 全备主库（补课），恢复到从库

主库备份：

[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R --triggers --single-transaction >/tmp/full.sql

从库恢复：

[root@db01 ~]# mysql -S /data/3308/mysql.sock 
 dukang [(none)]>set sql_log_bin=0;
 dukang [test]>source /tmp/full.sql

2.3 从库开启复制

2.3.1 告诉从哪个位置号开始自动开始复制

#告知从库关键复制信息 ip port user  password  binlog position 

[root@db_mha 3307]#vim /backup/full.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=691195;

[root@db01 ~]# mysql -S /data/3308/mysql.sock 
 dukang [(none)]>help change master to
 
db01-[world]>CHANGE MASTER TO
  MASTER_HOST='10.10.40.30',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_POS=691195,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_CONNECT_RETRY=10;
  
  
db01-[world]>start slave;

检查复制状态

db01 [mysql]>show slave  status \G
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

故障分析：

（1）看线程状态

dukang [(none)]>show slave status \G

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

（2）看具体报错

show variables like '%logs%';
Last_IO_Error: error connecting to master '[email protected]:3307' - retry-time: 10  retries: 3

（3）手工模拟连接

[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)

（4）解决
在配置文件中加入配置跳过解析

skip_name_resolve

pkill mysqld 

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &

(5)再次看从库状态

show slave status \G

5.主从故障监控\分析\处理

5.1 线程相关监控
主库:
show full processlist;
每个从库都会有一行dump相关的信息
HOSTS: 
db01:47176
State:
Master has sent all binlog to slave; waiting for more updates
如果现实非以上信息,说明主从之间的关系出现了问题

从库:
show slave status \G;
db01 [(none)]>show slave status \G
*************************** 1. row ***************************
主库有关的信息(master.info):重点关注
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
从库relaylog的信息(已经执行过的relaylog的位置点):重点关注
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
从库两个线程的基本状态:(重点关注)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明:在正常的主从复制关系中,都是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
从库和主库延时的秒数:
Seconds_Behind_Master: 0
分别记录的IO和SQL报错的具体信息(重点关注)
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
延时从库的状态
SQL_Delay: 0
SQL_Remaining_Delay: NULL
GTID复制有关的状态
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0
5.2 主从复制故障分析
5.2.1 IO :

5.2.1.1 连接主库
(1) 用户密码 IP port
Last_IO_Error: error reconnecting to master '[email protected]:3307' - retry-time: 10  retries: 7
[root@db01 ~]# mysql -urepl -p123333 -h 10.0.0.51 -P 3307
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)
原因:
	密码错误 
	用户错误 
	skip_name_resolve

	ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (113)
	地址错误
	端口

处理方法:
	stop  slave  
	reset slave all 
	change master to 
	start slave
(2) 主库连接数上线,或者是主库太繁忙
show slave  staus \G 
Last_IO_Errno: 1040
Last_IO_Error: error reconnecting to master '[email protected]:3307' - retry-time: 10  retries: 7

处理思路:
	拿复制用户,手工连接一下
	[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3307 
	mysql: [Warning] Using a password on the command line interface can be insecure.
	ERROR 1040 (HY000): Too many connections
处理方法:
	db01 [(none)]>set global max_connections=300;
(3) 防火墙,网络不通

5.2.1.2 请求二进制日志

主库缺失日志

从库方面,二进制日志位置点不对
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/data/mysql-bin.000002' at 154, the last byte read from '/data/3307/data/mysql-bin.000002' at 154.'
注意: 在主从复制环境中,严令禁止主库中reset master; 可以选择expire 进行定期清理主库二进制日志
解决方案: 重新构建主从

读取master.info
重写master.info

6.高级功能

半同步            ***
延时从            ***
过滤复制          *****
gtid复制          *****

MySQL 主从复制架构演变

高可用      MHA       *****
读写分离    Atlas     ****
分布式架构  Mycat     ***
扩展项_MGR(5.7.17)    ***

数据库