1、主库配置
vi /etc/my.cnf #主服务器唯一id server-id=1 #启用二进制日志 log-bin=mysql-bin #设置不要复制的数据库(可以设置多个) binlog-ignore-db=mysql binlog-ignore-db=information_schema #设置需要复制的数据库 binlog-do-db=test #设置logbin格式 binlog_format=STATEMENT
2、从库配置
vi /etc/my.cnf #最后添加如下配置 #从服务器唯一id server-id=2 #启用中继日志 relay-log=mysql-relay
3、主机建用户(重启mysql、设置防火墙)
#老版本创建用户授权一体化 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123'; #新版本需要分开 create user 'slave'@'%' identified by '123123'; grant REPLICATION SLAVE ON *.* TO 'slave'@'%' with grant option; flush privileges; #查看master状态 mysql> show master status; +------------------+----------+--------------+--------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+--------------------------+-------------------+ | mysql-bin.000008 | 869 | test | mysql,information_schema | | +------------------+----------+--------------+--------------------------+-------------------+ 1 row in set (0.00 sec)
4、从机配置需要复制的主机
mysql> CHANGE MASTER TO MASTER_HOST='100.98.100.186', -> MASTER_USER='slave', -> MASTER_PASSWORD='123123', -> MASTER_PORT= 3306, -> MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=869; Query OK, 0 rows affected, 8 warnings (0.09 sec) #启动从服务器复制功能 start slave; #查看从服务器状态 mysql> show slave status G; *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes
#重置slave (如果出问题直接重置slave主机,然后重新配置主机) stop slave; reset master;
5、验证主从
master 建库建表、从库查询
create database test; use test; create table student( id int, name varchar(100) ) insert into student value(1,'zs');
6、mycat读写分离配置
解压、配置schema.xml (balance='0'没有进行读写分离) server.xml使用默认配置、rule.xml分片没有规则
mv mycat /usr/local/ [root@node-1 conf]# cat schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="node-1" database="test" /> <dataHost name="node-1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="100.98.100.186" url="100.98.100.186:3306" user="root" password="123456"> <readHost host="100.98.100.186" url="100.98.100.186:3307" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema> 启动 ./mycat start
7、连接mycat 验证读写分离 (高版本mysql第一种方法不能用)
方法一: 1、主节点执行 insert into student valuse(4,@@hostname); 主从主机数据不一致了 2、mycat 去查询:select * from student; 方法二:直接使用mycat查询 select @@hostname from student; 发现没有读写分离 配置mycat balance='3'