mysql主从同步配置是通过主服务器将数据自动复制到从服务器实现的。1. 主服务器需开启二进制日志（log_bin）、设置唯一server-id、指定binlog格式（推荐row）及同步数据库；2. 创建专用复制用户并授权replication slave权限；3. 锁定表获取binlog文件名和位置后解锁；4. 从服务器设置不同server-id、开启中继日志（relay_log）、可选只读模式；5. 使用change master语句建立主从连接并启动复制；6. 通过show slave status检查同步状态；7. 可使用sublime text创建代码片段提高配置效率；8. 同步延迟高时可排查网络、硬件、大事务、慢sql、锁竞争，并启用多线程复制；9. 监控同步状态可通过show slave status、mysql enterprise monitor、第三方工具或自定义脚本实现；10. 主从切换方案包括手动切换、半自动切换（如mha）和全自动切换（如group replication）。
MySQL主从同步配置，简单来说就是让一台MySQL服务器（主服务器）的数据自动复制到另一台或多台MySQL服务器（从服务器）。本文将记录一次完整的主从同步配置过程，包括一些遇到的坑和解决方案，以及如何使用sublime text生成复制指令和监听配置模板，提升效率。
解决方案
首先，我们需要明确主从同步的基本原理。主服务器负责处理所有写操作，并将这些操作记录在二进制日志（binlog）中。从服务器连接到主服务器，读取binlog，并将这些操作应用到自身，从而保持数据一致。
一、主服务器配置
-
修改MySQL配置文件（my.cnf或my.ini）：
找到[mysqld]部分，添加或修改以下配置：
server-id=1 # 唯一ID，主服务器通常设为1 log_bin=mysql-bin # 开启二进制日志，指定日志文件前缀 binlog_format=ROW # 设置二进制日志格式，ROW模式更安全 binlog_do_db=your_database_name # 指定需要同步的数据库，可选 #binlog_ignore_db=mysql # 忽略同步的数据库，可选
- server-id：每个MySQL实例都必须有唯一的ID。
- log_bin：开启二进制日志，这是主从同步的基础。
- binlog_format：推荐使用ROW格式，它记录的是每一行数据的变化，而不是sql语句，更加安全可靠，避免因SQL语句执行环境不同导致的数据不一致。STATEMENT格式记录SQL语句，MIXED格式是前两者的混合。
- binlog_do_db 和 binlog_ignore_db：用于指定需要同步或忽略同步的数据库。如果不指定，默认同步所有数据库。
-
重启MySQL服务：
修改配置文件后，需要重启MySQL服务才能使配置生效。
-
创建用于复制的用户：
登录MySQL，执行以下SQL语句：
CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; FLUSH PRIVILEGES;
- ‘repl’@’%’：创建一个名为repl的用户，允许从任何IP地址连接。生产环境建议限制IP地址，例如’repl’@’192.168.1.100’。
- REPLICATION SLAVE：授予该用户复制权限。
- FLUSH PRIVILEGES：刷新权限，使修改生效。
-
锁定表并获取binlog信息：
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
FLUSH TABLES WITH READ LOCK会锁定所有表，防止在复制过程中发生数据变化。SHOW MASTER STATUS会显示当前的binlog文件名和位置，这些信息将在从服务器配置中使用。记录下File和position的值。
-
解锁表：
UNLOCK TABLES;
解锁表，恢复正常的写操作。
二、从服务器配置
-
修改MySQL配置文件（my.cnf或my.ini）：
找到[mysqld]部分，添加或修改以下配置：
server-id=2 # 唯一ID，从服务器通常设为大于1的数字 relay_log=relay-log # 开启中继日志，指定日志文件前缀 #read_only=1 # 设置为只读模式，防止从服务器写入数据，可选
- server-id：从服务器的ID必须与主服务器不同。
- relay_log：开启中继日志，从服务器会从主服务器拉取binlog，并存储到中继日志中。
- read_only：设置为只读模式，可以防止从服务器被意外写入数据，保证数据一致性。
-
重启MySQL服务：
修改配置文件后，需要重启MySQL服务才能使配置生效。
-
配置主从关系：
登录MySQL，执行以下SQL语句：
CHANGE MASTER TO MASTER_HOST='your_master_ip', MASTER_USER='repl', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='记录的binlog文件名', MASTER_LOG_POS=记录的binlog位置; START SLAVE;
- MASTER_HOST：主服务器的IP地址。
- MASTER_USER：用于复制的用户。
- MASTER_PASSWORD：用于复制的用户的密码。
- MASTER_LOG_FILE：主服务器的binlog文件名。
- MASTER_LOG_POS：主服务器的binlog位置。
-
检查同步状态：
SHOW SLAVE STATUSG
查看Slave_IO_Running和Slave_SQL_Running是否都为Yes，以及Seconds_Behind_Master是否为0或接近0。如果Seconds_Behind_Master持续增加，说明从服务器同步延迟较高，需要排查原因。
三、Sublime Text生成复制指令和监听配置模板
为了方便配置，可以使用Sublime Text创建一个代码片段，快速生成复制指令和监听配置模板。
-
创建代码片段文件：
在Sublime Text中，选择Tools -> Developer -> New Snippet…，创建一个新的代码片段文件。
-
编辑代码片段文件：
输入以下内容：
<snippet> <content><![CDATA[ CHANGE MASTER TO MASTER_HOST='${1:your_master_ip}', MASTER_USER='repl', MASTER_PASSWORD='${2:your_password}', MASTER_LOG_FILE='${3:binlog文件名}', MASTER_LOG_POS=${4:binlog位置}; START SLAVE; SHOW SLAVE STATUSG ]]></content> <tabTrigger>mysql_slave</tabTrigger> <description>MySQL Slave Configuration</description> <scope>source.sql</scope> </snippet>：包含代码片段的内容，${1:your_master_ip}表示第一个占位符，your_master_ip是默认值。 ：触发代码片段的关键词，这里设置为mysql_slave。 ：代码片段的描述。 ：代码片段的作用域，这里设置为source.sql，表示只在SQL文件中生效。
-
保存代码片段文件：
将文件保存为MySQL Slave Configuration.sublime-snippet，保存在Sublime Text的Packages/User目录下。
-
使用代码片段：
在Sublime Text中打开一个SQL文件，输入mysql_slave，然后按下Tab键，即可生成复制指令模板。
同样的方法，可以创建监听配置模板的代码片段：
<snippet> <content><![CDATA[ SHOW SLAVE STATUSG ]]></content> <tabTrigger>show_slave</tabTrigger> <description>Show Slave Status</description> <scope>source.sql</scope> </snippet>
副标题1：主从同步延迟高怎么办？
主从同步延迟高是一个常见的问题，可能由多种原因引起。以下是一些常见的解决方法：
- 网络问题： 检查主从服务器之间的网络连接是否稳定，带宽是否足够。可以使用ping命令或traceroute命令测试网络连接。
- 硬件资源不足： 检查从服务器的CPU、内存、磁盘I/O是否足够。如果硬件资源不足，可能会导致从服务器处理速度慢，从而导致延迟。
- 大事务： 主服务器上的大事务会导致从服务器需要花费很长时间才能完成同步。尽量避免在主服务器上执行大事务，可以将大事务拆分成多个小事务。
- 慢SQL： 主服务器上的慢SQL会导致从服务器同步延迟。优化主服务器上的SQL语句，可以使用EXPLaiN命令分析SQL语句的执行计划。
- 锁竞争： 从服务器在应用binlog时，可能会遇到锁竞争，导致同步延迟。优化数据库表结构，减少锁竞争。
- 多线程复制： MySQL 5.6之后支持多线程复制，可以提高从服务器的同步速度。可以通过设置slave_parallel_workers参数来开启多线程复制。例如：SET GLOBAL slave_parallel_workers = 4;
副标题2：如何监控主从同步状态？
除了使用SHOW SLAVE STATUSG命令查看同步状态外，还可以使用以下方法监控主从同步状态：
- 使用MySQL Enterprise Monitor： MySQL Enterprise Monitor是MySQL官方提供的监控工具，可以监控主从同步状态，并提供告警功能。
- 使用第三方监控工具： 有很多第三方监控工具可以监控MySQL主从同步状态，例如zabbix、prometheus等。
- 编写自定义脚本： 可以编写自定义脚本，定期检查主从同步状态，并发送告警邮件或短信。
以下是一个使用python编写的简单监控脚本示例：
import MySQLdb import time def check_slave_status(host, user, password): try: conn = MySQLdb.connect(host=host, user=user, passwd=password, db='mysql') cursor = conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("SHOW SLAVE STATUS") result = cursor.fetchone() if result: slave_io_running = result['Slave_IO_Running'] slave_sql_running = result['Slave_SQL_Running'] seconds_behind_master = result['Seconds_Behind_Master'] if slave_io_running == 'Yes' and slave_sql_running == 'Yes': print("Slave is running normally.") if seconds_behind_master is not None and seconds_behind_master > 60: print(f"Warning: Slave is {seconds_behind_master} seconds behind master.") else: print("Error: Slave is not running properly.") else: print("Error: Slave is not configured.") conn.close() except MySQLdb.Error as e: print(f"Error connecting to MySQL: {e}") if __name__ == "__main__": master_host = 'your_slave_ip' master_user = 'your_user' master_password = 'your_password' check_slave_status(master_host, master_user, master_password)
副标题3：主从切换的方案有哪些？
当主服务器发生故障时，需要将从服务器切换为主服务器，以保证服务的可用性。以下是一些常见的主从切换方案：
- 手动切换： 手动将从服务器提升为主服务器，并修改应用程序的连接配置。这种方案简单易行，但需要人工干预，耗时较长。
- 半自动切换： 使用工具（例如MHA）自动检测主服务器故障，并自动将从服务器提升为主服务器。这种方案可以减少人工干预，但需要配置和维护MHA。
- 全自动切换： 使用高可用集群（例如Galera Cluster、MySQL Group Replication）自动检测主服务器故障，并自动将从服务器提升为主服务器。这种方案无需人工干预，但配置和维护较为复杂。
无论选择哪种方案，都需要进行充分的测试，确保切换过程顺利进行。
