数据库 主从&读写分离

节点规划

1、基础环境搭建

### mysql1
# 修改主机名
[root@mysql1 ~]# hostnamectl set-hostname mysql1
# 配置解析文件
[root@mysql1 ~]# echo "10.35.172.77 mysql1
10.35.172.78 mysql2" >> /etc/hosts
# 关闭 selinux&防火墙
[root@mysql1 ~]# setenforce 0
[root@mysql1 ~]# systemctl stop firewalld
# SecureXF上传 mariad-repo.tar.gz && 解压到 /opt
[root@mysql1 ~]# tar -zxvf mariadb-repo.tar.gz -C /opt
# 配置yum源
[root@mysql1 ~]# mkdir /etc/yum.repo.d/bak
[root@mysql1 ~]# mv /etc/yum.repo.d/* /bak
[root@mysql1 ~]# echo "[mariadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0
enabled=1
[centos]
name=centos
baseurl=ftp://10.35.172.81/centos
gpgcheck=0
enabled=1" > /etc/yum.repos.d/local.repo 

### mysql2
[root@mysql2 ~]# hostnamectl set-hostname mysql2
[root@mysql2 ~]# echo "10.35.172.77 mysql1
10.35.172.78 mysql2" >> /etc/hosts
[root@mysql2 ~]# setenforce 0
[root@mysql2 ~]# systemctl stop firewalld
# SecureXF 上传 mariad-repo.tar.gz
[root@mysql1 ~]# tar -zxvf mariadb-repo.tar.gz -C /opt
[root@mysql2 ~]# mkdir /etc/yum.repo.d/bak
[root@mysql2 ~]# mv /etc/yum.repo.d/* /bak
[root@mysql2 ~]# echo "[mariadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0
enabled=1
[centos]
name=centos
baseurl=ftp://10.35.172.81/centos
gpgcheck=0
enabled=1" > /etc/yum.repos.d/local.repo 

### mycat
[root@mycat ~]# hostnamectl set-hostname mycat
[root@mycat ~]# setenforce 0
[root@mycat ~]# systemctl stop firewalld
[root@mycat ~]# mkdir /etc/yum.repo.d/bak
[root@mycat ~]# mv /etc/yum.repo.d/* /bak
# SecureXF 上传 mariad-repo.tar.gz
[root@mycat ~]# tar -zxvf mariadb-repo.tar.gz -C /opt
[root@mycat ~]# echo "[mariadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0
enabled=1
[centos]
name=centos
baseurl=ftp://10.35.172.81/centos
gpgcheck=0
enabled=1" > /etc/yum.repos.d/local.repo 

2、安装服务

# 安装 mariadb 并启动
[root@mysql1 ~]# yum install -y mariadb mariadb-server
[root@mysql1 ~]# systemctl start mariadb && systemctl enable mariadb

[root@mysql2 ~]# yum install -y mariadb mariadb-server
[root@mysql2 ~]# systemctl start mariadb && systemctl enable mariadb

# 初始化mariadb
[root@mysql1 ~]# mysql_secure_installation 
## 注意：Disallow root login remotely? [Y/n] n
##      其他为 yes ;密码设置为 000000

[root@mysql2 ~]# mysql_secure_installation 
## 注意：Disallow root login remotely? [Y/n] n
##      其他为 yes ;密码设置为 000000

# 安装JDK & 查看JDK版本
[root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
[root@mycat ~]# java -version

# 二进制安装 mycat
## SecureXF 上传 Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

# 配置环境变量
[root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile
[root@mycat ~]# source /etc/profile

3、配置服务

### 主从复制
# mysql1
[root@mysql1 ~]# vi/etc/my.cnf.d/server.cnf
# 在 [mysqld]标签下添加
log_bin = mysql-bin           #记录操作日志
binlog_ignore_db = mysql      #不同步mysql系统数据库
server_id = 77                #数据库集群中的每个节点id都要不同
# 重启数据库
[root@mysql1 ~]# systemctl restart mariadb
# 进入数据库并配置
[root@mysql1 ~]# mysql -uroot -p000000
MariaDB [(none)]> grant all privileges  on *.* to root@'%' identified by "000000";
MariaDB [(none)]> grant replication slave on *.* to 'user'@'mysql2' identified by '000000';

# mysql2
[root@mysql2 ~]# vi/etc/my.cnf.d/server.cnf
# 在 [mysqld]标签下添加
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 78
[root@mysql2 ~]# systemctl restart mariadb
[root@mysql2 ~]# mysql -uroot -p000000
MariaDB [(none)]> change master to master_host='mysql1',master_user='user',master_password='000000';

### 读写分离
# mycat
# schema.xml配置文件 ;将原内容替换为下面文字
[root@mycat ~]# vi /usr/local/mycat/conf/schema.xml 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema> 
<dataNode name="dn1" dataHost="localhost1" database="test" />  
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1"  slaveThreshold="100">  
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="172.30.11.12:3306" user="root" password="000000">
        <readHost host="hostS1" url="172.30.11.13:3306" user="root" password="000000" />
    </writeHost>
</dataHost>
</mycat:schema>
# 修改配置文件权限
[root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml
# 编辑mycat的访问用户
[root@mycat ~]# vi /usr/local/mycat/conf/server.xml 
# 在配置文件的最后部分，修改 password schemas
<user name="root">
        <property name="password">000000</property>
        <property name="schemas">USERDB</property
# 然后删除如下几行：
<user name="user">
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
</user>
# 启动Mycat服务
[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start

4、验证

### 主从复制
# mysql2
# 登录查询主从状态
[root@mysql2 ~]# mysql -uroot -p000000
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G

# mysql1 创建库test，并在库test中创建表company，插入表数据
[root@mysql1 ~]# mysql -uroot -p000000
MariaDB [(none)]> create database test;
MariaDB [(none)]> use test;
MariaDB [test]> create table company(id int not null primary key,name varchar(50),addr varchar(255));
MariaDB [test]> insert into company values(1,"alibaba","china");
MariaDB [test]> select * from company;

# mysql2 查看数据库列表。找到test数据库，查询表 验证从数据库的复制
[root@mysql2 ~]# mysql -uroot -p000000
MariaDB [(none)]> use test;
MariaDB [test]> show tables;
MariaDB [test]> select * from company;

### 读写分离
# 安装客户端工具
[root@mycat ~]# yum install -y MariaDB-client
# 使用mysql命令查看Mycat服务的逻辑库USERDB
[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p000000
MySQL [(none)]> show databases;
MySQL [(none)]> use USERDB
MySQL [USERDB]> show tables;
MySQL [USERDB]> select * from company;
# 使用mysql命令对表company添加一条数据
MySQL [USERDB]> insert into company values(2,"bastetball","usa");
MySQL [USERDB]> select * from company;

# 验证Mycat服务对数据库读写操作分离
[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p000000 -e 'show  @@datasource;'