17370845950

新闻动态

mysql如何创建主从复制数据库_mysql创建主从复制数据库的教程

MySQL主从复制通过配置主库启用二进制日志、创建复制用户并获取日志位置，从库设置server-id并连接主库同步数据，实现读写分离与数据备份。

MySQL主从复制，简单来说，就是把一个MySQL服务器（主服务器）的数据复制到另一个或多个MySQL服务器（从服务器）上。这样做的好处显而易见：读写分离，提升性能；数据备份，提高可用性；分析报表，减轻主库压力等等。接下来，我们就一步步看看如何搭建MySQL主从复制。

解决方案

配置主服务器（Master）
- 修改MySQL配置文件（my.cnf或my.ini）
  
  找到你的MySQL配置文件，通常在
```
/etc/mysql/mysql.conf.d/mysqld.cnf
```
  或
```
/etc/my.cnf
```
  ，也可能在其他地方，具体取决于你的操作系统和MySQL版本。
  
  在
```
[mysqld]
```
  部分添加或修改以下配置：
```
server-id   = 1  # 每个服务器的唯一ID，主服务器通常设置为1
log_bin     = mysql-bin  # 启用二进制日志，这是主从复制的关键
binlog_do_db = your_database_name # (可选) 只复制指定的数据库
#binlog_ignore_db = your_database_name # (可选) 忽略指定的数据库
#binlog_format = ROW # 推荐使用ROW模式，保证数据一致性
```
  - ```
  server-id
```
  ：必须是唯一的，用于区分不同的MySQL服务器。
- ```
log_bin
```
    ：指定二进制日志的文件名前缀。
  - ```
  binlog_do_db
```
  和
```
  binlog_ignore_db
```
  ：这两个选项允许你控制哪些数据库会被复制，哪些会被忽略。如果不设置，默认复制所有数据库。
- ```
binlog_format
```
    ：指定二进制日志的格式。
```
ROW
```
    模式记录每一行数据的变化，保证数据一致性，但日志量较大。
```
STATEMENT
```
    模式记录SQL语句，日志量较小，但某些情况下可能导致数据不一致。
```
MIXED
```
    模式是前两者的混合。推荐使用
```
ROW
```
    模式。
- 重启MySQL服务
  
  修改配置文件后，需要重启MySQL服务才能使配置生效。
```
sudo systemctl restart mysql
```
- 创建复制用户并授权
  
  登录到MySQL服务器，创建一个专门用于复制的用户，并授予其必要的权限。
```
CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
```
  - ```
  'repl'@'%'
```
  ：表示允许任何IP地址的主机使用
```
  repl
```
  用户进行复制。如果需要限制，可以将
```
  '%'
```
  替换为指定的IP地址或网段。
- ```
'your_password'
```
    ：替换为你自己的密码。
  - ```
  REPLICATION SLAVE
```
  ：授予用户复制权限。
- ```
FLUSH PRIVILEGES
```
    ：刷新权限，使修改生效。
- 锁定主服务器并获取二进制日志信息
  
  在开始复制之前，需要锁定主服务器，并获取当前的二进制日志文件名和位置。
```
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
```
```
SHOW MASTER STATUS
```
  命令会显示如下信息：
```
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
```
  记录下
```
File
```
  （二进制日志文件名）和
```
Position
```
  （位置），稍后会在配置从服务器时用到。
- 解锁主服务器
  
  获取二进制日志信息后，解锁主服务器。
```
UNLOCK TABLES;
```
- 备份主服务器数据（可选）
  
  为了保证数据一致性，建议在配置从服务器之前，备份主服务器的数据。可以使用
```
mysqldump
```
  命令进行备份。
```
mysqldump -u root -p --all-databases > all_databases.sql
```
  或者，只备份需要复制的数据库：
```
mysqldump -u root -p your_database_name > your_database_name.sql
```
配置从服务器（Slave）
- 修改MySQL配置文件（my.cnf或my.ini）
  
  找到你的MySQL配置文件，在
```
[mysqld]
```
  部分添加或修改以下配置：
```
server-id   = 2  # 每个服务器的唯一ID，从服务器通常设置为大于1的数字
#relay_log   = relay-log # (可选) 指定中继日志的文件名前缀，默认是主机名
#relay_log_index = relay-log.index # (可选) 指定中继日志索引文件的文件名
#read_only = 1 # (可选) 设置从库只读
```
  - ```
  server-id
```
  ：必须是唯一的，不能与主服务器或其他从服务器重复。
- ```
relay_log
```
    ：指定中继日志的文件名前缀。中继日志是从服务器用来存储从主服务器接收到的二进制日志的文件。如果不设置，默认是主机名。
  - ```
  relay_log_index
```
  ：指定中继日志索引文件的文件名。
- ```
read_only = 1
```
    ：设置从库为只读模式，防止误操作导致数据不一致。
- 重启MySQL服务
  
  修改配置文件后，需要重启MySQL服务才能使配置生效。
```
sudo systemctl restart mysql
```
- 配置从服务器连接到主服务器
  
  登录到从服务器的MySQL，使用以下命令配置从服务器连接到主服务器：
```
CHANGE MASTER TO
  MASTER_HOST='your_master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='your_password',
  MASTER_LOG_FILE='mysql-bin.000001',  # 替换为主服务器的二进制日志文件名
  MASTER_LOG_POS=154;  # 替换为主服务器的二进制日志位置
```
  - ```
  MASTER_HOST
```
  ：主服务器的IP地址。
- ```
MASTER_USER
```
    ：在主服务器上创建的复制用户。
  - ```
  MASTER_PASSWORD
```
  ：复制用户的密码。
- ```
MASTER_LOG_FILE
```
    ：从主服务器获取的二进制日志文件名。
  - ```
  MASTER_LOG_POS
```
  ：从主服务器获取的二进制日志位置。
- 启动复制
```
START SLAVE;
```
- 检查复制状态
```
SHOW SLAVE STATUS\G
```
  检查
```
Slave_IO_Running
```
  和
```
Slave_SQL_Running
```
  是否都为
```
Yes
```
  。如果不是，检查
```
Last_Error
```
  字段，查看错误信息并进行排查。
  
  如果
```
Last_Error
```
  显示“Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'”，可能是因为
```
MASTER_LOG_FILE
```
  和
```
MASTER_LOG_POS
```
  设置不正确，或者主服务器的二进制日志文件已经被删除。
恢复主服务器数据（如果备份过）

如果在配置从服务器之前备份了主服务器的数据，现在可以在从服务器上恢复数据。
```
mysql -u root -p < all_databases.sql
```
或者，只恢复需要复制的数据库：
```
mysql -u root -p your_database_name < your_database_name.sql
```
测试复制

在主服务器上创建一个新的数据库或表，然后在从服务器上检查是否已经同步。
```
-- 在主服务器上
CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(255));
INSERT INTO test_table (id, name) VALUES (1, 'test');

-- 在从服务器上
USE test_replication;
SELECT * FROM test_table;
```
如果从服务器上能够看到主服务器上创建的数据库和表，并且数据已经同步，那么恭喜你，MySQL主从复制已经成功搭建！

mysql主从复制有哪些常见问题？

数据一致性问题： 这是最常见的问题。由于网络延迟、主从服务器性能差异等原因，从服务器的数据可能与主服务器的数据不一致。可以使用
```
pt-table-sync
```
等工具进行数据同步和修复。另外，使用
```
ROW
```
模式的二进制日志可以提高数据一致性。
主从延迟问题： 从服务器的数据落后于主服务器。这会导致读取从服务器的数据时，可能获取到旧的数据。可以通过监控
```
Seconds_Behind_Master
```
指标来判断延迟情况。优化SQL查询、提升服务器硬件性能、使用更快的网络连接等方式可以减少延迟。
网络问题： 主从服务器之间的网络连接不稳定，会导致复制中断。需要检查网络配置、防火墙设置等。
二进制日志问题： 二进制日志丢失或损坏，会导致复制失败。定期备份二进制日志，并确保二进制日志的配置正确。
服务器ID冲突： 主从服务器的
```
server-id
```
相同，会导致复制异常。确保每个服务器的
```
server-id
```
都是唯一的。

如何监控MySQL主从复制的状态？

SHOW SLAVE STATUS
命令：这是最常用的监控命令，可以查看从服务器的复制状态，包括
```
Slave_IO_Running
```
、
```
Slave_SQL_Running
```
、
```
Seconds_Behind_Master
```
、
```
Last_Error
```
等重要指标。
MySQL Enterprise Monitor： MySQL官方提供的监控工具，可以提供更全面的监控和告警功能。
第三方监控工具： 例如Prometheus、Grafana等，可以自定义监控指标，并进行可视化展示。
自定义脚本： 可以使用Shell脚本或Python脚本等，定期执行
```
SHOW SLAVE STATUS
```
命令，并将结果发送到监控系统。

主从复制的模式有哪些？各有什么优缺点？

单向复制： 一个主服务器对应一个或多个从服务器。这是最常见的复制模式。优点是配置简单，易于管理。缺点是主服务器出现故障时，需要手动切换到从服务器。
双向复制： 两个服务器互为主从服务器。优点是提高了可用性，任何一个服务器出现故障，另一个服务器都可以继续提供服务。缺点是配置复杂，容易出现数据冲突。
级联复制： 主服务器将数据复制到第一级从服务器，第一级从服务器再将数据复制到第二级从服务器，以此类推。优点是减轻了主服务器的压力。缺点是复制延迟可能会比较大。
多源复制： 一个从服务器可以从多个主服务器复制数据。优点是可以聚合多个数据源的数据。缺点是配置复杂，容易出现数据冲突。

选择哪种复制模式，取决于具体的业务需求和场景。单向复制适用于读多写少的场景，双向复制适用于高可用性要求的场景，级联复制适用于主服务器压力较大的场景，多源复制适用于需要聚合多个数据源的场景。

17370845950

关于我们

服务项目

广告推广

案例欣赏