主从复制是mysql提供的一个热备份机制,我们可以利用此机制实现读写分离,提高系统的性能。一般情况下是一主多从。本文章因为条件所限,写的是一主一从,不过不影响功能的理解。
MySQL主从配置、读写分离
主从配置,简而言之,就是一台主服务器和一台从服务器。
主从配置,一般是从服务器需要和主服务器进行交互,完成数据的同步。
1、授权从服务器能访问。
mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO root@‘%’ IDENTIFIED BY ‘1234’;
‘%’表示所有客户端IP都能链接,如想指定IP链接,可以将%换成具体IP地址。
授权一个帐户root,并且只能允许从10.100.0.200这个地址上来登陆,密码是1234
2、修改Master服务器中的MySQL配置(my.ini)
windows中是my.ini, linux中是my.conf。
[mysqld]
server-id=1
log-bin=mysql-bin #二进制日志文件的配置
binlog-do-db=java1701 #主要进行二进制日志记录的数据库
windows下的日志文件生成在:C:\ProgramData\MySQL\MySQL Server 5.7\Data下。
修改完配置之后,需要重启MySQL服务器。
重启成功之后,输入如下指令查看结果:
mysql> show master status
3、Slave服务器(从服务器)的配置
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
第一步:修改my.conf文件
[mysqld]
server-id=131
第二步:配置从服务器
mysql>change master to master_host='10.10.159.131',master_port=3306,master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=120
注意语句中间不要断开,master_host主服务器的IP地址,master_port为mysql服务器端口号(无引号),master_user为执行同步操作的数据库账户,“120”无单引号(此处的120就是show master status 中看到的position的值,这里的mysql-bin.000001就是file对应的值)。
第二步:启动从服务器复制功能
Mysql>start slave;
第三步:检查从服务器复制功能状态:
mysql> show slave status
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)
注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
4、测试
在主服务器上进行新增数据,从服务器将会自动同步。
MyCat的主从配置
<writeHost host="hostM1" url="192.168.91.231:3306" user="root" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.91.232:3306" user="root" password="123456" />
</writeHost>
读写分离
在主服务器上进行增删改操作,在从服务器上进行读操作。 在实际项目中,读操作是占比非常大的操作,分离开来能减轻服务器的压力。并且减少死锁的概率。
读写分离的实现
实现比较简单,在项目中配置两个数据源就ok。
备份::::
<!-- schema表示一个逻辑数据库,name是逻辑数据库的名称,checkSQLschema:是否检查SQL语法。
sqlMaxLimit:默认查询前100条记录。类似:select * from tb_book limit 0,100 -->
<schema name="MYCAT_1704" checkSQLschema="false" sqlMaxLimit="100">
<!-- 配置逻辑表,name是逻辑表名,dataNode映射的物理表的地址,rule:分表规则 -->
<!-- auto-sharding-long根据数据的长度进行分表:0-50000;50001-100000;100001-150000-->
<!-- auto sharding by id (long) -->
<table name="tb_book" dataNode="mycat_1,mycat_2" rule="auto-sharding-long" />
</schema>
<!-- dataNode是一个数据节点,就是指代一个物理数据库
name:当前dataNode的名称(不能重复)
dataHost:是当前物理数据库的主机地址
database:就是物理数据库
-->
<dataNode name="mycat_1" dataHost="mycat_host" database="db_mycat_1704" />
<!--<dataNode name="mycat_2" dataHost="mycat_host_linux" database="db_mycat_1704_linux" />
dataHost就是物理主机地址 ,name是唯一标识dataHost的值。maxCon\minCon最大和最小连接池,
balance\writeType跟主从分区有关,一般情况采用默认值就行
dbType:物理数据库的类型,默认MySQL。dbDriver:MySQL默认是native,其他数据库另外一个值JDBC。
-->
<dataHost name="mycat_host" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 心跳 :用来检测MySQL客户端是否连接正常 -->
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456" />
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<dataHost name="mycat_host_linux" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 心跳 :用来检测MySQL客户端是否连接正常 -->
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.72.188:3306" user="root"
password="123456" />
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
正确的配置:(auto-sharding-long默认需要有3个dataNode)
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<table name="tb_book" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="localhost1" database="db_mycat_1704" />
<dataNode name="dn2" dataHost="linuxhost" database="db_mycat_1704_linux" />
<dataNode name="dn3" dataHost="localhost1" database="java1" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<dataHost name="linuxhost" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.72.188:3306" user="root"
password="123456">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
</mycat:schema>