mysql主从基础
1.概念
MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。
主从复制的逻辑有以下几种：
一主一从：单向主从同步模式，只能在Master端写入数据。
一主多从：提高系统的读性能。
双主复制：此架构可以在Master1或Master2进行数据写入，或者两端同时写入（特殊设置）。
多主一从（MySQL5.7开始支持）
级联复制
为什么mysql要设置主从复制？
- 数据库数据是一个公司或者集团企业最为重要的资产，以防数据的丢失和损坏，需要进行备份
- 当用户的访问量越来越高的时候，一旦查询也就是读取数据的操作太频繁了，势必网站崩掉，服务器宕机，很影响用户的体验度
- 提高数据库系统的可用性
2.mysql主从复制原理
- 主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
- 从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
从服务器slave为什么不能直接存储二进制日志文件里面的数据（为什么不开启bin-log日志）？
本来做数据的主从同步就是为了让计算机快速的进行读写操作，而且是大批量的数据，一旦大量数据进行写入或者更新数据，从数据库slave如果直接从二进制日志来接收，数据是以队列形式进行传输的，若队列的数据没有快速处理，堆积起来，从服务器可能也会崩溃宕机，所以从性能上考虑，从服务器slave创建了I/O线程对象将数据转到中继日志，起个缓存功能。
过程分析
- 在从库上执行change master to；会将主库的信息保存到从库中的master.info文件中
- 在从库执行start slave;开启io_thread, sql_thread;
- io_thread工作；io_thread通过master.info文件中主库的连接信息去连接主库；连接成功后主库就会开启dump_thread;
- dump_thread读取主库新产生的二进制日志；然后投递给io_thread；
- io_thread接收dump_thread投递的新的二进制日志，将日志写入到relay log(中继日志)；
- io_thread就会等待主库dump_thread主动把新产生的二进制日志投递；
- sql_thread会将relay log新产生的日志恢复到数据库（relay重放），写到磁盘
2.1主从复制模式
MySQL主从复制有异步模式、半同步模式、GTID模式以及多源复制模式，MySQL默认模式是异步模式。所谓异步模式，是指MySQL主服务器上I/O thread 线程将二进制日志写入binlog文件之后就返回客户端结果，不会考虑二进制日志是否完整传输到从服务器以及是否完整存放到从服务器上的relay日志中，这种模式一旦主服务(器)宕机，数据就会发生丢失。但是能保证效率。
异步复制：
3.主从状态详解
show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.190.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 194
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000011
Slave_IO_Running: Yes
Slave_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_Master_Log_Pos: 194
Relay_Log_Space: 705
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 77cf5494-00ce-11ec-9c86-000c290c7222
Master_Info_File: /data/mysqldata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 77cf5494-00ce-11ec-9c86-000c290c7222:1-4
Executed_Gtid_Set: 77cf5494-00ce-11ec-9c86-000c290c7222:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Master_Host: 192.168.190.128 #主库ip
Master_User: repl #主库用户
Master_Port: 3306 #主库端口号
Master_Log_File: mysql-bin.000011 #当前从库io_thread正在读取主库的二进制日志文件
Read_Master_Log_Pos: 194 #当前从库io_thread正在读取主库二进制日志文件的位置
Relay_Log_File: localhost-relay-bin.000004 #当前从库sql_thread正在读取从库的中继日志文件
Relay_Log_Pos: 407 #当前sql_thread正在读取从库中继日志文件的位置
Relay_Master_Log_File: mysql-bin.000011 #当前从库sql_thread从relay log中读取的正在进行的sql语句，对应主库的sql语句是在哪个binlog中；
Exec_Master_Log_Pos: 194 #从库sql_thread当前执行的事件，对应主库的binlog中的position
Seconds_Behind_Master: 0 #主从复制延迟的时间；如果是0表示主从无延迟
Last_IO_Error #显示io线程错误信息
Last_SQL_Error #显示sql线程错误信息
SQL_Delay: 0 #延迟复制
Retrieved_Gtid_Set: #当前io_thread已经接受到的binlog
Executed_Gtid_Set: #当前从库sql_thread执行的二进制日志位置
4.主从所需前提
前提条件：
-
两个实例不同的server_id
-
主库开启binlog
-
主库创建replication slave权限的用户
例如：
Master主机配置：
cat /home/data/mysql3306/my.cnf
server_id = 1
log_bin = /home/data/mysql3306/mysql-bin
binlog_format=MIXED
上面两个是必须配置的，其他参数根据自己的MySQL安装目录和业务情况自行配置
Slave主机配置：
cat /home/data/mysql3306/my.cnf
server_id = 2
Slave主机的binlog不是必须开启的，其他参数根据自己的MySQL安装目录和业务情况自行配置，如果有级联复制的需求，才进行开启，一般主从架构不开启，以节省磁盘I/O
5.主从配置示例
两个数据库实例
192.168.190.128（主）
192.168.190.128（从）
开始前，一定要确保主从数据一致
如果主从库数据差距大，先把主库的备份文件在从库上恢复，在change master时指定备份的pos的偏移量和使用的binlog文件
5.1配置文件
主：
server_id = 1
log_bin=mysql-bin
从：
server-id=2
5.2主库创建一个用于复制的账号
create user 'repl'@'192.168.190.129' identified by '123456';
grant replication slave on *.* to 'repl'@'192.168.190.129';
#或者写成一句
grant replication slave on *.* TO 'repl'@'192.168.190.129' identified by '123456';
#刷新权限
flush privileges;
5.3从库启动主从复制
mysql> help change master to;
CHANGE MASTER TO
MASTER_HOST='master2.example.com',
MASTER_USER='replication',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
配置并开启主从
因为没有都是数据的新库，直接从头开始
gtid:
CHANGE MASTER TO
MASTER_HOST='192.168.190.128',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
master_auto_position=1,
MASTER_CONNECT_RETRY=10;
start slave;
#要使用`master_auto_position=1,`必须开启gtid,否则
show slave status \G; 查看从库状态
MASTER_CONNECT_RETRY*连接*失败*重试*的时间间隔，单位为秒
如果是有数据的，从库恢复全备之后，找到全备文件的binlog文件和偏移量，或者在锁库时show master status;，稍稍更改即可
传统主从：
CHANGE MASTER TO
MASTER_HOST='192.168.190.128',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql_bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
6.常用指令
start slave;
#开启io_thread和sql_thread
start slave io_thread;
start slave sql_thread;
stop slave;
#关闭io_thread和sql_thread
stop slave io_thread;
stop slave sql_thread;
#查看进程
show processlist;
#删除master.info,relay-log.info数据；删除所有relay log；将延迟选项master_delay设为0；
reset slave;
#删除所有的二进制日志文件
reset master;
show binary logs; #显示系统中的所有二进制日志。
show master status; #正在使用的binlog
7.主从状态中断的一些可能性
从库复制中断要考虑两个线程：io_thread和sql_thread
两个都是yes才表明正常
io_thread：连接主库，接收binlog，写入relay log，更新master.info
sql_thread：主要因为逻辑不满足sql执行，sql_thread回放是逻辑操作
常见原因：
-
io_thread中断，多半是与主库连接问题，或是用于从库复制的账号权限问题
-
sql_thread中断，常见原因大多是主从数据不一致
例子：主库插入一条数据，但是从库已经有了这条数据，报错，sql线程中断了
解决方案，从库找到这条数据，删除（或者重新构建主从）
set sql_log_bin=0;
delete from t1 where id=1;
set sql_log_bin=1;
start slave sql_thread;
