mysql主从复制

cgcQAQ
141 阅读6分钟

mysql主从复制

一、cnetos7安装mysql（yum版本）

# 禁用packageKit，启用yum

systemctl stop packagekit systemctl disable packagekit

#如果yum被禁用，需要移除禁用线程

sudo rm -f /var/run/yum.pid

#centos7或更高

#备份yum源

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

下载新的 CentOS-Base.repo 到 /etc/yum.repos.d/

wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo

#清理yum缓存

yum clean all

#重建缓存

yum makecache

#更新软件包

yum update -y

#或者只升级所有包，不升级软件和系统内核，软件和内核保持原样

yum upgrade -y

对于MySQL 8

sudo wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm sudo rpm -Uvh mysql80-community-release-el7-3.noarch.rpm

对于MySQL 5.7（如果需要）

sudo wget https://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm sudo rpm -Uvh mysql-community-release-el7-5.noarch.rpm

#安装mysql

sudo yum install mysql-server --nogpgcheck

#启动mysql

sudo systemctl start mysqld

#设置开机自启动

sudo systemctl enable mysqld

#mysql初始设置

  • 安装完成后，MySQL会自动生成一个临时密码或随机密码，通常可以在 /var/log/mysqld.log 文件中找到。使用这个密码进行首次登录，并运行 mysql_secure_installation 脚本来完成数据库的安全初始化，包括设置root用户密码、删除匿名用户、禁止root远程登录等操作。

  • 查看初始密码grep 'temporary password' /var/log/mysqld.log image.png

  • 进入初始设置流程

    mysql_secure_installation

  • 设置新密码，需要大小写数字和符号

image.png

  • 设置是否更改root用户的密码

image.png

  • 接下来就是是否删除匿名用户等，是否创建测试表等

  • 最后提示是否重载设置，选择y，如果选择no，刚刚的配置不会立即生效，可以稍后在mysql命令界面使用FLUSH PRIVILEGES;完成设置刷新

image.png

image.png

#开放防火墙

sudo firewall-cmd --permanent --add-port=3306/tcp sudu firewall-cmd --reload

image.png

#如果发现not allow connect to this mysql server，需要配置允许外部连接

#执行以下命令

GRANT ALL PRIVILEGES ON *.* TO '你的用户名'@'%' IDENTIFIED BY '你的密码';

#然后刷新策略配置

flush privileges;

#如果出现

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password'' at line 1

错误，需要#分开几个步骤执行(高版本mysql把创建用户与赋予权限分开了)

  • 创建账户

    create user 'root'@'%' identified by '你的密码';

  • 赋予权限

    grant all privileges on *.* to 'root'@'%' with grant option;

  • 刷新

    flush privileges;

image.png

二、mysql主从复制

设置master

  • 找到mysql配置文件，通常在/etc/my.cnf或者/etc/mysql/my.cnf上

  • 修改配置文件，找到mysql进程配置

    [mysqld]

#设置服务器id

server-id=1

#开启bin_log，mysql-bin 等同于 /var/lib/mysql/mysql-bin 因为这是默认值，默认是打开的
#log-bin=mysql-bin
#或者(不确定，mysql8.0是下面这个)
#log_bin=binlog

#设置binlog的格式，statement/row/mixed 推荐mixed混合

binlog_format=mixed

#设置binlog清理时间

expire_logs_days=7

#如果只需要同步特定的数据库

#binlog-do-db=db_name

  • 重启服务器应用更改

​ systemctl restart mysqld

  • 创建用于复制的账号：登录主库服务器

    #创建一个拥有复制权限的用户账户
create user 'slave'@'%' identified by 'Root@#!123_456';
#赋予复制权限
grant replication slave  on *.* to 'slave'@'%' with grant option;
#修改用户的密码插件，否则会被SSL限制（如果需要开启则需要设置ssl相关设置）
alter user 'slave'@'%' identified with mysql_native_password by 'Root@#!123_456';
#修改用户的密码策略永不过期（想要密码使用不过期策略，密码需要设置得够复杂）
alter user 'slave'@'%' PASSWORD EXPIRE NEVER;
# 刷新权限使其生效
flush privileges; 
#获取当前主库二进制日志的位置信息，这将是从库启动时复制的偏移量
SHOW MASTER STATUS;

image.png

| binlog.000005 | 877

设置slave

  • 设置从库的配置文件

    vim /etc/my.cnf

    [mysqld]
server-id=2

  • 并重启mysql使其生效 systemctl restart mysqld

  • 在从库上配置主库连接信息，登录mysql客户端执行以下命令

    -- 设置主库主机地址
-- 设置具有复制权限的用户名
-- 设置密码
-- 主库的binlog文件
-- 主库binlog标记位
CHANGE MASTER TO
MASTER_HOST='192.168.31.128',
MASTER_USER='slave',
MASTER_PASSWORD='Root@#!123_456',
MASTER_LOG_FILE='binlog.000005',
MASTER_LOG_POS=877;

image.png

  • 启动复制线程

    START SLAVE;

  • 验证复制状态：检查从库的复制是否正常运行

    SHOW SLAVE STATUS ;

    如果 Slave_IO_Running 和 Slave_SQL_Running 都显示 "Yes"，并且没有其他错误，则表明主从复制已经成功建立。

image.png

验证

主库创建数据库，创建表，插入数据，从库也会创建数据库、表和相应数据，这个自行验证

其他问题

失败的时候，针对 SHOW SLAVE STATUS; 其中字段进行解析：

  • Slave_IO_Status 显示 Connecing to Source，说明从库连接不上，解决办法

    • 检查master创建的复制权限用户名密码是否正确
    • 检查该用户的密码策略是否有ssl，有的话需要改为 mysql_native_password 否则自行设置ssl
    • 检查该用户密码是否会过期
    • 主机ip地址、防火墙等是否开启
    • master的配置是否有执行策略刷新命令
    • 修改了配置文件服务有没有重启

  • Slave_SQL_Running 为NO

    • 检查 Last_Error 字段是否有报错，如果有，则需要分析错误信息

      • 如果出现以下错误，则需要把用户密码设置为从不过期

        Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000005, end_log_pos 1284; Error 'Your password does not satisfy the current policy requirements' on query. Default database: ''. Query: 'ALTER USER 'slave'@'%' PASSWORD EXPIRE NEVER'

      • 如果出现以下错误，则需要分析二进制文件

        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 binlog.000005, end_log_pos 1284. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

        • 进入服务器，执行命令查看文件 mysqlbinlog --start-position=1284 /path/to/mysql-bin.000005 ，其中start-position可以指定要查看的偏移量（我们查看到报错的位置就好）

        • 如果分析不出问题，可以把该binlog删除掉。。。一般是结构或者版本不对应导致从库无法执行master的二进制文件

          -- 删除指定时间以前的日志文件，在mysql服务器执行，慎重使用
-- PURGE BINARY LOGS BEFORE '2024-02-01 22:00:00'
-- 当然，你也可以渠去到服务器目录，用rm删除对应文件。。。我建议时用 mv去备份一下再删除。。。

  • 如果还是看不出问题，执行下面的sql，看具体报错的woker异常信息

    SELECT * FROM performance_schema.replication_applier_status_by_worker;

image.png

附带PDF版本

链接: pan.baidu.com/s/1GeswsXp6… 提取码: xgmg 复制这段内容后打开百度网盘手机App，操作更方便哦

avatar
文章
阅读
粉丝