MySQL 主备架构

一、情况准备

• 服务器要求 ：
  
  
  
  • 至少准备两台服务器，分别作为主库（Master）和备库（Slave）。服务器的硬件设置应尽量相同或相近，以保证性能的一致性，包罗 CPU、内存、磁盘空间等。
    
  • 系统时钟保持同步，可以使用 NTP（Network Time Protocol）服务来实现，克制因时间差异导致的数据不一致或其他题目。
    
  
  
• 操作系统与软件安装 ：
  
  
  
  • 操作系统：发起选择稳定的企业级 Linux 系统，如 CentOS、Ubuntu Server 等，不同的操作系统在安装和设置 MySQL 时大概会有一些差异，但总体思绪是相似的。
    
  • 安装 MySQL：在所有服务器上安装相同版本的 MySQL，版本的一致性对于保证主备同步的稳定性和兼容性至关重要。可以通过系统的包管理工具（如 yum、apt）举行安装，也可以从 MySQL 官方网站下载源码举行编译安装，安装过程中需按照提示完成相干设置，如设置 root 用户暗码等。
    
  
  
• 网络连通性 ：确保主备服务器之间网络畅通，能够通过 IP 地址相互访问，可以使用 ping 下令举行测试，比方在主库服务器上 ping 备库服务器的 IP 地址，以及在备库服务器上 ping 主库服务器的 IP 地址，查看是否能够正常通讯。
  

二、主库（Master）设置

• 修改 MySQL 设置文件（my.cnf 或 my.ini） ：
  
  
  
  • 找到设置文件中 [mysqld] 部分，在该部分添加或修改以下内容：
    
    
    
    • server-id = 1 ：设置主库的唯一服务器 ID，在整个 MySQL 主备架构中，每个服务器的 server-id 必须是唯一的，用于标识不同的服务器，这里将主库设置为 1。
      
    • log_bin = mysql-bin ：启用二进制日记功能，这是实现主备同步的关键，二进制日记会记载数据库的修改操作，备库将根据这些日记来同步数据变化。mysql-bin 是二进制日记文件的前缀名，可以根据需要举行修改，但要确保路径有效且有写入权限。
      
    • binlog_format = MIXED ：指定二进制日记的格式，MIXED 表示混淆模式，它会根据实际情况自动选择语句级别的复制（Statement-based Replication，SBM）或行级别的复制（Row-based Replication，RBM）。语句级别的复制会记载 SQL 语句本身，而行级别的复制会记载数据行的变化情况，混淆模式在大多数情况下能够取得较好的平衡，既能保证数据的一致性，又能兼顾性能等其他因素。
      
    • auto_increment_increment = 2 ：假如数据库中有自增主键的表，设置该参数可以克制主备库的自增值冲突。比方，主库设置为 2，备库设置为 2，这样主库的自增值会依次递增 2（如 1、3、5…），备库的自增值也会依次递增 2（如 2、4、6…），从而防止在主备切换或其他情况下出现主键重复的题目。
      
    • auto_increment_offset = 1 ：与auto_increment_increment共同使用，用于设置自增主键的起始值。主库设置为 1，备库设置为 2，这样主库和备库的自增值就不会重叠。
      
    
    
  • 完成设置文件的修改后，保存并退出编辑器。
    
  
  
• 重启 MySQL 服务 ：使设置文件的修改生效，实行下令如sudo systemctl restart mysql（详细下令根据系统和安装方式而定），在 CentOS 系统中可以使用service mysqld restart，在 Ubuntu 系统中可以使用sudo service mysql restart。
  
• 创建用于复制的用户 ：
  
  
  
  • 登录到主库的 MySQL，实行以下 SQL 语句创建一个具有复制权限的用户，用于备库毗连主库举行数据同步：
    
    
    
    • CREATE USER 'repl_user'@'%' IDENTIFIED BY 'password';：创建一个名为repl_user的用户，允许从恣意主机（'%'表示恣意主机）毗连，暗码为password。在实际生产情况中，为了安全起见，通常会限制该用户的来源 IP 地址范围，比方假如备库的 IP 地址是192.168.1.100，则可以创建为CREATE USER 'repl_user'@'192.168.1.100' IDENTIFIED BY 'password';，这样只有来自该 IP 地址的毗连才会被允许。
      
    • GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'password';：授予该用户REPLICATION SLAVE权限，使其能够读取主库的二进制日记，用于同步数据。
      
    • FLUSH PRIVILEGES;：刷新权限，使刚才创建的用户和授予权限立刻生效。
      
    
    
  
  

三、备库（Slave）设置

• 修改 MySQL 设置文件（my.cnf 或 my.ini） ：
  
  
  
  • 在 [mysqld] 部分设置server-id，比方server-id = 2，确保与主库的server-id不同，并且在整个主备架构中是唯一的。假如有多个备库，依次递增设置server-id的值。
    
  
  
• 重启 MySQL 服务 ：同样使用系统提供的服务管理下令重启 MySQL 服务，使设置生效。
  

四、数据初始化（全量备份与恢复）

• 在主库上举行全量备份 ：
  
  
  
  • 可以使用mysqldump下令举行全量备份，该下令会将数据库中的数据导出为 SQL 脚本文件。基本的下令格式为：
    
    
    
    • mysqldump -u root -p --all-databases > backup.sql
      
    • 这里使用root用户登录 MySQL 举行备份，--all-databases参数表示备份所有数据库的数据，backup.sql是备份文件的名称，可以根据实际需要举行修改。假如只需要备份特定的数据库，可以使用-B参数后跟数据库名称，如mysqldump -u root -p -B database_name > backup.sql。
      
    
    
  • 还可以使用物理备份工具如mysqlbackup（假如使用的是 MySQL Enterprise Edition）或xtrabackup（开源工具）举行备份，这些工具可以实现更快的备份和恢复速度，并且在备份过程中对数据库性能的影响相对较小。以xtrabackup为例，备份下令大致为：
    
    
    
    • innobackupex --user=root --password=password /path/to/backup
      
    • 这会将备份文件存储在指定的/path/to/backup目次下，备份过程中会锁定表，但时间相对较短，并且对于 InnoDB 存储引擎的表可以实现热备份。
      
    
    
  
  
• 将备份文件传输到备库服务器 ：
  
  
  
  • 可以使用scp（安全复制协议）、rsync（长途同步工具）等下令将主库上的备份文件传输到备库服务器。比方，使用scp下令的格式为：
    
    
    
    • scp backup.sql user@slave_server_ip:/destination_path
      
    • 其中，user是备库服务器上的用户，slave_server_ip是备库服务器的 IP 地址，/destination_path是备库上要存储备份文件的路径。
      
    • 
      
    • 假如备份文件较大，可以考虑使用压缩工具先对备份文件举行压缩，再举行传输，以提高传输效率。比方，可以先使用gzip下令对backup.sql文件举行压缩，生成backup.sql.gz文件，然后传输压缩后的文件，在备库服务器上再举行解压缩。
      
    
    
  
  
• 在备库上恢复备份数据 ：
  
  
  
  • 登录到备库的 MySQL，实行以下下令恢复备份的数据：
    
    
    
    • mysql -u root -p < backup.sql
      
    • 这会将备份的 SQL 脚本文件中的数据导入到备库中，创建相应的数据库、表，并插入数据等内容。假如备份文件是通过物理备份工具生成的，如xtrabackup，则需要先将备份文件解压（假如是压缩备份），然后使用相应的恢复下令，如innobackupex --apply-log /path/to/backup（准备备份）和innobackupex --copy-back /path/to/backup（将备份数据恢复到 MySQL 数据目次），并在恢复完成后修改数据目次的权限，使其符合 MySQL 的要求。
      
    
    
  
  

五、设置主备毗连关系

• 在主库上查看二进制日记文件和位置 ：
  
  
  
  • 实行SHOW MASTER STATUS;下令，查看主库当前的二进制日记文件名和位置，这些信息将用于备库设置同步的起始点。下令输出示例：
    
    
    
    • ±-----------------±---------±-------------±-----------------+
      | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
      ±-----------------±---------±-------------±-----------------+
      | mysql-bin.000001 | 123 | | |
      ±-----------------±---------±-------------±-----------------+
      
    • 这里File列显示当前正在使用的二进制日记文件名，Position列显示当前二进制日记文件中的写入位置，表示从开始到当前位置的所有数据修改操作。
      
    
    
  
  
• 在备库上实行 CHANGE MASTER TO 下令 ：
  
  
  
  • 实行以下 SQL 下令，设置备库毗连到主库并指定同步的二进制日记文件和位置：
    
    
    
    • CHANGE MASTER TO MASTER_HOST='master_server_ip', MASTER_USER='repl_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=123;
      
    • 其中：
      
      
      
      • MASTER_HOST：指定主库的 IP 地址，更换为实际的主库服务器 IP。
        
      • MASTER_USER：之前在主库上创建的复制用户的用户名，这里是repl_user。
        
      • MASTER_PASSWORD：复制用户的暗码。
        
      • MASTER_LOG_FILE：从主库SHOW MASTER STATUS;下令获取的二进制日记文件名，如mysql-bin.000001。
        
      • MASTER_LOG_POS：对应的二进制日记位置，这里是 123。
        
      
      
    
    
  
  
• 启动备库的复制线程 ：实行START SLAVE;下令，开始从主库同步数据。这条下令会启动两个线程：I/O 线程和 SQL 线程。I/O 线程负责从主库读取二进制日记并复制到备库当地的中继日记（relay log）中，SQL 线程则负责读取中继日记并实行其中的 SQL 语句，从而将主库的数据变化同步到备库。
  

六、验证主备同步是否正常

• 查看主库状态 ：在主库上实行SHOW MASTER STATUS;下令，确认二进制日记文件和位置是否在不停更新，假如有新的数据修改操作，这些值应该会发生变化，这表明主库的二进制日记功能正常工作。
  
• 查看备库状态 ：在备库上实行SHOW SLAVE STATUS\G下令（注意后面的\G，用于以垂直格式显示结果，更清晰易读），查看以下关键字段：
  
  
  
  • Slave_IO_Running：其值应为Yes，表示 I/O 线程正在正常运行，能够从主库读取二进制日记并写入到当地中继日记。
    
  • Slave_SQL_Running：其值也应为Yes，表示 SQL 线程正在正常运行，能够读取中继日记并实行其中的 SQL 语句，将数据同步到备库。
    
  • Seconds_Behind_Master：表示备库落后主库的秒数。假如主备同步正常且没有延长，该值通常为 0；假如该值较大且持续增长，大概表示存在网络题目、备库性能题目或其他异常情况导致同步延长，需要进一步排查和办理。
    
  
  
• 举行数据测试 ：在主库上对数据库举行一些简单的数据修改操作，比方在某个表中插入、更新或删除数据，然后立刻查询备库中相应的表，确认数据是否能够实时同步过来。可以通过比力主库和备库的数据行数、特定字段的值等来验证数据的一致性。
  

七、后续维护与监控

• 定期备份 ：尽管主备同步可以提供一定水平的数据冗余和容灾本领，但仍然需要定期对主备库举行全量备份和增量备份，以防止因硬件故障、人为误操作或其他不可预见的情况导致数据丢失。备份计谋可以根据业务数据的重要性和变化频率来制定，比方天天举行一次全量备份，每小时举行一次增量备份等。
  
• 监控主备状态 ：可以使用 MySQL 自带的下令如定期实行SHOW SLAVE STATUS\G来监控备库的同步状态，也可以借助第三方的监控工具如 Nagios、Zabbix、Prometheus 等，对主备库的性能指标（如 CPU 使用率、内存使用率、磁盘 I/O、网络带宽等）、数据库状态（如主备延长、毗连数、QPS、TPS 等）举行实时监控和告警，实时发现并处理潜在的题目。
  
• 故障处理 ：假如出现主备同步停止的情况，需要根据详细的错误信息举行分析和处理。常见的题目包罗网络故障、主库二进制日记被清理、备库磁盘空间不足、数据不一致等。可以根据SHOW SLAVE STATUS\G下令中的Last_Error字段显示的错误信息，结合 MySQL 的错误日记（通常位于/var/log/mysql/error.log或根据设置指定的其他路径）举行排查。比方，假如是网络故障导致 I/O 线程停止，可以检查网络毗连是否恢复，然后实行START SLAVE;下令重新启动同步线程；假如是因主库二进制日记被清理，备库无法继承同步，大概需要重新举行数据初始化和主备设置。
  
• 性能优化 ：根据业务需求和系统运行情况，对 MySQL 主备架构举行性能优化。比方，调解 MySQL 设置参数（如innodb_buffer_pool_size、max_connections、query_cache_size等）以提高数据库的性能；优化 SQL 查询语句，克制复杂的查询和频繁的表扫描；对表举行合理的索引设计，提高数据读写效率；在主备间合理分配网络带宽，确保数据同步的稳定性等。
  

通过以上全面、详细的步骤搭建和维护 MySQL 主备架构，可以有效地实现数据的冗余备份和高可用性，为业务系统的稳定运行提供保障。在实际操作过程中，需要根据详细的业务场景和系统情况举行适当的调解和优化。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告