MySQL 主从配置 - 实现读写分离

环境

两台 CentOS 6.5 测试机，均已安装 MySQL 5.6 服务。选取其中一台作为主（master），另外一台作为从（salve）服务。

主：192.168.1.102

从：192.168.1.104

主（master）配置

主库负责写操作（INSERT，UPDATE，DELETE），设置 InnoDB 为默认存储引擎，编辑 my.cnf

# vi /etc/my.cnf

在 [mysqld] 后面加上

default-storage-engine=InnoDB	 // InnoDB 为行级锁, 支持事务, 并发写入性能比 MyISAM 好
server-id=102					 // 只要主从不一样即可, 通常取IP的最后一段
log_bin=master-log-bin			// 开启二进制日志, 同步的原理是salve从master同步日志并在slave本地执行一次
binlog_do_db=test_db			  // 需要做同步的数据库名字, 多个配置多行
binlog-ignore-db=mysql			// 不需要做同步的数据库名, 多个配置多行

重启 MySQL 服务

# service mysqld restart

在主库创建复制权限的用户

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO salveusr@'%' IDENTIFIED BY 'salveusr';

从（slave）配置

从库负责读操作，设置 MyISAM 为默认存储引擎，编辑 my.cnf

# vi /etc/my.cnf

在 [mysqld] 后面加上

default-storage-engine=MyISAM	// MyISAM 查询速度比 InnoDB 快一些
server-id=104
replicate-do-db=test_db
replicate-ignore-db=mysql

重启 MySQL 服务

# service mysqld restart

在从库执行

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.102', MASTER_USER='salveusr', MASTER_PASSWORD='salveusr';
mysql> start slave;

准备测试的 SQL

在主从服务器分别创建 test_db 数据库

CREATE DATABASE test_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

在主从服务器 test_db 数据库中分别创建 user 表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` varchar(255) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
)

JDBC 测试

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;

public class Test {
	
	private static final String USERNAME = "root";
	
	private static final String PASSWORD = "root";
	
	private static final String SLAVE_URL = "jdbc:mysql://192.168.1.104:3306/test_db?autoReconnect=true&useUnicode=true&characterEncoding=utf-8";
	
	private static final String MASTER_URL = "jdbc:mysql://192.168.1.102:3306/test_db?autoReconnect=true&useUnicode=true&characterEncoding=utf-8";

	public static void main(String[] args) throws SQLException {
		// 先查询一次, 查询使用的是 slave 数据源
		select();
		// 插入一条数据, 插入使用的是 master 数据源
		insert("张三", "男");
		// 再次查询, 有一条数据, 该数据便是上面 master 数据源插入的数据
		select();
	}

	// 查询
	public static void select() throws SQLException {
		try (Connection conn = getSlaveConnection()) {
			String sql = "SELECT * FROM USER";
			try (PreparedStatement stmt = conn.prepareStatement(sql)) {
				try (ResultSet rs = stmt.executeQuery()) {
					while (rs.next()) {
						int id = rs.getInt("id");
						String name = rs.getString("name");
						String sex = rs.getString("sex");
						Date createTime = rs.getTimestamp("create_time");
						LocalDateTime datetime = createTime.toInstant().atZone(ZoneId.systemDefault())
								.toLocalDateTime();
						System.out.println(id + "\t" + name + "\t" + sex + "\t" + datetime);
					}
					System.out.println("--- 查询结束 ---");
				}
			}
		}
	}

	// 插入
	public static void insert(String name, String sex) throws SQLException {
		try (Connection conn = getMasterConnection()) {
			String sql = "INSERT INTO USER(id, name, sex, create_time) VALUES(null, ?, ?, now())";
			try (PreparedStatement stmt = conn.prepareStatement(sql)) {
				stmt.setString(1, name);
				stmt.setString(2, sex);
				stmt.executeUpdate();
			}
		}
	}

	// slave 连接
	public static Connection getSlaveConnection() throws SQLException {
		return DriverManager.getConnection(SLAVE_URL, USERNAME, PASSWORD);
	}

	// master 连接
	public static Connection getMasterConnection() throws SQLException {
		return DriverManager.getConnection(MASTER_URL, USERNAME, PASSWORD);
	}

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}

测试结果

--- 查询结束 ---
1	张三	男	2016-10-20T05:04:59
--- 查询结束 ---