mysql数据库主从复制（mysql-replication）

win9斩华雄、

已于 2023-12-25 15:02:27 修改

阅读量1.5k

点赞数 21

文章标签：数据库 mysql linux 运维服务器

于 2023-12-23 11:25:09 首次发布

本文链接：https://blog.csdn.net/weixin_57328245/article/details/135160741

版权

mysql数据库主从复制：mysql-replication

一、理论知识

1.Replication

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。

2.mysql主从复制的优点

横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。读拓展性和负载均衡。
数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。主库的写入数据会持续地在冗余的从库节点上被执行保留，减少数据丢失的风险。
分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
备份 - 可以使用从服务器数据进行备份，减轻主服务器的压力。
高可用性保障 - 当主库发生故障时，可以快速的切到其某一个从库，并将该从库提升为主库，因为数据都一样，所以不会影响系统的运行

3.Replication的原理

在这里插入图片描述
Binlog
从比较宽泛的角度来探讨复制的原理，MySQL的Server之间通过二进制日志来实现实时数据变化的传输复制，这里的二进制日志是属于MySQL服务器的日志，记录了所有对MySQL所做的更改。这种复制模式也可以根据具体数据的特性分为三种：

Statement：基于语句格式
Statement模式下，复制过程中向获取数据的从库发送的就是在主库上执行的SQL原句，主库会将执行的SQL原有发送到从库中。
Row：基于行格式
Row模式下，主库会将每次DML操作引发的数据具体行变化记录在Binlog中并复制到从库上，从库根据行的变更记录来对应地修改数据，但DDL类型的操作依然是以Statement的格式记录。
Mixed：基于混合语句和行格式
MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。
最早的实现是基于语句格式，在3.23版本被引入MySQL，从最初起就是MySQL Server层的能力，这一点与具体使用的存储引擎没有关联；在5.1版本后开始支持基于行格式的复制；在5.1.8版本后开始支持混合格式的复制。

这三种模式各有优劣，相对来说，基于Row的行格式被应用的更广泛，虽然这种模式下对资源的开销会偏大，但数据变化的准确性以及可靠性是要强于Statement格式的，同时这种模式下的Binlog提供了完整的数据变更信息，可以使其应用不被局限在MySQL集群系统内，可以被例如Binlogserver，DTS数据传输等服务应用，提供灵活的跨系统数据传输能力，目前互联网业务的在线MySQL集群全部都是基于Row行格式的Binlog。

二、配置主从复制（以mysql5.7版本为例）（模拟无数据）

1.准备环境

准备两台服务器（一主一从）

防火墙和selinux均关闭（方法见下）

[root@master ~]# systemctl stop firewalld --now && setenforce 0
[root@slave ~]# systemctl stop firewalld --now && setenforce 0

设置主机名解析（方法如下）

[root@master ~]# vim /etc/hosts   插入下列信息，两台服务器ip和主机名
10.12.155.10   master
10.12.155.11   slave
[root@slave ~]# vim /etc/hosts   插入下列信息，两台服务器ip和主机名
10.12.155.10   master
10.12.155.11   slave

2.清理环境

#清理数据库
[root@master ~]# yum -y erase `rpm -qa | egrep "mysql|mariadb"`
[root@master ~]# rm -rf /etc/my* && rm -rf /var/lib/mysql && rm -rf /usr/bin/mysql
#检查清理效果
[[ ! -f /etc/my.cnf ]] && [[ ! -d /var/lib/mysql ]] && [[ ! -f /usr/bin/mysql ]] && echo "环境已经清理完成" || echo "环境未清理"

3、安装mysql

推荐使用yum安装，yum安装方法见教程：如何yum安装mysql

安装完成后重启，获取初始密码

[root@master ~]# cat /var/log/mysqld.log | grep  password | awk -F':' '{print $4}'
# 此条命令会获取一个12位的字符串初始密码

修改密码

[root@master ~]# mysqladmin -uroot -p'初始密码' password '新密码'

4、配置主服务器（master）

开启biinlog二进制日志

[root@master ~]# mkdir  /data
[root@master ~]# chown  -R  mysql.mysql  /data
[root@master ~]# vim /etc/my.cnf   #插入下列两行信息，serverID必须具有唯一性，binlog日志存放路径必须存在且授权
server-id=1
bin-log=/data/mysql-bin
[root@master ~]# systemctl restart mysqld   #这里注意改完配置文件后必须要重启服务，保证修改的配置生效

创建授权用户

[root@master ~]# myslq -uroot -p'密码'
mysql> grant replication slave on *.* to 'repl'@'10.12.155.11' identified by '账号密码'
#这里是在mysql数据库创建一个新用户并给它授予主从复制的从库权限，注意这是mysql5.7版本的操作方法，如果是mysql8.0版本，则需要先创建用户，然后授权，分两步。

查看主库信息

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
#此条命令可以查看主库的主从复制相关信息，主要是用来获取File、Position这两条信息，后面配从库会用到。

5、配置从服务器（slave）

设置server-id

[root@slave ~]# vim /etc/my.cnf
server-id=2    #server-id必须唯一
[root@slave ~]# systemctl restart mysqld  #重启服务使修改配置生效

从服务器配置连接主服务器信息

[root@slave ~]# myslq -uroot -p'密码'
mysql> ? change master to   #此条命令可以查看配置列表模板，辅助配置
mysql> change master to
 -> MASTER_HOST='master',         #主库的ip,这里做了主机名解析，所以填主机名
  ->MASTER_USER='repl',     #主库授权的账户名称
  ->MASTER_PASSWORD='password',    #主库授权的账户密码
 -> MASTER_PORT=3306,       #主库的mysql服务的端口
  ->MASTER_LOG_FILE='mysql-bin.000001',    #主库二进制日志名称
  ->MASTER_LOG_POS=154,         #主从关系开始的二进制日志位置点信息
  ->MASTER_CONNECT_RETRY=10;     #主从关系连接的尝试最长时间

启动从服务器的复制线程

mysql> start slave:

查询是否成功建立主从关系

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Reconnecting after a failed master event read
                Master_Host: master
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 415
             Relay_Log_File: localhost-relay-bin.000002
              Relay_Log_Pos: 581
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB:

#12、13条信息（Slave_IO_Running、Slave_SQL_Running）这两处显示两个yes，则说明主从复制建立成功,也可以在主库写入数据，在从库进行查询，如果能查到主库写入的信息，则也能说明主从关系建立成功。

在从站上暂停复制

您可以使用和stop slave和start slave语句停止并启动从站上的复制。
要停止从主服务器处理二进制日志，请使用：

mysql> stop slave;   //暂停
mysql> start slave;  //启动
mysql> reset slave:  //重新设置

三、主数据库有数据情况下如何配置

主数据库有数据的情况下，使用myqldump工具将主服务器中现有数据导出，使用scp命令复制到每个从服务器

3.1配置主服务器

开启binlog二进制日志

 [root@master ~]# vim  etc/my.cnf
 server-id=1     
 bin-log=/data/mysql-bin

创建日志目录并授权

 [root@master /]# mkdir data
 [root@master /]# chown mysql.mysql /data

重启服务

 [root@master /]# systemctl restart mysqld

创建复制数据的用户并授权

[root@master ~]# myslq -uroot -p'密码'
mysql> grant replication slave on *.* to 'repl'@'10.12.155.11' identified by '账号密码'

主数据库有数据的情况下，使用myqldump工具将主服务器中现有数据导出，使用scp命令复制到每个从服务器

[root@master ~]# mysqldump  -u'用户名' -p'密码' --all-databases >all.sql

[root@master ~]# scp all.sql  slave:/root/

3.2配置从服务器

添加server-id

 [root@slave ~]# vim  etc/my.cnf
 server-id=2

重启服务

 [root@slave /]# systemctl restart mysqld

导入主服务器数据库数据

 [root@slave /]# mysql  -uroot -p'密码'  <all.sql
 或者
 [root@slave /]# myslq -uroot -p'密码'
 mysql>source /all.sql

查看主服务器binlog日志名称和位置信息

[root@master ~]# myslq -uroot -p'密码'
mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 154
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
#此条命令可以查看主库的主从复制相关信息，主要是用来获取File、Position这两条信息，后面配从库会用到。

配置从服务器连接主服务器相关信息

[root@slave ~]# myslq -uroot -p'密码'
mysql> ? change master to   #此条命令可以查看配置列表模板，辅助配置
mysql> change master to
 -> MASTER_HOST='master',         #主库的ip,这里做了主机名解析，所以填主机名
  ->MASTER_USER='repl',     #主库授权的账户名称
  ->MASTER_PASSWORD='password',    #主库授权的账户密码
 -> MASTER_PORT=3306,       #主库的mysql服务的端口
  ->MASTER_LOG_FILE='mysql-bin.000001',    #主库二进制日志名称
  ->MASTER_LOG_POS=154,         #主从关系开始的二进制日志位置点信息
  ->MASTER_CONNECT_RETRY=10;     #主从关系连接的尝试最长时间

启动从服务器复制线程

[root@slave ~]# myslq -uroot -p'密码'
mysql> start slave；

查询配置结果

[root@slave ~]# myslq -uroot -p'密码'
mysql> show slave status\G
  *************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: master
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 415
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 581
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: