侧边栏壁纸
博主头像
与晚风述往事博主等级

万般皆下品,唯有读书高。

  • 累计撰写 149 篇文章
  • 累计创建 29 个标签
  • 累计收到 7 条评论

目 录CONTENT

文章目录

MySQL 主从复制(二进制日志Position方式)

与晚风述往事
2022-04-11 / 0 评论 / 0 点赞 / 162 阅读 / 3,957 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-04-11,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

介绍

为了保证数据的安全性,在原单实例的基础上,调整为主从复制,避免因单台服务器出现问题造成的大规模数据丢失。

在MySQL数据库中,主从同步有两种方式:

  • 基于二进制日志文件位置(Position)的方式
  • 基于全局事务标识符的方式

本篇文章基于二进制日志文件位置的方式来说明主从复制的流程。

MySQL数据库中,实例会将更新数据和更改定义作为“事件”写入到二进制日志文件中,副本数据库会读取源库中的二进制文件,然后执行这些事件,来保证源库和副本数据库数据一致。

在二进制日志文件中,这些“事件”都是按事务执行时间的先后来排序的,副本数据库可以指定从哪个事件开始同步。

方案

方案介绍:采用binlog Position方式搭建一主一从
数据库版本:MySQL 社区版 5.7.35

1、安装MySQL

首先需要在两台服务器中安装好MySQL数据库,安装数据库的方式根据自己的环境或偏好选择即可。

安装的具体步骤不再说明,如有需要可参考以下链接:https://bxbdba.com/archives/centos7installmysql57

2、配置

在安装好数据库之后,我们开始配置MySQL主从需要的参数。

配置主从复制时,需要注意以下事项:

  • 主数据库和从数据库的server-id必须不一致(唯一),且不能为0。
  • 主数据库必须启用二进制日志文件,从库非必须,但建议从库也启用。

[源端]

[root@localhost src]# vim /etc/my.cnf

在主库的my.cnf文件中的mysqld模块下新增两行参数

[mysqld]
log-bin=mysql-bin
server-id=1

image
说明:

  • log-bin 启用二进制日志文件。二进制日志文件类似于Oracle的归档日志文件,记录着数据的变更信息。
  • server-id 服务器的唯一ID。如果指定为0,则源端会拒绝来自副本端的任何连接。

[从端]

[root@localhost src]# vim /etc/my.cnf

从库编辑参数文件my.cnf,在[mysqld]模块内增加两行参数。

[mysqld]
log-bin=mysql-bin
server-id=2

image-1649313701014
说明:

  • log-bin 启用二进制日志文件。二进制日志文件类似于Oracle的归档日志文件,记录着数据的变更信息。
  • server-id 服务器的唯一ID。如果指定为0,则源端会拒绝来自副本端的任何连接。

3、重启数据库

配置好MySQL的参数文件后,我们重启主库和从库,让刚刚修改的参数生效。

[源端]

[root@localhost src]# mysql/bin/mysqladmin -uroot -p shutdown

image-1649313895231

[root@localhost src]# mysql/bin/mysqld_safe &

image-1649313983681

[从端]

[root@localhost src]# mysql/bin/mysqladmin -uroot -p shutdown

image-1649313917412

[root@localhost src]# mysql/bin/mysqld_safe &

image-1649314010390

4、创建复制用户

主库和从库正常启动后,我们在主库上创建一个特定用户,让从库使用它连接到主库。注意,此选项非必须,但建议创建一个特有的复制用户,以尽量避免其他用户受到威胁的可能性。

对于多个从库时,可以创建多个复制用户,让每个从库使用不同的用户连接到主库,也可以让所有从库使用相同的复制用户连接到主库。

[源端]
打开MySQL Shell窗口

[root@localhost src]# mysql/bin/mysql -uroot -p

创建一个repl用户。注意,主机名建议设置为具体的IP地址或IP地址段,生产环境中严格机禁止使用%;用户密码建议设置成复杂的长字符串。

mysql> CREATE USER 'repl'@'192.168.3.%' IDENTIFIED BY 'repl';

授权REPLICATION SLAVE权限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.3.%';

5、拷贝源库中的数据

创建好复制用户后,检查一下源库是否已有数据。如果源库中有历史数据,则必须要将这部分数据拷贝到从库中,让两个库数据完全一致。如果没有历史数据,则跳过该步骤即可。

拷贝数据库有多种方案,我这里就介绍两种常用的:

  • 对于少量历史数据的主库,建议使用mysqldump
  • 对于大量历史数据的主库,建议使用冷备份

注意,以下步骤取自于主库均使用默认InnoDB引擎。如果你的数据库中还有非InnoDB引擎的表需要备份,则需要参考官方文档进行数据同步。

mysqldump方式

5.1、主库备份全库数据

[源端]

[root@localhost src]# mysql/bin/mysqldump -uroot -p --all-databases --master-data > source_dump.sql

说明:

  • –all-databases 指定导出所有数据库
  • –master-data 指定导出时附加CHANGE MASTER TO 二进制日志坐标(文件名和位置)的语句,如果没有指定此句,则需要手动将所有表锁定在单独的会话里。

5.2、将sql文件拷贝至从服务器

[源端]

[root@localhost src]# scp source_dump.sql root@192.168.3.6:/usr/local/src

image-1649317416147

5.3、从库还原

[从端]

[root@localhost src]# mysql/bin/mysql -uroot -p < source_dump.sql

冷备份

5.1、锁定所有表

首先我们将主库给锁住,以避免二进制文件再次因事务执行而发生变化。注意,执行语句后,不要关闭此客户端,否则会解除锁定。

[源端]

mysql> FLUSH TABLES WITH READ LOCK;

5.2、查看当前二进制日志的信息

锁定主库后,我们新打开个客户端,查询二进制日志的信息。查询结果出来后,记录二进制日志的文件名和Position信息。

[源端]

mysql> SHOW MASTER STATUS;

image-1649322342238

5.3、停止主库

将主库给停止掉,开始拷贝数据文件。

[源端]

[root@localhost src]# mysql/bin/mysqladmin -uroot -p shutdown

5.4、拷贝数据目录

停掉主库后,我们将主库中的数据文件打包并拷贝到从库中。通常情况下,可以参考my.cnf文件中datadir的参数值,以获取到当前MySQL数据库的数据文件目录。

[源端]

查看my.cnf后,进入到数据文件目录中,我这里以/usr/local/mysql为例。

[root@localhost ~]# cd /usr/local/mysql

打包数据文件,通常在该目录下会有与之库名相同的文件夹名称,我这里以mysql库为例。

[root@localhost local]# tar cf source_data.tar mysql

将打包的tar文件拷贝到从库所在的服务器

[root@localhost src]# scp source_data.tar root@192.168.3.6:/usr/local/src

image-1649323442387

5.5、停机从库并还原tar包

拷贝完成后,停止掉正在运行的从库,然后将tar包解压到数据目录中。

[从端]
从库停机

[root@localhost src]# mysql/bin/mysqladmin -uroot -p shutdown

删除从库的原数据文件,如果没有,则直接解压。

[root@localhost mysql]# rm -rf /usr/local/mysql/mysql

解压tar目录的文件

[root@localhost src]# tar xvf source_data.tar -C /usr/local/mysql

5.6、启动主库和从库

还原完数据文件后,重新启动主库和从库。
[源端]

[root@localhost src]# mysql/bin/mysqld_safe &

[从端]

[root@localhost src]# mysql/bin/mysqld_safe &

6、从库设置源配置

主库和从库数据同步后,在从端配置连接主库所需要的用户,以及从哪个二进制日志文件和二进制日志文件的坐标开始同步。

[从端]

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.3.137',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=615;

说明:

  • MASTER_HOST 主库的主机名
  • MASTER_USER 连接主库使用的用户名
  • MASTER_PASSWORD 连接主库使用的用户密码
  • MASTER_LOG_FILE 从库与主库开始同步使用的二进制日志文件名
  • MASTER_LOG_POS 从库与主库开始同步使用的二进制日志Position
    image-1649318271896

7、启动复制线程

配置好从库连接到主库使用的链接信息后,就可以启用复制线程了。

[从端]

mysql> START SLAVE;

image-1649318491513

8、查看主从库的状态

至此,MySQL的主从(一主一从)就配置完了。接下来我们就可以插入一条数据到主库,然后在从库中查询,看能否查询到结果。

测试流程就不再写了,直接查询两个库的同步状态。

[源端]

mysql> show master status;

image-1649319268182

[从端]

mysql> show slave status\G;

image-1649319320924

0

评论区