MYSQL5.7.28 本地 CentOS7和Windows10 主从同步搭建

1 前置准备：

数据库版本：

mysql 5.7.28

系统信息：

主库 master 本地虚机 CentOS7  192.168.146.3

从库  slave 本地电脑 windows10  192.168.199.221

2 master主库配置（在Centos7操作）

2.1 编辑主库配置文件

 vim /etc/my.cnf
server-id=1 #设置主服务器的ID(不能和别的服务器重复，建议使用ip的最后一段)
innodb_flush_log_at_trx_commit=2  #可以配置的值：0/1/2; 0、效率最高，最不安全；1、最安全，但是效率最低；2、安全和效率平衡的取舍，在服务器系统挂掉的情况下会丢失数据；
sync_binlog=1  #，值可设置 1、500、1000；可自己根据测试性能配置
log-bin=mysql-bin  #binlog日志文件名
binlog-ignore-db=mysql # 表示不同步mysql库
binlog-ignore-db=information_schema # 表示不同步information_schema库
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-do-db=xxxx  # 这个表示只同步某个库 （如果没有此项，表示同步所有的库）

2.1 创建主从同步账号

#登录master MySQL 
$ mysql -u root -p    
mysql> create user 'Sync'@'%' identified by 'Sync@0000'; # 5.7要求密码必须含有大小写英文，符号和数字
mysql> grant FILE on *.* to 'Sync'@'192.168.199.221' identified by 'Sync@0000'; #赋予FILE权限，允许从从库ip访问主库二进制日志文件bin-log
mysql> grant replication slave on *.* to 'Sync'@'192.168.199.221' identified by 'Sync@0000'; #赋予主从同步权限
mysql> flush privileges;

2.2 重启master MySQL，使my.cnf 配置生效；查看主库状态：

$ service mysqld restart #重启MySQL
mysql -u root -p
mysql> show master status; #查看主库的状态  file,position 这两个值很有用，记一下;需要放到slave配置中
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000007 |      154 |              | mysql,information_schema,sys,performance_schema |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

3 slave 从库配置(Windows10 上操作）

 3.1 假如从库位于Linux系统 ，本次搭建没有涉及，贴上参考。

# 从库位于Linux系统的配置信息

vim /etc/my.cnf

server-id=111 
#log-bin=mysql-bin #从库提高性能可以不开bin-log日志
replicate-ignore-db=mysql  #配置不需要复制的库mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
replicate_do_db=python #标记出需要同步的数据库名,在多从配置时可以按需配置
innodb_flush_log_at_trx_commit=2 # 可以配置的值：0/1/2; 0、效率最高，最不安全；1、最安全，但是效率最低；2、安全和效率平衡的取舍，在服务器系统挂掉的情况下会丢失数据；
sync_binlog=1000 # 每进行n次事务提交之后，MySQL将binlog_cache中的数据强制写入磁盘。
slave_parallel_workers=4 #根据实际情况决定开启多少个线程用于主从复制
slave_parallel_type=logical_clock #基于组提交的并行复制方式
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

  3.2 此次搭建从库位于Windows10 

   进入到MySQL的安装目录找到my.ini文件 打开

   Note:  这server-id要写在默认字符编码上面
    否则报错 mysql: [ERROR] unknown variable 'server-id=2'
    参考：https://blog.csdn.net/zcbdandan/article/details/89500578

[mysqld]
port = 3306
basedir=D:/Program Files/mysql-5.7.28-winx64
datadir=D:/Program Files/mysql-5.7.28-winx64/data
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# server-id  

server-id=2 
[mysql]
default-character-set=utf8

键盘：win+r 输入services.msc 进入服务 右键重启MySQL服务

3.3  进入Windows10从服务器数据库进行配置

$ mysql -u root -p #登录slave mysql
mysql> stop slave; #关闭从库
mysql> change master to master_host='192.168.146.3', master_user='Sync' ,master_password='Sync@0000', master_log_file='mysql-bin.000007' ,master_log_pos=154; #配置主库信息
mysql> start slave; #开启从库 
mysql> show slave status \G; 

# Slave_IO_Running,Slave_SQL_Running 都为Yes的时候表示配置成功；
# 可以在主库上对数据进行操作,再在从库上刷新是否同步;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.146.3
                  Master_User: Sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 437
               Relay_Log_File: LAPTOP-BI7VE0KG-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000007
             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: 437
              Relay_Log_Space: 986
              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: 110
                  Master_UUID: 12365c61-1015-11ea-8c51-000c29023405
             Master_Info_File: D:\Program Files\mysql-5.7.28-winx64\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)

ERROR:
No query specified

mysql>

上图 是搭建成功的信息

4 Troubleshooting：

4.1 Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'Sync'@'%' (using passwo

 分析：权限问题

mysql> show grants for 'Sync'@'%';
+----------------------------------------------+
| Grants for Sync@%                            |
+----------------------------------------------+
| GRANT USAGE SLAVE ON *.* TO 'Sync'@'%' |
+----------------------------------------------+

Solution：登陆主库修改权限 然后登陆从服务器 stop slave，然后start salve

mysql> GRANT REPLICATION SLAVE ON *.* TO  'Sync'@'%' identified by 'Sync@0000';
mysql> show grants for 'Sync'@'%';
+----------------------------------------------+
| Grants for Sync@%                            |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'Sync'@'%' |
+----------------------------------------------+

4.2 网络不通  主从库IP是否互相ping得通

4.3.账户密码错误

  本次搭建过程就把同步的账户密码写错了，所以是连接不上主服务器的，修改密码也麻烦，直接drop user，新增一个用户。
4.4 防火墙是否关闭 

4.5 mysql配置文件问题 

  server-id 设置有规律，主从要不一样。
4.6 pos不正确 

     设置从库的位置信息一定要和主服务器下show master status bin-log的名称和位置信息完全一致，要不从服务器 
     slave_IO线程读取不到主服务器bin-log,它存着主库的数据更改操作记录。