【Centos7系列】主从数据库及读写分离

Iceboundnana 发布于 2023-06-01 998 次阅读

数据库主从

基础配置

使用云平台创建两台虚拟机分别为mysql1和mysql2，实例类型使用1核/2G内存/20G硬盘

使用CRT远程连接工具连接两台云主机

双节点配置hosts文件

[root@mysql1 ~]# cat >> /etc/hosts <<EOF
192.168.200.8 mysql1
192.168.200.4 mysql2
EOF

双节点分别上传mariad-repo.tar.gz的压缩包，并解压至/opt目录

[root@mysql1 ~]# tar -zxvf mariadb-repo.tar.gz -C /opt/

配置本地yum源并安装mariadb服务

[root@mysql1 ~]# cat > /etc/yum.repos.d/local.repo <<EOF
[mariadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0
enabled=1
EOF
[root@mysql1 ~]# yum -y install mariadb mariadb-server

开启相应服务并初始化

[root@mysql1 ~]# systemctl start mariadb && systemctl enable mariadb
[root@mysql1 ~]# mysql_secure_installation

配置mysql1

[root@mysql1 ~]# vi /etc/my.cnf.d/server.cnf
在[mysqld]中添加如下内容
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 100
[root@mysql1 ~]# systemctl restart mariadb

使用mysql1数据库赋权

MariaDB [(none)]> grant all privileges on . to 'root'@'%' identified by '000000';
MariaDB [(none)]> grant replication slave on . to 'user'@'mysql2' identified by '000000';

配置mysql2

修改配置文件/etc/my.cnf.d/server.cnf，并重启服务

[root@mysql2 ~]# vi /etc/my.cnf.d/server.cnf
在[mysqld]中添加如下内容
[mysqld]
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = 200
[root@mysql2 ~]# systemctl restart mariadb

使用mysql2数据库开启slave

MariaDB [(none)]> change master to master_host='mysql1',master_user='user',master_password='000000';

开启slave并查看数据库slave状态

MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: mysql1
Master_User: user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1084
Relay_Log_File: mysql2-relay-bin.000002
Relay_Log_Pos: 1383
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: 1084
Relay_Log_Space: 1693
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: 100
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)

mysql1数据库建立数据

在mysql数据库中创建库test，并在此库中创建表company并插入表数据

###创建库
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use test;
Database changed
###创建表
MariaDB [test]> create table company(id int null primary key,name varchar(50),addr varchar(255));
Query OK, 0 rows affected (0.011 sec)
###插入数据
MariaDB [test]> insert into company values(1,"alibaba","china");
Query OK, 1 row affected (0.002 sec)
###查询表数据
MariaDB [test]> select * from company;
+----+---------+-------+
| id | name | addr |
+----+---------+-------+
| 1 | alibaba | china |
+----+---------+-------+
1 row in set (0.001 sec)

部署数据库读写分离

基础配置

使用云平台创建两台虚拟机分别为mycat，实例类型使用1核/2G内存/20G硬盘

使用CRT远程连接工具连接两台云主机

上传mariad-repo.tar.gz的压缩包，并解压至/opt目录

[root@mycat ~]# tar -zxvf mariadb-repo.tar.gz -C /opt/

配置本地yum源

[root@mycat ~]# cat > /etc/yum.repos.d/local.repo <<EOF
[mariadb]
name=mariadb
baseurl=file:///opt/mariadb-repo
gpgcheck=0
enabled=1
EOF

安装Java JDK环境

[root@mycat ~]# yum install -y java-1.8.0-openjdk java-1.8.0-openjdk-devel

部署Mycat读写分离中间件服务

将软件包Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz上传至虚拟机的/root目录下，并将软件包解压到/use/local目录中

[root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/

赋予解压后的Mycat目录权限

[root@mycat ~]# chmod -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 ~]# cat /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>
IP需要修改实际的IP地址

代码说明

  • sqlMaxLimit：配置默认查询数量
  • database：为真实数据库名
  • balance=“0”：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上
  • balance=“1”：全部的readHost与stand by writeHost参与select语句的负载均衡，简单来说，当双主双从模式（M1->S1，M2->S2，并且M1与M2互为主备），正常情况下，M2、S1、S2都参与select语句的负载均衡
  • balance=“2”：所有读操作都随机的在writeHost、readhost上分发
  • balance=“3”：所有读请求随机地分发到wiriterHost对应的readhost执行，writerHost不负担读压力，注意balance=3只在1.4及其以后版本有，1.3版本没有
  • writeType=“0”：所有写操作发送到配置的第一个writeHost，第一个挂了需要切换到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件dnindex.properties中
  • writeType=“1”：所有写操作都随机的发送到配置的writeHost

Official


    <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- mycat_testdb是mycat的虚拟数据库名称，链接需要用的 -->
    <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <!-- database 是MySQL数据库的库名 -->
    <dataNode name="dn1" dataHost="localhost1" database="test" />
    <!--
    dataNode节点中各属性说明：
    name：指定逻辑数据节点名称；
    dataHost：指定逻辑数据节点物理主机节点名称；
    database：指定物理主机节点上。如果一个节点上有多个库，可使用表达式db$0-99，     表示指定0-99这100个数据库；
    dataHost 节点中各属性说明：
        name：物理主机节点名称；
        maxCon：指定物理主机服务最大支持1000个连接；
        minCon：指定物理主机服务最小保持10个连接；
        writeType：指定写入类型；
            0，只在writeHost节点写入；
            1，在所有节点都写入。慎重开启，多节点写入顺序为默认写入根据配置顺序，第一个挂掉切换另一个；
        dbType：指定数据库类型；
        dbDriver：指定数据库驱动；
        balance：指定物理主机服务的负载模式。
            0，不开启读写分离机制；
            1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡；
            2，所有的readHost与writeHost都参与select语句的负载均衡，也就是说，当系统的写操作压力不大的情况下，所有主机都可以承担负载均衡；
-->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- 可以配置多个主从 -->
        <writeHost host="hostM1" url="192.168.212.200:3306" user="root" password="root">
            <!-- 可以配置多个从库 -->
            <readHost host="hostS2" url="192.168.212.203:3306" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

修改配置文件权限

[root@mycat ~]# chown root:root /usr/local/mycat/conf/schema.xml

编辑mycat的访问用户

注意代码严格遵守缩进

###修改/usr/local/mycat/conf/目录下的server.xml文件，修改root用户的访问密码与数据库，密码设置为000000，访问Mycat的逻辑库为USERDB
[root@mycat ~]# vi /usr/local/mycat/conf/server.xml
###修改
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
###内容如下
        <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

查询端口

###使用netstat -ntpl命令查看虚拟机端口开放情况，如果有开放8066和9066端口，则表示Mycat服务开启成功

[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 127.0.0.1:32000 0.0.0.0:* LISTEN 12106/java
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 513/rpcbind
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1077/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 949/master
tcp6 0 0 :::1984 :::* LISTEN 12106/java
tcp6 0 0 :::8066 :::* LISTEN 12106/java
tcp6 0 0 :::41734 :::* LISTEN 12106/java
tcp6 0 0 :::9066 :::* LISTEN 12106/java
tcp6 0 0 :::40395 :::* LISTEN 12106/java
tcp6 0 0 :::111 :::* LISTEN 513/rpcbind
tcp6 0 0 :::22 :::* LISTEN 1077/sshd
tcp6 0 0 ::1:25 :::* LISTEN 949/master

验证数据库集群服务读写分离功能

在mycat主机安装mariadb-client服务

[root@mycat ~]# yum install -y MariaDB-client

在mycat主机登录数据库并查看Mycat服务的逻辑库USERDB

[root@mycat ~]# mysql -h127.0.0.1 -P8066 -uroot -p000000
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| USERDB |
+----------+
1 row in set (0.005 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.012 sec)

用Mycat服务添加表数据

MySQL [USERDB]> insert into company values(2,"basteball","usa");
Query OK, 1 row affected (0.004 sec)

验证Mycat服务对数据库读写操作分离

[root@mycat ~]# mysql -h127.0.0.1 -P9066 -uroot -p000000 -e'show @@datasource;'
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.200.8 | 3306 | W | 0 | 10 | 1000 | 63 | 0 | 2 |
| dn1 | hostS1 | mysql | 192.168.200.4 | 3306 | R | 0 | 5 | 1000 | 59 | 3 | 0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
此作者没有提供个人介绍。
最后更新于 2023-06-01