Mysql实现主从复制

1)、准备工作:

​ 1、两台主机或者俩个不同的mysql实例(这里使用的docker),但是要保证版本一致,不一致会出现一些问题。

​ 2、要能找到到两台mysql实例所对应的配置文件地址,Linux的配置文件名–my.cnf、Windows的配置文件–my.ini。

​ 3、要明确哪个是主库,哪个是从库,因为它们所做配置不一样。

2)、docker安装运行mysql

1、安装运行mysql

采用的虚拟机,linux发行版本为centos

  • 查询对应的mysql8的实例

    docker search mysql8
    
  • 以上命令打印内容

    [root@localhost mysql]# docker search mysql8
    NAME                                  DESCRIPTION                           STARS     OFFICIAL 
    justusqiao/mysql8                        mysql8.0.13-1debian9                  1                    
    cnbbx/mysql8                             来自官方镜像,自行配置                                   1         
    codesmithtech/mysql8                     Simple MySQL 8 container              1                    [OK]
    abkrim/mysql8_legacy_password                                                     1          
    
  • 需要安装一个mysql的镜像

    docker pull justusqiao/mysql8
    #等待装完即可
    
  • 找到刚安装的mysql8的id

    docker images
    
  • 以上命令打印内容

                                                 ↓   ID
    ->  mysql                          8         c60d96bd2b77   7 days ago      514MB
        zookeeper                      3.4       4b03fe5b3f64   2 months ago    260MB
        redis                          5         59fbd83692ee   2 months ago    98.4MB
        mysql                          latest    c0cdc95609f1   2 months ago    556MB
        portainer/portainer            latest    580c0e4e98b0   4 months ago    79.1MB
        drone/drone                    1         112b23978e25   7 months ago    67.3MB
    ->  cnbbx/mysql8                   latest    682818f5a0d3   16 months ago   465MB
    
  • 运行主库(master)

    docker run -p 3310:3306 --name mysql_master -v /mysql/master/mysql:/etc/mysql -v /mysql/master/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=123456 -d c60d96bd2b77
    

    -p 3310:3306 后者是docker的端口,前者是自己主机的端口,如果都是在同一主机,主库端口不能和从库一致

    –name 给自己容器取名,无所谓,唯一就好
    -v /mysql/master/mysql:/etc/mysql 前者路径为本地配置文件路径,之后要用到。后者路径为容器内部路径
    -v /mysql/master/mysql-files:/var/lib/mysql-files/ 该路径也需要做映射,不然运行容器会闪退
    -e MYSQL_ROOT_PASSWORD=123456 为mysql的密码,自行设计就好
    -d 后台运行的作用,后面接着的就是docker images查出来的id

  • 运行从库(slave)

    docker run -p 3312:3306 --name mysql_slave -v /mysql/slave/mysql:/etc/mysql -v /mysql/slave/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=123456 -d c60d96bd2b77
    

    -p 3312:3306 主机端口不能和主库一致
    注意/var/lib/mysql-files/该路径一定要映射,不然会报错

  • 查看容器是否在运行

     docker ps
    

    找到自己刚刚取名的–name 如果STATUS为 Up 49 minutes 则为正在运行

    以下为打印内容 状态为正在启动.

    CONTAINER ID   IMAGE          COMMAND          CREATED       STATUS            PORTS          NAMES                                                                                             
    9ee812727c44   c60d96bd2b77  "docker-entrypoint.s…"   About an hour ago   Up 49 minutes           33060/tcp, 0.0.0.0:3310->3306/tcp, :::3310->3306/tcp         mysql_master
    0bd88c58bde0   682818f5a0d3   "docker-entrypoint.s…"   About an hour ago   Up 49 minutes  33060/tcp, 0.0.0.0:3312->3306/tcp, :::3312->3306/tcp        mysql_slave
    
2、自己遇到的一些问题(没出错可略过)

如果没有找到自己对应运行的容器,使用docker ps -a 查询所有的容器

docker ps -a 
  • 找到对应的ID 尝试再次启动

    docker start xx
    

    **STATUS为 Exited (127) 4 weeks ago(类似这种就是异常退出) **

  • 使用docker logs xx(id)查看报错日志 id为容器id,也就是docker ps -a打印出来的ID

    docker logs 08828a8db55a
    
    • 以下是我的一些报错信息(打印内容)

      mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)
      2021-07-30T01:32:25.274345Z 0 [ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
      2021-07-30 09:32:25+08:00 [ERROR] [Entrypoint]: Unable to start server.
      

      报错大概为/var/lib/mysql-files/没有映射,在上文运行容器命令已经加上了。

  • 也可以删除容器,xx为容器ID(删除之后重新运行docker run)

    docker rm xx
    
  • 运行主库(master)

    docker run -p 3310:3306 --name mysql_master -v /mysql/master/mysql:/etc/mysql -v /mysql/master/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=123456 -d c60d96bd2b77 --lower-case-table-names=1
    
  • 运行从库(slave)

    docker run -p 3312:3306 --name mysql_slave -v /mysql/slave/mysql:/etc/mysql -v /mysql/slave/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=123456 -d 682818f5a0d3 --lower-case-table-names=1
    
  • 查看运行

    docker ps 
    

    STATUS必须为Up才是运行成功

  • 为什么做数据卷挂载配置文件?

    是因为我进入docker容器内部,里面不能新建文件操作,无vi vim edit等操作

    • 如果另外想进入容器排查问题,命令如下下 (xx为id)

      docker exec -it xx /bin/bash
      

3)、编写配置文件

!注意:需要第二步的数据卷配置文件地址

如:-v /mysql/master/mysql:/etc/mysql,所对应的本机的/mysql/master/mysq文件夹。

以上主库配置文件地址为:/mysql/master/mysq

从库配置文件地址为:/mysql/slave/mysql

1、主库配置文件

​ 我这里路径为/mysql/master/mysql

​ 需要自己创建,文件名:Linux为my.cnf,Windows为my.ini.(不能自己取名)

​ 为了方便省事,我是在windows建好对应文件,用xshell的ftp工具传上去的。(上传目录地址为以上地址)

[client]
port = 3306
[mysqld]
#标识唯一即可,不能和从库冲突
server-id = 3310
#怕不加报错,所以还是还是加上了
datadir = /mysql_data
#为什么为3306,是因为容器内部映射为3306
port = 3306
#要同步的数据库
binlog-do-db=test
#用来开启二进制日志文件
log-bin=mysql-bin
2、从库配置文件

​ 我这里路径为/mysql/slave/mysql

[client]
port = 3306
[mysqld]
#唯一标识,不可重复
server-id = 3312
datadir = /mysql_data
#因为容器内部映射为3306
port = 3306
#要同步的数据库(注:主库字段不一样) 数据库为一致
replicate-do-db=test
#用来开启二进制日志文件
log-bin=mysql-bin

4)、创建主库账号和配置主从库关联

1、创建一个专门用来做主从复制的账号,在master库上,在主库上创建。

​ 我百度一大堆,都使用sql语句创建用户,会出现版本不一致问题,授权过低问题等等、所以我这里采用的是Navicat Premium 15工具创建,省事儿。

TlhyKspng](https://imgtu.com/i/WLNpKH)

Tlh6rnpng](https://imgtu.com/i/WLUQ6e)

设置权限,服务器权限全部添加上即可~

#新建完之后,主库运行一下刷新
flush privileges;
2、主库和从库关联
  • 主库操作:
#查看主库的状态,需要复制一下查询出来的值,从库会用到
show master status;

  • 从库操作:
#从库连接主库的主机地址,可以在从库电脑尝试先ping,是否ping通

#ip和端口为主库的,
change master to master_host='192.168.10.23',
#主库的端口
master_port=3310,
#用户和密码为刚刚新建的
#新建的用户名
master_user='slave',
#新建的密码
master_password='123456',
#file和pos为上文查询出来的
master_log_file='mysql-bin.000003',
master_log_pos=2615;

#查看状态
SHOW SLAVE STATUS;
#开启主从复制
START slave;
#停止主从复制,停止之后需要再次确认主库配置和从库配置是否一致
STOP slave;

#SHOW SLAVE STATUS显示Slave_IO_State为Waiting for master to send event即为成功
#而且SHOW SLAVE STATUS之后显示Slave_IO_Runing和Slave_SQL_Runing必须都为YES状态

完成之后则只要测试给主库做一些增删改查操作,看看从会不会同样发送,主从复制就完成了。

5)、搭建之中出现的一些问题:

Slave_IO_State为connecting to master状态

1、如果出现以下结果,则进入容器测试一下slave账号是否能登录进去。
#密码错误登录不上
error connecting to master 'slave@192.168.10.23:3310' - retry-time: 60 retries: 1 message: Access denied for user 'slave'@'172.17.0.1' (using password: YES)
  • 进入主库docker容器

     docker exec -it 9ee812727c44 /bin/bash
    
  • 测试slave能否能登录

    mysql -uslave -p123456
    

    #出现错误 navicat能登录,本地确登录不了,只能修改一下密码了

    mysql: [Warning] Using a password on the command line interface can be insecure.
    ERROR 1045 (28000): Access denied for user 'slave'@'localhost' (using password: YES)
    
  • 登录root修改slave密码

    mysql -uroot -p123456
    

    选中数据库

    use mysql;
    

    修改slave账户密码

    ALTER USER 'slave'@'%' IDENTIFIED BY '123456';
    

    打印结果则为成功

    Query OK, 0 rows affected (0.00 sec)
    

    刷新一下

    flush privileges;
    

    再次登录发现了成功了
    重新绑定主从库操作即可–先查询主库数据信息,再修改绑定sql对应的值

2、出现密码插件报错
error connecting to master 'slave@192.168.10.23:3310' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

3、主库出现sql报错,从库写入错误sql出现同步中断问题
  • 报错语句:
Could not execute Write_rows event on table pyh_monitoring.sys_log; Duplicate entry '2458733' for key 'sys_log.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 374971691

其大意为:sys_log这张表的主键id已经存在了,不能再插入了。

  • 解决思路:

系统出现sql运行错误是很常见的事情,必须让从库忽略掉这些错误。

  • 解决办法:

从库的my.ini或者my.cnf文件添加配置文件

#忽略1062错误
slave_skip_errors = 1062
#忽略全部错误--只写这个就好了
slave_skip_errors=all
4、Linux默认区分大小写报错
  • 报错语句
Caused by: org.quartz.JobPersistenceException: Couldn't retrieve trigger: Table 'pyh_monitoring.QRTZ_TRIGGERS' doesn't exist [See nested exception: java.sql.SQLSyntaxErrorException: Table 'pyh_monitoring.QRTZ_TRIGGERS' doesn't exist]

大意为说找不到这张表QRTZ_TRIGGERS

  • 排查原因

数据库有qrtz_triggers这张表,找到原因是区分大小写

  • 解决过程

给my.ini添加lower_case_table_names=1 ,重启,在mysql8.0没用。

报错语句

Different lower_case_table_names settings for server ('1') and data dictionary ('0').

其大意为:和默认配置不同

  • 解决方案

    #给两个数据库的my.ini添加
    lower_case_table_names=1
    

    停掉docker容器,删除,重新运行

    docker ps
    docker stop xx
    docker ps -a 
    docker rm xx
    

    运行主库

    docker run -p 3310:3306 --name mysql_master -v /mysql/master/mysql:/etc/mysql -v /mysql/master/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=123456 -d c60d96bd2b77 --lower-case-table-names=1
    

    运行从库

    docker run -p 3312:3306 --name mysql_slave -v /mysql/slave/mysql:/etc/mysql -v /mysql/slave/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=123456 -d 682818f5a0d3 --lower-case-table-names=1
    

    数据从新拷贝,再重新绑定主从库。

Q.E.D.