MySQL主从模式及配置
主服务器：192.168.20.239
从服务器：192.168.20.176
主从原理
主节点
1、当主节点上进行 insert、update、delete 操作时，会按照时间先后顺序写入到 binlog 中；
2、当从节点连接到主节点时，主节点会创建一个叫做 binlog dump 的线程；
3、一个主节点有多少个从节点，就会创建多少个 binlog dump 线程；
4、当主节点的 binlog 发生变化的时候，也就是进行了更改操作，binlog dump 线程就会通知从节点 (Push模式)，并将相应的 binlog 内容发送给从节点；
从节点
当开启主从同步的时候，从节点会创建两个线程用来完成数据同步的工作。
I/O线程： 此线程连接到主节点，主节点上的 binlog dump 线程会将 binlog 的内容发送给此线程。此线程接收到 binlog 内容后，再将内容写入到本地的 relay log。
SQL线程： 该线程读取 I/O 线程写入的 relay log，并且根据 relay log 的内容对从数据库做对应的操作。
主从配置一般都是和读写分离相结合，主服务器负责写数据，从服务器负责读数据，并保证主服务器的数据及时同步到从服务器。
一、配置主服务器：
1：在mysql下找到my.ini文件，一般在 C:\ProgramData\MySQL\MySQL Server 5.7
在其[mysqld] 下添加主服务器端配置：
#服务器 id
server-id=1
#二进制文件存放路径
log-bin=C:\ProgramData\MySQL\MySQL Server 5.7\Data\mysql-bin
#待同步的数据库 （如果这一行没有，那么就是针对所有的不用忽略的数据库进行同步）
#binlog-do-db=test_db
#忽略不同步的数据库，这个可以不用写
#binlog-ignore-db=information_schema
#忽略不同步的数据库，这个可以不用写
#binlog-ignore-db=mysql
注意，上面的mysql-bin 并不是指一个文件夹，而是指 存放为二进制数据
2：保存my.ini文件，重启数据库，然后会在C:\ProgramData\MySQL\MySQL Server 5.7\Data 下看到 mysql-bin.index 文件和 mysql-bin.000001 文件；
3：连接主数据库
mysql -u root -p123456
4：给要连接的从服务器设置权限：(注意下面的 单引号 分号 )
grant replication slave,reload,super on *.* to 'backup'@'192.168.20.%' identified by '123456';
注释： 给主机192.168.20.%添加权限，用户名：backup，密码：123456；（只需输入一次就可以了）
*.* 表示任意数据库中的任意表，'192.168.20.%' 表示只允许192.168.20.··网段的“从”访问“主” 的数据库
如果你把字符，单引号或者是分号漏掉了，那么运行会没有反应的，如果运行成功，会显示 query ok
mysql> use mysql; mysql> select user from user; +---------------+ | user | +---------------+ | test | | backup | | mysql.session | | mysql.sys | | root | +---------------+
#查看用户发现多了backup
实际就是在mysql中添加一个backup的 slave 账号，并授权给从服务器。创建backup用户，并授权给192.168.20.··使用。
5：输入命令 show master status; # 找到File 和 Position 的值记录下来； （如果你不小心把主服务器上的数据库删除了，然后重新添加了一个同名的，但是这里的position 是会改变的，一定要注意）
6: 导出主数据库的数据
mysqldump -u root -p123456 --databases test_db > test_db.mysql
7：把导出的sql语句，导入从数据库
方法一：
mysql>source F:\dir\test_db.mysql
方法二：
先创建DB，然后：mysql -uroot -p123456 test_db < test_db.mysql
这样从服务器上也有了 test_db 这个数据库了
二、配置从服务器：
1：在mysql下找到my.ini文件，在其[mysqld] 下添加从服务器端配置：
#服务器 id ，不能和主服务器一致
server-id=2
#同步的数据库(需要备份的数据库名)，不写本行 表示 同步所有数据库
#replicate-do-db=test_db
#忽略不同步的数据库，这个可以不用写
#binlog-ignore-db=information_schema
#忽略不同步的数据库，这个可以不用写
#binlog-ignore-db=mysql
#忽略不同步的数据库，这个可以不用写
#binlog-ignore-db=test
2：保存my.ini文件，重启从数据库，在mysql5.1以上版本中是不支持1中master设置的，如果添加了master设置，数据库就无法重启了(这个可能是针对以前的低于5.1的数据库的，反正我们没有用到那么低的，所以不用管什么 master设置)；
3：用上面分配的账号backup，尝试连接主数据库，成功
mysql -ubackup -p123456 -h192.168.20.239
4：重新打开一个dos界面，用管理员账号连接从数据库
mysql –uroot –p123456
修改对主数据库的连接的参数：
mysql> change master to master_host='192.168.20.239',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
ps：可能会报一个错误，大概的意思是说slave线程正在运行，不能设置，这样的话，执行mysql> stop slave; 停止slave线程，然后再设置连接的参数；
设置后，可以执行命令查看状态，Master_User: backup 就是刚刚的账号
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.20.239 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-P02RV6K-relay-bin.000019 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: No 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: 1258 Relay_Log_Space: 308 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: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: a2085118-c063-11ec-a4a8-00090faa0001 Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\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: 220508 16:56:41 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
注意：上面的IO线程启动失败了
Slave_IO_Running: No
错误信息 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
原因：一方面是因为网络通信的问题，也有可能是日志读取错误的问题。因为我是重新设置的，所以可能是读取顺序的错误。
解决办法：
在 slave 执行 mysql> stop slave 在 master 执行 mysql> show master status; 刷新日志：mysql> flush logs; 因为刷新日志file的位置会+1，即File变成为:mysqld-bin.000002 马上到 slave 执行 mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002',MASTER_LOG_POS=154; mysql> slave start;
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.20.239 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-P02RV6K-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ******************************************************
Slave_IO_Running: Yes 这个表示：连接到主库，并读取主库的日志到本地，生成本地日志文件. yes表示 网络正常
Slave_SQL_Running: Yes 这个表示：读取本地日志文件，并执行日志里的SQL命令。 yes表示 表结构正常
5：在 master 中添加一行数据，发现 slave 中也自动增加了，并且Position保持一致。
6：使用 show processlist 语句可查看线程状态
主数据库上：可以看到线程Id=5的 State 是 「Master has sent all binlog to slave; waiting for more updates」，说明同步线程一直在运行中。
mysql> show processlist; +----+--------+-----------------------+---------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+-----------------------+---------+-------------+------+---------------------------------------------------------------+------------------+ | 2 | root | localhost:11714 | test_db | Query | 0 | starting | show processlist | | 5 | backup | DESKTOP-P02RV6K:50511 | NULL | Binlog Dump | 2315 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+--------+-----------------------+---------+-------------+------+---------------------------------------------------------------+------------------+
从数据库上：
mysql> show processlist; +----+-------------+-----------------+---------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------------+---------+---------+------+--------------------------------------------------------+------------------+ | 6 | root | localhost:58744 | test_db | Query | 0 | starting | show processlist | | 9 | system user | | NULL | Connect | 2230 | Waiting for master to send event | NULL | | 10 | system user | | NULL | Connect | 858 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------------+---------+---------+------+--------------------------------------------------------+------------------+
三、可能出问题的地方
1：开始一定要把数据库数据同步，保证数据一致性。
2：如果Slave_IO_Running: NO 那么要看看是不是 (1) A有账户在用dos界面在登陆数据库而没有退出 (2) A数据库的postion和file有改变，而B里面change的时候，还是用的以前的数据？
3：主库不小心死机，重启之后发现主库数据更新，但是从库不执行，造成了主从的数据不同步。但是在从上面查看 Slave_IO_Running 和 从库的状态，都是正常的。这个时候要注意在主库看看 show master status 看看postion和file是不是和从库一致的，我就发现我的主库死机之后再次开机就不一样了。这个时候在从库执行
CHANGE MASTER TO MASTER_HOST='192.168.20.239',MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154
但是这里还是有个问题，虽然这个时候，主从可以开始同步了，但是由于这中间，如果不是及时发现，那么主从的数据还是会有差异的，那这个怎么解决呢？ 答案是，只能重新设置主从。
4：如果我在主里面 delete from xx表 那么在从里面，到底是读出主里面删除了哪些行，进而对从进行删除？还是直接也是和主一样，运行了 delete from xx呢？答案是后者，从会像主一样，直接运行 delete from xx 。所以如果你是有上面3这个问题发生（主从数据不同步了），后面再进行了其他操作的话，会越来越不同步的，所以赶紧重新做主从
5：从库挂了怎么办
在主服务的 binlog dump 线程将指定的 binlog 信息发给从服务时，除了日志内容，还包括本次发送内容在主服务端的 bin-log 日志文件名称以及位置信息。
从服务的 I/O 线程接收到信息后将日志内容写入realy-log 文件(mysql-relay-bin.xxxxxx)的末端，并将读取到的主服务端的 bin-log 的文件名和位置记录到 master-info 中（通过 show slave status 中的 Master_Info_File 字段可以看到 master.info 保存的位置），以便下一次读取时能告诉主服务从哪里开始同步。
从服务的 SQL 线程检测到 realy-log 新增了内容后，解析日志文件生成对应的 sql 语句，并应用这些 sql 到数据库，保证主从数据一致性。
所以，即使从库挂掉了，因为有 master.info 记录了上一次同步的位置，只要同步服务再次启动，那就可以从上次同步的位置继续增量同步了。
参考资料
1：公众号[古时的风筝]MySQL主从配置和读写分离 https://www.cnblogs.com/fengzheng/p/13401783.html
2：windows 下mysql 主从库的配置 https://www.cnblogs.com/joeylee/archive/2013/01/24/2875515.html
