一、概述
1. 数据库主从概念、优点、用途
主从数据库是什么意思呢,主是主库的意思,从是从库的意思。数据库主库对外提供读写的操作,从库对外提供读的操作。
数据库为什么需要主从架构呢?
高可用,实时灾备,用于故障切换。比如主库挂了,可以切从库。读写分离,提供查询服务,减少主库压力,提升性能备份数据,避免影响业务。
2. 数据库主从复制原理
主从复制原理,简言之,分三步曲进行:
①主数据库有个 binlog 二进制文件,记录了所有增删改 SQL 语句;
②(binlog线程)从数据库把主数据库的binlog文件的 SQL 语句复制到自己的中继日志relaylog;
③(io线程)从数据库的relaylog重做日志文件,再执行一次这些sql语句。
(sql执行线程)详细的主从复制过程如图:
二、安装mysql数据库
在主从服务器上均需要完成以下工作:
1.创建本地工作目录: /usr/rdc/mysql-8.0.23 以及 其下的 文件夹 conf、logs、data;并给工作目录授权
cd /usr/rdc/mysql-8.0.23chmod -R 777 data
2.安装mysql数据库(基于docker)
docker search mysqldocker pull mysql:8.0.23
3.获取mysql配置文件、日志文件、数据文件位置
#step 1.启一个该版本mysql的容器docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.23#step 2.进入容器查找配置文件、日志文件、数据文件位置docker ps#查看容器iddocker exec -it mysql /bin/bashfind / -name "f"find / -name "log"
#step 3.退出容器,将容器中/etc/mysql/f文件拷贝到宿主机目录:/usr/rdc/mysql-8.0.23/conf/exitdocker cp mysql:/etc/mysql/f /usr/rdc/mysql-8.0.23/conf#step 4.打开配置文件查看vi /usr/rdc/mysql-8.0.23/conf/f
由配置文件可知:数据文件位置为/var/lib/mysql, 自定义配置文件可以放到容器的/etc/mysql/conf.d目录下,日志文件目录为 /var/log
记住以上三个文件所在位置,下面创建容器做数据卷映射时需要一一对应。
#退出容器,并销毁exitdocker stop mysqldocker rm mysql
二、配置master库
1.在主服务器中编辑f文件
vi /usr/rdc/mysql-8.0.23/conf/f
2.配置文件的[mysqld]节点中添加以下内容
[mysqld]# 设置数据库引擎为INNODBdefault-storage-engine=INNODB# 设置授权访问的加密策略default_authentication_plugin=mysql_native_password# 主从复制配置.start# 服务器IDserver-id=306# 启用二进制日志log-bin=master-bin# 设置logbin格式:STATEMENT(同步SQL脚本) / ROW(同步数据行) / MIXED(混合同步)binlog_format=MIXED# 设置日志最长保存时间expire_logs_days=30# 0-读写,1-只读read-only=0# 设置忽略同步的数据库binlog-ignore-db=information_schemabinlog-ignore-db=mysqlbinlog-ignore-db=performance_schemabinlog-ignore-db=sys# 设置需要同步的数据库#binlog-do-db=pmonitor#binlog-do-db=ucoal# 主从复制配置.end
3.创建容器(映射数据卷)
docker run -p 3306:3306 --privileged=true --restart=always --name mysql -v /usr/rdc/mysql-8.0.23/conf:/etc/mysql/conf.d -v /usr/rdc/mysql-8.0.23/logs:/var/log/mysql -v /usr/rdc/mysql-8.0.23/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.23
4.进入容器访问mysql
#查看容器iddocker ps#进入容器docker exec -it 容器id /bin/bash#访问mysql数据库mysql -uroot -p
5.创建从机访问主库使用的账号
#创建账号create user 'slave1'@'%' identified by 'slave1';#授权grant replication slave on *.* to 'slave1'@'%';#更新用户密码方案(一定要执行否则无法远程访问)alter user 'slave1'@'%' identified with mysql_native_password by 'slave1';#刷新flush privileges;
6.重启容器
docker restart 容器id或名称
7.获取日志文件名和偏移量
执行一下命令:
show master status;
记住file 和 position两个字段的值,配置从机时需要。
三、配置slave库
1.在从服务器中编辑f文件
vi /usr/rdc/mysql-8.0.23/conf/f
2.配置文件的[mysqld]节点中添加以下内容
[mysqld]# 设置数据库存储引擎为INNODBdefault-storage-engine=INNODB# 设置授权验证的加密策略default_authentication_plugin=mysql_native_password# 主从复制配置.start# 服务器IDserver-id=306# 启用中继日志relay-log=slave-relay-binrelay-log-index=slave-relay-bin.index# 设置日志最长保存时间expire_logs_days=30# 0-读写,1-只读;slave设置为只读(具有super权限的用户除外)read_only=1# 开启二进制日志功能,以便本机可以作为其它Slave的Master时使用log-bin=slave-bin# 设置logbin格式:STATEMENT(同步SQL脚本) / ROW(同步数据行) / MIXED(混合同步)binlog_format=MIXED# 1表示slave将复制事件写进自己的二进制日志log_slave_updates=1# 设置允许复制的库# replicate-do-db=pmonitor-cloud# replicate-do-db=ucoal# 设置忽略复制的库# replicate-ignore-db=mysql# replicate-ignore-db=information_schema# replicate-ignore-db=performance_schema#主从复制配置.end
3.创建容器(映射数据卷)
docker run -p 3306:3306 --privileged=true --restart=always --name mysql -v /usr/rdc/mysql-8.0.23/conf:/etc/mysql/conf.d -v /usr/rdc/mysql-8.0.23/logs:/var/log/mysql -v /usr/rdc/mysql-8.0.23/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0.23
4.进入容器访问mysql
#查看容器iddocker ps#进入容器docker exec -it 容器id /bin/bash#访问mysql数据库mysql -uroot -p
5.主库参数关联设置
#先停止从服务器线程stop slave;
change master to master_host='192.168.0.201', master_port=3306, master_user='slave1', master_password='slave1', master_log_file='master-bin.000001', master_log_pos=156, get_master_public_key=1;
注:需要使用在主库中通过show master status查询到的file和postion替换上述指令中的master_log_file 和 master_log_pos的值
#启动从服务器线程start slave;
6.重启容器
docker restart mysql
7.查看从机状态
show slave status \G;
使用上述命令查看状态,Slave_IO_Running、Slave_SQL_Running两个参数均为YES时,表示集群状态正常。
否则,需要根据 Last_IO_Error 或 Last_SQL_Error的报错信息进行排查。
四、踩坑笔记
当我们把自定义的f配置文件设置的权限过大时,会导致mysql在执行时出于安全考虑忽略该配置文件的加载,从而使得自定义配置失效(包括:集群的server-id、binlog日志和relaylog日志的定义全部无效),此问题一度困扰良哥很久,后来通过查看docker日志才发现问题所在。以下做个备忘:
0.埋坑
#设置配置文件权限为777chmod -R 777 /usr/rdc/mysql-8.0.23/conf#启动docker容器docker start mysql#查看docker日志docker logs mysql
发现一行警告:World-writable config file ‘/etc/mysql/conf.d/f’ is ignored.
这句话的意思是所有人都可以写的配置文件已经被忽略加载了!
这个文件正好是我们在创建容器时映射的自定义配置文件。
最好的解决办法当时然把f配置文件的权限降低,然后重启容器。
chmod -R 644 /usr/rdc/mysql-8.0.23/conf/fdocker restart mysql
但是,良哥是个好奇心很重的人,想看看不降低f权限的情况下集群会发生什么状况,以及如何修复并使之正常运行:
1.查看主从同步状态
在从机中登录mysql,执行以下指令:
show slave status \G;
Slave_IO_Running状态为NO,根据报错信息可知集群的server-id重复了。
2.登录主服务器查看配置是否生效
#查看server-idshow variables like 'server_id';#查看binlog日志文件名称show global variables like '%log_bin%';
发现server-id,与binlog日志文件均未按配置文件要求生成。
解决办法:
#step 1.编辑容器中的配置文件/etc/mysql/fvim /etc/mysql/f
#step 2.加入以下内容#服务器IDserver-id=306#启用二进制日志log-bin=master-bin#设置logbin格式:STATEMENT / ROW / MIXEDbinlog_format=ROW
#step 3.重启容器exitdocker restart mysql
主服务器配置已生效:
3.登录从服务器查看配置是否生效
与配置主服务器类似,查看server-id和relay-bin日志文件是否与配置文件一致。
show variables like 'server_id'; show global variables like '%relay_log%';
查询结果与配置文件不一致。
解决办法:
#step 1.编辑容器中的配置文件/etc/mysql/fvim /etc/mysql/f
#step 2.加入以下内容#服务器IDserver-id=306#开启二进制日志功能,以便本机可以作为其它Slave的Master时使用log-bin=slave-bin#启用中继日志relay-log=slave-relay-binrelay-log-index=slave-relay-bin.index
#step 3.重启容器exitdocker restart mysql
查看配置
show variables like 'server_id';show global variables like '%relay_log%';show global variables like '%log_bin%';
配置已生效:
查看主从复制状态
show slave status\G;
主从复制没启起来:
手动启一下:
start slave;
发现启不起来:
解决办法:
#重置slavereset slave;#重新关联主服务器change master to master_host='192.168.0.201', master_port=3306, master_user='slave1', master_password='slave1', master_log_file='master-bin.000001', master_log_pos=156, get_master_public_key=1;#手动启用从机start slave;
完美解决:
4.其他解决方案
集群中的服务器ID重复,使用使用下列命令分别在主从机上查看server_id
show variables like 'server_id';
说明从机配置文件中的server-id未生效。
可以通过以下两种方式修复:
①将f文件中的server-id 改成 server_id,重启mysql容器;
②在从机mysql控制台执行如下命令:
stop slave;set global server_id = 306;start slave;
执行完成后再查看从机状态,服务正常了。
五、延伸 – 双主双从配置
配置数据库的主从复制,4个数据库,2主机,2从机
步骤1:主机配置
找到Mysql配置文件,[mysqld] 下修改下面内容
[mysqld]server-id = 101 # 主服务器唯一IDlog-bin=自己本地的路径/data/mysqlbin # 启用二进制日志,日志的存放地址binlog_format=STATEMENT # 二进制日志格式binlog-ignore-db=mysql # 设置不要复制的数据库binlog-do-db=需要复制的主数据库名字1 # 设置需要复制的数据库binlog-do-db=需要复制的主数据库名字2 # 设置需要复制的数据库binlog-do-db=需要复制的主数据库名字3 # 设置需要复制的数据库log-slave-updates # 在作为从数据库的时候,有写入操作也要更新二进制日志文件
另一个主机也这样配置,注意server-id 不能重复
步骤2:从机配置
修改f配置文件
[mysqld]server-id = 201 # 从服务器唯一IDrelay-log=mysql-relay # 开启中继日志
另一个从机也这样配置,注意server-id 不能重复
步骤3:重启服务、关闭防火墙
更改配置文件后,重启Mysql服务
关闭防火墙 systemctl stop firewalld
步骤4:创建用户并授权给从机
在2个主机上创建用户:
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从机器数据库IP 或者 % 所有' IDENTIFIED BY '密码';flush privileges;
查询主机的状态:
show master status;# 执行上面命令,得到 File(binlog日志) Position(接入点) Binlog_Do_DB(要复制的数据库) Binlog_IgnoreDB()
在从机上配置需要复制的主机
从机1复制主机1,从机2复制主机2,
从机1执行:
CHANGE MASTER TO MASTER_HOST='主机1IP',MASTER_USER='主机用户',MASTER_PASSWORD='主机密码',MASTER_LOG_FILE='binlog日志名字',MASTER_LOG_POS=具体的接入点值;
从机2执行:
CHANGE MASTER TO MASTER_HOST='主机2IP',MASTER_USER='主机用户',MASTER_PASSWORD='主机密码',MASTER_LOG_FILE='binlog日志名字',MASTER_LOG_POS=具体的接入点值;
如果操作失败,需要重新配置,执行下面2个命令
stop slave; # 停止同步操作reset master; # 重置主从配置
然后两个从机执行下面命令,开启同步
start slave;
步骤5:(重点来了)两个主机互相复制
主机1执行
CHANGE MASTER TO MASTER_HOST='主机2IP',MASTER_USER='主机用户',MASTER_PASSWORD='主机密码',MASTER_LOG_FILE='binlog日志名字',MASTER_LOG_POS=具体的接入点值;
主机2执行
CHANGE MASTER TO MASTER_HOST='主机1IP',MASTER_USER='主机用户',MASTER_PASSWORD='主机密码',MASTER_LOG_FILE='binlog日志名字',MASTER_LOG_POS=具体的接入点值;
然后两台主机分别执行:
start slave;
步骤6:检查是否成功
show slave status\G; # 检查状态
如果结果下面的字段为Yes 代表配置成功
Slave_IO_Running: YesSlave_SQL_Running: Yes