mysql 写入性能 qps mysql单机写入性能

转载

mob64ca140a59b0 2024-04-20 21:31:25

文章标签 mysql 写入性能 qps mysql 服务器 mysql读写分离运维 文章分类 MySQL 数据库

在数据库集群架构中，主数据库负责处理事务性查询（写入操作），而从数据库只负责处理select查询（读操作），这样可以提高数据库整体读写性能。主数据库另外一个功能就是负责将数据变更同步到从数据库中。

读写分离的作用

1）分摊服务器压力，提高机器的系统处理效率。

2）当写入操作不变时，就可以大大分摊读取操作的压力，提高系统性能。另外，当读取操作的压力被分摊后，又间接提高了写入的性能。因此，数据库整体读写性能提高了。

3）增加冗余，提高服务可用性。当一台数据库服务器宕机后，可以调整另外一台从数据库以最快速度恢复服务。

准备工作

第一步，需要3台以上装有CentOS7.7版本以上的64位系统的服务器。

第二步，在2台服务器（单主单从）即公网IP分别为x.x.x.x和y.y.y.y的服务器上配置相同的环境。都进行源码安装mysql-5.6.40。

Mysql数据库主从复制

第一步，主从复制的基本原理。

mysql复制过程分成三步：

1）master将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件（binary log events）。

2）slave将master的binary log events拷贝到它的中继日志（relay log）。

3）slave重做中继日志中的事件，将改变应用到自己的数据库中。

第二步，主从复制的基本原则。

1）每个slave只有一个master；

2）每个slave只能有一个唯一的服务器ID；

3）每个master可以有多个slave。

第三步，配置。

主从服务器上安装的mysql，版本要求一致且后台以服务运行。

主从服务器上安装的mysql都配置在[mysqld]结点下，都是小写。

第四步，主服务器修改my.cnf配置文件。

对于公网IP为x.x.x.x的服务器，在my.cnf文件中添加下面的代码：

#主服务器唯一Id[必填]

server-id=1

#启用二进制日志[必填]

log-bin=/data/mysql/data/mysqlbin

#binlog保留时间7天

expire_logs_days=7

#主机，读写都可以

read-only=0

#忽略同步的数据库

replicate-ignore-db=mysql,information_schema,performance_schema

#设置需要复制的数据库[可选]

binlog-do-db=test

#设置logbin格式

binlog-format=STATEMENT

第五步，从服务器修改my.cnf配置文件。

对于公网IP为y.y.y.y的服务器，在my.cnf文件中添加下面的代码：

#从服务器唯一Id

server-id=2

#启用二进制日志[必填]

log-bin=/data/mysql/data/mysqlbin

#只读，对拥有super权限的账号是不生效的

read_only = 1

第六步，配置完，主从服务器都重启mysql服务。

命令：service mysql restart

第七步，建立账户并授权。

1）在主机上建立账户并授权slave。

命令：mysql -uroot -p123456

# 创建用户

mysql > CREATE USER root@'%' IDENTIFIED BY '123456';

# 在公网IP为x.x.x.x的服务器授权slave并更新权限

mysql > GRANT REPLICATION SLAVE ON *.* TO root@'172.25.217.107' IDENTIFIED BY '123456';

mysql > flush privileges;

# 查询master的状态

mysql > show master status;

若结果显示下面的列表，则说明主服务器配置成功。

mysql 写入性能 qps mysql单机写入性能_mysql 写入性能 qps

2）从服务器上配置需要复制的主服务器。

命令：mysql -uroot -p123456

# 在公网IP为y.y.y.y的服务器上配置

mysql > CHANGE MASTER TO MASTER_HOST = '172.25.217.108',

MASTER_PORT = 3306,

MASTER_USER = 'root',

MASTER_PASSWORD = '123456',

MASTER_LOG_FILE = 'mysqlbin.000001',

MASTER_LOG_POS = 410;

若结果显示“ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first”，则需执行命令“mysql > STOP slave;”，然后执行CHANGE MASTER命令。

# 启动从服务器复制功能

mysql > start slave;

mysql > show slave status\G;

若结果显示“Slave_IO_Running: Yes Slave_SQL_Running: Yes”，则说明主从配置成功。

mysql 写入性能 qps mysql单机写入性能_运维_02

第八步，测试Mysql数据库主从复制。

1）先在公网IP为x.x.x.x的主服务器上创建需要复制的数据库test。

命令：mysql -uroot -p123456

mysql> show databases;

mysql> create database test;

mysql> show databases;

mysql 写入性能 qps mysql单机写入性能_mysql 写入性能 qps_03

2）接着创建需要复制的表格user，并在数据表中插入数据。

命令：mysql -uroot -p123456

mysql> use test;

mysql> show tables;

mysql> CREATE TABLE IF NOT EXISTS `user`(

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(30) NOT NULL,

`age` INT(3) NOT NULL,

PRIMARY KEY ( `id` )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show tables;

mysql 写入性能 qps mysql单机写入性能_mysql读写分离_04

mysql> insert into user set id='110',name='赵六',age='20';

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_运维_05

3）然后在从服务器上检查是否会得到与上面一样的结果。若得到与上面一样的结果，则说明Mysql数据库主从复制配置成功。

Mycat数据库中间件

1）Mycat是一个开源的数据库系统，但是并没有存储引擎。

2）Mycat是数据库中间件，就是介于客户端和服务器之间，进行数据处理与交互的中间服务。接到客户端的请求，通过判断后，转发到后端数据库。

3）Mycat在使用之前，需要先安装JAVA运行环境(JRE),由于Mycat中使用了JDK7中的一些特性，所以要求必须在JDK7以上的版本上运行。

部署mycat

部署在公网IP为z.z.z.z的服务器上

第一步，下载server-jre-8u202-linux-x64.tar.gz安装包。

登录网址：https://www.oracle.com/java/technologies/javase/javase8-archive-downloads.html下载server-jre-8u202-linux-x64.tar.gz安装包，然后使用WinSCP将安装包上传到/usr/local/src文件夹中。也可以使用wget命令下载。

命令：cd /usr/local/src

wget https://www.oracle.com/java/technologies/javase/javase8-archive-downloads.html/jre-8u202-linux-x64.tar.gz

第二步，解压server-jre-8u202-linux-x64.tar.gz安装包。

命令：cd /usr/local/src

tar -zxvf server-jre-8u202-linux-x64.tar.gz -C /usr/local

第三步，配置环境变量。

配置/etc/profile文件，在文件添加下面的代码：

export JAVA_HOME=/usr/local/jdk1.8.0_202

export JRE_HOME=$JAVA_HOME/jre

export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar

export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$PATH

第四步，测试server-jre是否配置成功。

# 刷新profile文件

命令：source /etc/profile

# 查看jdk版本

命令：java -version

若结果显示server-jre的版本，则说明server-jre配置成功。

第五步，下载Mycat-server-1.6.5.tar.gz安装包。

到地址：Index of /1.6.5/下载Mycat-server-1.6.5.tar.gz安装包。然后使用WinSCP将安装包上传到/usr/local/src文件夹中。也可以使用wget命令下载。

命令：cd /usr/local/src

wget http://dl.mycat.org.cn/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz

第六步，解压Mycat-server-1.6.5.tar.gz安装包。

命令：cd /usr/local/src

tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/

第七步，配置mycat。

Mycat-server-1.6.5中有两个配置文件，分别为：

# 定义用户以及系统相关变量，如端口等。

/usr/local/mycat/conf/server.xml

# 定义逻辑库，表、分片节点等。

/usr/local/mycat/conf/schema.xml

1）配置server.xml。

在/usr/local/mycat/conf/server.xml中，代码修改为：

<user name="root" defaultAccount="true">

        <property name="password">123456</property>

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

    </user>

    <user name="user">

        <property name="password">123456</property>

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

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

    </user>

</mycat:server>

2）配置schema.xml。

在/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="test" checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn1" ></schema>

    <dataNode name="dn1" dataHost="testhost" database="test" />

    <dataHost name="testhost" maxCon="1000" minCon="10" balance="3" writeType="0"         
             dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">

        <heartbeat>select user()</heartbeat>

        <writeHost host="hostM1" url="x.x.x.x:3306" user="root"  password="123456">

            <readHost host="hostS1" url="y.y.y.y:3306" user="root"  password="123456"/>

        </writeHost>

    </dataHost>

</mycat:schema>

注意：

1）schema节点中的“name”的值和server.xml文件中user节点中的“schemas”值一样。

2）dataNode节点中的“database”为需要复制的数据库名称。

3）dataHost节点中的“balance”和“switchType”。

balance指负载均衡类型，目前有4种取值：

balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

balance="1"：当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

balance="2"：所有读操作都随机的在writeHost、readhost上分发。

balance="3"：所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力。

switchType指切换的模式，目前也有4种取值：

switchType="-1"：不自动切换。

switchType="1"：默认值，表示自动切换。

switchType="2"：基于MySQL主从同步的状态决定是否切换。

switchType="3"：基于MySQL galary cluster的切换机制（适合集群）。

因此，一般balance设置balance="1"(双主双从)或balance="3"(单主单从)。switchType设置switchType="-1"。因为单纯的MySQL主从集群并不允许将数据读进从服务器中。

第八步，在主服务器数据库上给用户授权。

命令：mysql -uroot -p123456

mysql> grant all on *.* to root@'%' identified by '123456';

mysql> flush privileges;

第九步，在mycat的服务器上测试mycat用户登录。

1）安装并配置mysql-5.6.20。

注意：安装的mysql与/usr/local/mycat/conf/server.xml文件中要求的一致。

mysql 写入性能 qps mysql单机写入性能_mysql读写分离_06

2）远程登录主服务器数据库。

命令：mysql -uroot -p123456 -hx.x.x.x -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_运维_07

第十步，调整JVM。

在/usr/local/mycat/conf/wrapper.conf文件后面的“wrapper.ping.timeout=120”上面添加下面的代码：

wrapper.startup.timeout=300

第十一步，启动mycat服务。

命令：/usr/local/mycat/bin/mycat start

# 查看mycat服务是否启动

命令：jps

netstat -lntp | grep java

mysql 写入性能 qps mysql单机写入性能_运维_08

若mycat服务没有启动，则把/usr/local/mycat/conf/wrapper.conf中的“wrapper.java.additional.3=-XX:MaxPermSize=64M”注释掉，然后启动mycat服务，因为该代码限制了大小，导致mycat服务无法启动。

第十二步，测试mycat。

# 在主服务器连接mycat服务器或在mycat服务器上登录

命令：mysql -uroot -p123456 -hz.z.z.z -P8066

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_服务器_09

# 在mycat服务器上登录，查看数据源状态，配置了主从，可以切换数据源状态

命令：mysql -uroot -p123456 -hz.z.z.z -P9066

mysql> show @@datasource;

mysql 写入性能 qps mysql单机写入性能_mysql_10

mysql> switch @@datasource dn1:0;

注意：name是schema.xml文件中，dataNode节点中的name值。

index是schema.xml文件中，dataNode节点中的dataHost的writeHost的索引，按照从上往下的配置顺序，索引从0开始。

测试mysql读写分离

第一步，主服务器数据库宕机，MyCAT不会自动调度写请求至从服务器。在mycat服务器上连接从服务器，可以正常查询及写入数据。启动主服务器数据库服务，在mycat服务器上连接主服务器，主服务器数据库中不会自动添加刚刚从服务器上写入的数据。

# 主服务器数据库服务关闭

命令：service mysql stop

# 在mycat服务器上登录，测试是否可以正常查询及写入数据

命令：mysql -uroot -p123456 -hz.z.z.z -P8066

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

若结果显示“ERROR 1184 (HY000): Connection refused”，则说明不可以正常查询及写入数据。

mysql 写入性能 qps mysql单机写入性能_服务器_11

# 在mycat服务器上连接从服务器，可以正常查询及写入数据

命令：mysql -uroot -p123456 -hy.y.y.y -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_运维_12

# 启动主服务器数据库服务，在mycat服务器上连接主服务器，主服务器数据库中不会自动添加刚刚从服务器上写入的数据

命令：service mysql start

mysql -uroot -p123456 -hx.x.x.x -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_服务器_13

第二步，主服务器数据库服务启动，关闭两台从服务器，MyCAT自动调度读请求至主服务器。在mycat服务器上连接主服务器，可以正常查询及写入数据。启动从服务器数据库服务，在mycat服务器上连接从服务器，从服务器数据库中会自动添加刚刚主服务器上写入的数据。

# 启动主服务器数据库服务

命令：service mysql start

# 从服务器数据库服务关闭

命令：service mysql stop

# 在mycat服务器上登录，测试是否可以正常查询及写入数据

命令：mysql -uroot -p123456 -hz.z.z.z -P8066

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> insert into user set id='120',name='吴起',age='25';

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_mysql 写入性能 qps_14

# 在mycat服务器上连接主服务器，可以正常查询及写入数据

命令：mysql -uroot -p123456 -hx.x.x.x -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> insert into user set id='100',name='张三',age='25';

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_运维_15

# 启动从服务器数据库服务，在mycat服务器上连接从服务器，从服务器数据库中会自动添加刚刚主服务器上写入的数据

命令：service mysql start

mysql -uroot -p123456 -hy.y.y.y -P3306

mysql> show databases;

mysql> use test;

mysql> show tables;

mysql> select * from user;

mysql 写入性能 qps mysql单机写入性能_运维_15

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：jquery 侧边栏收起 html侧边栏展开代码

下一篇：检查括号是否匹配java c程序括号匹配检查buaa

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯