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工具创建,省事儿。
设置权限,服务器权限全部添加上即可~
#新建完之后,主库运行一下刷新
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.