麦克斯仇
Think different
158
文章
18670
阅读
首页
INDEX
文章
ARTICLE
关于
ABOUT
MySQL主从复制
创建日期：
2022/05/16
修改日期：
2024/02/29
MySQL
# 简介 ## 如何提升并发能力 一般应用对数据库而言都是 **“读多写少”** ，对数据库读取数据的压力比较大，采用数据库集群的方案，做 **主从架构** 、进行 **读写分离** ，这样同样可以提升数据库的并发处理能力。但并不是所有的应用都需要对数据库进行主从架构的设置，毕竟设置架构本身是有成本的。 如果目的在于提升数据库高并发访问的效率，首先考虑的是如何 **优化SQL和索引** ，这种方式简单有效；其次是采用 **缓存的策略** ，比如使用 `Redis` 将热点数据保存在内存数据库中，提升读取的效率；最后才是对数据库采用 **主从架构** ，进行 **读写分离**。 ## 主从复制的作用 - 读写分离 - 数据备份 - 高可用 ## 架构图  # 原理 > 三个线程 实际上主从同步的原理就是基于 `binlog` 进行数据同步的。在主从复制过程中，会基于 3 个线程来操作，一个主库线程，两个从库线程。  1. `二进制日志转储线程 （Binlog dump thread）` ：是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件（ `Event` ）的时候，会在 `Binlog` 上加锁，读取完成之后，再将锁释放掉。 2. `从库 I/O 线程` ：会连接到主库，向主库发送请求更新 `Binlog` 。这时从库的 `I/O` 线程就可以读取到主库的二进制日志转储线程发送的 `Binlog` 更新部分，并且拷贝到本地的 `中继日志（Relay log）` 。 3. `从库 SQL 线程` ：会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。  > 复制三步骤 1. `Master` 将写操作记录到二进制日志（ `binlog` ）。 2. `Slave` 将 `Master` 的 `binary log events` 拷贝到它的中继日志（ `relay log` ）； 3. `Slave` 重做中继日志中的事件，将改变应用到自己的数据库中。 `MySQL` 复制是异步的且串行化的，而且重启后从 接入点 开始复制。 > 复制的问题 复制的最大问题： 延时 > 复制的基本原则 - 每个 `Slave` 只有一个 `Master` - 每个 `Slave` 只能有一个唯一的服务器 `ID` - 每个 `Master` 可以有多个 `Slave` # 服务搭建 ## 基础环境搭建 准备多台服务器，并按如下教程安装 `MySQL` ，且所有节点的版本必须相同 - [Windows安装MySQL](https://maxqiu.com/article/detail/2) - [CentOS7安装MySQL](https://maxqiu.com/article/detail/5) - [Docker常用镜像：MySQL](https://maxqiu.com/article/detail/32) 注意：如果是虚拟机环境，通过安装一台机器后克隆虚拟机完成多台服务器的安装，需要修改如下配置 - `MAC` 地址：通过虚拟机修改<br> - `IP` 地址：动态 `IP` 不用管，静态 `IP` 需要修改 - 主机名（hostname）： - `Linux`：`hostnamectl set-hostname xxx` - `Windows`：略，自行搜索教程 - `server-uuid`：在数据目录下，有一个 `auto.cnf` 文件，文件里面有个 `server-uuid` ， 每个服务的 `server-uuid` 需要不同。如果不修改，最后查看状态时会显示：`Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. ` ## 主机配置 > 配置文件 修改 `MySQL` 配置文件，在 `mysqld` 下面添加或修改如下配置，修改完成后重启服务器 ```ini # [必须] 服务器唯一ID（主从复制时需要不一样） server-id=1 # [可选] 是否只读，0（默认）表示读写（主机），1表示只读（从机） read-only=OFF # [可选] binlog 文件位置（默认 datadir 目录下，不建议和数据文件放在同一目录或磁盘）（不配置时默认启用并使用默认路径和文件名） log_bin=/usr/local/mysql/log/log-bin ``` 修改完成后重启服务器 > 建立账户并授权 1. 连接 `MySQL` 2. 创建账户并设置密码，如果从机只有一台，`%` 可以设置为从机 `IP`，或者为多个从机分别创建账户<br>`CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';` 2. 授权<br>`GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';` > 查询主机状态 通过 `SHOW MASTER STATUS;` 查看二进制文件状态状态，记录 `File` 和 `Position` ``` mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | log-bin.000004 | 2090 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ``` 注意：执行完此步骤后不要再操作主服务器，防止主服务器状态值变化。如有操作，需要重新查看最新状态 ## 从机配置 > 配置文件 修改 `MySQL` 配置文件，在 `mysqld` 下面添加或修改如下配置，修改完成后重启服务器 ```ini # [必须] 服务器唯一ID（主从复制时需要不一样） server-id=2 # [可选] 是否只读，0（默认）表示读写（主机），1表示只读（从机） read-only=ON # [可选] 二进制日志文件位置（默认 datadir 目录下，不建议和数据文件放在同一目录或磁盘）（不配置时使用默认路径和文件名） log_bin=/usr/local/mysql/log/log-bin # [可选] 设置中继日志文件位置 relay_log=/usr/local/mysql/log/log-relay ``` 修改完成后重启服务器 > 配置主机信息 使用如下命令配置需要复制的主机信息 ``` CHANGE MASTER TO MASTER_HOST='主机的地址', MASTER_PORT='主键的端口', MASTER_USER='主机用户名', MASTER_PASSWORD='主机用户名的密码', MASTER_LOG_FILE='主机的File值', MASTER_LOG_POS=主机的Position值; ``` 举例：`CHANGE MASTER TO MASTER_HOST='192.168.220.101',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='log-bin.000004',MASTER_LOG_POS=2090;` > 开启同步 使用 `START SLAVE;` 开启同步 > 查看状态 使用 `SHOW SLAVE STATUS\G` 查看状态，如果正常运行，则 `Slave_IO_Running` 和 `Slave_SQL_Running` 均显示为 `Yes` ``` mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.220.101 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log-bin.000004 Read_Master_Log_Pos: 2090 Relay_Log_File: log-relay.000002 Relay_Log_Pos: 324 Relay_Master_Log_File: log-bin.000004 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: 2090 Relay_Log_Space: 528 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: 1 Master_UUID: 539c4f2f-d453-11ec-9853-000c29120db6 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) ``` > 失败排查 若未能正确配置，可能有如下原因 1. 网络不通 2. 账户密码错误 3. 防火墙 4. 配置文件问题 5. 连接服务器时语法 6. 主服务器权限 7. 其他 当发现具体问题时，建议先执行 `STOP SLAVE;` 停止主从，再执行 `RESET SLAVE;` 重置状态，然后确认主库的二进制文件状态，最后再从 `CHANGE MASTER ...` 开始执行 ## 测试主从复制 在主库执行一下创建库、创建表、插入数据等操作，查看从库是否刷新数据 ## 关闭主从 执行 `STOP SLAVE;` 停止主从
18
全部评论
