mysql数据库主从同步_mysql主从同步

目录

        一、准备工作
            1.1 安装或关闭以下服务
            1.2 本次安装环境
        2、主数据库配置
            2.1主数据库配置
            2.2创建用户
            2.3查看信息
        三、从主数据库配置
            3.1从数据库配置
            3.2连接主服务器
            3.3测试

一、准备工作
1.1 安装或关闭以下服务

关闭防火墙等

linux时间校对

mysql8安装

mysql增删改查
1.2 本次安装环境
配置信息    说明
linux系统版本    CentOS7.4
内核    ml-3.10.0
mysql版本    8.0.33
主mysqlIP地址    192.168.1.10
从mysqlIP地址    192.168.1.11
2、主数据库配置

完成mysql8安装并登录数据库，完成主数据库增加
2.1主数据库配置

vim /etc/my.cnf
[mysqld]
server-id=1                #server-id表示主库的唯一标识
log-bin=mysql-bin      #log-bin表示开启二进制日志
binlog-do-db=testdb   #binlog-do-db表示需要同步的数据库名

    server-id 设置主服务器的唯一标识
    log_bin 指定二进制日志的名称
    binlog-do-db 需要同步的数据库名。
    重启数据库

systemctl restart  mysqld

2.2创建用户

创建用户test并指定该地址在192.168.1.11上登录，授予REPLICATION SLAVE权限

mysql> CREATE USER 'test'@'192.168.1.11' IDENTIFIED BY 'Yrdy123!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.1.11';

    REPLICATION SLAVE权限 允许用户作为复制从服务器连接到主服务器并执行复制操作

2.3查看信息

查看主数据库服务器的二进制日志文件和位置信息
mysql> show master status;
±--------------------------±------------±-------------±--------------------------------±------------------------+
| File | Position| Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±--------------------------±------------±-------------±--------------------------------±------------------------+
| mysql-bin.000001|718 | testdb | | |
±--------------------------±------------±-------------±--------------------------------±------------------------+

    File 当前正在写入二进制日志的日志文件名。
    Position 当前正在写入二进制日志的位置。

三、从主数据库配置
3.1从数据库配置

vim /etc/my.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-do-db=testdb

重启数据库

systemctl restart  mysqld

创建数据库

mysql> CREATE DATABASE testdb;          #从数据库需要先创建与主数据库相同的数据库

3.2连接主服务器

连接主服务器

mysql> change master to master_host='192.168.1.10', master_user='test', master_password='Yrdy123!', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=718;

    MASTER_HOST = ‘主服务器IP地址’,
    MASTER_PORT = 主服务器端口号,
    MASTER_USER = ‘拥有主从复制权限的账号名称’,
    MASTER_PASSWORD = ‘拥有主从复制权限的账号密码’,
    MASTER_LOG_FILE = ‘主服务器上的File值’,
    MASTER_LOG_POS = 主服务器上的Position值;

启动从服务器与主服务器的同步

mysql> start slave;

查看服务器的状态

mysql> show slave status\G;

在这里插入图片描述

    确保Slave_IO_Running 和 Slave_SQL_Running 值都为 Yes，表示主从同步已经成功配置。

3.3测试

在主数据表中新增列

mysql> ALTER TABLE 第99小学
ADD COLUMN 备注信息 VARCHAR(100) ；

从数据库查看是否新增成功

mysql> DESC 第99小学;

4、其他
4.1连接完毕后发现Slave_IO_Running值异常，

在这里插入图片描述
解决
发现设置连接主服务器的IP地址写错，关闭主从复制，在重新连接

停止 MySQL 的主从复制，在主数据库上先输入
STOP SLAVE;
主数据库上重置主从同步配置
RESET SLAVE;
从数据库上重置主从同步配置
RESET SLAVE;

重新连接主服务器（注意重新查看主服务器Position值）

mysql> change master to master_host='192.168.1.10', master_user='test', master_password='Yrdy123!', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=718;

4.2报错Error connecting to source 'test@192.168.1.10:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

    “default_authentication_plugin=mysql_native_password” 是 MySQL 数据库中的一个配置选项，它指定了默认的身份验证插件。在 MySQL 8.0 之前的版本中，默认的身份验证插件是 “mysql_native_password”。这个插件使用基于密码的身份验证，将用户提供的密码与存储在数据库中的加密密码进行比较来验证用户身份。
    然而，在 MySQL 8.0 版本中，推荐使用更安全的身份验证插件 “caching_sha2_password”。因此，默认的身份验证插件已从 “mysql_native_password” 更改为 “caching_sha2_password”。
    解决

vim /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password

修改完成后重启服务

systemctl restart  mysqld

进入数据库删除已创建用户并在重新创建一次

mysql> DROP USER 'test'@'192.168.1.11';           #删除用户
mysql> CREATE USER 'test'@'192.168.1.11' IDENTIFIED BY 'Yrdy123!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'192.168.1.11';

再次查看主数据库的状态，File 和 Position数值可能会变化，其他在按上面进行一次操作

mysql> show master status;

4.3报错Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000003, end_log_pos 1789. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

解决
关闭主从同步后，在从数据库创建数据库，需要和主数据库保持一致。创建完毕后再次连接主服务器正常

5、主从同步原理：
1.binlog 日志：

binlog 记录了对于数据操作的ddl 和dml 语句，这些语句是可以直接执行；
2.binlog 日志记录：
2.1 日志记录格式：

在 MySQL 8 中，binlog-format 参数已经被弃用，被 default_binlog_format 参数所取代。在 MySQL 8 中，如果未显式指定二进制日志的格式，default_binlog_format 参数将控制 MySQL 记录二进制日志的格式。
default_binlog_format 参数有以下可选值：

    ROW：记录行级别别更改。在 MySQL 8 中，这是默认的二进制日志格式。
    STATEMENT：记录每个需要记录的 SQL 语句。
    MIXED：混合使用 ROW 和 STATEMENT 两种格式，选择使用哪一种格式取决于具体的 SQL 语句。

2.2 三种记录格式的区别：

    基于语句级别的复制（statement-based replication，SBR）：这种方式是最初的复制方式，将主服务器上执行的修改数据的SQL语句记录到二进制日志文件中，然后将这些SQL语句发送到从服务器，从服务器再执行这些SQL语句完成数据同步。此方法的优点是复制过程中只传输SQL语句，网络传输量小；缺点是可能会有一些数据不一致的情况出现，例如在主服务器上执行的一些非确定性函数(如 uuid，date 等)，或者在从服务器上存在不存在的表或数据行。

    基于行级别的复制（row-based replication，RBR）：这种方式是在SBR基础上发展而来，它直接将修改数据的二进制数据发送到从服务器，然后在从服务器上执行相同的二进制数据完成数据同步。此方法的优点是能够保证数据一致性，能够处理一些复杂的SQL语句；缺点是对于大量的操作，可能会产生大数据量传输。

    基于混合复制（mixed-based replication，MBR）：它是综合了SBR和RBR两种方式的优点，即在不同的情况下选择不同的方式。例如，对于不涉及大量数据变更的SQL语句使用语句级别的方式，对于大量数据变更的SQL语句使用行级别方式。该方式比较灵活，能够平衡性能和数据一致性的关系。

需要注意的是，建议在配置主从复制时明确指定二进制日志的格式，以避免误解和不必要的问题。在一致性设置和数据同步方面，明确定义二进制日志格式可以提高主从数据同步的效率和稳定性。
2.3 binlog 主从复制流程：

在这里插入图片描述

    slave 节点先发送请求给到主节点，主节点在有数据变化时就可以推送数据给到 从节点；
    从节点接到数据后先写到log 中，然后在由其它的线程对log 数据进行解析然后入库；

6、总结：

Mysql 主从同步主要是通过master 节点开启binlog 日志，然后slave 节点消费binlog 日志的数据，将数据更新到各自的库中。