| IP | 主机名 | 节点 |
|---|---|---|
| 192.168.100.10 | mycat | Mycat中间件服务节点 |
| 192.168.100.20 | db1 | MariaDB数据库集群主节点 |
| 192.168.100.30 | db2 | MariaDB数据库集群从节点 |
基础环境配置
修改主机名
#使用hostnamectl命令修改3台主机的主机名。[root@server ~]# hostnamectl set-hostname db1[root@server ~]# su[root@db1 ~]#[root@server ~]# hostnamectl set-hostname db2[root@server ~]# su[root@db2 ~]#[root@server ~]# hostnamectl set-hostname mycat[root@server ~]# su[root@mycat ~]#
编辑hosts文件
[root@db1 ~]# vim /etc/hosts
[root@db2 ~]# vim /etc/hosts
[root@mycat ~]# vim /etc/hosts
#添加☟
192.168.100.10 mycat
192.168.100.20 db1
192.168.100.30 db2
安装JDK环境
#mycat节点安装jdk
[root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel
[root@mycat ~]# java -version
openjdk version "1.8.0_302"
OpenJDK Runtime Environment (build 1.8.0_302-b08)
OpenJDK 64-Bit Server VM (build 25.302-b08, mixed mode)
部署MariaDB主从数据库集群服务
安装MariaDB服务
#在db1和db2上安装MariaDB
# yum install -y mariadb mariadb-server
# systemctl start mariadb
# systemctl enable mariadb
# 初始化数据库
# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n]
New password:
Re-enter new password:
Sorry, you can't use an empty password here.
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n]
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
配置数据库集群主节点
#db1和db2都改
[root@db1 ~]# vim /etc/my.cnf
[mysqld]
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 20 //IP后俩位
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#重启
[root@db1 ~]# systemctl restart mariadb
开放主节点的数据库权限
[root@db1 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB 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 "123456";
Query OK, 0 rows affected (0.00 sec)
#创建一个user用户让db2访问
MariaDB [(none)]> grant replication slave on *.* to 'user'@'db2' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
配置从节点db2同步主节点db1
[root@db2 ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 5.5.68-MariaDB 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='user',master_password='123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: db1
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 527
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 811
Relay_Master_Log_File: mysql-bin.000001
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: 527
Relay_Log_Space: 1107
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: 20
1 row in set (0.00 sec)
ERROR: No query specified
验证主从数据库的同步功能
#在主节点db1的数据库中创建库test，并在库test中创建表company，插入表数据。创建完成后，查看表company数据
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use test
Database changed
MariaDB [test]> create table company(id int not null primary key,name varchar(50),addr varchar(255));
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> insert into company values(1,"facebook","usa");
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from company;
+----+----------+------+
| id | name | addr |
+----+----------+------+
| 1 | facebook | usa |
+----+----------+------+
1 row in set (0.00 sec)
#此时从db2查看，可以查看到刚刚的创建信息
部署Mycat读写分离中间件服务
安装Mycat服务
在mycat上传 Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# ls
anaconda-ks.cfg Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# mv mycat/ /usr/local/
#赋予解压后的Mycat目录权限
[root@mycat ~]# chown -R 777 /usr/local/mycat/
# 在/etc/profile系统变量文件中添加Mycat服务的系统变量，并生效变量
[root@mycat ~]# echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile
[root@mycat ~]# source /etc/profile
编辑Mycat的逻辑库配置文件
#直接换成以下
[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/">
<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" wri
teType="0" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.100.20:3306" user="root" password="123456">
<readHost host="hostS1" url="192.168.100.30:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
#修改schema.xml的用户权限
[root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml
编辑mycat的访问用户
[root@mycat ~]# vi /usr/local/mycat/conf/server.xml //文件最后
<user name="root">
<property name="password">123456</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
Starting Mycat-server...
[root@mycat ~]# netstat -ntpl
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 941/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1098/master
tcp 0 0 127.0.0.1:32000 0.0.0.0:* LISTEN 1897/java
tcp6 0 0 :::22 :::* LISTEN 941/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1098/master
tcp6 0 0 :::1984 :::* LISTEN 1897/java
tcp6 0 0 :::8066 :::* LISTEN 1897/java
tcp6 0 0 :::45799 :::* LISTEN 1897/java
tcp6 0 0 :::9066 :::* LISTEN 1897/java
tcp6 0 0 :::45677 :::* LISTEN 1897/java
验证数据库集群服务读写分离功能
配置yum
mv /etc/yum.repos.d/* /media/
#3台集群虚拟机的Yum安装源文件配置部分：
vim /etc/yum.repos.d/local.repo
[mariadb]
name=mariadb
baseurl=file:///opt/gpmall-repo
gpgcheck=0
enabled=1
root@mycat ~]# cd /opt
[root@mycat opt]# ls
[root@mycat opt]# cd
[root@mycat ~]# ls
anaconda-ks.cfg gpmall-repo Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat ~]# mv gpmall-repo/ /opt/
用Mycat服务查询数据库信息
[root@mycat ~]# yum install -y MariaDB-client
[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
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 |
+----------+
| USERDB |
+----------+
1 row in set (0.002 sec)
MySQL [(none)]> use USERDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [USERDB]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| company |
+----------------+
1 row in set (0.006 sec)
MySQL [USERDB]> select * from company;
+----+----------+------+
| id | name | addr |
+----+----------+------+
| 1 | facebook | usa |
+----+----------+------+
1 row in set (0.054 sec)
用Mycat服务添加表数据
MySQL [USERDB]> select * from company;
+----+----------+------+
| id | name | addr |
+----+----------+------+
| 1 | facebook | usa |
+----+----------+------+
1 row in set (0.054 sec)
MySQL [USERDB]> insert into company values(2,"bastetball","usa");
Query OK, 1 row affected (0.006 sec)
MySQL [USERDB]> select * from company;
+----+------------+------+
| id | name | addr |
+----+------------+------+
| 1 | facebook | usa |
| 2 | bastetball | usa |
+----+------------+------+
2 rows in set (0.002 sec)
验证Mycat服务对数据库读写操作分离
[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p123456 -e 'show @@datasource;'
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.100.20 | 3306 | W | 0 | 6 | 1000 | 147 | 0 | 1 |
| dn1 | hostS1 | mysql | 192.168.100.30 | 3306 | R | 0 | 6 | 1000 | 146 | 4 | 0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
若有收获，就点个赞吧
0 人点赞
