MySQL 主从复制一主两从环境配置实战

MySQL 初始化

MySQL 主从复制是指数据可以从一个 MySQL 数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式;从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表.

1. 安装指定版本

yum install -y http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum install -y mysql-community*5.7.30-1.el7.x86_64

2. 初始化账号

set global validate_password_policy=0; set global validate_password_length=4;
alter user user() identified by 'root';
update mysql.user set host='%' where user='root';
flush privileges;

Master 端配置

1. 修改 mysqld 配置文件

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
max_connections = 2000
server-id=1
log-bin=mysql-binlog
binlog_format = ROW
binlog_row_image = minimal
; 仅记录数据库 demo01 的二进制日志 binlog
binlog-do-db = demo01

systemctl restart mysqld

2. 创建复制账号

grant replication slave on _._ to 'repl'@'%' identified by '123456';
flush privileges;

3. 查看主库状态
show master status\G;

*************************** 1. row ***************************
             File: mysql-binlog.000002
         Position: 306
     Binlog_Do_DB: demo01
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Slave 端配置

1. 修改 mysqld 配置文件

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
max_connections = 2000

server-id=2
log-bin=mysql-binlog
binlog_format = ROW
binlog_row_image = minimal
master_info_repository = TABLE
relay_log_info_repository = TABLE
; 当 relay-log 损坏时, 可根据当前slave端sql线程回放的位置, 重新从master上获取日志
relay_log_recovery = 1

; 备注:
; mysql slave端二进制日志没有其它用途，其实可以不必开启从端 binlog

2. 设置主从复制时的元数据(master 同步主机账号及 binlog 文件和位置)

mysql> change master to
master_host="192.168.31.17",
master_port=3306,
master_user="repl",
master_password="123456",
master_log_file="mysql-binlog.000002",
master_log_pos=306;

; 设置从库只读, 并启动slave
mysql> set global super_read_only=1;
mysql> start slave;

3. 查看状态
show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.17
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000002
          Read_Master_Log_Pos: 306
               Relay_Log_File: c8-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: mysql-binlog.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: 306
              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  # slave 端落后于 master 端多少秒
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: 1
                  Master_UUID: 7aa006de-1b3f-11eb-842d-525400f46c59
             Master_Info_File: mysql.slave_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 thread 按照 Master_Log_File、Read_Master_Log_Pos 位置读取主库的 binlog，并写入到本地 relay log
slave sql thread 按照 Relay_Log_File、Relay_Log_Pos 位置进行 realy log 的回放

从服务器 sql 线程执行状态

Waiting for the next event in relay log 该状态是读取 relay log 之前的初始状态
Reading event from the relay log 该状态表示此线程已经在 relay log 中读取了一个事件准备执行
Making temp file 该状态表示此线程正在执行 LOAD_DATA_INFILE 并且正在创建一个临时文件来保存从服务器将要读取的数据
Slave has read all relay log; waiting for the slave I/O thread to update it 该线程已经处理完了 relay log 中的所有事件，现在正在等待 slave /O 线程更新 relay log 文件
Waiting for slave mutex on exit 当线程停止的时候会短暂的出现该情况
updating # 表示正在更新
Opening tables
Sending data

验证测试程

Master 端建库建表, 看 slave 端是否同步

master 端

-- 查看 slave 情况
show slave hosts;
show master status\G;

create database demo01;
create database demo02;
use demo01;
create table user(
id int auto_increment,
name varchar(16) not null,
age int(16) not null,
primary key(id)
)engine=innodb charset=utf8;

-- 插入入数据
insert into user(name,age) values('tom',31),('user1', 18),('user2', 16),('user3', 33),('user4', 12);

-- 查看 二进制文件
show binary logs;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| mysql-binlog.000001 |       177 |
| mysql-binlog.000002 |      1079 |
+---------------------+-----------+
2 rows in set (0.00 sec)

-- 查看 二进制事件
show binlog events in 'mysql-binlog.000002';
+---------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name            | Pos  | Event_type     | Server_id | End_log_pos | Info      |
+---------------------+------+----------------+-----------+-------------+--------------------------------------+
| mysql-binlog.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.30-log, Binlog ver: 4|
| mysql-binlog.000002 |  123 | Previous_gtids |         1 |         154 | |
| mysql-binlog.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 |  219 | Query          |         1 |         306 | flush privileges |
| mysql-binlog.000002 |  306 | Anonymous_Gtid |         1 |         371 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 |  371 | Query          |         1 |         471 | create database demo01|
| mysql-binlog.000002 |  471 | Anonymous_Gtid |         1 |         536 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 |  536 | Query          |         1 |         748 | use `demo01`; create table user(
id int auto_increment,
name varchar(16) not null,
age int(16) not null,
primary key(id)
)engine=innodb charset=utf8 |
| mysql-binlog.000002 |  748 | Anonymous_Gtid |         1 |         813 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'|
| mysql-binlog.000002 |  813 | Query          |         1 |         887 | BEGIN|
| mysql-binlog.000002 |  887 | Table_map      |         1 |         940 | table_id: 108 (demo01.user)|
| mysql-binlog.000002 |  940 | Write_rows     |         1 |        1048 | table_id: 108 flags: STMT_END_F|
| mysql-binlog.000002 | 1048 | Xid            |         1 |        1079 | COMMIT /* xid=24 */|
+---------------------+------+----------------+-----------+-------------+--------------------------------------+
13 rows in set (0.00 sec)

-- 二进制事件位置 Pos 813 ~ 1048 执行的是如下插入操作
insert into user(name,age) values('tom',31),('user1', 18),('user2', 16),('user3', 33),('user4', 12);

slave 端

-- 从端查看是否同步
show databases;

-- 查看指定数据是否同步
use demo01;
select * from user;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | tom   |  31 |
|  2 | user1 |  18 |
|  3 | user2 |  16 |
|  4 | user3 |  33 |
|  5 | user4 |  12 |
+----+-------+-----+
5 rows in set (0.01 sec)

posted @ 2020-11-28 09:49 JiaWen技术圈阅读(109) 评论(0) 编辑收藏举报

刷新页面返回顶部

Loading

运维技术帮

MySQL 主从复制一主两从环境配置实战

MySQL 初始化

Master 端配置

Slave 端配置

验证测试程

公告