MySQL主从复制与读写分离两种实现方案

[diaosichengxuyuan]

1.应用层实现读写分离

(1)原理：

(2)定义动态数据源，实现通过集成Spring提供的AbstractRoutingDataSource，只需要实现determineCurrentLookupKey方法即可，由于DynamicDataSource是单例的，线程不安全的，可以采用ThreadLocal保证线程安全，DynamicDataSourceHolder是一个ThreadLocal对象。

public class DynamicDataSource extends AbstractRoutingDataSource{  
  
    @Override  
    protected Object determineCurrentLookupKey() {  
        return DynamicDataSourceHolder.getKey();  
    }  
  
} 

(3)定义数据源的AOP切面，可以拦截所有Service层方法，然后根据方法名判断是应该走读库还是写库

public class DataSourceAspect {  
  
    /** 
     * 在进入Service方法之前执行 
     */  
    public void before(JoinPoint point) {  
        // 获取到当前执行的方法名  
        String methodName = point.getSignature().getName();  
        if (isSlave(methodName)) {  
            // 标记为读库  
            DynamicDataSourceHolder.setKey("slave");  
        } else {  
            // 标记为写库  
            DynamicDataSourceHolder.setKey("master");  
        }  
    }  
  
    /** 
     * 判断是否为读库 
     */  
    private Boolean isSlave(String methodName) {  
        // 方法名以query、find、get开头的方法名走从库  
        return StringUtils.startsWithAny(methodName, "query", "find", "get");  
    }  
  
} 

(4)配置动态数据源

    <bean id="dataSource" class="cn.itcast.usermanage.spring.DynamicDataSource">  
        <!-- 设置多个数据源 -->  
        <property name="targetDataSources">  
            <map key-type="java.lang.String">  
                <!-- 这个key需要和程序中的key一致 -->  
                <entry key="master" value-ref="masterDataSource"/>  
                <entry key="slave" value-ref="slave01DataSource"/>  
            </map>  
        </property>  
        <!-- 设置默认的数据源，这里默认走写库 -->  
        <property name="defaultTargetDataSource" ref="masterDataSource"/>  
    </bean>  

(5)主库master配置
在my.ini修改：
<1>开启主从复制，主库的配置：log-bin=mysql3306-bin
<2>指定主库serverid：server-id=101
<3>指定同步的数据库，如果不指定则同步全部数据库：binlog-do-db=mybatis_1128
<4>在主库创建同步用户，授权用户slave01使用123456密码登录mysql
grant replication slave on *.* to 'slave01'@'127.0.0.1'identified by '123456';
<5>执行SQL语句查询状态：SHOW MASTER STATUS

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql3306-bin.000006 | 1120 | mybatis_1128 | |
+------------------+----------+--------------+------------------+

(6)从库slave配置
在my.ini修改：
<1>指定serverid，只要不重复即可，从库也只有这一个配置，其他都在SQL语句中操作：server-id=102
<2>执行以下SQL：
CHANGE MASTER TO
master_host='127.0.0.1',
master_user='slave01',
master_password='123456',
master_port=3306,
master_log_file='mysql3306-bin.000006',
master_log_pos=1120;
<3>启动slave同步：START SLAVE;
<4>查看同步状态：SHOW SLAVE STATUS;

Slave_IO_Running: YES
Slave_SQL_Running: YES

2.基于mysql-proxy代理实现读写分离

Amoeba、mysql-proxy、mycat等中间件都支持读写分离，下面演示的是mysql-proxy实现读写分离的过程
(1)主从数据库的配置跟上面一样，就不重复了。
(2)安装mysql-proxy，地址：http://dev.mysql.com/downloads/mysql-proxy/

tar zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz
mv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

(3)配置mysql-proxy，创建主配置文件

cd /usr/local/mysql-proxy
mkdir lua #创建脚本存放目录
mkdir logs #创建日志目录
cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本
vi /etc/mysql-proxy.cnf   #创建配置文件
[mysql-proxy]
user=root #运行mysql-proxy用户
admin-username=proxy #主从mysql共有的用户
admin-password=123.com #用户的密码
proxy-address=192.168.0.204:4000 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.0.203 #指定后端从slave读取数据
proxy-backend-addresses=192.168.0.202 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true    #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
保存退出！
chmod 660 /etc/mysql-porxy.cnf

(4)修改读写分离配置文件

vi /usr/local/mysql-proxy/lua/rw-splitting.lua
if not proxy.global.config.rwsplit then
 proxy.global.config.rwsplit = {
  min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1
  max_idle_connections = 1, #默认8，改为1
  is_debug = false
 }
end

(5)启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
netstat -tupln | grep 4000 #已经启动
tcp 0 0 192.168.0.204:4000 0.0.0.0:* LISTEN 1264/mysql-proxy
关闭mysql-proxy使用：killall -9 mysql-proxy

(6)测试读写分离
<1>在主服务器创建proxy用户用于mysql-proxy使用，从服务器也会同步这个操作

grant all on *.* to 'proxy'@'192.168.0.204' identified by '123.com';

<2>使用客户端连接mysql-proxy

mysql -u proxy -h 192.168.0.204 -P 4000 -p123.com

<3>创建数据库和表，这时的数据只写入主mysql，然后再同步从slave，可以先把slave的关了，看能不能写入

mysql> create table user (number INT(10),name VARCHAR(255));
mysql> insert into user values(01,'zhangsan');
mysql> insert into user values(02,'lisi');

<4>登陆主从mysq查看新写入的数据如下

mysql> use test;
Database changed
mysql> select * from user;
+--------+----------+
| number | name |
+--------+----------+
| 1 | zhangsan |
| 2 | lisi |
+--------+----------+

<5>再登陆到mysql-proxy，查询数据，看出能正常查询

mysql -u proxy -h 192.168.0.204 -P 4000 -p123.com
mysql> use test;
mysql> select * from user;
+--------+----------+
| number | name |
+--------+----------+
| 1 | zhangsan |
| 2 | lisi |
+--------+----------+

<6>登陆从服务器关闭mysql同步进程，这时再登陆mysql-proxy肯定会查询不出数据

slave stop；

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
mysql> select * from user;
ERROR 1146 (42S02): Table 'test.user' doesn't exist

参考：https://blog.csdn.net/liu976180578/article/details/77684583
https://www.cnblogs.com/fyc119/p/7529903.html