1. 京淘后端项目架构
1.1 数据库主从同步
1.1.1 业务需求说明
当数据库发生宕机的现象,如果配置了数据库主从同步,则可以从从库中获取现有记录,显示数据的恢复.
1.2 数据库主从同步原理
1.2.1 数据库冷备份
说明: 人工的将数据库文件定期导出,一式三份 如果其中一份丢失,另外2份可以留作替补.
弊端: 数据不能实时备份.
解释: 工作中数据库的冷备份是恢复数据最后有效的手段(重大事故)
1.2.2 数据库热备份
说明: 最为主要的作用可以实现实时备份. 几乎保证数据不丢失.
克隆2台服务器
被克隆的服务器要处于关机状态,
1、选中需要克隆的服务器,右键,管理,克隆,创建完整克隆,CentOS7-db01
2、修改ip,
#编辑网卡 vi /etc/sysconfig/network-scripts/ifcfg-ens32
IPADDR="192.168.111.131"
#重启网卡
service network restart
1、安装mariadb数据库
yum -y install mariadb-server
2、启动mariadb数据库、开机启动
systemctl start mariadb
systemctl enable mariadb
3、数据库初始化操作
mysql_secure_installation
Enter current password for root (enter for none): 输入root用户的当前密码(输入无):这里直接回车 Set root password? [Y/n] 设置root密码?[是/否]:这里输入y New password: 新密码:这里输入root Re-enter new password: 重新输入新密码:这里输入root Remove anonymous users? [Y/n] 删除匿名用户?[是/否]:这里输入y Disallow root login remotely? [Y/n] 不允许root用户远程登录?[是/否]:这里输入n Remove test database and access to it? [Y/n] 删除test数据库?[是/否]:这里输入y Reload privilege tables now? [Y/n] 现在重新加载特权表吗?[是/否]:这里输入y
#开启端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重新加载防火墙:
firewall-cmd --reload
mysql -u root -p
输入密码 root
use mysql;
#将host='localhost' 改为 '%',表示任何ip地址都可以访问
update user set host='%' where host='localhost';
#刷新数据库权限
flush privileges;
刚刚新建的数据库,再克隆一份
1、选中需要克隆的服务器,右键,管理,克隆,创建完整克隆,CentOS7-db02
2、修改ip,
#编辑网卡 vi /etc/sysconfig/network-scripts/ifcfg-ens32
IPADDR="192.168.111.132"
#重启网卡
service network restart
1.3 数据库备份实现
1.3.1 实现数据库导入
2个数据都创建相同的数据库名称和表名和字段: 测试数据库名mydatabase 和、测试表名 mytable 、一个int类型的id字段就可以了
1.3.2 开启二进制日志文件
- 默认条件下 数据库的二进制日志文件是关闭的.如果需要备份则手动开启
- 编辑二进制日志文件 /etc/my.cnf
- 编辑配置文件,修改数据节点的唯一标识
新增2行,其中server-id用来区分节点的编号,唯一。log-bin设置二进制文件的名称。
server-id=1
log-bin=mysql-bin
另外一台
server-id=2
log-bin=mysql-bin
- 默认数据库文件位置 cat /etc/my.cnf
- 重启数据库 让二进制文件生效即可
systemctl restart mariadb
1.4 实现主从挂载
1.4.1 主库状态
主库IP这里使用192.168.111.131
检查主库的状态,主要查询 要同步的 文件名和位置
#在主库执行
# 在主库131查询状态
SHOW MASTER STATUS;
1.4.2 实现主从挂载
这一步在 从库192.168.111.132 里面执行
# 在从库中执行
/*132从库 从库链接主库
IP/端口/用户名/密码/二进制文件名/pos位置
*/
CHANGE MASTER TO
MASTER_HOST="192.168.111.131",#主库ip
MASTER_PORT=3306,
MASTER_USER="root",
MASTER_PASSWORD="root",
MASTER_LOG_FILE="mysql-bin.000001",
MASTER_LOG_POS=245;
1.4.3 启动主从
启动主从服务,在从库执行下面命令
# 在从库中执行
# 启动主从服务
START SLAVE;
这一步在 从库192.168.111.132 里面执行,出现如下2个yes就成功了。
#检查主从状态
SHOW SLAVE STATUS;
测试主从
主从要达到的效果,修改主库的数据,从库的数据跟着改变。千万不要手动去改从库的数据,去看主库变化没有,千万不要这样操作,这样主从就废了。
创建数据库是编码要选择uft8,不然中文会乱码
1.4.4 主库主从搭建问题
- 停止主从服务
stop slave - 排查错误
- 重启进行主从挂载
- 重置主从服务 reset slave
- 重复挂载即可 如果不行 推倒重做
1.5 实现数据库读写分离/负载均衡
主库:可读、可写。
从库:只可读。
1.5.1 Mycat数据库代理
前身是阿里的一个产品。这个要依赖jdk。http://www.mycat.org.cn/
mycat 其实就是一种反向代理
基于心跳的自动故障切换,支持读写分离。支持数据的多片自动路由与聚合。
活跃的、性能好的开源数据库中间件!
我们致力于开发高性能的开源中间件而努力!
重要通告,最近有区块链项目借Mycat宣传,与Mycat毫无关系,大家不要相信!
1.5.2 Mycat安装
1). 解压安装包 如果所示.
https://download.csdn.net/download/u014644574/86759510
解压mycat安装包
tar -xvf Mycat-server-1.7.0-DEV-20170416134921-linux.tar.gz
2. 目录结构
bin: Mycat的可执行文件
conf: Mycat的配置文件
logs: Mycat日志 如果报错
这里需要改conf目录下的2个配置文件,自带的配置文件里面太复杂,先备份用自己上传的。
# 备份
mv server.xml server.xml.bak
mv schema.xml schema.xml.bak
# 将上传的配置文件移动到conf目录下
mv schema.xml mycat/conf/
mv server.xml mycat/conf/
1.5.3 编辑server.xml
说明: 该配置主要实现用户与代理之间的配置关系,
端口号: 8066端口
配置代理的用户名和密码
修改配置文件: vi /root/mycat/conf/server.xml
末尾修改如下:
上面配置了2个代理用户(实际数据库这2个用户可以没有),上面创建了一个代理数据库和2个代理用户。
代理数据库名称 mydb
读写代理用户/密码:root/root
只读代理用户/密码:user/user
可见代理的数据名、用户名、密码全都是虚拟的,根本没有,这样保护真实的数据库。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0-->
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->
<property name="sequnceHandlerType">2</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
<property name="processorBufferPoolType">0</property>
<!--默认是65535 64K 用于sql解析时最大文本长度 -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
<!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
<property name="handleDistributedTransactions">0</property>
<!--
off heap for merge/order/group/limit 1开启 0关闭
-->
<property name="useOffHeapForMerge">1</property>
<!--
单位为m
-->
<property name="memoryPageSize">64k</property>
<!--
单位为k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!--
单位为m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用zookeeper协调切换 -->
<property name="useZKSwitch">true</property>
<!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>-->
<!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
</system>
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<!--
<firewall>
<whitehost>
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<!--用户标签-->
<user name="root">
<property name="password">root</property>
<!--与schema.xml中的配置相同 注意数据库的大小写-->
<property name="schemas">mydb</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">mydb</property>
<property name="readOnly">true</property>
</user>
</mycat:server>
1.5.4 编辑schema.xml
这一步是配置代理数据库,代理用户密码,和真实数据库用户名密码的映射。
vi /root/mycat/conf/schema.xml
用户写操作访问131,用户的读操作访问131/132
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--name属性是自定义的 dataNode表示数据库的节点信息 jtdb表示逻辑库-->
<schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="mynode"/>
<!--定义节点名称/节点主机/数据名称-->
<dataNode name="mynode" dataHost="localhost1" database="testdb" />
<!--参数介绍-->
<!--balance 0表示所有的读操作都会发往writeHost主机,1表示所有的读操作发往readHost和闲置的主节点中-->
<!--writeType=0 所有的写操作都发往第一个writeHost主机-->
<!--writeType=1 所有的写操作随机发往writeHost中-->
<!--dbType 表示数据库类型 mysql/oracle-->
<!--dbDriver="native" 固定参数 不变-->
<!--switchType=-1 表示不自动切换, 主机宕机后不会自动切换从节点-->
<!--switchType=1 表示会自动切换(默认值)如果第一个主节点宕机后,Mycat会进行3次心跳检测,如果3次都没有响应,则会自动切换到第二个主节点-->
<!--并且会更新/conf/dnindex.properties文件的主节点信息 localhost1=0 表示第一个节点.该文件不要随意修改否则会出现大问题-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select 1</heartbeat>
<!--配置第一台主机主要进行写库操作,在默认的条件下Mycat主要操作第一台主机在第一台主机中已经实现了读写分离.因为默认写操作会发往137的数据库.读的操作默认发往141.如果从节点比较忙,则主节点分担部分压力.
-->
<writeHost host="hostM1" url="192.168.111.131:3306" user="root" password="root">
<!--读数据库1-->
<readHost host="hostS1" url="192.168.111.132:3306" user="root" password="root" />
<!--读数据库2-->
<readHost host="hostS2" url="192.168.111.131:3306" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
1.5.5 mycat命令
cd /root/mycat/bin
#启动
./mycat start
#停止
./mycat stop
#重启
./mycat restart
#查看状态
./mycat status
启动mycat默认端口8066
#开启端口
firewall-cmd --zone=public --add-port=8066/tcp --permanent
#重新加载防火墙:
firewall-cmd --reload
1.5.6 修改数据库地址
<dependencies>
<!--web应用基本环境配置 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--引入jdbc包-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--引入数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
测试代码
@SpringBootApplication
public class MydbApplication implements CommandLineRunner {
@Autowired
private JdbcTemplate jdbcTemplate;
public static void main(String[] args) {
SpringApplication.run(MydbApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
String sql = "SELECT * FROM test_tb";
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
for (int i = 0; i < list.size(); i++) {
for (Map.Entry<String, Object> entry : list.get(i).entrySet()) {
System.out.println(entry.getKey() + "=" + entry.getValue());
}
}
}
@Service
public class DemoService {
}
}
单机配置131
spring.datasource.url=jdbc:mysql://192.168.111.131:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=root
单机配置132
spring.datasource.url=jdbc:mysql://192.168.111.132:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=root
由于mycat安装在了131上面,所有ip连接131就可以了
读/写 数据库连接方式
spring.datasource.url=jdbc:mysql://192.168.111.131:8066/mydb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
spring.datasource.username=root
spring.datasource.password=root
只读数据库连接方式,此连接方式,修改数据会失败。只能读数据。
spring.datasource.url=jdbc:mysql://192.168.111.131:8066/mydb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
spring.datasource.username=user
spring.datasource.password=user
怎么测试负载均衡?
我们手动修改主库的数据,从库必然会改变,这样测试不出来效果,
现在我们修改从库(生产千万不要修改从库,这样做不会导致主从失效,只会导致在从库修改的这一条数据和主库不一致,可以在主库或者从库直接把这条数据改回来就行了),主库是数据不会变的,现在执行查询,发现数据一次是主库的,一次是修改后的从库的数据。说明负载均衡生效了。
1.5.7 负载均衡实现
1.6 高可用 -双机热备(主主复制)
1.6.1 介绍
当数据库主机宕机之后,要求实现高可用机制.用户程序将不会受到影响. 数据库高可用机制.
1.6.2 双机热备思想
1.6.3 反向搭建
以前 131 是主机 132是从库。
现在,都是主库。互为主从。
在主从的基础上,再反向搭建一次主从,现在 131 是从库,132是主库。
1).在主库132 查询状态,主要是查询 要同步的 文件名和位置
#主库上执行
SHOW MASTER STATUS;
2). 在从库131 执行,从库链接主库,挂载命令
# 在从库中执行
# 从库链接主库
CHANGE MASTER TO
MASTER_HOST="192.168.111.132", #要链接到哪里
MASTER_PORT=3306,
MASTER_USER="root",
MASTER_PASSWORD="root",
MASTER_LOG_FILE="mysql-bin.000003",
MASTER_LOG_POS=472;
# 在从库中执行
#启动挂载主从
START SLAVE;
# 在从库中执行
# 检查主从状态,查询是否挂载成功
SHOW SLAVE STATUS;
出现2个yes 表示成功。
现在,在2台数据库之间修改,发现都可以相互修改了。
1.6.4 修改Mycat配置文件
server.xml 不用变
修改 schema.xml 实现 双主发生故障自动切换
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema
xmlns:mycat="http://io.mycat/">
<!--mydb表代理数据库名称 dataNode表示数据库的节点信息 mydb表示逻辑库-->
<schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="mynode"/>
<!--定义节点名称/节点主机/数据名称 testdb就是真实数据库名称-->
<dataNode name="mynode" dataHost="localhost1" database="testdb" />
<!--参数介绍-->
<!--balance 0表示所有的读操作都会发往writeHost主机 -->
<!--balance 1表示所有的读操作发往readHost和闲置的主节点中-->
<!--writeType=0 所有的写操作都发往第一个writeHost主机-->
<!--writeType=1 所有的写操作随机发往writeHost中-->
<!--dbType 表示数据库类型 mysql/oracle-->
<!--dbDriver="native" 固定参数 不变-->
<!--switchType=-1 表示不自动切换, 主机宕机后不会自动切换从节点-->
<!--switchType=1 表示会自动切换(默认值)如果第一个主节点宕机后,Mycat会进行3次心跳检测,如果3次都没有响应,则会自动切换到第二个主节点-->
<!--并且会更新/conf/dnindex.properties文件的主节点信息 localhost1=0 表示第一个节点.该文件不要随意修改否则会出现大问题-->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select 1</heartbeat>
<!--配置第一台主机主要进行写库操作,在默认的条件下Mycat主要操作第一台主机在第一台主机中已经实现了读写分离.
因为默认写操作会发往131的数据库.读的操作默认发往132.如果从节点比较忙,则主节点分担部分压力.
-->
<writeHost host="hostM1" url="192.168.111.131:3306" user="root" password="root">
<!--读数据库1-->
<readHost host="hostS1" url="192.168.111.132:3306" user="root" password="root" />
<!--读数据库2-->
<readHost host="hostS2" url="192.168.111.131:3306" user="root" password="root" />
</writeHost>
<!--定义第二台主机 由于数据库内部已经实现了双机热备.-->
<!--Mycat实现高可用.当第一个主机131宕机后.mycat会自动发出心跳检测.检测3次.-->
<!--如果主机131没有给Mycat响应则判断主机死亡.则回启东第二台主机继续为用户提供服务.-->
<!--如果131主机恢复之后则处于等待状态.如果132宕机则131再次持续为用户提供服务.-->
<!--前提:实现双机热备.-->
<writeHost host="hostM2" url="192.168.111.132:3306" user="root" password="root">
<!--读数据库1-->
<readHost host="hostS1" url="192.168.111.131:3306" user="root" password="root" />
<!--读数据库2-->
<readHost host="hostS2" url="192.168.111.132:3306" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
修改后需要重启mycat
注意:在双主模式下,虽然数据库之间已经可以相互修改了。
但是mycat的user代理用户 始终是只读,比如下面的配置更新语句会报错,只能执行查询语句。
上面的配置中第1台,在配置
测试1台数据库宕机
#停止第1台
systemctl stop mariadb
启动第1台
systemctl start mariadb