项目中,如果数据量大的情况下,可以使用【数据库主从复制+读写分离】的方式优化,其他方式也很多,这里只记录下这种方式
一、说明
1.需要的环境等:
序号 | 环境 | 说明 |
---|---|---|
1 | mysql5.7服务器 | 两台数据库服务器,一台作为主数据库,一台作为从数据库 |
2 | jdk | 安装mycat的时候会用到jdk |
3 | mycat | windows/linux版本都可以,我测试用的是windows版本 |
4 | 数据库管理工具 | 我用的navicat,方便测试数据 |
二、搭建一主一从
1.安装mysql+jdk1.8
直接略过,具体安装方式,可以查看我的博客:
linux系统安装jdk1.8、mysql5.7、redis(压缩包版本教程)
2.主数据库配置
注意:数据库如果是windows系统安装的,需要在安装目录的my.ini
这个文件配置
修改配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf
vi /etc/mysql/mysql.conf.d/mysqld.cnf
具体配置如下:
# 主机配置-主机配置
# 主服务唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 设置需要复制的数据库(这里可以不设置)
binlog-do-db=需要复制的主数据库名字
# 设置 logbin 格式
binlog_format=STATEMENT
binlog日志三种格式
STATEMENT
ROW
MIXED
3.从数据库配置
注意:数据库如果是windows系统安装的,需要在安装目录的my.ini
这个文件配置
修改配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf
vi /etc/mysql/mysql.conf.d/mysqld.cnf
具体配置如下:
# 主机配置-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay
4.注意事项及其他配置
- 主机、从机配置好后需要重启MySQL 服务
# 查看启动状态
systemctl status mysql.service
# 停止MySQL服务
systemctl stop mysql.service
# 启动MySQL服务
systemctl start mysql.service
# 重启MySQL服务
systemctl restart mysql.service
- 主机、从机需要关闭防火墙
# 查看防火墙状态
systemctl status firewalld
# 关闭防火墙
systemctl stop firewalld
# 开启防火墙
systemctl start firewalld
- 在主机上建立账户并授权 slave
# 登录MySQL
mysql -uroot -p123456
# 切换数据库
use mysql;
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
#123456改成自己的数据库密码
- 登录主机,查询master的状态
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000001 | 430 | testdb | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
记录 File
和Position
的值。
**执行完次步骤后不要再操作主服务器MySQL,防止主服务器状态值发生变化**
- 登录从机,配置需要复制的主机,把上一步的两个记录值填写上
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_PORT=3306,
MASTER_USER='主数据库的用户名',
MASTER_PASSWORD='主数据库的密码',
MASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=154;
- 启动从服务器复制功能
start slave;
- 如果该服务配置过主从复制,则需要先进行重置
# 停止从服务复制功能
stop slave;
# 重新配置
reset master;
#查看从服务器状态
show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.146
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
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: 430
Relay_Log_Space: 523
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: 1
Master_UUID: 41b59ebe-ea5d-11ec-9dd9-000c2930ff90
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
Slave_IO_Running
Slave_SQL_Running
都为Yes
,则说明主从配置成功
5.验证
- 登录主数据库
mysql -uroot -p123456
#创建数据库
create database testdb;
#创建表
use testdb;
create table test_table(id int, name varchar(255));
#插入数据
insert into test_table values (1, 'Micromaple');
#查询数据
select * from test_table;
- 正常情况下:在主数据库创建一个数据库,从数据库就有一个相同的数据库,主数据库创建一张表,从数据库也有相同的一张表,主数据库增删改,从数据库也跟着增删改
6.安装mycat
6.1 下载地址:mycat下载地址
找一个windows-1.x版本的下载即可
下载完成后解压
6.2 配置windows环境变量
-
新增系统变量
MYCAT_HOME
,配置mycat根目录
-
修改
Path
变量,新增
%MYCAT_HOME%\bin
修改配置文件 -->mycat–>conf
6.3 修改wrapper.conf
文件,配置jdk环境
6.4 修改server.xml
文件
这里的root和123456就是登陆mycat的用户和密码
6.5 修改schema.xml
文件
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="localhost1" database="testdb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="主数据库ip地址:3306" user="主数据库用户名"
password="主数据库密码">
<readHost host="hostS1" url="从数据库ip地址:3306" user="从数据库用户名" password="从数据库密码" />
</writeHost>
</dataHost>
</mycat:schema>
- balance(负载均衡)类型目前有4种:
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"
:所有读请求随机的分发到readhost执行,writerHost不负担读压力。
为了能看到效果,把BALANCE设置成1,会在两个主机之间切换查询。
6.6 启动mycat
- 安装完jdk和mysql后,进入mycat解压目录下的bin目录(如果未配置环境变量):
E:\environment\mycat\bin
- 在打开的cmd命令行窗口中,执行如下命令安装mycat(注意需要管理员账户登录,如果不是请使用管理员身份运行cmd打开命令行窗口):
#安装服务
mycat.bat install
#启动mycat服务
mycat.bat start
#查看mycat的运行状态:
mycat.bat status
#停止mycat服务
mycat.bat stop
#重启服务
mycat.bat restart
6.7 测试连接
- 启动成功后,使用navicat连接mycat,新建一个mysql连接如下图:连接成功,其中
8066
端口是mycat的默认端口 - 账户:
root
- 密码:
123456
- 端口:
8066
- ip地址:
输入安装mycat的服务器的ip地址
6.8 验证配置
- 在主数据库插入数据带系统变量数据,造成主从数据不一致,方便查看效果
insert into test_table values(1, @@hostname)
@@hostname
:主机名称
- 如果主机和从机名称一样可以修改主机名称,让主从插入数据不一致
hostnamectl set-hostname mysql-master
- 远程登录Mycat
mysql -umycat -p123456 -h192.168.110.145 -P8066
ip换成自己的
- 切换数据库
use TESTDB
- 执行查询语句,观察是否随机切换查询
select * from test_table;