MySQL主从配置

2022-09-27 14:16:40

一、实验环境

实验用途IP地址操作系统
MySQL主服务器192.168.31.61Centos7.9
MySQL从服务器192.168.31.62Centos7.9

需要关闭selinux,防火墙,并清空Iptables规则。

软件名称版本
MySQLl数据库5.7

二、实验原理

  • MySQL Replication,可以实现将数据从一台库服务器( master)复制到一 台或多台数据库服务器 (slave)。默认情况下属于异步复制,无需维持长连接,通过配置,可以复制所有的库或者几个甚至中一些表,这个功能是 MySQL 内建的,本身自带的。

复制过程简单的说就是 master 将数据库的改变写入二进制日志,slave同步这些二进制日志,并根据这些二进制日志行数据操作。
复制原理图解

DML:SQL操作语句, update, insertinsert,delete
Relay log (中继日志)

  • Replication的作用
  1. Fail Over
    故障切换
  2. Backup Server
    备份服务,无法对 SQL 语句执行产生的故障恢复,有限备份
  3. High Performance
    高性能,可以多台 slave, 实现读写分离
  • Replication 工作原理
    整体上来说,复制有 3 个步骤:
  1. master 将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events)
  2. slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)
  3. slave 重做中继日志中的事件,修改 salve 上的数据。
    在这里插入图片描述
  • MySQL 主从复制:
  1. 第一步:master 记录二进制日志。在每个事务更新数据完成之前,master 在二进制日志记录这些改变。MySQL 将事务写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master 通知存储引擎提交事务。
  2. 第二步:slave 将 master 的 binary log 拷贝到它自己的中继日志。首先,slave 开始一个工作线程——I/O 线程。I/O 线程在 master 上打开一个普通的连接,然后开始 binlog dump process。Binlog dump process 从 master 的二进制日志中读取事件,如果已经执行完 master 产生的所有文件,它会睡眠并等待 master 产生新的事件。I/O 线程将这些事件写入中继日志。
  3. 第三步:SQL slave thread(SQL 从线程)处理该过程的最后一步。SQL 线程从中继日志读取事件,并重新执行其中的事件而更新 slave 的数据,使其与 master 中的数据一致。
  • Replication 常见方案
  1. One master and Muti salve 一主多备
    一般用来做读写分离的,master 写,其他 slave 读,这种架构最大问题 I/O 压力集中 在 Master 上<多台同步影响 IO>
    在这里插入图片描述

  2. M-S-S
    使用一台 slave 作为中继,分担 Master 的压力,slave 中继需要开启 bin-log,并配置 log-slave-updates
    Slave 中继可使用 Black-hole 存储引擎,不会把数据存储到磁盘,只记录二进制日志。
    在这里插入图片描述

  3. M-M 双主互备 (互为主从)
    很多人误以为这样可以做到 MySQL 负载均衡,实际没什么好处,每个服务器需要做同样的同步更新,破坏了事物的隔离性和数据的一致性。
    在这里插入图片描述

  4. M-M-M
    监控三台机器互相作对方的 master
    天生的缺陷:复制延迟,slave 上同步要慢于 master,如果大并发的情况那延迟更严重
    在这里插入图片描述

三、搭建主从

1.安装MySQL

主从2台服务器都要安装,下面过程以主服务器安装为例,与从服务器安装过程相同

#首先解压mysql5.7安装包[root@master ~]# tar xvf mysql-5.7.tar.gz#使用yum本地安装mysql[root@master ~]# yum install -y mysql*.rpm#启动mysql[root@master ~]# systemctl start mysqld#关闭密码强度审计插件[root@master ~]# echo "validate-password=OFF" >> /etc/my.cnf#重启mysql[root@master ~]# systemctl restart mysqld#筛选日志文件,查找mysql初始root密码[root@master ~]# grep password /var/log/mysqld.log | head -1
2021-07-14T11:50:39.368593Z 1[Note] A temporary password is generatedfor root@localhost:/j_ieG?jn8!!#登录mysql[root@master ~]# mysql -u root -p'/j_ieG?jn8!!'#修改root密码
mysql>set passwordfor root@localhost=password('123456');
Query OK, 0 rows affected, 1 warning(0.00 sec)

mysql>exit#测试修改的密码[root@master ~]# mysql -u root -p'123456'Type'help;' or'\h'for help.Type'\c' to clear the current input statement.

mysql>

至此MySQL安装完成,下面开始进行主从的搭建

2.MySQL主服务器配置

[root@master ~]# mysql -u root -p'123456'Type'help;' or'\h'for help.Type'\c' to clear the current input statement.

mysql> create database test;#创建测试主从同步的数据库
Query OK, 1 row affected(0.00 sec)

mysql> use test;#切换使用新建的数据库
Database changed
mysql> create table test1(id int,name varchar(20));#创建测试表test1
Query OK, 0 rows affected(0.01 sec)
mysql>exit

配置MySQL配置文件

[root@master ~]# vim /etc/my.cnf#启用二进制日志
log-bin=mysql-bin-master#本机数据库 ID 标示,主从配置中ID要唯一
server-id=1#可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-do-db=test#不可以被从服务器复制的库
binlog-ignore-db=mysql#保存并重启MySQL服务(如果重启卡死现象,kill 掉再启动)[root@master ~]# systemctl restart mysqld

授权主从同步slave用户权限

[root@master ~]# mysql -u root -p'123456'
mysql> grant replication slave on*.* to slave@192.168.31.62 identified by"123456";#ip地址为从库的IP
Query OK, 0 rows affected, 1 warning(0.00 sec)
mysql> show master status;#查看状态信息+-------------------------+----------+--------------+------------------+-------------------+| File| Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set|+-------------------------+----------+--------------+------------------+-------------------+| mysql-bin-master.000003|      450| test| mysql||+-------------------------+----------+--------------+------------------+-------------------+
1 row inset(0.00 sec)

mysql> show binlog events\G*************************** 1. row***************************
   Log_name: mysql-bin-master.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.25-log, Binlog ver: 4*************************** 2. row***************************
   Log_name: mysql-bin-master.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info:......

导出测试主从的数据库,要保证主从数据在同步之前保持他一致

[root@master ~]# mysqldump -uroot p'123456' -B test > test.sql#scp传输到从服务器[root@master ~]# scp test.sql root@192.168.31.62:/root
root@192.168.31.62's password: 
test.sql                                                     100% 1929     2.8MB/s   00:00

3.MySQL从服务器配置

#测试slave用户是否可以正常连接[root@slave ~]# mysql -uslave -p123456 -h 192.168.31.61Type'help;' or'\h'for help.Type'\c' to clear the current input statement.

mysql> show databases;+--------------------+| Database|+--------------------+| information_schema|+--------------------+
1 row inset(0.00 sec)

mysql>exit#只有复制的权限, 是看不到其他库的。导入测试数据库使两台数据库数据一致。[root@slave ~]# mysql -uroot -p123456 < test.sql

修改从服务器配置文件

[root@slave ~]# vim /etc/my.cnf#从服务器没必要开启bin-log日志,
server-id=2#从服务器ID号,不要和主ID相同,如果设置多个从服务器,每个从服务器必须有一个唯一的server-id值,必须与主服务器的以及其它从服务器的不相同。可以认为 server-id值类似于IP地址:这些ID值能唯一识别复制服务器群集中的每个服务器实例。#重启从服务器MySQL服务[root@slave ~]# systemctl restart mysqld#从服务器设置主服务器配置[root@slave ~]# mysql -uroot -p123456Type'help;' or'\h'for help.Type'\c' to clear the current input statement.

mysql> change master to master_host='192.168.31.61',master_user='slave',master_password='123456';#IP地址为主服务器地址。
Query OK, 0 rows affected, 2 warnings(0.01 sec)
mysql>start slave;#启动slave
mysql> show slave status\G;#查看状态*************************** 1. row***************************
               Slave_IO_State: Waitingfor master to send event
                  Master_Host: 192.168.31.61
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000003
          Read_Master_Log_Pos: 450
               Relay_Log_File: node1-relay-bin.000004
               Relay_Log_Pos: 677
        Relay_Master_Log_File: mysql-bin-master.000003
              Slave_IO_Running: Yes
              Slave_SQL_Running: Yes

可以看到出现了两个为YES,则主从配置成功了

参数作用
Slave_IO_Running负责与 Master 主机的 IO 通信
Slave_SQL_Running负责自己的 slave mysql 进程

4.查看主服务器主从同步状态

[root@master ~]# mysql -u root -p'123456'Type'help;' or'\h'for help.Type'\c' to clear the current input statement.

mysql> show processlist\G*************************** 1. row***************************
     Id: 8
   User: slave
   Host: node1:56922
     db: NULL
Command: Binlog Dump
   Time: 143
  State: Master has sent all binlog to slave; waitingfor more updates
   Info: NULL

4.测试主从同步

在主服务器模拟程序插入数据

[root@master ~]# mysql -u root -p'123456'
mysql> use test;
Reading table informationfor completion of table and column names
You can turn off this feature to get a quicker startup with-A

Database changed
mysql> insert into test1 values(1,'man')->;
Query OK, 1 row affected(0.00 sec)

mysql>select*from test1;+------+------+| id| name|+------+------+|    1| man|+------+------+
1 row inset(0.00 sec)

切换到从库进行查看

[root@slave~]# mysql -u root -p'123456'
mysql>select*from test.test1;+------+------+| id| name|+------+------+|    1| man|+------+------+
1 row inset(0.00 sec)

mysql>

至此,主从同步基础搭建完成。

四、主从同步排错

1.主从不同步
登录主库看一下bin -log 的位置,然后再进行同步。

mysql> show master status;+-------------------------+----------+--------------+------------------+-------------------+| File| Position| Binlog_Do_DB| Binlog_Ignore_DB| Executed_Gtid_Set|+-------------------------+----------+--------------+------------------+-------------------+| mysql-bin-master.000003|      713| test| mysql||+-------------------------+----------+--------------+------------------+-------------------+
1 row inset(0.00 sec)

登录从库执行 MySQL 命令:

mysql> stop slave;#停止slave服务
mysql> change master to master_log_file='mysql-bin-master.000003',master_log_pos=713;#根据上面主服务器的show master status的结果,进行从服务器的二进制数据库记录回归,达到同步的效果。
Query OK, 0 rows affected(0.00 sec)

mysql>start slave;#启动 I/O 线程和 SQL 线程看一下从服务器的同步情况
mysql> show slave status\G*************************** 1. row***************************
               Slave_IO_State: Waitingfor master to send event
                  Master_Host: 192.168.31.61
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000003
          Read_Master_Log_Pos: 713
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 327
        Relay_Master_Log_File: mysql-bin-master.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果出现Slave_IO_Running: Yes Slave_SQL_Running: Yes那代表已经在同步了。

排错总结:

IO_Running排错思路: 二进制日志没有开启 IPTABLES 没有放开端口 对应的主机 IP 地址写错了 用户名或密码写错了

SQL_Running排错思路:
主从服务器数据库结构不统一。出错后如果数据少,可以手动解决创建插入,再更新 slave 状态。 注:如果主上误删除了。那么从上也就误删除了。 #因此主上要定期做 mysqldump 备份。

  • 作者:tianmunan
  • 原文链接:https://blog.csdn.net/tianmunan/article/details/118874734
    更新时间:2022-09-27 14:16:40