HCIE云服务实验第五期-云数据库架构设计体验-RDS和数据库缓存及数据库中间件实践

1 环境准备

vpc准备

安全组配置

2 新增云服务器ECS-WEB01

安装httpd

yum install httpd -y

安装php

yum install -y php

安装相关组件

yum install -y php-pdo.x86_64 php-mysql

3 Mysql主从部署

新增ECS实例ECS-DB01、ECS-DB02

ECS-WEB01  保持一致 ，重装系统为Centos7.6

在ECS-DB01上部署mysql

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

rpm -ivh mysql-community-release-el7-5.noarch.rpm

yum install mysql-server  -y

在ECS-DB01上启动mysql

systemctl start mysqld

 

在ECS-DB01上修改mysql密码

mysql -u root

use mysql

update user set password=PASSWORD("Huawei@123!") where User='root';

确认ECS-DB01的mysql状态

netstat -nutple|grep 3306

配置ECS-DB01为主库

备份mysql配置文件：

mv /etc/my.cnf /etc/my.cnf.bak

新增新的my.cnf配置文件：

cat << EOF >> /etc/my.cnf

[mysqld]

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

lower_case_table_names = 1

innodb_strict_mode = 1

sql_mode =

symbolic-links=0

character_set_server = utf8

log-bin = mysql-bin

binlog_format=row

server-id = 2

expire_logs_days = 10

slave_skip_errors = 1062

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

EOF

通过以下命令确认mysql配置文件：

cat /etc/my.cnf

重启mysqld:

systemctl restart mysqld

 

 

在ECS-DB01上设置同步账户

mysql -pHuawei@123!

create user 'copy'@'172.16.%' identified by 'huawei@123';

在mysql>界面输入以下命令为同步账户授予复制权限：

grant replication slave on *.* to 'copy'@'172.16.%' identified by 'huawei@123';

在mysql>界面输入以下命令为root账户授予vpc01访问呢权限：

grant all privileges on *.* to 'root'@'172.16.%' identified by 'Huawei@123!';

在mysql>界面输入以下命令使配置生效：

flush privileges；

记录binlog信息

确认主库binlog的File值和Position值，并记录，用于后续配置从库同步：

show master status;

在ECS-DB02上部署mysql

安装同ECS-DB01，此处略

确认ECS-DB02的mysql状态

为ECS-DB02的root设置访问权限

在mysql>界面输入以下命令为root用户授予vpc01访问权限：

grant all privileges on *.* to 'root'@'172.16.%' identified by 'Huawei@123!';

设置ECS-DB02为从库

登录ECS-DB02，通过以下命令备份mysql配置文件：

mv /etc/my.cnf /etc/my.cnf.bak

 

通过复制以下整段命令执行配置从库的配置文件：

cat << EOF >> /etc/my.cnf

[mysqld]

join_buffer_size = 128M

sort_buffer_size = 2M

read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

sql_mode =

lower_case_table_names = 1

innodb_strict_mode = 1

log-bin = mysql-bin

binlog_format=row

character_set_server = utf8

server-id = 3

read-only = 1

expire_logs_days = 10

slave_skip_errors = 1062

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

EOF

 

确认配置文件

通过以下命令确认文件：

cat /etc/my.cnf

重启ECS-DB02的mysql

通过以下命令重启mysqld：

systemctl restart mysqld

记录ECS-DB01的内网IP地址

设置ECS-DB02为从库

mysql -pHuawei@123!

输入以下命令设置从库：

CHANGE MASTER TO MASTER_HOST='172.16.0.15', MASTER_USER='copy', MASTER_PASSWORD='huawei@123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=773;

开启主从同步

在mysql>界面

start slave;

查看主从同步状态

在mysql>界面输入以下命令查看同步状态，查看Slave_IO_Running和Slave_SQL_Running的状态。

show slave status \G;

在ECS-DB01的mysql中插入数据表

mysql -pHuawei@123!

通过以下命令新建库：

create database demo;

通过以下命令创建数据表：

use demo;

CREATE TABLE IF NOT EXISTS `users` (`id` INT UNSIGNED AUTO_INCREMENT, `firstname` VARCHAR(100) NOT NULL, `lastname` VARCHAR(100) NOT NULL,  PRIMARY KEY(`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过以下命令插入数据：

insert into users values (1, 'Kelton', 'Hoover');

insert into users values (2, 'Siena', 'Alvarado');

insert into users values (3, 'Zayden', 'Bennett');

insert into users values (4, 'Kinley', 'Carter');

insert into users values (5, 'Athena', 'Wallace');

insert into users values (6, 'Gavin', 'Duran');

insert into users values (7, 'Alyssa', 'Oneill');

insert into users values (8, 'Kylan', 'Branch');

insert into users values (9, 'Nigel', 'Rodriguez');

insert into users values (10, 'Mackenzie', 'Watson');

insert into users values (11, 'Cara', 'Richard');

insert into users values (12, 'Madyson', 'Macdonald');

insert into users values (13, 'Jordin', 'Stanton');

insert into users values (14, 'Sidney', 'Dean');

insert into users values (15, 'Lainey', 'Skinner');

insert into users values (16, 'Adalyn', 'Sellers');

insert into users values (17, 'Alisson', 'Blake');

insert into users values (18, 'Jamarcus', 'Clarke');

insert into users values (19, 'Yoselin', 'Yoder');

insert into users values (20, 'Holden', 'Bridges');

登录ECS-DB02的mysql，查看同步是否生效

在ECSDB02上通过以下命令查看同步信息

mysql -pHuawei@123!

然后通过命令show databases;查看公告新建的库是否已经正常同步：

使用demo库：

use demo;

 

查看表数据：

select * from users;

4 部署mycat

登录ECS-mycat01，通过以下命令下载mycat，并安装到目录/usr/local：

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

tar -xvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

mv mycat /usr/local/ -f

安装java

yum install -y java-1.8.0-openjdk

修改mycat数据库配置文件

cd /usr/local/mycat/conf

mv schema.xml schema.xml.bak

masterdb=主库地址（ECS-DB01的内网ip地址）172.16.0.15

slavedb=从库地址（ECS-DB02的内网ip地址）172.16.0.214

 

 

cat << EOF >> schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

 

        <schema name="copydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="copynode"></schema>

 

        <dataNode name="copynode" dataHost="dthost" database="demo" />

        <dataHost name="dthost" maxCon="1000" minCon="10" balance="1"

        writeType="0" dbType="mysql" dbDriver="native">

                <heartbeat>select user()</heartbeat>

                <!-- can have multi write hosts -->

                <writeHost host="hostmaster" url="172.16.0.15:3306" user="root" password="Huawei@123!">

                <!-- can have multi read hosts -->

                        <readHost host="hostslave" url="172.16.0.214:3306" user="root" password="Huawei@123!"

                weight="1" />

                </writeHost>

        </dataHost>

</mycat:schema>

EOF

确认已经修改的配置文件：

cat schema.xml

修改mycat服务配置文件

通过以下命令设置mycat配置文件server.xml：

cd /usr/local/mycat/conf

mv server.xml server.xml.bak

cat << EOF >> server.xml

<?xml version="1.0" encoding="UTF-8"?>

<!-- - - Licensed under the Apache License, Version 2.0 (the "License");

        - you may not use this file except in compliance with the License. - You

        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0

        - - Unless required by applicable law or agreed to in writing, software -

        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT

        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the

        License for the specific language governing permissions and - limitations

        under the License. -->

<!DOCTYPE mycat:server SYSTEM "server.dtd">

<mycat:server xmlns:mycat="http://io.mycat/">

        <system>

        <property name="useSqlStat">0</property>

        <property name="useGlobleTableCheck">0</property>

        <property name="serverPort">3310</property>

        <property name="manageport">9066</property>

 

                <property name="sequnceHandlerType">2</property>

                <property name="processorBufferPoolType">0</property>

                <property name="frontWriteQueueSize">4096</property>

                <property name="processors">32</property>

                <property name="handleDistributedTransactions">0</property>

                <property name="useOffHeapForMerge">1</property>

                <property name="memoryPageSize">1m</property>

                <property name="spillsFileBufferSize">1k</property>

                <property name="useStreamOutput">0</property>

                <property name="systemReserveMemorySize">384m</property>

                <property name="useZKSwitch">true</property>

        </system>

 

        <user name="root">

                <property name="password">Huawei@123!</property>

                <property name="schemas">copydb</property>

        </user>

 

        <user name="user">

                <property name="password">Huawei@123!</property>

                <property name="schemas">copydb</property>

                <property name="readOnly">true</property>

        </user>

</mycat:server>

EOF

确认mycat服务的配置文件

cat server.xml

启动mycat

cd /usr/local/mycat/bin

./mycat start

通过以下命令查看mycat的状态：

netstat -nutple|grep 3310

验证mycat

 

登录ECS-mycat01虚机，使用mysql命令通过3310端口登录数据库

mysql -h127.0.0.1 -uroot -P3310 -pHuawei@123!

安装msql客户端

show databases;

use copydb;

select * from users;

5 部署php代码

 

登录ECS-WEB01虚机：

cd /var/www/html/

mycatip="172.16.0.236"

 

 

cat << EOF >>index.php

<!DOCTYPE html>

<html>

<body>

 

<?php

echo "<table style='border: solid 1px black;'>";

 echo "<tr><th>Id</th><th>Firstname</th><th>Lastname</th></tr>";

 

class TableRows extends RecursiveIteratorIterator {

    function __construct(\$it) {

        parent::__construct(\$it, self::LEAVES_ONLY);

    }

 

    function current() {

        return "<td style='width: 150px; border: 1px solid black;'>" . parent::current(). "</td>";

    }

 

    function beginChildren() {

        echo "<tr>";

    }

 

    function endChildren() {

        echo "</tr>" . "\n";

    }

}

 

\$servername = "$mycatip";

\$username = "root";

\$password = "Huawei@123!";

\$dbname = "copydb";

\$dbport = 3310;

 

try {

    \$conn = new PDO("mysql:host=\$servername;port=\$dbport;dbname=\$dbname", \$username, \$password);

    \$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    \$stmt = \$conn->prepare("SELECT id, firstname, lastname FROM users");

    \$stmt->execute();

 

    // set the resulting array to associative

    \$result = \$stmt->setFetchMode(PDO::FETCH_ASSOC);

 

    foreach(new TableRows(new RecursiveArrayIterator(\$stmt->fetchAll())) as \$k=>\$v) {

        echo \$v;

    }

}

catch(PDOException \$e) {

    echo "Error: " . \$e->getMessage();

}

\$conn = null;

echo "</table>";

?>

 

</body>

</html>

EOF

启动httpd服务

使用以下命令开启httpd：

systemctl start httpd

验证web服务是否能正常访问

打开浏览器，输入ECS-WEB01的外网IP查看是否能显示名字列表：

6 迁移到RDS数据库

创建迁移任务

修改rds参数

重新校验

参数确认启动迁移

登录rds验证迁移结果

打开RDS管理列表，点击“登录

在RDS新增列

点击“新增”，并手动插入一条数据

配置只读数据库

等待只读库创建完毕

myscat后端数据库迁移到RDS

登录mycat虚机，修改配置文件，确认主库、从库、RDS的内网ip地址（将红色替换为实际值）

cd /usr/local/mycat/conf

sed -i "s/172.16.0.15/172.16.0.232/g" schema.xml

sed -i "s/172.16.0.214/172.16.0.90/g" schema.xml

重启mycat

cd /usr/local/mycat/bin

./mycat restart

验证web是否正常

在浏览器输入ECS-WEB01的外网IP，查看新插入的数据是否可以正常显示