MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MySQL主库与备库的配置与启动

2023-01-154.2k 阅读

MySQL 主库与备库的配置与启动

1. 配置主库

1.1 修改主库配置文件

在开始配置主库之前，确保你已经安装了 MySQL 数据库。通常，MySQL 的配置文件位于 /etc/mysql/my.cnf（在基于 Debian 或 Ubuntu 的系统上）或 /etc/my.cnf（在基于 Red Hat 或 CentOS 的系统上）。

打开配置文件，在 [mysqld] 部分添加或修改以下配置参数：

[mysqld]
# 主库唯一标识，取值范围 1 - 2^32 - 1
server-id = 1
# 开启二进制日志功能，记录数据库的更改操作
log-bin = /var/log/mysql/mysql-bin.log
# 定义要复制的数据库，可根据实际需求添加多个，每个一行
binlog-do-db = your_database_name
# 定义不需要复制的数据库，可根据实际需求添加多个，每个一行
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys

注意事项：

  • server-id 必须是一个唯一的整数，在整个复制环境中不能与其他服务器重复。如果有多台主库，每台主库也需要有不同的 server-id。
  • log-bin 参数指定二进制日志文件的路径和前缀。确保 MySQL 有写入该路径的权限。
  • binlog-do-db 和 binlog-ignore-db 用于筛选需要或不需要复制的数据库。根据实际业务需求进行配置。

修改完成后，保存并关闭配置文件。

1.2 重启 MySQL 服务

在修改配置文件后，需要重启 MySQL 服务以使配置生效。在基于 Debian 或 Ubuntu 的系统上，可以使用以下命令：

sudo systemctl restart mysql

在基于 Red Hat 或 CentOS 的系统上，命令如下：

sudo systemctl restart mysqld

1.3 创建复制用户

登录到 MySQL 主库：

mysql -u root -p

输入密码后，进入 MySQL 命令行。然后创建一个用于复制的用户，并赋予其复制权限。例如：

CREATE USER'replication_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO'replication_user'@'%';
FLUSH PRIVILEGES;

上述代码解释：

  • CREATE USER'replication_user'@'%' IDENTIFIED BY 'your_password';：创建一个名为 replication_user 的用户，@'%' 表示该用户可以从任何主机连接，your_password 替换为实际的密码。
  • GRANT REPLICATION SLAVE ON *.* TO'replication_user'@'%';：赋予 replication_user 用户复制从库相关的权限。
  • FLUSH PRIVILEGES;：刷新权限，使新的权限设置立即生效。

1.4 获取主库状态

获取主库的二进制日志文件名和位置，这两个值将在配置备库时使用。在 MySQL 命令行中执行以下命令：

SHOW MASTER STATUS;

执行结果类似如下：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |    154    | your_database_name | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下 File 和 Position 的值，后续配置备库时会用到。

2. 配置备库

2.1 修改备库配置文件

同样，打开备库的 MySQL 配置文件（路径与主库类似），在 [mysqld] 部分添加或修改以下配置参数：

[mysqld]
# 备库唯一标识，不能与主库及其他备库重复
server-id = 2
# 启用中继日志，记录从主库接收到的二进制日志事件
relay-log = /var/log/mysql/mysql-relay-bin.log
# 定义不需要复制的数据库，可根据实际需求添加多个，每个一行
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys

注意事项：

  • server-id 必须与主库及其他备库不同。
  • relay-log 参数指定中继日志文件的路径和前缀，确保 MySQL 有写入该路径的权限。
  • replicate-ignore-db 用于筛选不需要复制的数据库，与主库的 binlog-ignore-db 配置保持一致。

修改完成后，保存并关闭配置文件。

2.2 重启 MySQL 服务

与主库类似，修改配置文件后需要重启 MySQL 服务以使配置生效。在基于 Debian 或 Ubuntu 的系统上：

sudo systemctl restart mysql

在基于 Red Hat 或 CentOS 的系统上：

sudo systemctl restart mysqld

2.3 配置备库连接主库

登录到 MySQL 备库：

mysql -u root -p

进入 MySQL 命令行后，执行以下命令配置备库连接主库：

CHANGE MASTER TO
    MASTER_HOST='master_host_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='your_password',
    MASTER_LOG_FILE='master_binlog_file',
    MASTER_LOG_POS=master_binlog_position;

上述代码解释：

  • MASTER_HOST='master_host_ip'：将 master_host_ip 替换为主库的实际 IP 地址。
  • MASTER_USER='replication_user'：使用之前在主库创建的复制用户。
  • MASTER_PASSWORD='your_password'：复制用户的密码。
  • MASTER_LOG_FILE='master_binlog_file'：将 master_binlog_file 替换为在主库执行 SHOW MASTER STATUS; 时得到的 File 值。
  • MASTER_LOG_POS=master_binlog_position：将 master_binlog_position 替换为在主库执行 SHOW MASTER STATUS; 时得到的 Position 值。

2.4 启动备库复制

在配置好备库连接主库后，启动备库的复制功能：

START SLAVE;

2.5 检查备库状态

执行以下命令检查备库状态：

SHOW SLAVE STATUS \G;

在输出结果中，重点关注以下两个参数：

  • Slave_IO_Running：如果为 Yes，表示 I/O 线程正在运行，负责从主库读取二进制日志。
  • Slave_SQL_Running：如果为 Yes，表示 SQL 线程正在运行，负责将读取到的二进制日志事件应用到备库。

另外，Seconds_Behind_Master 表示备库落后主库的时间（以秒为单位），如果为 0 则表示备库与主库同步良好。理想情况下，应该看到 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，且 Seconds_Behind_Master 为 0 或接近 0。

如果 Slave_IO_Running 或 Slave_SQL_Running 为 No，需要检查错误日志以排查问题。在基于 Debian 或 Ubuntu 的系统上，MySQL 错误日志通常位于 /var/log/mysql/error.log；在基于 Red Hat 或 CentOS 的系统上，位于 /var/log/mysqld.log。

3. 常见问题及解决方法

3.1 主库和备库时间不同步

如果主库和备库的系统时间不一致，可能会导致复制出现问题，特别是在使用基于 GTID（全局事务标识符）的复制时。可以通过以下方法解决：

  • 在主库和备库上安装并配置 NTP（网络时间协议）服务，使其与可靠的时间服务器同步。在基于 Debian 或 Ubuntu 的系统上，可以使用以下命令安装 NTP：
sudo apt-get install ntp

然后编辑 NTP 配置文件 /etc/ntp.conf，确保配置了正确的时间服务器。修改完成后，重启 NTP 服务：

sudo systemctl restart ntp
  • 在基于 Red Hat 或 CentOS 的系统上，安装 NTP 服务的命令如下：
sudo yum install ntp

编辑 /etc/ntp.conf 文件，配置好时间服务器后，重启 NTP 服务：

sudo systemctl restart ntpd

3.2 复制延迟

复制延迟是指备库落后主库的时间。常见原因及解决方法如下：

  • 网络问题：主库和备库之间的网络延迟或带宽不足可能导致复制延迟。可以使用 ping 和 traceroute 命令检查网络连接情况，确保网络稳定且带宽足够。如果网络不稳定，可以尝试优化网络配置或更换网络设备。
  • 备库负载过高：备库的 CPU、内存或磁盘 I/O 负载过高，会导致 SQL 线程应用日志事件缓慢。可以使用系统监控工具（如 top、htop 查看 CPU 和内存使用情况，iostat 查看磁盘 I/O 情况）来找出负载过高的原因。如果是 CPU 负载过高，可以考虑优化查询语句或增加 CPU 资源；如果是内存不足，可以增加内存或优化内存配置；如果是磁盘 I/O 瓶颈，可以考虑更换更快的磁盘或优化磁盘 I/O 配置。
  • 大事务：主库上执行的大事务会导致备库的复制延迟。尽量避免在主库上执行长时间运行的大事务，可以将大事务拆分成多个小事务执行。同时，可以通过 SHOW ENGINE INNODB STATUS \G 命令查看 InnoDB 引擎状态，找到正在执行的大事务并进行优化。

3.3 复制中断

复制可能由于各种原因中断，如网络故障、主库或备库重启等。当中断发生时，可以按照以下步骤恢复复制：

  • 检查错误日志：在主库和备库的错误日志中查找导致复制中断的原因。根据错误信息进行相应的修复。
  • 重新配置备库：如果是网络故障导致复制中断，在网络恢复后，备库可能需要重新连接主库。可以在备库上执行 STOP SLAVE; 停止复制，然后再次执行 CHANGE MASTER TO 命令重新配置主库连接信息，最后执行 START SLAVE; 启动复制。
  • 数据一致性检查：在恢复复制后，建议检查主库和备库的数据一致性。可以使用一些工具（如 pt-table-checksum）来比较主库和备库的数据。如果发现数据不一致，可以根据具体情况进行修复，例如通过手动同步数据或重新初始化备库等方式。

4. 基于 GTID 的主备配置

4.1 GTID 简介

GTID（Global Transaction Identifier）是 MySQL 5.6 引入的一种新的复制机制，它为每个在主库上提交的事务分配一个全局唯一的标识符。与传统的基于二进制日志文件名和位置的复制方式相比，GTID 使得复制更加可靠和易于管理，特别是在处理主库切换和故障恢复时。

4.2 主库配置（基于 GTID）

在主库的配置文件中，除了前面提到的基本配置外，还需要添加或修改以下与 GTID 相关的配置参数：

[mysqld]
# 启用 GTID 模式
gtid_mode = ON
# 强制 GTID 一致性
enforce_gtid_consistency = ON

修改完成后，保存配置文件并重启 MySQL 服务。

登录到 MySQL 主库，创建复制用户并赋予权限：

CREATE USER'replication_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO'replication_user'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

这里增加了 REQUIRE SSL 选项，表示要求备库使用 SSL 连接主库，可根据实际需求决定是否添加。

4.3 备库配置（基于 GTID）

在备库的配置文件中，同样添加或修改 GTID 相关配置：

[mysqld]
# 启用 GTID 模式
gtid_mode = ON
# 强制 GTID 一致性
enforce_gtid_consistency = ON

保存配置文件并重启 MySQL 服务。

登录到 MySQL 备库，配置连接主库：

CHANGE MASTER TO
    MASTER_HOST='master_host_ip',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='your_password',
    MASTER_AUTO_POSITION = 1;

这里使用 MASTER_AUTO_POSITION = 1 表示使用 GTID 自动定位主库的位置，而不需要像传统方式那样指定二进制日志文件名和位置。

启动备库复制：

START SLAVE;

4.4 检查基于 GTID 的复制状态

在备库上执行 SHOW SLAVE STATUS \G; 命令，检查 Slave_IO_Running、Slave_SQL_Running 以及 Seconds_Behind_Master 等参数。此外，还可以查看 Retrieved_Gtid_Set 和 Executed_Gtid_Set，这两个参数分别表示备库从主库获取到的 GTID 集合和已经应用的 GTID 集合。如果这两个集合一致，说明备库与主库同步良好。

5. 启动与优化

5.1 主库和备库的启动顺序

在生产环境中，建议先启动主库，确保主库正常运行并监听端口后，再启动备库。这样可以避免备库在启动时尝试连接尚未准备好的主库而出现连接失败的问题。

在启动主库时，可以通过查看 MySQL 日志文件（如 /var/log/mysql/error.log 或 /var/log/mysqld.log）来确认主库是否成功启动并正常运行。同样，在启动备库后，通过查看日志文件和执行 SHOW SLAVE STATUS \G; 命令来确认备库是否成功连接主库并开始复制。

5.2 性能优化

  • 主库性能优化：
    • 优化查询语句：使用 EXPLAIN 关键字分析查询语句，找出性能瓶颈并进行优化。例如，确保查询语句使用了合适的索引，避免全表扫描。
    • 调整缓存参数：合理调整 innodb_buffer_pool_size 参数，该参数决定了 InnoDB 存储引擎缓存数据和索引的内存大小。根据服务器的内存情况，适当增大该参数可以提高查询性能。
    • 控制写入频率：如果主库写入操作频繁，可以考虑批量写入而不是单个写入，这样可以减少磁盘 I/O 次数，提高写入性能。
  • 备库性能优化：
    • 配置并行复制：从 MySQL 5.6 开始支持并行复制，可以通过设置 slave_parallel_workers 参数来启用并行复制功能。该参数指定了备库 SQL 线程可以使用的并行工作线程数，根据备库的 CPU 核心数合理设置该参数，可以提高备库应用日志的速度。
    • 优化 I/O 性能：确保备库的磁盘 I/O 性能良好。可以使用固态硬盘（SSD）代替传统机械硬盘，或者对磁盘进行适当的分区和格式化优化。同时，合理设置 innodb_log_file_size 和 innodb_log_files_in_group 参数，优化 InnoDB 日志文件的写入性能。
    • 减少不必要的操作：避免在备库上执行与复制无关的高负载操作，如大规模的数据导入或复杂的查询分析等，以免影响复制性能。

通过以上对 MySQL 主库与备库的配置、启动以及常见问题解决和性能优化的介绍，希望能帮助你搭建一个稳定、高效的 MySQL 主备复制环境，满足业务对于数据备份、高可用性和读写分离等方面的需求。在实际应用中，还需要根据具体的业务场景和服务器资源情况进行进一步的优化和调整。