mysql5.7.31 主从复制搭建

• 生活家

Mysql 主从复制

一.检查环境

  • 机器一(192.168.0.109) centos7,mysql5.7.31

    • 开放3306端口

  • 机器二(192.168.0.111) centos7,mysql5.7.31

    • 开放3306端口

  • 需要注意 如果是通过克隆虚拟机方式搭建环境 注意坑 mysql server uuid 冲突

    • # 找到mysql datadir
mysql> show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.01 sec)

----------------------------------------------
# 编辑 /data/mysql/ 路径下 auto.cnf 文件
[auto]
server-uuid=80422fd7-34a5-11eb-b54e-000c29d7a930

# 将uuid修改至唯一,随便更改一位即可 保存退出 重启mysql服务
service mysqld restart

二.修改 mysql配置文件

  • 新增配置文件 master-slave.cnf 位于/etc/my.cnf.d/文件夹下
# master-slave.cnf 文件内容如下 根据提示相应修改
[mysqld]
# *设置server_id，一般设置为IP 要有唯一性* 
server_id=110
# 复制过滤：需要备份的数据库，输出binlog
#binlog-do-db=roncoo
# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
# 中继日志
relay_log=mysql-relay-bin
# 开启二进制日志功能，可以随便取，最好有含义
log-bin=mysql-bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

  • 在 /etc/my.cnf 中引入/etc/my.cnf.d/文件夹下的配置文件

    将 !includedir /etc/my.cnf.d 追加到/etc/my.cnf 文件末尾

    • 方式一:
    [root@192 ~]# echo '!includedir /etc/my.cnf.d' >> /etc/my.cnf
    • 方式二:
    vim 编辑 /etc/my.cnf 将 !includedir /etc/my.cnf.d 追加到文件末尾

1.修改两台机器 /etc/my.cnf 配置文件

  • 机器一 ( 192.168.0.109)
  • /etc/my.cnf
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/usr/local/env/mysql-5.7.31
# 设置mysql数据库的数据的存放目录
datadir=/data/mysql
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
symbolic-links=0
explicit_defaults_for_timestamp=true
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
log-error=/data/mysql/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
pid-file=/data/mysql/mariadb.pid
!includedir /etc/my.cnf.d
  • /etc/my.cnf.d/master-slave.cnf
[mysqld]
# *设置server_id，一般设置为IP 要有唯一性* 
server_id=109
# 复制过滤：需要备份的数据库，输出binlog
#binlog-do-db=roncoo
# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
# 中继日志
relay_log=mysql-relay-bin
# 开启二进制日志功能，可以随便取，最好有含义
log-bin=mysql-bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

  • 机器二 ( 192.168.0.111)

  • /etc/my.cnf

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/usr/local/env/mysql-5.7.31
# 设置mysql数据库的数据的存放目录
datadir=/data/mysql
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
symbolic-links=0
explicit_defaults_for_timestamp=true
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
log-error=/data/mysql/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid
pid-file=/data/mysql/mariadb.pid
!includedir /etc/my.cnf.d
  • /etc/my.cnf.d/master-slave.cnf
[mysqld]
# *设置server_id，一般设置为IP 要有唯一性* 
server_id=111
# 复制过滤：需要备份的数据库，输出binlog
#binlog-do-db=roncoo
# 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
# 中继日志
relay_log=mysql-relay-bin
# 开启二进制日志功能，可以随便取，最好有含义
log-bin=mysql-bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

2.重启两台机器mysql服务

service mysqld restart

3.新增slave用户 配置 开始主从复制

​ 两台机器mysql服务主从配置完成后 是可以 互为主从 的, 我们先做主从模式

  • 选出一台机器做Master(192.168.0.109) 一台机器做Slave(192.168.0.111)

  • 在Master机器上新增用户,并查看Master状态;

    • 新增用户
    # 新增用户 slave 允许从ip为192.168.0.111的机器,通过密码 pwd.slave 来访问
mysql> grant replication slave,replication client on *.* to 'slave'@'192.168.0.111' identified by 'pwd.slave';

# 刷新使其生效;
mysql> FLUSH PRIVILEGES;
    • 查看Master status;
    #查看Master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1307 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 记住文件名 mysql-bin.000002 和 Position 1307;

  • 在Slave机器上 设置链接master的信息,并开始主从复制;

    • 在Slave机器上 设置链接master的信息
    # 先停止以下,避免会出错;
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

# 设置master服务器的一些参数 参数说明看下面的解释
mysql> change master to master_host='192.168.0.109', master_user='slave', master_password='pwd.slave', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=1060, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

# start slave
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

# 查看slave status
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.109
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1307
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1307
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 111
                  Master_UUID: 80422fd7-34a5-11eb-b54e-0100c29da930
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

# 当看到 以下时 说明链接成功...
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

    master_host='192.168.0.109' 			# Master的IP地址
master_user='slave' 					# 用于同步数据的用户（在Master中授权的用户）
master_password='pwd.slave'				# 同步数据用户的密码
master_port=3306						# Master数据库服务的端口
master_log_file='mysql-bin.000002'		#指定Slave从哪个日志文件开始读复制数据（可在Master上使用show master status查看到日志文件名）
master_log_pos=1307						# 从哪个POSITION号开始读
master_connect_retry=30 				#当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。

    # 可通过此命令查看链接状态 横向展示不便 就竖向展示了
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 6
   User: root
   Host: 192.168.0.105:65147
     db: NULL
Command: Sleep
   Time: 44
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 12
   User: slave
   Host: 192.168.0.111:56010
     db: NULL
Command: Binlog Dump
   Time: 1067
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 3. row ***************************
     Id: 14
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
3 rows in set (0.00 sec)

4.测试MySQL的主从备份

测试就是在主库中进行DDL以及DML语句的执行，看从库是否会跟主库一样发生变化

master中

create database test01;
use test01;

CREATE TABLE `test01` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

insert into test01(username) values('zhangsan');

查看master与slave是否都有变化,如果数据一致，就说明配置成功

互为主从

将 新建主从用户 配置主从参数 互换着再来一次就好了 可以自己尝试

本文地址：https://blog.csdn.net/weixin_45228674/article/details/110530295

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐