搭建mysql主从服务器

搭建mysql主从服务器

前言

为什么要搭建mysql主从服务器

1. 在传统仅有单个mysql服务器的项目中,一旦同时间遇到大量用户对数据库进行速写操作,服务器就就很容易宕机,进而导致数据丢失,这种原因是由于数据库无法有效处理高并发写入请求,导致资源耗尽,响应时间延长甚至崩溃.因此,就有必要采用多服务器协同解决大量请求的问题,减轻单台服务器的压力。

MySQL主从服务器原理(1主1从为例)

binlog日志文件相关操作

序号	sql语句	解释
1	show variables like ‘log_bin’;	查看日志是否启用
2	show variables like ‘%datadir%’;	查看binlog所在位置
3	show binary logs;	显示所有日志文件
4	show binlog events in ‘日志名’;	查看binlog日志

如上图所示,我们先分别创建两个docker容器挂载mysql分别扮演主服务器(master),从服务器(slave)

• 主服务器是主要的数据库服务器，负责处理写（增删改）和读（查）的操作
• 当主服务器收到读写操作时，这些操作记录将被记录在二进制日志文件binlog里，称为二进制日志事件（Binary Log Events ）。
• 从服务器通过开启IOthread读取binlog中的操作记录，并开启IOtrread中的写线程写入slave服务器的relayLog文件中
• 从服务器开启SQLthread,读取rapayLog文件中的数据，进而更新数据库中的内容，从而实现主服务器和从服务器间数据的同步

此外，mysql主从赋不仅局限为1主1从，还可以配置1主多从，以满足不同需求，接下来我们来看看mysql主从服务器的搭建过程。

mysql主从服务器搭建（1主2从为例）

创建存储文件夹

从容器中拷贝原始的my.cnf配置文件

• 先创建一个基础docker容器

• cd /usr/local/software/mysql/3306/conf 来到需要拷贝原始my.cnf的目录下

• ./ —当前位置

• docker cp 容器名称: 文件地址 目标地址

• 再将容器内/etc/mysql/my.cnf 拷贝到当前位置

  docker run -it --name mytest -e MYSQL_ROOT_PASSWORD=123 -d mysql
  docker cp mytest:/etc/mysql/my.cnf ./
  

搭建主(master)服务器

docker run \
-it \
--name mysql_3306 \
--privileged \
--network wn_docker_net \
--ip 172.18.12.2 \
-p 3306:3306 \
-v  /usr/local/software/mysql/3306/conf/my.cnf:/etc/mysql/my.cnf \
-v  /usr/local/software/mysql/3306/data:/var/lib/mysql \
-v /usr/local/software/mysql/3306/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql

测试客户端Navicat能否成功连接

master服务器配置my.cnf

# Custom config should go here
server-id=200    #server‘id
log_bin=wnhz-master-logbin    #logbin name
binlog_format=row

vim 文本编辑器

• i: 插入
• esc :退出编辑
• :wq – 退出并储存
• 插入完成后需要重启docker容器才能生效

查看master服务器状态

show master status;

搭建从服务器(slave)

docker run \
-it \
--name mysql_3310 \
--privileged \
--network wn_docker_net \
--ip 172.18.12.3 \
-p 3310:3306 \
-v  /usr/local/software/mysql/3310/conf/my.cnf:/etc/mysql/my.cnf \
-v  /usr/local/software/mysql/3310/data:/var/lib/mysql \
-v /usr/local/software/mysql/3310/mysql-files:/var/lib/mysql-files \
-e MYSQL_ROOT_PASSWORD=123 \
-d mysql

master创建用户slave进行主从关联

create user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';
flush privileges;

进入slave容器并连接mysql

change master to master_host='172.18.12.2', master_user='slave',master_password='123',MASTER_LOG_FILE='wnhz-master-logbin.000002',MASTER_LOG_POS=156;

启动slave复制

start slave;

查询slave状态

show slave status \G;

出现两个yes即配置成功

配置异常处理

主从设置中出现不是两个yes

• 先关闭slave

  stop slave
  

• 重置slave中的replaylog

  reset slave;
  

• 重新配置

show master status;    #查看maseter主容器状态

change master to master_host='172.18.12.2', master_user='slave',master_password='123',MASTER_LOG_FILE='wnhz-master-logbin.000001',MASTER_LOG_POS=156;  #slave

• 重新运行slave

  start slave;
  

客户端连接不上mysql

出现这种问题多是电脑的安全级别太高导致的,以下是两种解决方案

1. 永久关闭防火墙

   sudo systemctl disable iptables
   

2. 防火墙开启指定端口

   firewall-cmd --add-ports=3306/tcp --permanent 
   firewall-cmd --reload
   firewall-cmd --list-ports 
   

创建只读账号

为了进一步维护数据库的完整性，安全性和性能，在实际应用中，我们往往需要创建特定权限的用户。

创建权限为读操作的用户

create user 'asd'@'%' IDENTIFIED WITH mysql_native_password BY '123';
GRANT SELECT ON *.* TO 'asd'@'%';
flush privileges;

附录

master主服务器my.cnf配置

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password
# Custom config should go here
!includedir /etc/mysql/conf.d/

server-id=200    #server‘id
log_bin=wnhz-master-logbin    #logbin name
binlog_format=row

slave从服务器my.cnf配置

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password
# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id=201    #slave‘id
log_bin=wnhz-slave-01-logbin    #logbin name
relay_log=wnhz-slave-01-relay
read-only=1