MySQL5.7 主从同步环境搭建

太过爱你忘了你带给我的痛 2023-06-25 08:15 4阅读 0赞

### 一、准备环境：(一主一从) ###

##  1.1 两台虚拟机均安装MySQL5.7。（mysqladmin --version） ##

![2019122714321888.png][]

**参考：**

1.  [Linux Centos7.5 安装MySQL5.7][Linux Centos7.5 _MySQL5.7]
2.  [mysql 5.7连接不上：Host '192.168.87.1' is not allowed to connect to this MysQL server][mysql 5.7_Host _192.168.87.1_ is not allowed to connect to this MysQL server]
3.  [Mysql--Host 'xxx' is blocked because of many connection errors;unblock with 'mysqladmin flush-hosts'][Mysql--Host _xxx_ is blocked because of many connection errors_unblock with _mysqladmin flush-hosts]

###   1.2成功安装后，创建两个数据库。 ###

## ![20191227142324766.png][] ##

### 数据库脚本： ###

(1)创建数据库
    #创建数据库user_db
    CREATE DATABASE `user_db` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
    
    (2)在user_db中创建t_user表
    DROP TABLE IF EXISTS `t_user`;
    CREATE TABLE `t_user` (
    `user_id` bigint(20) NOT NULL COMMENT '用户id',
    `fullname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名',
    `user_type` char(1) DEFAULT NULL COMMENT '用户类型',
    PRIMARY KEY (`user_id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

## 2.配置 ##

### ** 2.1主库配置：** ###

**vim  /etc/my.cnf**

**注意：（错误配置）**

**网上很多博客是如下这么配置的。我刚开始也是这么配置的，配置完毕，重启mysql是启动不了。**

[mysqld]
    #开启日志
    log‐bin = mysql‐bin
    #设置服务id，主从不能一致
    server‐id = 1
    #设置需要同步的数据库
    binlog‐do‐db=user_db
    #屏蔽系统库同步
    binlog‐ignore‐db=mysql
    binlog‐ignore‐db=information_schema
    binlog‐ignore‐db=performance_schema

**提示使用systemctl status mysqld.service和journalctl -xe查看错误信息；**

![2019122713220139.png][]

**并且使用命令：cat /var/log/mysqld.log 查看mysql日志，会报如下类似错误： **

![20191227132406799.png][]

2019-12-25T13:34:26.236167Z 0 [ERROR] unknown variable 'binlog‐do‐db=user_db'
    2019-12-25T13:34:26.236192Z 0 [ERROR] Aborting

**正确配置（完整配置）：（不是使用-，而改用\_）**

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    
    [mysqld]
    
    validate_password=off
    max_connect_errors=1000
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    
    ##################主从配置的新增部分
    #开启日志文件
    log_bin=mysql-bin
    #设置服务id，主从不能一致
    server_id=1
    #设置需要同步的数据库
    binlog_do_db=user_db
    ##屏蔽系统库同步
    binlog_ignore_db=mysql
    binlog_ignore_db=information_schema
    binlog_ignore_db=performance_schema
    
    
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

**按照正确配置完后，重启mysql服务；使用命令：systemctl restart mysqld**

![20191227134346385.png][]

### **2.2从库配置：****（完整配置)** ###

[mysqld]
    
    validate_password=off
    #
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    #
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    
    #开启日志
    log_bin=mysql‐bin
    #设置服务id，主从不能一致
    server_id=2
    #设置需要同步的数据库
    replicate_wild_do_table=user_db.%
    #屏蔽系统库同步
    replicate_wild_ignore_table=mysql.%
    replicate_wild_ignore_table=information_schema.%
    replicate_wild_ignore_table=performance_schema.%
    
    
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

**按照正确配置完后，重启mysql服务；使用命令：systemctl restart mysqld**

![20191227134346385.png][]

### **2.3 查看log\_bin是否成功开启 （主、从库都可查询下）** ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70][]

## 3.**创建用于复制的用户（主库）** ##

#切换至主库bin目录，登录主库，输入密码
    mysql ‐uroot ‐p 
    
    #授权主备复制专用账号
    grant replication slave on *.* to 'slave'@'%' identified by '123456';
    
    #刷新权限
    FLUSH PRIVILEGES;
    
    #确认位点 记录下文件名以及位点
    show master status;

**截图： （用命令show master status;查出的东西，后面从库配置需要用到****）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1][]

### 4.设置从库向主库同步数据、并检查链路 ###

#切换至从库bin目录，登录从库（输入密码）
    mysql ‐uroot ‐p
    
    #先停止同步
    STOP SLAVE;
    
    #修改从库指向到主库，使用上一步记录的文件名以及位点
    CHANGE MASTER TO
    master_host = '192.168.87.133',
    master_user = 'slave',
    master_password = '123456',
    master_log_file = 'mysql-bin.000009',
    master_log_pos = 582;
    
    #启动同步
    START SLAVE;
    
    #查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功，如果不为Yes，请检error_log，#然后排查相关异常。
    show slave status\G
    
    ######（一般不需要执行）注意： 如果之前此备库已有主库指向 需要先执行以下命令清空#########
    STOP SLAVE IO_THREAD FOR CHANNEL '';
    reset slave all;

### ps: ###

<table style="width:621px;"> 
 <tbody> 
  <tr> 
   <td><strong>master_host</strong></td> 
   <td style="width:488px;">master主节点:ip地址</td> 
  </tr> 
  <tr> 
   <td><strong>master_user</strong></td> 
   <td style="width:488px;">master主节点:主节点创建的用于复制的账号</td> 
  </tr> 
  <tr> 
   <td><strong>master_password</strong></td> 
   <td style="width:488px;">master主节点:主节点创建的用于复制的密码</td> 
  </tr> 
  <tr> 
   <td><strong>master_log_file</strong></td> 
   <td style="width:488px;">master主节点：sql命令show master status查询出来的file字段的值</td> 
  </tr> 
  <tr> 
   <td><strong>master_log_pos&nbsp;</strong></td> 
   <td style="width:488px;">master主节点：sql命令show master status查询出来的Position字段的值</td> 
  </tr> 
 </tbody> 
</table>

**完整截图：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2][]

**ps:查看从库状态Slave\_IO\_Runing和Slave\_SQL\_Runing都为Yes说明同步成功，如果不为Yes，请检查error\_log，然后 排查相关异常。**

## 5.测试： ##

**在主库（192.168.87.133）中t\_user表中插入一条数据；然后刷新从库t\_user表，查看是否同步过来了数据。**

**5.1主库**：t\_user

![20191227142828289.png][]

**5.2从库：t\_user **（刷新表）

![20191227142820845.png][]

[2019122714321888.png]: https://img-blog.csdnimg.cn/2019122714321888.png
[Linux Centos7.5 _MySQL5.7]: https://blog.csdn.net/qq_37495786/article/details/83538614
[mysql 5.7_Host _192.168.87.1_ is not allowed to connect to this MysQL server]: https://blog.csdn.net/qq_37495786/article/details/103186100
[Mysql--Host _xxx_ is blocked because of many connection errors_unblock with _mysqladmin flush-hosts]: https://blog.csdn.net/qq_37495786/article/details/85046302
[20191227142324766.png]: https://img-blog.csdnimg.cn/20191227142324766.png
[2019122713220139.png]: https://img-blog.csdnimg.cn/2019122713220139.png
[20191227132406799.png]: https://img-blog.csdnimg.cn/20191227132406799.png
[20191227134346385.png]: https://img-blog.csdnimg.cn/20191227134346385.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019122713454898.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019122713502715.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191227140749344.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9sdWNpZmVyLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70
[20191227142828289.png]: https://img-blog.csdnimg.cn/20191227142828289.png
[20191227142820845.png]: https://img-blog.csdnimg.cn/20191227142820845.png