前言

一台数据库扛不住读写压力，怎么办？搭主从复制，主库写、从库读，分担压力。

一、主从复制原理

原理：

1. 主库把写操作写进binlog
2. 从库IO线程读主库binlog，写进中继日志（relay log）
3. 从库SQL线程读中继日志，重放SQL

作用：

1. 读写分离，分担主库压力
2. 数据备份，主库挂了从库顶上
3. 异地容灾

二、准备工作

环境：

要求：

1. MySQL版本一致
2. 主库开启binlog
3. 主从时间同步

三、配置主库

1. 改my.cnf

[mysqld]
# 服务ID（唯一）
server_id = 1

# 开启binlog
log_bin = mysql-bin

# binlog格式
binlog_format = ROW

# 只复制哪些库（可选）
# binlog_do_db = mydb

# 忽略哪些库（可选）
# binlog_ignore_db = mysql

2. 重启主库

systemctl restart mysqld

3. 创建复制用户

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'repl123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;

4. 查看主库状态

SHOW MASTER STATUS;

输出：

记住File和Position，后面从库要用。

5. 锁表导数据

FLUSH TABLES WITH READ LOCK;

开另一个终端导出数据：

mysqldump -u root -p --all-databases --master-data=2 > all.sql

解锁：

UNLOCK TABLES;

四、配置从库1

1. 改my.cnf

[mysqld]
# 服务ID（唯一，和主库不同）
server_id = 2

# 中继日志
relay_log = relay-bin

2. 重启从库

systemctl restart mysqld

3. 导入主库数据

mysql -u root -p < all.sql

4. 关联主库

CHANGE MASTER TO
MASTER_HOST='192.168.1.10',
MASTER_USER='repl',
MASTER_PASSWORD='repl123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=156;

5. 开启从库

START SLAVE;

6. 查看状态

SHOW SLAVE STATUS\G

看两个值：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

都是Yes就成功了。

五、配置从库2

和从库1一样，只是server_id改成3：

[mysqld]
server_id = 3
relay_log = relay-bin

然后重复上面的步骤3-6。

六、验证主从同步

主库插入数据

CREATE TABLE test (id INT);
INSERT INTO test VALUES (1);

从库查

SELECT * FROM test;

能查到就同步成功了。

七、常用操作

查看从库状态

SHOW SLAVE STATUS\G

停止从库

STOP SLAVE;

重置从库

RESET SLAVE;

查看主库有哪些从库

SHOW SLAVE HOSTS;

常见坑

坑1：server_id重复

主库和从库server_id一样，复制失败。

解决： 每个MySQL实例server_id必须唯一。

坑2：防火墙挡了3306

从库连不上主库。

解决： 主库开放3306端口。

坑3：复制用户权限不够

解决： 复制用户需要REPLICATION SLAVE权限。

坑4：数据不一致

主从数据对不上。

原因：

1. 从库写了数据
2. 主库跳过了错误
3. 大事务导致延迟

解决： 从库只读：

[mysqld]
read_only = 1

坑5：从库延迟很大

主库写太快，从库追不上。

解决：

1. 用半同步复制
2. 从库硬件升级
3. 拆分更多从库

总结

搭建步骤：

1. 主库开启binlog，设server_id=1
2. 主库创建复制用户
3. 主库锁表导出数据
4. 从库改server_id，导入数据
5. 从库关联主库，START SLAVE
6. 验证两个Yes

记住： 一主多从配置简单，关键是server_id唯一、binlog格式一致。

遇到问题加QQ23979811 协助处理

前言

做网站的同学肯定都遇到过这种情况：网站用户越来越多，数据库压力越来越大，单台数据库扛不住了。这时候怎么办？很常见的方案就是做主从复制，主库写数据，从库读数据，把读写压力分开。

很多新手同学觉得主从复制很难，不敢尝试。其实只要按照步骤来，一步步配置，很快就能搭起来。这篇文章就把一主一从的主从复制搭建完整教程整理出来，从原理到实操，一步一步讲清楚。

一、主从复制是干嘛的

简单来说，主从复制就是：主数据库（Master）负责写数据，从数据库（Slave）负责读数据。主库的数据会自动同步到从库上。

主从复制的作用

1. 读写分离：写操作走主库，读操作走从库，减轻主库压力
2. 数据备份：从库相当于实时备份，主库挂了从库还能顶上
3. 做数据分析：可以在从库上跑复杂的统计查询，不影响主库的业务

主从复制的原理

主从复制的原理其实很简单，三步：

1. 主库把数据变更记录到二进制日志（binlog）里
2. 从库把主库的binlog拉过来，写到自己的中继日志（relay log）里
3. 从库重放中继日志里的SQL，这样数据就和主库一致了

二、准备工作

搭建主从复制之前，先准备好环境：

服务器准备

需要两台服务器：

• 主库服务器：IP假设是 192.168.1.10
• 从库服务器：IP假设是 192.168.1.11

软件要求

• 两台服务器都要安装MySQL，版本最好一样
• 主库和从库的server-id不能一样
• 两台服务器的MySQL端口要能互相通（一般是3306）

三、配置主库

首先配置主库，编辑主库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加这些配置：

[mysqld]
# 唯一的服务器ID，主库和从库不能一样
server-id = 1

# 开启二进制日志
log-bin = mysql-bin

# 需要同步的数据库，多个用逗号隔开
binlog-do-db = testdb

# 不同步的数据库
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

修改完之后重启主库的MySQL：

systemctl restart mysqld

给从库创建一个复制账号

主库上要创建一个专门用来复制的用户，从库要用这个账号来连主库拉数据。

登录主库MySQL：

mysql -u root -p

然后执行：

CREATE USER 'repl'@'192.168.1.11' IDENTIFIED BY 'repl123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.11';
FLUSH PRIVILEGES;

这个命令创建了一个叫repl的用户，密码是repl123456，只能从从库的IP（192.168.1.11）连接，并且有复制的权限。

查看主库的状态

接下来要记录主库的binlog文件名和位置，从库配置的时候要用：

SHOW MASTER STATUS;

执行完之后会看到类似这样的结果：

+------------------+----------+--------------+----------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000001 |      156 | testdb       | mysql,information_schema,...     |                   |
+------------------+----------+--------------+----------------------------------+-------------------+

记住File和Position的值，比如这里是 mysql-bin.000001 和 156。

注意：执行完这个命令之后，主库就不要再写数据了，不然Position会变，到时候从库同步就会出错。

四、配置从库

接下来配置从库。编辑从库的my.cnf配置文件：

vi /etc/my.cnf

在 [mysqld] 下面添加：

[mysqld]
# 唯一的服务器ID，和主库不一样就行
server-id = 2

# 开启中继日志
relay-log = relay-bin

# 只读模式，从库默认只读，防止误写
read_only = 1

修改完之后重启从库的MySQL：

systemctl restart mysqld

配置从库连接主库

登录从库的MySQL：

mysql -u root -p

然后执行下面的命令，告诉从库主库在哪里，用什么账号连接：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl123456',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=156;

这里的MASTER_LOG_FILE和MASTER_LOG_POS就是刚才在主库上用SHOW MASTER STATUS查出来的值。

启动从库复制

配置完之后，启动从库的复制线程：

START SLAVE;

查看从库状态

看看从库是不是正常运行：

SHOW SLAVE STATUS\G

执行完之后会看到很多信息，重点看这两个：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如果这两个都是Yes，那就说明主从复制已经搭好了！

如果是No，那就说明哪里出错了，下面的Last_Error字段会显示错误信息，根据错误信息去排查就行。

五、测试主从复制

现在来测试一下主从复制是不是正常工作。

在主库上创建一个表，插入一些数据：

USE testdb;
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO test (name) VALUES ('张三'), ('李四'), ('王五');

然后去从库上查一下：

USE testdb;
SELECT * FROM test;

如果能看到刚才插入的那三条数据，那就说明主从复制是正常工作的！

六、主从复制的常见问题

主从延迟

主从复制不是实时的，有时候主库写了数据，从库要等一会儿才能读到。如果主库压力很大，或者从库性能差，延迟可能会很大。

可以用这个命令查看从库延迟了多少秒：

SHOW SLAVE STATUS\G

看Seconds_Behind_Master这个值，就是延迟的秒数。如果是0，说明没有延迟。

从库误写数据

很多同学不知道从库是只读的，不小心在从库上写了数据，导致主从数据不一致，复制出错。

解决方法：一定要开启read_only=1，并且不要用root账号在从库上操作。

常见坑

坑1：主库和从库的server-id一样

很多同学配置的时候，忘了改从库的server-id，结果主从复制启动不起来。记住：主库和从库的server-id必须不一样。

坑2：主库防火墙没开

主库的防火墙没开3306端口，导致从库连不上主库。一定要检查防火墙是不是放行了3306端口。

坑3：配置完CHANGE MASTER之后忘了START SLAVE

很多同学配置完主库和从库之后，就以为完事了，结果发现数据根本没同步。这是因为忘了执行START SLAVE启动复制线程。

坑4：SHOW MASTER STATUS之后主库还在写数据

很多同学在主库执行了SHOW MASTER STATUS之后，还在往主库写数据，导致Position变了，从库配置的时候用的是旧的Position，结果同步出错。记住：执行SHOW MASTER STATUS之后，主库就不要再写数据了。

坑5：主库和从库数据一开始就不一致

很多同学的主库已经有很多数据了，直接配置主从复制，结果从库同步的时候出错。正确的做法应该是：先把主库的数据导出，导入到从库，然后再配置主从复制。

总结

MySQL主从复制搭建的步骤：

1. 准备两台服务器，都安装好MySQL
2. 配置主库：设置server-id，开启binlog
3. 主库创建复制账号
4. 主库执行SHOW MASTER STATUS，记录File和Position
5. 配置从库：设置server-id，开启relay log
6. 从库执行CHANGE MASTER TO，指向主库
7. 从库执行START SLAVE启动复制
8. 查看从库状态，确认IO和SQL线程都是Yes
9. 在主库写数据，从库查数据，测试同步

主从复制是数据库架构的基础，学会了之后才能搞读写分离、分库分表这些更高级的东西。

遇到问题加QQ23979811 协助处理