详解MySQL主从复制及读写分离攻略

概述

MySQL主从复制和读写分离是MySQL数据库的高可用性和性能优化的常用手段。主从复制可以帮助我们实现数据备份和数据可靠性并提高读写性能，读写分离能够分担主数据库的读压力，优化读取性能。本文将详细讲解MySQL主从复制和读写分离的配置方法及注意事项。

MySQL主从复制

什么是MySQL主从复制？

MySQL主从复制是一种异步的复制方式，可以复制一份数据到其他的MySQL服务器上。其中，主服务器（Master）是提供的数据源，从服务器（Slave）则是数据源的副本。Master服务器可以接收客户端的写请求，而Slave服务器只能接受客户端的读请求，因此Master用于处理写请求，Slave用于处理读请求。

如何配置MySQL主从复制？

下面是MySQL主从复制的基本配置步骤：

配置Master的my.cnf文件，增加以下配置：

server-id=1 log-bin=mysql-bin binlog-do-db=test_db binlog-ignore-db=mysql

在Master中创建一个可以复制的账号：

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

备份Master中要复制的数据库：

mysqldump -uroot -ppassword --single-transaction test_db > test_db.sql

在Slave中配置my.cnf文件，增加以下配置：

server-id=2 relay-log=mysql-relay-bin log-bin=mysql-bin read-only=1

将Master中备份的数据库恢复到Slave中：

mysql -uroot -ppassword < test_db.sql

在Slave中配置从Master复制数据的相关参数：

CHANGE MASTER TO MASTER_HOST='192.168.1.101',MASTER_PORT=3306,MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4;

启动Slave的复制功能：

START SLAVE;

查看Slave的复制状态：

SHOW SLAVE STATUS \G;

示例

下面我们来看一个MySQL主从复制的示例。假设有一台Master服务器和两台Slave服务器，我们要把Master上的test_db数据库复制到两台Slave服务器上。假设Master的IP地址为192.168.1.101，Slave1的IP地址为192.168.1.102，Slave2的IP地址为192.168.1.103。

在Master中的my.cnf文件中增加以下配置：

server-id=1 log-bin=mysql-bin binlog-do-db=test_db binlog-ignore-db=mysql

在Master中创建一个可以复制的账号：

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

备份Master中要复制的数据库：

mysqldump -uroot -ppassword --single-transaction test_db > test_db.sql

在Slave1和Slave2中配置my.cnf文件，增加以下配置：

server-id=2 relay-log=mysql-relay-bin log-bin=mysql-bin read-only=1

将Master中备份的数据库恢复到Slave1和Slave2中：

mysql -uroot -ppassword < test_db.sql

在Slave1和Slave2中配置从Master复制数据的相关参数：

CHANGE MASTER TO MASTER_HOST='192.168.1.101',MASTER_PORT=3306,MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4;

启动Slave1和Slave2的复制功能：

START SLAVE;

查看Slave1和Slave2的复制状态：

SHOW SLAVE STATUS \G;

现在，你可以在Master上插入新的数据，然后在Slave1和Slave2上查看数据是否同步。

MySQL读写分离

什么是MySQL读写分离？

MySQL读写分离是指将数据库的读和写请求分别分配给不同的服务器进行处理的技术，主要目的是减轻主服务器的负载，提高读取性能。MySQL读写分离需要在主服务器和从服务器之间设置一个代理服务器（Proxy），通过Proxy将读操作转发到从服务器，将写操作转发到主服务器。

如何配置MySQL读写分离？

下面是MySQL读写分离的基本配置步骤：

安装Proxy服务器，比如MyCat或者MaxScale。
在Proxy服务器中配置主服务器和从服务器的连接。
配置Proxy服务器的读写分离规则，比如将所有的SELECT语句发送到从服务器，将所有的INSERT、UPDATE、DELETE语句发送到主服务器。
修改应用程序连接MySQL数据库的代码，将连接参数中的IP地址改为Proxy服务器的IP地址。

示例

下面我们来看一个MySQL读写分离的示例，假设有一台Master服务器和两台Slave服务器，我们要将读操作转发到两台Slave服务器上，将写操作转发到Master服务器上。假设Master的IP地址为192.168.1.101，Slave1的IP地址为192.168.1.102，Slave2的IP地址为192.168.1.103，Proxy服务器的IP地址为192.168.1.104。

安装MyCat或者MaxScale服务器。
在Proxy服务器中配置主服务器和从服务器的连接。比如，在MyCat中可以使用以下配置：

<system> <systemProperties> <property name="socksProxyHost" value="192.168.1.101"/> <property name="socksProxyPort" value="3306"/> <property name="mysql-slave-1" value="192.168.1.102:3306"/> <property name="mysql-slave-2" value="192.168.1.103:3306"/> </systemProperties> </system>

配置MyCat的读写分离规则，比如将所有的SELECT语句发送到从服务器，将所有的INSERT、UPDATE、DELETE语句发送到主服务器。可以使用以下配置：

<rule name="test_db_rule"> <tableRule name="test_table"> <rule> <columns>*</columns> <algorithm>modula</algorithm> <shardCount>2</shardCount> </rule> <rule> <columns>*</columns> <algorithm>round-robin</algorithm> <dataSource>mysql-slave-1, mysql-slave-2</dataSource> </rule> </tableRule> <function name="modula" class="io.mycat.route.function.PartitionByMod"> <property name="count" value="2" /> <property name="partitionLength" value="2" /> </function> </rule>