Centos7搭建主从数据库并配置读写分离
一.数据库主从配置1.安装Mysql
2.创建用户并设置暗码
CREATE USER 'user'@'%' IDENTIFIED BY '123456';
update mysql.user set authentication_string=password("123456") where user="user";
grant all privileges on *.* to user@'%' identified by '123456'; #授权
select host,user,authentication_string from mysql.user;select host,user,authentication_string from mysql.user; #检查
flush privileges;
3.主从服务器配置
主服务器：192.168.200.231
从服务器：192.168.200.232
主服务器mysql配置
# grep -Ev "^#|^$" /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
server-id=129 #唯一
log_bin=mysql-bin
log-slave-updates=1
binlog-do-db=test#需要同步的数据库,如果没有本行表示同步所有的数据库
binlog-ignore-db=mysql #被忽略的数据
skip-grant-tables
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
从服务器mysql配置
# grep -Ev "^#|^$" /etc/my.cnf
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
server-id=128 #唯一
log_bin= mysql-bin
relay-log= mysql-relay-bin
read-only=1 #限制普通用户对从数据可的更新操作
log-slave-updates=1
replicate-do-db=test #要同步的数据库,不写本行表示同步所有数据库
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
4.重启服务
5.验证是否能够链接到主数据库
CHANGE MASTER TO
MASTER_HOST='192.168.200.231',
MASTER_USER='DBuser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',# 执行mysql> show master status; 查询对应的参数
MASTER_LOG_POS=120,# 执行mysql> show master status; 查询对应的参数
MASTER_CONNECT_RETRY=10;
start slave;
show slave status \G #查抄状态
https://i-blog.csdnimg.cn/direct/ea273dc499814f82be34a8e95e09d5f8.png
二.配置mycat
1.安装java环境最幸亏jdk7以上版本
2.安装mycat
# tar xf Mycat-server-1.6.6.1.tar.gz -C /usr/local
# useradd mycat
# chown -R mycat:mycat /usr/local/mycat
# passwd mycat
3.修改内存配置
wrapper.java.additional.10=-Xmx4G
wrapper.java.additional.11=-Xms1G
改成
wrapper.java.additional.10=-Xmx1G
wrapper.java.additional.11=-Xms256M
vi /etc/sysconfig/network
HOSTNAME=你的主机名(xxxx)，我的主机名是mysql-loadbalancer
4.修改环境变量
# vi /etc/profile
MYCAT_HOME=/usr/local/mycat
PATH=$MYCAT_HOME/bin:$PATH
# source /etc/profile
5.配置mycat
修改server.xml如下方：
<user name="root" defaultAccount="true">
<property name="password"></property>
<property name="schemas">TESTDB,mysql</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="DBuser">
<property name="password">123456</property>
<property name="schemas">TESTDB,mysql</property>
<property name="readOnly">true</property>
</user>
修改schema.xml如下方：
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<schema name="mysql" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="localhost1" database="test" />
<dataNode name="dn2" dataHost="localhost1" database="mysql" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.200.231:3306" user="DBuser" password="123456">
<readHost host="hostS1" url="192.168.200.232:3306" user="DBuser" password="123456" /> <!-- 读写分离; 写走hostM1,读走hostS1; hostM1宕机了, hostS1也不可用 -->
</writeHost>
<writeHost host="hostM2" url="192.168.200.232:3306" user="DBuser" password="123456" /><!-- 高可用,hostM1宕机了, hostM2顶上 -->
</dataHost>
</mycat:schema>
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]