MySQL主从同步

1、MySQL安装

参考文章：Centos7部署mysql5.6-CSDN博客

2、主从复制原理

1、Master数据库只要发生变化，就会立即记录到Binary log日志文件中。

2、Slave数据库启动一个I/O thread连接到Master数据库，请求Master变化的二进制文件(binlog)。

3、Slave I/O线程获取到的二进制日志会保存到自己的relay log日志里。

4、Slave 有一个SQL线程会定时检查Relay log是否发生变化，若是变化就更新数据。

3、主从复制应用场景

1、主库出现问题，可以快速切换到从库提供服务。

2、实现读写分离，降低主库的访问压力。

3、可以在从库备份，以避免备份期间影响主库服务。

4、环境准备和搭建

操作系统版本：CentOS Linux release 7.9.2009 (Core)

MySQL版本：5.7.44

主机	IP	mysql用户	mysql密码
mysql-master	192.168.7.67	root	Aa123456.
mysql-salve	192.168.7.77	root	Aa123456.

4.1、master数据库配置

[root@docker mysql]# vim my.cnf

增加一下内容：

#二进制日志
server_id=01
log_bin=/data/mysql/binlogs/mysql-bin
binlog_format= mixed
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=10m	    #单个文件最大值
expire_logs_days = 10	#过期时间，0为不清理
read-only=0		        #1代表只读，0代表读写
#忽略同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

#重启mysql服务
[root@docker mysql]# systemctl restart mysql

4.2、master添加同步账户和授权

[root@docker mysql]# mysql -uroot -p

mysql> create user 'slave'@'%' identified by 'Aa123456.';
mysql> grant replication slave on *.* to 'slave'@'%' identified by 'Aa123456.';
mysql> flush privileges;
# 查看主库的状态
mysql> show master status;

说明：
replication 复制权限，也可以是all；
slave 表示从库；
*. 表示同步所有数据库，也可以指定某一个库；
slave 用户仅是mysql数据库的一个认证用户；
% 全网都能登陆访问，可指定也可以设置成某一个网段都可以访问。

4.3、slave数据库配置

配置文件与master数据库的配置一致，改下slave数据库配置里的server-id=02。

slave服务器执行：

[root@docker data]# mysql -uroot -p

mysql> change master to master_host='192.168.7.67',master_user='slave',master_password='Aa123456.',master_port=3306,master_log_file='mysql-bin.000009',master_log_pos=836;

mysql> start slave;

#查看slave状态
mysql> show slave status\G;

 说明：

master_host='192.168.7.67'            # 指定master数据库地址

master_user='slave'                         # 指定连接用户

master_password='Aa123456.'       # 指定连接密码

master_port=3306                           # 指定连接端口（端口切记不可加引号）

master_log_file='mysql-bin.000009' #在master数据库通过show master status查看File

master_log_pos=836;                       #master数据库通过show master status查看position

4.4、slave是否正常连接master数据库

使用show slave status\G，查看Slave_IO_Running和Slave_SQL_Running两个属性值为YSE，就可以了 。

4.5、显示Slave_IO_Running：NO

报错：

1、Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be diff

原因：是因为我的slave数据库是直接复制master数据库的，导致server_uuid是相同的。

解决：在slave数据库的data目录里找到auto.cnf，修改server_uuid即可，修改后需重启MySQL服务

2、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’。

解决：

1、主从数据库数据库需要先手动同步，如果是一致的可以忽略

2、执行以下语句：

mysql> stop slave;
mysql> reset slave;
#有时候之行为reset slave,需要手动去删除Relay-log
mysql> start slave;

5、主从数据同步（如果主从都是新安装的，可以省略这步）

5.1、将主服务器要同步的数据库加锁，避免同步时发生改变:

>use database_name;
>flush tables with read lock;

5.2、使用mysqldump工具导出数据:

mysqldump -uroot -pxxx database_name >database_name.sql

5.3、将初始数据导入从数据库:

>create database database_name;
>use database_name;
>source database_name.sql;

5.4、导入完成后，解锁数据库:

>unlock tables;

6、验证主从复制

在主库创建一个名为school的数据库。

在从库可以查看已经同步过来。

7、show slave status参数详解

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.7.67
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 967
               Relay_Log_File: docker-relay-bin.000013
                Relay_Log_Pos: 1180
        Relay_Master_Log_File: mysql-bin.000012
             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: 967
              Relay_Log_Space: 1601
              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: 05d523a3-1bce-11ef-8170-000c29ea25c6
             Master_Info_File: /data/mysql/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: 
     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)

Slave_IO_State	slave连接到master的状态 常见状态: 1、waiting for master to send event 说明：已经成功连接到master，正等待二进制日志时间的到达 2、reconnecting after a failed master event read 说明：/O线程正尝试重连master 3、waiting for the slave sql thread to free enough relay log space 说明：relay log的大小已经整张到了最大值，等待释放relay log的空间 4、connecting to master 说明： I/O线程正尝试连接到master
Connect_Retry	连接中断后，重新尝试连接的时间间隔。默认值是60秒
Master_Log_File	当前I/O线程正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos	当前I/O线程正在读取的二进制日志的位置
Relay_Log_File	当前slave SQL线程正在读取并执行的relay log的文件名
Relay_Log_Pos	当前slave SQL线程正在读取并执行的relay log文件中的位置
Relay_Master_Log_File	当前slave SQL线程读取并执行的relay log的文件中多数近期事件，对应的主服务器二进制日志文件的名称
Slave_IO_Running	I/O线程是否被启动并成功地连接到主服务器上
Slave_SQL_Running	SQL线程是否被启动
Replicate_Do_DB   Replicate_Ignore_DB   Replicate_Do_Table   Replicate_Ignore_Table   Replicate_Wild_Do_Table   Replicate_Wild_Ignore_Table	这些参数都是为了用来指明哪些库或表在复制的时候不要同步到从库；一般情况下 ，限制的时候都用Replicate_Wild_Ignore_Table这个参数；
Last_Errno	错误数量为0并且消息为空字符串表示没有错误
Skip_Counter	用于设置跳过sql执行步数
Exec_Master_Log_Pos	slave SQL线程当前执行的事件，对应在master相应的二进制日志中的position
Relay_Log_Space	所有原有的中继日志结合起来的总大小
Until_Condition   Until_Log_File   Until_Log_Pos	在START SLAVE语句的UNTIL子句中指定的值
Master_SSL_Allowed   Master_SSL_CA_File   Master_SSL_CA_Path   Master_SSL_Cert    Master_SSL_Cipher   Master_SSL_Key   Master_SSL_Verify_Server_Cert   Master_SSL_Crl   Master_SSL_Crlpath	这些字段显示了被从属服务器使用加密相关的参数。这些参数用于连接主服务器
seconds_Behind_Master	slave当前的时间戳和master记录该事件时的时间戳的差值
Last_IO_Errno   Last_IO_Error   Last_SQL_Errno   Last_SQL_Error	最后一次I/O线程或者SQL线程的错误号和错误消息
Replicate_Ignore_Server_Ids	主从复制，从库忽略的主库服务器Id号。就是不以这些服务器Id为主库
Master_Server_Id   Master_UUID   Master_Info_File	分别表示主库服务器id号，主库服务器的UUID好，还有在从库中保存主库服务器相关的目录位置
SQL_Delay	Slave滞后多少秒于master
SQL_Remaining_Delay	表示有多少秒左右的延迟
Slave_SQL_Running_State	SQL线程运行状态：         1) Reading event from the relay log         线程已经从中继日志读取一个事件，可以对事件进行处理了         2) Has read all relay log; waiting for the slave I/O thread to update it         线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志          3) Waiting for slave mutex on exit         线程停止时发生的一个很简单的状态
Master_Retry_Count	连接主库失败最多的重试次数
Master_Bind	slave从库在多网络接口的情况下使用，以确定用哪一个slave网络接口连接到master
Last_IO_Error_Timestamp   Last_SQL_Error_Timestamp	最后一次I/O线程或者SQL线程错误时的时间戳