MySQL主从集群快速搭建 原创 数据库 2022年3月11日 14:10 夏至未至 1046 当前内容 20347 字，在路上，马上到，马上到 ### 主从复制介绍 #### 复制基础 MySQL之间数据复制的基础是二进制日志文件，主节点(master)数据库启用二进制日志(binlog)后，数据库中所有操作都会以事件的方式记录在二进制日志中。 #### 复制原理 先看复制原理图：  主数据库服务器中操作记录到`binlog`中，其他从服务器通过一个 `I/O` 线程与主服务器保持通信，并监控`master`的二进制日志文件的变化，如果发现`master`二进制日志文件发生变化，则会把变化复制到自己的`中继日志`中，然后`slave`的一个SQL线程会把相关的事件执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。 ### 主从集群搭建 以下过程，均为实际应用实操，如果你搭建过程，有任何不一样的地方，欢迎留言讨论： 主:192.168.128.188 从:192.168.128.177 #### 主节点 ##### 修改数据库配置文件 [root@master /]# cat /etc/my.cnf ... server-id=188 log-bin=mysql-bin binlog_format=ROW ... ##### 创建日志同步用户 1. 主节点日志需要被同步到其他从节点，主节点就需要建一个有复制权限的用户，以供外部各从节点使用 [root@master /]# mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> create user 'slave'@'%' identified by '123456'; Query OK, 0 rows affected (0.41 sec) mysql> 2. 这一步如果报错，按如下方式处理，或者，也可以设置较复杂点的密码，安全级别够就能过去： mysql> create user 'slave'@'%' identified by '123456'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> mysql> mysql> set global validate_password_policy=0; Query OK, 0 rows affected (0.03 sec) mysql> mysql> set global validate_password_length=1; Query OK, 0 rows affected (0.02 sec) mysql> 3. 给此用户复制权限，使得其能远程复制主节点日志 mysql> mysql> grant replication slave, replication client on *.* to 'slave'@'%'; Query OK, 0 rows affected (0.72 sec) mysql> mysql> flush privileges; Query OK, 0 rows affected (0.10 sec) mysql> ##### 重启实例并检查配置 1. 停止主实例 [root@master /]# systemctl stop mysqld.service [root@master /]# [root@master mysql]# systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Fri 2022-03-11 11:00:02 PST; 3min 30s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 13724 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 13703 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 13727 (code=exited, status=0/SUCCESS) Mar 11 10:59:03 master.codecome systemd[1]: Starting MySQL Server... Mar 11 10:59:38 master.codecome systemd[1]: Started MySQL Server. Mar 11 11:00:00 master.codecome systemd[1]: Stopping MySQL Server... Mar 11 11:00:02 master.codecome systemd[1]: Stopped MySQL Server. [root@master mysql]# 2. 删除主服务器auto.cnf文件，待重启实例时，重新在 auto.cnf中生成UUID，auto.cnf 在数据目录下 [root@master mysql]# pwd /var/lib/mysql [root@master mysql]# [root@master mysql]# rm -rf auto.cnf [root@master mysql]# 3. 启动实例 [root@master mysql]# systemctl start mysqld.service [root@master mysql]# [root@master mysql]# systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Fri 2022-03-11 11:05:22 PST; 59s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 13892 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 13869 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 13895 (mysqld) Tasks: 27 CGroup: /system.slice/mysqld.service └─13895 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Mar 11 11:05:13 master.codecome systemd[1]: Starting MySQL Server... Mar 11 11:05:22 master.codecome systemd[1]: Started MySQL Server. [root@master mysql]# 4. 验证binlig是否开启，复制用户是否创建并且权限正确 mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.01 sec) mysql> mysql> show grants for 'slave'@'%'; +-------------------------------------------------------------------+ | Grants for slave@% | +-------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%' | +-------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ##### 加锁等待从库配置完成 1. 加读锁，防止两边数据不一致，如果你能保证当前节点并无外部使用，可以不用加锁： mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> 2. 查看当前数据库的二进制日志以及位置，从节点配置需要用到(当前窗口不要关闭，当前会话持有全局读锁。) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> #### 从节点 ##### 修改数据库配置文件 [root@slave /]# cat /etc/my.cnf ... server-id=177 relay-log=mysql-relay-bin ... ##### 重启实例并检查配置 1. 停止从实例 [root@slave /]# systemctl stop mysqld.service [root@slave /]# [root@slave /]# systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Thu 2022-03-10 14:29:54 PST; 12s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 1607 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 1083 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 1610 (code=exited, status=0/SUCCESS) Mar 10 01:04:22 slave.codecome systemd[1]: Starting MySQL Server... Mar 10 01:05:01 slave.codecome systemd[1]: Started MySQL Server. Mar 10 14:29:47 slave.codecome systemd[1]: Stopping MySQL Server... Mar 10 14:29:54 slave.codecome systemd[1]: Stopped MySQL Server. [root@slave /]# 2. 删除 auto.cnf 文件 [root@slave /]# cd /var/lib/mysql [root@slave mysql]# [root@slave mysql]# rm -rf auto.cnf [root@slave mysql]# 3. 启动从实例 [root@slave mysql]# systemctl start mysqld.service [root@slave mysql]# [root@slave mysql]# systemctl status mysqld.service ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2022-03-10 14:31:01 PST; 8s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 16111 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 16090 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 16114 (mysqld) Tasks: 27 CGroup: /system.slice/mysqld.service └─16114 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid Mar 10 14:30:31 slave.codecome systemd[1]: Starting MySQL Server... Mar 10 14:31:01 slave.codecome systemd[1]: Started MySQL Server. [root@slave mysql]# 4. 检查日志开启情况 [root@slave mysql]# mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like '%relay_%'; +---------------------------+--------------------------------------+ | Variable_name | Value | +---------------------------+--------------------------------------+ | max_relay_log_size | 0 | | relay_log | mysql-relay-bin | | relay_log_basename | /var/lib/mysql/mysql-relay-bin | | relay_log_index | /var/lib/mysql/mysql-relay-bin.index | | relay_log_info_file | relay-log.info | | relay_log_info_repository | FILE | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | +---------------------------+--------------------------------------+ 11 rows in set (0.00 sec) mysql> ##### 从节点连接主节点验证 [root@slave mysql]# mysql -uslave -p123456 -P3306 -h192.168.128.188 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.7.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 如果连接不上，就算后续配置完成，集群复制关系也不正常，如果如下报错： [root@slave mysql]# mysql -uslave -p123456 -P3306 -h192.168.128.188 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.128.188' (113) [root@slave mysql]# 在确保密码正常的情况下，就需要关闭主机防火墙了: [root@master mysql]# systemctl stop firewalld [root@master mysql]# [root@master mysql]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled) Active: inactive (dead) since Fri 2022-03-11 11:49:47 PST; 7min ago Docs: man:firewalld(1) Process: 651 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS) Main PID: 651 (code=exited, status=0/SUCCESS) Mar 10 00:20:55 master.codecome systemd[1]: Starting firewalld - dynamic firewall daemon... Mar 10 00:21:01 master.codecome systemd[1]: Started firewalld - dynamic firewall daemon. Mar 11 11:49:39 master.codecome systemd[1]: Stopping firewalld - dynamic firewall daemon... Mar 11 11:49:47 master.codecome systemd[1]: Stopped firewalld - dynamic firewall daemon. [root@master mysql]# ##### 从服务器配置同步信息 change master to master_host='主机IP',master_user='主机复制用户名',master_password='主机复制用户密码',master_port=主机端口号,master_log_file='主机当前binlog文件名',master_log_pos=主机当前binlog的Pos; mysql> change master to master_host='192.168.128.188',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000003',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.04 sec) mysql> 建议一定检查好，再执行。 ##### 从服务器启动复制线程 mysql> start slave; Query OK, 0 rows affected (0.03 sec) mysql> ##### 主服务器解锁 mysql> mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> ### 主从测试验证 #### 逻辑验证 在从节点上查询复制状态： [root@slave mysql]# mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.128.188 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 956 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 1122 Relay_Master_Log_File: mysql-bin.000003 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: 956 Relay_Log_Space: 1329 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: 188 Master_UUID: 32f471aa-a16e-11ec-823c-000c292885ab Master_Info_File: /var/lib/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) ERROR: No query specified mysql> Slave_IO_Running: Yes 代表成功连接到master并且下载日志正常 Slave_SQL_Running: Yes 代表成功执行日志中的SQL语句到当前从机 在主节点上查询集群状态： [root@master mysql]# mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 5.7.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave hosts\G; *************************** 1. row *************************** Server_id: 177 Host: Port: 3306 Master_id: 188 Slave_UUID: c25b8893-a0c1-11ec-b5dd-000c29ed8d72 1 row in set (0.00 sec) ERROR: No query specified mysql> 可以查到，当前主节点，有一个从节点信息。 #### 业务验证 在主节点建库，查看从节点同步情况： 主 [root@master mysql]# mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 Server version: 5.7.37-log MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> create database codecome; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | codecome | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> 从 [root@slave mysql]# mysql -uroot -p123456 -S/var/lib/mysql/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.7.37 MySQL Community Server (GPL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | codecome | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> ### MySQL主从复制总结 1. 主从复制必须保证两台数据库实例的 server-id 不一致 2. 主服务器必须开启二进制日志；从服务器必须开启中继日志 3. 主从复制搭建必须保证初始数据一致 4. 主服务器必须要给从服务器创建一个复制用户，并授予复制权限 5. Master—>Slave架构，主写会同步到从；而从写不会同步到主 ### 集群常见问题汇总 mysql> show slave status\G *************************** 1. row *************************** ... Slave_IO_Running: Connecting Slave_SQL_Running: Yes ... 这种情况，常见于复制用户连接不上主节点，常见原因为密码错误、主节点防火墙开启。 mysql> show slave status\G *************************** 1. row *************************** ... Slave_IO_Running: Yes Slave_SQL_Running: No ... Last_SQL_Errno: 1008 Last_SQL_Error: Error 'Can't drop database 'xxx'; database doesn't exist' on query. Default database: 'xxx'. Query: 'drop database xxx' ... 这种情况，常见于在搭建集群前，没有做主从集群静态数据的同步，也就是主从起始数据不一样，主机点有的数据库，从数据库没有，当主节点操作这个数据库时，从就同步报错，解决方法为把上边从的配置过程，在走一遍即可。 本文标题： MySQL主从集群快速搭建 本文作者： 夏至未至 发布时间： 2022年3月11日 14:10 最近更新： 2025年3月27日 16:54 原文链接： 许可协议： 署名-非商业性-禁止演绎 4.0 国际(CC BY-NC-ND 4.0) 请按协议转载并保留原文链接及作者 主从复制(1) 上一个 Base64转二进制流报错 下一个 Linux虚拟机时间大于宿主机8小时(已解决) 请可爱的你登录后回复 评论列表 (2条) 东方不败者 2022-03-13 18:29 · IP未知 按图理解最重要，理解了，搭建就很顺 回复 发表 夏至未至 作者 @ 东方不败者 2022-05-11 13:37 · IP未知 精辟 回复 发表 查看更多评论
评论列表 (2条)
东方不败者
2022-03-13 18:29 · IP未知按图理解最重要，理解了，搭建就很顺
夏至未至 作者 @ 东方不败者
2022-05-11 13:37 · IP未知精辟