一篇文章看懂MySQL主从复制与读写分离

1. 什么是MySQL主从复制和读写分离？

在MySQL中，主从复制（Master-Slave Replication）和读写分离（Read-Write Separation）都是常见的数据库解决方案。主从复制是指将数据库的主库数据同步到从库中，从而实现主从数据库的数据一致性和备份，它可以提高数据库的可靠性和可用性；而读写分离则是将读请求和写请求分别分配到不同的库中处理，从而实现数据库的读写分离和负载均衡，可以提高数据库的性能和吞吐量。

2. 如何进行MySQL主从复制？

• 第一步：在主库上创建一个新的MySQL用户，并开启二进制日志。

mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SHOW BINARY LOGS;

• 第二步：在从库上创建一个新的MySQL用户，并向主库请求同步数据。

mysql> CHANGE MASTER TO
    -> MASTER_HOST='master_host_name',
    -> MASTER_USER='replication_user_name',
    -> MASTER_PASSWORD='replication_password',
    -> MASTER_LOG_FILE='recorded_log_file_name',
    -> MASTER_LOG_POS=recorded_log_position;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G;

3. 如何进行MySQL读写分离？

• 第一步：在主库上创建一个新的MySQL用户，并授权该用户读写权限。

mysql> CREATE USER 'rw_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON `mydb`.* TO 'rw_user'@'%';

• 第二步：在从库上创建一个新的MySQL用户，并授权该用户只读权限。

mysql> CREATE USER 'ro_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT SELECT ON `mydb`.* TO 'ro_user'@'%';

• 第三步：使用MySQL代理或负载均衡器，将读请求和写请求分别路由到不同的数据库上。

例如使用MySQL Proxy：

proxy --proxy-address=localhost:4040 \
      --proxy-backend-addresses=master:3306 \
                              slave1:3306 \
                              slave2:3306 \
      --proxy-lua-script=/path/to/rw-splitting.lua

其中，/path/to/rw-splitting.lua为一个Lua脚本文件，用于将读请求路由到从库上，而将写请求路由到主库上。例如：

function read_query(packet)
  if packet:byte() == proxy.COM_QUERY then
    local query = packet:sub(2)
    local prefix = string.lower(query:sub(1, 6))
    if prefix == 'select' or prefix == 'show ' then
      proxy.queries:append(1, packet, {resultset_is_needed = true})
      return proxy.PROXY_SEND_QUERY
    else
      proxy.queries:append(2, packet, {})
      return proxy.PROXY_SEND_QUERY
    end
  end
end

function read_query_result(packet)
  if proxy.connection.server["id"] == 1 then
    return proxy.PROXY_SEND_RESULT
  elseif proxy.connection.server["id"] > 1 then
    resultset = proxy.peek_query_result(1)
    if resultset ~= nil then
      proxy.queries:append(3, string.char(proxy.COM_QUERY) .. 'SET @@SESSION.MASTER_POS_WAIT=' .. resultset[1][2] .. ';', {})
    end
  end
end

这个Lua脚本可以让MySQL Proxy将所有以SELECT或SHOW开头的读请求路由到从库上，而将所有其他请求路由到主库上。

4. 示例说明

（1）MySQL主从复制示例

针对第2步中的操作，在主库上创建一个新的数据库mydb，并插入一条新数据：

mysql> CREATE DATABASE mydb;
mysql> USE mydb;
mysql> CREATE TABLE `users` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `username` varchar(255) NOT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> INSERT INTO `users` (`username`) VALUES ('foo');

然后，在从库上查询users表中的数据，可以发现从库上已经同步了主库上的数据：

mysql> SELECT * FROM `mydb`.`users`;
+----+----------+
| id | username |
+----+----------+
|  1 | foo      |
+----+----------+

（2）MySQL读写分离示例

针对第三步中的操作，在主库上创建一个新的数据库mydb，并向该数据库中插入一条新数据：

mysql> CREATE DATABASE mydb;
mysql> USE mydb;
mysql> CREATE TABLE `users` (
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `username` varchar(255) NOT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> INSERT INTO `users` (`username`) VALUES ('foo');

此时，从库上是无法立即查询到新数据的，因为MySQL Proxy将写请求路由到了主库上。但是，从库上可以立即查询之前插入的数据：

mysql> SELECT * FROM `mydb`.`users`;
+----+----------+
| id | username |
+----+----------+
|  1 | foo      |
+----+----------+

如果需要实时同步主库和从库的所有数据，可以考虑使用MySQL Galera Cluster等高可用性解决方案。