一、什么是Mysql主从同步
将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上,然后将这些日志重新执行(重做);从而使得从数据库的数据与主数据库保持一致。
基本原理:
二、主从复制的作用
三、释义图
Binary log:主数据库的二进制日志。
Relay log:从服务器的中继日志。
第一步:master在每个事务更新数据完成之前,将该操作记录串行地写入到binlog文件中(主)。
第二步:salve开启一个I/O Thread,该线程在master打开一个普通连接,主要工作是binlog dump process。如果读取的进度已经跟上了master,就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中(从)。
第三步:SQL Thread会读取中继日志,并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致(从)。
四、主从配置实操
1、准备两台Mysql服务器(主:192.168.2.64,从:192.168.2.34),并满足以下要求
2、修改服务器Mysql配置,并重启Mysql服务,Linux下为 /etc/my.cnf ,Windows下为 my.ini 具体位置根据安装时设置为准。
master
[mysqld] #设置主服务器id,在主从关系中保持唯一 server-id=1 #设置同步哪个数据库,此数据库变更将操作写入二进制日志Binary log binlog-do-db=bds-oneaccept #要生成的二进制日志文件名称前缀,例如:mysql-bin.000001 log-bin=mysql-bin
slave
[mysqld] #设置从服务器id,在主从关系中保持唯一 server-id=2 #要复制的数据库名称,与主Mysql配置保持一致 replicate-do-db=bds-oneaccept #要生成的二进制日志文件名称前缀,例如:mysql-bin.000001 log-bin=mysql-bin
3、创建用于复制的专门用户,并刷新用户权限(主)
grant replication slave on *.* to 'jianguan'@'192.168.2.34' indentified by 'Spgtest_1';
flush privileges;
4、获取主数据库此刻数据坐标,用于主从启动后,复制数据的起始位置,获取到这个值后,主数据库就不能再有数据的修改操作(主)。
show master status;
5、在从数据库中设置复制数据的用户、坐标等信息(从),在执行此操作之前需保证主从数据库现时刻数据一致,因为是从设置的坐标处开始复制。
mysql> mysql> change master to master192.168.2.64', -> master_port=3306, -> master_user='jiangkun', -> master_password='Spgtest_1', -> master_log_file='mysql-bin.000001', -> master_log_pos=23431;
如果执行出现如下报错,则表示当前主从正在进行,需要先stop slave;再设置上述命令。
6、启动复制
start slave;
7、开始启动后,需要查看从Mysql复制状态
注意:其中Slave_IO_Running、Slave_SQL_Running两项为YES时,表示同步正在进行。
8、做简单测试,在主Mysql创建表,并插入数据,在从表查看,表已建,数据也已插入,主从同步成功!
9、查看主库数据坐标,已到mysql-bin.000001文件的24587坐标。
10、从库的执行位置表示同步已经完成,至此主从同步设置完成。
11、理解从库中的同步状态
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.64 //主库的ip地址 Master_User: jianguan //master上的一个用户,用来主从复制,创建主从复制的时候建立,具有reolication Master_Port: 3306 Connect_Retry: 60 //连接中断后,重新尝试连接的时间间隔,默认值60s Master_Log_File: mysql-bin.000001 //当前I/O线程正在读取的主服务器二进制日志文件的名称 Read_Master_Log_Pos: 24587 //当前I/O线程正在读取的二进制日志的位置 Relay_Log_File: data-anal-relay-bin.000002 //SQL线程当前正在读取和执行的中继日志文件的名称 Relay_Log_Pos: 1476 //当前已读取和执行的中继日志的位置 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes //YES表示I/O线程和主库连接正常并能实施复制工作,No则说明与主库通讯异常,一般只要网络端口通,复制所用用户名、秘密正常,很少出问题 Slave_SQL_Running: Yes //YES表示正常,非常容易出问题,比如手动在从表插入数据,主复制到从主键冲突就会导致同步终止 Replicate_Do_DB: bds-oneaccept //复制的数据库 Replicate_Ignore_DB: //复制中忽略的数据库 Replicate_Do_Table: //复制的表 Replicate_Ignore_Table: //复制中要忽略的表 Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 //slave的SQL线程读取日志参数的错误数量 Last_Error: //错误消息 Skip_Counter: //用于设置跳过sql执行步数。 Exec_Master_Log_Pos: 24587 //slave SQL线程当前执行的事件,对应在master相应的二进制日志中的position。(结合Relay_Master_Log_File理解,而且在Relay_Master_Log_File这个值等于Master_Log_File值的时候,Exec_Master_Log_Pos是不可能超过Read_Master_Log_Pos的。) Relay_Log_Space: 1687 //所有原有的中继日志结合起来的总大小。 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 //这个值是时间戳的差值。是slave当前的时间戳和master记录该事件时的时间戳的差值。 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 //最后一次IO错误号 Last_IO_Error: //最后一次IO错误消息 Last_SQL_Errno: 0 //最后一次SQL错误号 Last_SQL_Error: //最后一次SQL错误消息 Replicate_Ignore_Server_Ids: Master_Server_Id: 1 //主库服务器id号 Master_UUID: cc63533c-b9b5-11ea-83e9-000c29ae6191 //主库服务器的UUID Master_Info_File: /var/lib/mysql/master.info //从库中保存主库服务器相关的目录位置 SQL_Delay: 0 //一个非负整数,表示秒数,slave滞后于master多少秒 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 //连接主库失败最多的重试次数 Master_Bind: //slave从库在多网络接口的情况下使用,以确定用哪一个slave网络接口连接到master Last_IO_Error_Timestamp: //最后一次I/O线程错误时的时间戳。 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.01 sec)