MySQL主从服务的配置及其中可能会遇到的问题

2022-09-18

字数统计: 1.6k字 | 阅读时长≈ 6分

最近做了一次数据库的主从配置管理，把其中的主要操作的技术点还有过程中可能会遇到的问题在此记录一下，以备后需。

一、MySQL主从服务概述

MySQL主从服务是一种常用的数据同步和高可用性解决方案。通过配置主从关系，我们可以实现数据备份、读取分离和故障恢复等功能。本文将详细介绍MySQL主从服务的安装和配置步骤，帮助您顺利搭建主从环境。

二、安装MySQL主服务

准备工作：确保操作系统满足MySQL的安装要求，如Windows或Linux等。
下载MySQL安装包，根据操作系统选择相应的版本。
解压安装包，执行安装程序。在安装过程中，选择自定义安装，并注意勾选“启动为服务”。
完成安装后，使用管理员权限登录操作系统。
**
三、配置MySQL主服务

打开MySQL配置文件（my.ini），找到[mysqld]部分，进行以下配置：

sql
server-id=1  # 设置服务器ID  
log_bin=mysql-bin  # 开启二进制日志  
binlog_do_db=your_database_name  # 指定需要同步的数据库名

重启MySQL服务，使配置生效。
四、安装MySQL从服务

下载与主服务器相同的MySQL版本，并解压安装包。
安装过程中，选择自定义安装，并注意勾选“启动为服务”。
完成安装后，使用管理员权限登录操作系统。
五、配置MySQL从服务

打开MySQL配置文件（my.ini），找到[mysqld]部分，进行以下配置：

1 2	sql server-id=2 # 设置服务器ID，从服务器ID必须与主服务器不同

重启MySQL服务，使配置生效。
六、配置主从服务关系

在主服务器上执行以下命令，创建一个用于复制的用户，并授权该用户对指定数据库进行复制：

sql
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';  
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';  
GRANT SELECT ON your_database_name.* TO 'replication_user'@'%';

在主服务器上找到MySQL配置文件（my.cnf），找到[mysqld]部分，进行以下配置：

1 2	bash relay_log=mysql-relay-bin # 设置中继日志文件名

重启MySQL服务，使配置生效。

在从服务器上执行以下命令，设置从服务器连接到主服务器并开始复制：

1
2
3

sql
CHANGE MASTER TO MASTER_HOST='master_server_ip', MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.XXXXXX', MASTER_LOG_POS=XXXX;  
START SLAVE;

将上述命令中的’master_server_ip’、’replication_user’、’password’、’mysql-bin.XXXXXX’和’XXXX’替换为实际值。

重启从服务器，使配置生效。
现在，MySQL主从服务已经配置完成。
通过验证从服务器的复制状态，确保主从服务正常工作。
本笔记主要是MySQL主从服务的安装与配置过程。正确配置主从服务能够提高数据安全性、读写性能和容灾能力。

在安装和配置MySQL主从服务时可能遇到的问题及其解决方法：

问题：在主服务器上创建复制用户时，无法使用’%’通配符。
解决方法：使用具体的IP地址代替’%’通配符，如‘replication_user‘@’192.168.0.1’。

问题：在从服务器上执行CHANGE MASTER TO命令时，无法连接到主服务器。
解决方法：确保主服务器允许从服务器的访问，并检查主服务器的IP地址、用户名和密码是否正确。

问题：在从服务器上执行START SLAVE命令后，复制进程无法启动。
解决方法：检查主从服务器的配置文件是否正确，并确保主服务器正在生成新的二进制日志文件。

问题：在从服务器上复制进程启动后，出现错误或中断。
解决方法：检查主从服务器的日志文件，查找复制相关的错误信息。可能的原因包括网络问题、配置错误、权限问题等。

问题：主从服务器之间的同步出现延迟。
解决方法：增加sync_binlog参数的值，以增加二进制日志文件的同步频率。还可以检查网络延迟和从服务器的处理能力。

以上是一些常见问题和可能的解决方法。实际操作中还可能遇到其他问题，需要根据具体情况进行排查和解决。

在MySQL主从服务中设置白名单以限制非授权的访问：

修改MySQL配置文件：

打开主服务器的MySQL配置文件（my.cnf或my.ini），根据所使用的操作系统和MySQL版本，找到[mysqld]部分。
在该部分中添加或修改以下内容，以允许白名单配置：

1 2	csharp`bind-address = 127.0.0.1 # 限制主服务器只接受本地访问 skip-networking # 禁用远程访问`

同样，打开从服务器的MySQL配置文件，并执行相同的修改。
创建授权表：

在主服务器上执行以下命令，创建一个用于授权访问的用户，并授予该用户对指定数据库的访问权限：

1
2
3

sql`CREATE USER 'white_list_user'@'%' IDENTIFIED BY 'password';  
GRANT SELECT, INSERT, UPDATE, DELETE ON your_database_name.* TO 'white_list_user'@'%';  
FLUSH PRIVILEGES;`

将’white_list_user’和’password’替换为实际的用户名和密码，将your_database_name替换为要授权访问的数据库名称。

设置主从复制用户：

在主服务器上创建一个用于复制的用户，并授权该用户对指定数据库进行复制：

sql`CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';  
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';  
GRANT SELECT ON your_database_name.* TO 'replication_user'@'%';  
FLUSH PRIVILEGES;`

请将’replication_user’和’password’替换为实际的用户名和密码，将your_database_name替换为要同步的数据库名称。

重启主从服务器：

重启主服务器以使配置更改生效。
重启从服务器以使配置更改生效。
现在，已经在MySQL主从服务中设置了白名单以限制非授权的访问。
只有通过授权的用户才能访问指定的数据库。
需要注意，白名单配置将限制所有非授权的远程访问，包括从服务器对主服务器的访问。
如果需要从服务器能够访问主服务器进行复制，要确保授权表中包含从服务器的IP地址或主机名。