2.搭建双主双从
编号 角色 Ip地址 机器名
1 Master1 192.168.119.131 Hadoop2
2 Slave1 192.168.119.132 Hadoop3
3 Master2 192.168.119.133 Hadoop1
4 Slave2 192.168.119.134 Hadoop4
2.1修改配置文件
修改四台服务器的/etc/my.cnf文件
①Master1
[mysqld]
server-id=1 #设置主机id,配置唯一服务器id
log-bin=mysql-bin #启动二进制日志
#设置不需要复制的库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的库
binlog-do-db=mystudent
#设置logbin格式
binlog_format=STATEMENT
#在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
#表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=1
#已下为安装的基本配置
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
②Slove1
[mysqld]
server-id=2 #配置服务id
relay-log=mysql-relay #启动中继日志
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
③Master2
[mysqld]
server-id=3 #设置主机id,配置唯一服务器id
log-bin=mysql-bin #启动二进制日志
#设置不需要复制的库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的库
binlog-do-db=mystudent
#设置logbin格式
binlog_format=STATEMENT
在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
#表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=2
#已下为安装的基本配置
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
④Slave2
[mysqld]
server-id=4 #配置服务id
relay-log=mysql-relay #启动中继日志
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true
2.2 赋予从机权限
①Master1赋予Slave1权限(后面密码自行修改)
GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘789123’;
②Master2赋予Slave2权限
GRANT REPLICATION SLAVE ON . TO ‘slave’@’%’ IDENTIFIED BY ‘789123’;
2.3 从机配置需要复制的主机
①查询主机的起始索引和文件名;查询主机的状态,记录file。
Master1:
mysql> show master status
-> ;
±-----------------±---------±-------------±-------------------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-------------------------±------------------+
| mysql-bin.000003 | 1790 | mystudent | mysql,information_schema | |
±-----------------±---------±-------------±-------------------------±------------------+
Master2同理:
记录file 和position待会复制到出来
②Slave1配置Master1需要复制的主机
CHANGE MASTER TO MASTER_HOST='192.168.119.131',
MASTER_USER='slave',
MASTER_PASSWORD='789123',
MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1790;
③Slave2配置Master2需要复制的主机
CHANGE MASTER TO MASTER_HOST=‘192.168.119.133’, MASTER_USER=‘slave’,
MASTER_PASSWORD=‘789123’,
MASTER_LOG_FILE=‘mysql-bin.000004’,MASTER_LOG_POS=435;
2.4 启动从机
start slave;
2.5查询从机的状态
show slave status \G;
下面两个yes表示搭建成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2.6 主机之间互相备机
①Master 1:
CHANGE MASTER TO MASTER_HOST='192.168.119.133',
MASTER_USER='slave',
MASTER_PASSWORD='789123',
MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=435;
②Master2 :
CHANGE MASTER TO MASTER_HOST='192.168.119.133',
MASTER_USER='slave',
MASTER_PASSWORD='789123',
MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=435;
2.7 主机启动备机
互相启动:
start slave; 同理两个yes表示执行成功
2.8 Master1或Master2创建复制库,创建表,查询数据
create database mystudent;#mystudent为我配置的复制库
use mystudent;
create table student (
id varchar(20),
name varchar(20)
);
insert into student values(‘1’,’xxx’);
2.9 测试主从复制;
①每台服务器测试是否有数据复制进去即可
2.10修改配置mycat/conf配置文件schdule.xml
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
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.119.131:3306" user="root"
password="789123">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.119.132:3306" user="root" password="789123" />
</writeHost>
<writeHost host="hostM2" url="192.168.119.133:3306" user="root"
password="789123">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.119.134:3306" user="root" password="789123" />
</writeHost>
<!--<writeHost host="hostS1" url="localhost:3316" user="root"
password="123456" /> -->
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
配置文件的解读:
①balance解读(负载均衡配置)
(1)balance=“0”, 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
(2)balance=“1”,全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从 模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。
(3)balance=“2”,所有读操作都随机的在 writeHost、readhost 上分发。
(4)balance=“3”,所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力
②switchtype解读:这个字段代表当写服务器(writehost)挂了自动切换到第二个(writehost)
1 默认值,自动切换。
-1 表示不自动切换
2 基于 MySQL 主从同步的状态决定是否切换。
2.10启动mycat并连接服务器实现读写分离
①启动mycat
./mycat start
②新开窗口启动mycat数据库(8066为写读,9066只可以读部分)
mysql -u root -p -h 192.168.119.131 -P 8066;
2.11 mycat测试读写分离主备切换
①Master1服务器插入数据使服务器对应数据库都不一样
insert into student values(‘3’,@@hostname);
②mycat中查询会发现会读到master2,slave1,slave2数据:
select * from student;
③当Master1主机mysql服务关闭,系统则自动切换到Master2,插入一条语句
insert into student values(‘4’,@@hostname);