使用提供的虚拟机与软件包，基于上一题构建的主从数据库，进一步完成Mycat读写分离数据库的配置安装。需要用的配置文件schema.xml文件如下所示（server.xml文件不再给出）： select user() 配置读写分离数据库完毕后，使用netstat -ntpl命令查询端口启动情况。最后将netstat -ntpl命令的返回结果以文本形式提交到答题框。
YUM源配置
#mycat
#将镜像挂载到/mnt下 [root@mycat ~]# mount CentOS-7-x86_64-DVD-1511.iso /mnt/ #在/opt目录下创建centos文件夹 [root@mycat ~]# mkdir /opt/centos/ #将挂载文件内容拷贝到/opt/centos文件夹下 [root@mycat ~]# cp -rf /mnt/* /opt/centos/ #取消挂载 [root@mycat ~]# umount /mnt/ #将root目录下的gpmall-rep移动到/opt目录下 [root@mycat ~]# mv /root/gpmall-repo /opt/ #查看/opt目录下的两个文件内容 [root@mycat ~]# ls /opt/ centos gpmall-repo [root@mycat ~]# cat /etc/yum.repos.d/local.repo [centos] name=centos baseurl=file:///opt/centos gpgcheck=0 [mall] name=mall baseurl=file:///opt/gpmall-repo gpgcheck=0 [root@mycat ~]# yum repolist
安装vsftpdyum install -y vsftpd ···· [root@zookeeper1 ~]# vim /etc/vsftpd/vsftpd.conf 第一行添加 anon_root=/opt 重启vsftpd systemctl restart vsftpd #重启 systemctl enable vsftpd #设置开机自启db1
[root@db1 ~]# cat /etc/yum.repos.d/local.repo [centos] name=centos baseurl=ftp://192.168.200.11/centos gpgcheck=0 [mall] name=mall baseurl=ftp://192.168.200.11/gpmall-repo gpgcheck=0 [root@db1 ~]# yum repolistdb2
[root@db2 ~]# cat /etc/yum.repos.d/local.repo [centos] name=centos baseurl=ftp://192.168.200.11/centos gpgcheck=0 [mall] name=mall baseurl=ftp://192.168.200.11/gpmall-repo gpgcheck=0 [root@db2 ~]# yum repolist
关闭防火墙（三节点）
systemctl stop firewalld #关闭防火墙
systemctl disable firewalld #开机禁用
#关闭selinux
[root@zookeeper1 ~]# cat /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
#临时关闭
setenforce 0
hosts解析（三节点）
# 注释：这个其实配不配都可以，看个人喜欢用主机名还是IP地址咯
[root@mycat ~]# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.200.11 mycat
192.168.200.12 db1
192.168.200.13 db2
db1& db2
# 装数据库
[root@db1 ~]# yum install -y mariadb mariadb-server
# 启动数据库，设置开机自启
[root@db1# systemctl start mariadb
[root@db1# systemctl enable mariadb
#初始化数据库服务，设置完密码（000000）一路回车就行了
db1
配置db1的 my.cnf 配置文件。
[root@db1 ~]# vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[mysqld]
log_bin=mysql-bin
binlog_ignore_db=mysql
server_id=12
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
- log_bin=mysql-bin：开启bin-log，并指定文件目录和文件名前缀。
- binlog_ignore_db=mysql：不同步mysql系统数据库。如果是多个不同步库，就以此格式另写几行；也可以在一行，中间逗号隔开。
- server_id=12：数据库唯一ID，主从的标识号绝对不能重复
重启服务
[root@db1 ~]# systemctl restart mariadb
配置主从数据库，并创建测试数据库gpmall
[root@db1 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.18-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant all privileges on *.* to root@'%' identified by '000000';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> grant replication slave on *.* to 'root'@'db2' identified by '000000';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> create database gpmall;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> exit
Bye
db2
配置db2的 my.cnf 配置文件。
[root@db2 ~]# vim /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
[mysqld]
log_bin=mysql-bin
binlog_ignore_db=mysql
server_id=13
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
重启服务
[root@db2 ~]# systemctl restart mariadb
配置主从数据库，并验证是否成功，看到 Slave_IO_Running: Yes，Slave_SQL_Running: Yes 即表示无问题（能看到db1节点创建的 gpmall 数据库，也说明没问题）。
[root@db2 ~]# mysql -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.18-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> change master to master_host='db1',master_user='root',master_password='000000';
Query OK, 0 rows affected (0.101 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1008
Relay_Log_File: xnode3-relay-bin.000004
Relay_Log_Pos: 1307
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1008
Relay_Log_Space: 1988
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Slave_DDL_Groups: 4
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
|
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaB [(none)]> exit
Bye
Mycat：
[root@mycat ~]# yum install -y vim net-tools tree
[root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
[root@mycat ~]# tar -zvxf Mycat-server-1.6-RELEASE-20161028204710-linux.gz -C /usr/local/
[root@mycat ~]# chown -R 777 /usr/local/mycat/
[root@mycat ~]# vim /etc/profile
export MYCAT_HOME=/usr/local/mycat/
[root@mycat ~]# source /etc/profile
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
<?xml version='1.0'?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--注释：name=gpmall指的是逻辑数据库，在后面添加一个dataNode="dn1"，dn1上绑定的是真是数据库-->
<schema name="gpmall" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
<!--注释：name="dn1"上面与逻辑数据库引用的名称，database="gpmall"真实数据库名字-->
<dataNode name="dn1" dataHost="localhost1" database="gpmall" />
<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="db1:3306" user="root" password="000000">
<readHost host="hostS1" url="db2:3306" user="root" password="000000" />
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml
# 注释：修改root用户的访问密码与数据库
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
<user name="root">
<property name="password">000000</property>
<property name="schemas">gpmall</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="user"></user>的标签与内容
[root@mycat ~]# /bin/bash /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1114/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1992/master
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 3988/java
tcp6 0 0 :::45929 :::* LISTEN 3988/java
tcp6 0 0 :::9066 :::* LISTEN 3988/java
tcp6 0 0 :::40619 :::* LISTEN 3988/java
tcp6 0 0 :::22 :::* LISTEN 1114/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1992/master
tcp6 0 0 :::1984 :::* LISTEN 3988/java
tcp6 0 0 :::8066 :::* LISTEN 3988/java
# 注释：验证结果（读写分离是否成功）：
[root@mycat ~]# yum install -y MariaDB-client
# 注释：查看逻辑库
[root@mycat ~]# mysql -h 127.0.0.1 -P8066 -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| gpmall |
+----------+
1 row in set (0.002 sec)
MySQL [(none)]>
# 注释：查询对数据库读写操作的分离信息
[root@mycat ~]# mysql -h 127.0.0.1 -P9066 -uroot -p000000
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show @@datasource;
+----------+--------+-------+------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | db1 | 3306 | W | 0 | 10 | 1000 | 241 | 0 | 0 |
| dn1 | hostS2 | mysql | db2 | 3306 | R | 0 | 8 | 1000 | 243 | 4 | 0 |
+----------+--------+-------+------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.001 sec)
MySQL [(none)]>
7679
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
