1. 查看用户,
select user,host from mysql.user;
2. 删除用户
drop user copyuser@10.208.102.10x;
3. 创建用户
CREATE USER 'copyuser'@'%' IDENTIFIED BY '123456';
4. 用户授权
GRANT REPLICATION SLAVE ON *.* TO 'copyuser'@'%';
5. 刷新权限
flush privileges;
6. 查看主机状态
show master status;
7. 从机同步
CHANGE MASTER TO MASTER_HOST='10.208.102.101',MASTER_USER='copyuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000006',MASTER_LOG_POS=16675;
8. 启用,停用 slave
START SLAVE;
Stop SLAVE;
9. 查看slave状态
SHOW SLAVE STATUS\G;
图中Yes 代表成功。
10. 重新配置主从,在从机上执行
#删除Master中所有的binglog文件,并将日志索引文件清空,重新开始所有新的日志文件(慎用)
reset master;
11. mycat 命令
mycat status
mycat start
mycat stop
mycat restart
12. server.xml配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="defaultSqlParser">druidparser</property>
</system>
<!--以下设置为应用访问帐号权限 -->
<user name="root">
<property name="password">root</property>
<!--分片名,对应 schema.xml的分片id(通常直接改成需要同步的数据库名也可以)-->
<property name="schemas">test</property>
</user>
<!--以下设置为应用只读帐号权限 -->
<user name="user">
<property name="password">user</property>
<property name="schemas">test</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
13. schema.xml配置文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="test" checkSQLschema="ture" sqlMaxLimit="100" dataNode='dn1'></schema>
<dataNode name="dn1" dataHost="dthost1" database="test"/>
<dataHost name="dthost1" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="wh1" url="localhost:3306" user="root" password="123456">
<readHost host="rh1" url="10.208.102.217:3306" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
14. Navicat 访问mycat如下,一个只读账号,一个写入账号
用mycat写入账号写入一条数据,
然后主从库里都多出一条记录,
参考:
mysql主从复制