mysql 一主一从数据库的配置文件

angen2018

已于 2024-08-30 09:31:22 修改

阅读量786

点赞数 21

分类专栏： java 文章标签：数据库 mysql

于 2024-08-28 15:37:18 首次发布

本文链接：https://blog.csdn.net/qq_42572322/article/details/141641418

版权

java 专栏收录该内容

84 篇文章 1 订阅

订阅专栏

MySQL一主一从的配置主要涉及到主服务器（Master）和从服务器（Slave）的配置文件，通常是`my.cnf`或`my.ini`，具体配置如下：

### 主服务器（Master）配置：

1. **server-id**：为每个MySQL服务器设置一个唯一的ID。

 server-id=1

2. **log-bin**：启用二进制日志，用于记录所有修改数据的语句。

log-bin=mysql-bin

3. **binlog-do-db**：如果需要复制特定的数据库，可以使用此选项指定。

   binlog-do-db=db_name

4. **binlog-ignore-db**：如果需要忽略复制特定的数据库，如MySQL自带的数据库。

   binlog-ignore-db=mysql
   binlog-ignore-db=information_schema
   binlog-ignore-db=performance_schema
#binlog-do-db=需要备份的数据库名，可写多行
#binlog-ignore-db=不需要备份的数据库名，可写多行

5. **expire_logs_days**：设置二进制日志文件的过期时间，单位为天。

expire_logs_days=7

### 从服务器（Slave）配置：

1. **server-id**：同样需要设置一个唯一的ID，不能与主服务器的ID相同。

server-id=2

2. **relay-log**：设置中继日志文件的名称。

 relay-log=relay-log

3. **read-only**：设置从服务器为只读模式，防止在从服务器上执行写操作。

read-only=1

### 主从复制配置步骤：

1. **配置文件设置**：按照上述配置修改主从服务器的配置文件。

2. **创建复制用户**：在主服务器上创建用于复制的专用用户。
```sql
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
```

3. **重启服务**：在主从服务器上重启MySQL服务以应用配置更改。

4. **同步数据**：在从服务器上使用`CHANGE MASTER TO`语句配置复制。
```sql
CHANGE MASTER TO
MASTER_HOST='master_host_ip',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
```

注意：`MASTER_LOG_FILE`和`MASTER_LOG_POS`需要根据主服务器上的实际二进制日志文件和位置来设置。

在MySQL的主从复制配置中，Master_Log_File和Master_Log_Pos是两个关键参数，它们代表的意义如下：

Master_Log_File：

这个参数代表了主服务器（Master）上的二进制日志文件（binlog file）的名称。在MySQL中，每个写操作都会记录到二进制日志中，这些日志文件是复制过程中数据同步的基础。
当从服务器（Slave）需要开始复制数据时，它需要知道从哪个日志文件开始读取变更。Master_Log_File就是告诉从服务器应该从主服务器的哪个binlog文件开始接收数据。
Master_Log_Pos：

这个参数表示在Master_Log_File文件中的位置（position），即从服务器需要从该位置开始读取数据。二进制日志文件中的每个事件（如INSERT、UPDATE、DELETE语句）都有唯一的位置偏移量，Master_Log_Pos就是指定从服务器应该从哪个偏移量开始读取主服务器的日志事件。
在主从复制的上下文中，这两个参数共同确定了从服务器需要同步的数据起始点。当从服务器连接到主服务器时，它会请求从Master_Log_File指定的日志文件和Master_Log_Pos指定的位置开始的数据。

例如，如果主服务器的当前二进制日志状态是：

sql
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107      |              |                  |
+-------------------+----------+--------------+------------------+
这里mysql-bin.000001是Master_Log_File，107是Master_Log_Pos。从服务器在配置时需要使用这些值来初始化复制过程。

正确设置这些参数对于确保主从复制的一致性和连续性至关重要。如果设置不正确，可能会导致数据复制不同步或失败。

5. **启动复制**：在从服务器上启动复制进程。
```sql
START SLAVE;
```

6. **监控复制状态**：定期检查复制状态以确保复制正常进行。
```sql
SHOW SLAVE STATUS\G
```

确保在配置过程中替换配置文件中的示例值（如IP地址、数据库名、密码等）为实际环境中的值。同时，根据实际需求调整配置选项。

总结：

第一步先在主服务器上创建一个用户，用于给从服务器复制

建立用户

grant replication slave on *.* to gd@192.168.6.101 identified by '123456';
# grant replication slave on *.* to ‘用户名’@'主机’ identified by ‘密码’;
# 可在B Slave上做连接测试: mysql -h 192.168.8.100 -ugd -p

创建用户后，还要给用户权限
GRANT REPLICATION SLAVE ON *.* TO 'gd'@'192.168.6.101';

主服务器配置文件如下：

编辑配置文件/etc/my.cnf
# 确保有如下行
server-id = 1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
#binlog-do-db=需要备份的数据库名，可写多行
#binlog-ignore-db=不需要备份的数据库名，可写多行

从服务器配置

修改配置需要注意，需要删除从服务器上的/var/lib/mysql/master.info文件
[root@CentOS-2 ~]# rm -f /var/lib/mysql/master.info

编辑/etc/my.cnf
server-id=2
log-bin=mysql-bin
master-host=192.168.6.100
master-user=gd
master-password=123456
master-port=3306
replicate-do-db=test
# replicate-do-db=test 需要备份的数据库名
# replicate-ignore-db=mysql 忽略的数据库
# master-connect-retry=60 如果从服务器发现主服务器断掉，重新连接的时间差(秒)
MASTER_LOG_FILE='mysql-bin.000001'
MASTER_LOG_POS=107

配置好后重启

[root@CentOS-2 ~]# /etc/init.d/mysql restart

然后再次cat /var/lib/mysql/master.info 你会发现里面的内容是主服务器的内容

以上都配置并且主从服务器都重启好了后，在从服务器运行开始复制的命令

START SLAVE;

其他命令，了解一下

重置从服务器的复制状态：

停止从服务器的复制进程：STOP SLAVE;
重置从服务器的复制状态：RESET SLAVE;
重新启动复制进程：START SLAVE;

然后在从服务器运行

SHOW SLAVE STATUS\G

当使用 `SHOW SLAVE STATUS\G` 命令来检查MySQL主从复制状态时，以下是一些关键值，它们表示主从服务器配置成功：

1. **Slave_IO_Running**: 表示从服务器的I/O线程是否成功连接到主服务器并接收事件。如果值为"Yes"，则表示I/O线程正在运行并且已成功连接到主服务器 。

2. **Slave_SQL_Running**: 表示从服务器的SQL线程是否正在运行。如果值为"Yes"，则表示SQL线程正在正常执行中继日志中的事件 。

3. **Seconds_Behind_Master**: 表示从服务器SQL线程落后于主服务器的秒数。如果值为"0"，则表示从服务器没有延迟，与主服务器同步 。

4. **Master_Log_File 和 Read_Master_Log_Pos**: 分别显示了I/O线程当前读取的主服务器的二进制日志文件名和位置 。

5. **Relay_Master_Log_File 和 Exec_Master_Log_Pos**: 显示了SQL线程执行的最新事件对应的主服务器的二进制日志文件名和位置 。

6. **Relay_Log_File 和 Relay_Log_Pos**: 显示了SQL线程正在读取和执行的中继日志文件名和位置 。

7. **Last_Errno 和 Last_Error**: 应该为"0"和空字符串，表示没有错误发生 。

8. **Last_IO_Errno 和 Last_IO_Error**: I/O线程在处理中继日志时注册的最后错误，理想情况下应该是"0"和空字符串，表示没有I/O错误 。

9. **Last_SQL_Errno 和 Last_SQL_Error**: SQL线程在执行中继日志时注册的最后错误，理想情况下应该是"0"和空字符串，表示没有SQL错误 。

10. **Master_Host、Master_User、Master_Port**: 这些值显示了从服务器连接到主服务器的主机地址、用户和端口信息 。

如果上述值大部分显示正常，并且没有错误或延迟，那么可以认为主从服务器配置成功。如果存在问题，需要根据错误信息进行相应的故障排查和处理 。

MySQL的半同步复制（semi-synchronous replication）是一种提高数据复制安全性的方式，它确保了在事务提交后，至少有一个从服务器接收到了事务的二进制日志并写入到其中继日志中，然后主服务器才会向客户端确认提交成功。这降低了数据在主服务器故障时丢失的风险，同时不会像全同步复制那样严重影响性能。

从MySQL 5.5.9版本开始，半同步复制作为一个插件提供。以下是开启半同步复制的基本步骤：

1. **安装半同步复制插件**：
在主服务器和从服务器上安装半同步复制插件。这通常涉及到下载插件并使用`INSTALL PLUGIN`语句进行安装。

2. **配置主服务器**：
在主服务器的配置文件中（通常是`my.cnf`或`my.ini`），设置以下参数来启用半同步复制：
```ini
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=10000 # 超时时间，单位为毫秒
```

3. **配置从服务器**：
在从服务器的配置文件中设置以下参数：
```ini
[mysqld]
rpl_semi_sync_slave_enabled=1
```

4. **重启MySQL服务**：
在主服务器和从服务器上重启MySQL服务以应用配置更改。

5. **设置从服务器复制规则**：
在从服务器上执行`CHANGE MASTER TO`语句来设置主服务器的连接信息，并指定`MASTER_LOG_FILE`和`MASTER_LOG_POS`。

6. **启动复制**：
在从服务器上使用`START SLAVE`语句启动复制进程。

7. **监控半同步复制状态**：
使用`SHOW STATUS LIKE 'rpl_semi_sync%';`来监控半同步复制的状态和性能。

8. **调整超时时间**（可选）：
根据网络延迟和业务需求，可能需要调整`rpl_semi_sync_master_timeout`的值。如果超时时间设置得太短，可能会因为网络延迟导致事务提交失败。

9. **处理异常**：
如果遇到半同步复制失败的情况，需要检查主从服务器的日志文件，查找错误信息并进行相应的处理。

请注意，半同步复制插件可能需要特定的许可，并且在商业版本中可用。在生产环境中配置半同步复制之前，建议在测试环境中进行充分测试，以确保其符合业务需求和性能标准。