search

BruceOuyang / issuelist

用于记录日常碰到的各种问题和经验总结 （请看Issues）
4 stars 2 forks source link

mysql 主从复制 #98

Open BruceOuyang opened 2 years ago

BruceOuyang commented 2 years ago

两种方式： 1、基于 binlog+pos 的主从复制 2、基于 gtid 的主从复制

假设两个 mysql 节点的网络连接信息：

master : 192.168.114.177:3306
slave  : 192.168.114.178:3306

官方资料参考 https://dev.mysql.com/doc/refman/5.7/en/replication.html

BruceOuyang commented 2 years ago

基于 binlog+pos 的主从复制

master 节点 mysql 配置

  1. 修改 my.cnf 配置，在 [mysqld] 下追加如下设置 
    [mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = mixed

server-id 服务id，必须指定，在主从关系中保持唯一 log-bin 用于开启二进制日志，设置任意值 binlog_format 混合模式，主从必须设置一致

  1. 重启 mysql 服务

  2. 检查 binlog 状态

    
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /www/server/data/mysql-bin       |
| log_bin_index                   | /www/server/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
| sql_log_bin                     | ON                               |
+---------------------------------+----------------------------------+

mysql> show binlog events; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 | | mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000001 | 154 | Stop | 1 | 177 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 3 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000010'; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000010 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.37-log, Binlog ver: 4 | | mysql-bin.000010 | 123 | Previous_gtids | 1 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec)

mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 437 | | mysql-bin.000003 | 685 | | mysql-bin.000004 | 819 | | mysql-bin.000005 | 177 | | mysql-bin.000006 | 651 | | mysql-bin.000007 | 177 | | mysql-bin.000008 | 1496 | | mysql-bin.000009 | 177 | | mysql-bin.000010 | 154 | +------------------+-----------+ 10 rows in set (0.00 sec)


4. 创建一个用来同步的用户

mysql> grant replication slave on . to replicator@'192.168.114.%' identified by 'replicator'; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

> 用户名：replicator 密码：replicator  主机：192.168.114.x网段

5. 查看 master 状态

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000010 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)

> 此处的 File 与 Position 用于 slave 连接 master 时设置

#### slave 节点 mysql 配置
1. 修改 my.cnf 配置，在 `[mysqld]` 下追加如下设置

[mysqld] server-id = 2 binlog_format = mixed


> server-id 必须指定，在主从关系中保持唯一
> binlog_format 混合模式，主从必须设置一致

2. 重启 mysql 服务

3. 连接 master 服务器

mysql> change master to master_host='192.168.114.177', master_port=3306, master_user='replicator', master_password='replicator', master_log_file='mysql-bin.000010',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.01 sec)

> master_host 主节点 mysql 服务的 IP
> master_port 主节点 mysql 服务的端口
> master_user 用来执行同步操作的 mysql 用户名
> master_password 用来执行同步操作的 mysql 用户密码
> master_log_file 同步主节点数据起始位置日志文件，通过在主节点执行 `show master status` 查看
> master_log_pos 同步主节点数据起始位置日志文件位置，通过在主节点执行 `show master status` 查看 

5. 启动 slave

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


6. 查看 slave 状态

mysql> show slave status\G; 1. row Slave_IO_State: Waiting for master to send event Master_Host: 192.168.114.177 Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 607 Relay_Log_File: mysql02-relay-bin.000002 Relay_Log_Pos: 773 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... ... ...

1 row in set (0.00 sec)

ERROR: No query specified

> Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 表示连接 master 成功

7. 关闭 slave

stop slave;


#### 测试
1. 在 master 创建表和插入数据

mysql> CREATE DATABASE db_demo01 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; Query OK, 1 row affected (0.00 sec)

mysql> use db_demo01; Database changed

mysql> create table tb_user(id int primary key comment '主键ID'); Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb_user values(1); Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_user; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)


2. 在 slave 查看数据

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_demo01 | | mysql | | performance_schema | | sync_demo01 | | sync_demo02 | | sys | +--------------------+ 7 rows in set (0.00 sec)

mysql> use db_demo01; Database changed mysql> show tables; +---------------------+ | Tables_in_db_demo01 | +---------------------+ | tb_user | +---------------------+ 1 row in set (0.00 sec)

mysql> select * from tb_user; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)



> follow https://blog.csdn.net/qq_15092079/article/details/81672920
BruceOuyang commented 2 years ago

基于 gtid 的主从复制

如果之前配置过主从，需要先在 slave 节点执行 stop slave 关闭从节点的同步功能

master 节点 mysql 服务配置

  1. 修改 my.cnf 配置，在 [mysqld] 下追加如下设置

    server-id = 1
log-bin = mysql-bin
binlog_format = mixed
gtid-mode = ON
enforce-gtid-consistency = ON

  2. 重启 mysql 服务

  3. 创建一个用来同步的用户

    
mysql> grant replication slave on *.* to replicator@'192.168.114.%' identified by 'replicator';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

> 用户名：replicator 密码：replicator 主机：192.168.114.x网段

#### slave  节点 mysql 服务配置
1. 修改 my.cnf 配置，在 `[mysqld]` 下追加如下设置

server-id = 2 log-bin = mysql-bin binlog_format = mixed gtid-mode = ON enforce-gtid-consistency = ON log_slave_updates = ON skip-slave-start = 1


2. 重启 mysql 服务

3. 连接 master 服务器

mysql> change master to master_host='192.168.114.177', master_port=3306, master_user='replicator', master_password='replicator', master_auto_position=1; Query OK, 0 rows affected, 2 warnings (0.01 sec)


#### 测试
1. 在 master 创建表和插入数据

mysql> drop schema if exists db_demo01;

mysql> CREATE DATABASE db_demo01 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; Query OK, 1 row affected (0.00 sec)

mysql> use db_demo01; Database changed

mysql> create table tb_user(id int primary key comment '主键ID'); Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb_user values(1); Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_user; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)

mysql> show master status; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mysql-bin.000001 | 326 | | | 441184f9-9deb-11ec-a83d-000c29cda359:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec)


2. 在 slave 查看数据

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_demo01 | | mysql | | performance_schema | | sync_demo01 | | sync_demo02 | | sys | +--------------------+ 7 rows in set (0.00 sec)

mysql> use db_demo01; Database changed mysql> show tables; +---------------------+ | Tables_in_db_demo01 | +---------------------+ | tb_user | +---------------------+ 1 row in set (0.00 sec)

mysql> select * from tb_user; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec)

mysql> show slave status\G; 1. row Slave_IO_State: Waiting for master to send event Master_Host: 192.168.114.177 Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 326 Relay_Log_File: mysql02-relay-bin.000003 Relay_Log_Pos: 539 Relay_Master_Log_File: mysql-bin.000001

                            ...
                            ...
                            ...

              Master_UUID: 441184f9-9deb-11ec-a83d-000c29cda359
                            ...
                            ...

       Retrieved_Gtid_Set: 441184f9-9deb-11ec-a83d-000c29cda359:1
        Executed_Gtid_Set: 441184f9-9deb-11ec-a83d-000c29cda359:1

                            ...
                            ...


> 观察  Executed_Gtid_Set 与 master 应该是一致的
> follow : https://blog.51cto.com/u_13434336/2178937