云盘算篇-Linux下MySQL的入门利用-主从复制/分离-持续更新
目次一.数据备份
1.为什么要备份
2.MySQL备份类型
2.1.物理备份
2.2.逻辑备份
2.3.物理和逻辑备份的区别
3.物理备份方式
3.1.完备备份
3.2.增量备份
3.3.差异备份
4.xtrabackup安装
5.物理备份流程
5.1完全备份
5.2增量备份
5.3差异备份
6.mysqldump逻辑备份流程
6.1库的数据和结构
6.2库的结构
6.3表的数据和结构
6.4表的结构
6.5数据的导入导出
7.binlog恢复数据
二.主从复制
1.binlog方式
1.1master配置
1.2slave配置
1.3错误处置惩罚
1.4binlog模式一主二从
2.M-S架构GTID基于事务ID的主从复制
2.1master操纵
2.2slave操纵
2.3GTID模式一主二从
三、主从分离
1部署环境
2修改Mycat配置文件
2.1配置server.xml
2.2配置schema.xml
2.3调解JVM 配置wrapper.conf文件
2.4创建真实用户并授权
2.5启动mycat
2.6测试Mycat
如果大家喜好可以点一个小小的关注，后续持续更新云盘算相关技术
一.数据备份
1.为什么要备份
[*]备份：能够防止由于呆板故障以及人为误操纵带来的数据丢失，例如将数据库文件保存在了别的地方。
[*]冗余： 数据有多份冗余，但不等备份，只能防止呆板故障带来的数据丢失，例如主从模式、数据库集群。
2.MySQL备份类型
2.1.物理备份
直接复制数据库文件，实用于大型数据库环境，不受存储引擎的限定，但不能恢复到差异的MySQL版本。
热备(hot backup)
[*]在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日记文件
[*]对应用根本无影响(但是性能还是会有下降,以是尽量不要在主库上做备份,在从库上做)
冷备(cold backup)
[*]备份数据文件,需要停机，是在关闭数据库的时间进行的
[*]备份 datadir 目次下的全部文件
温备(warm backup)
[*]针对myisam的备份(myisam不支持热备),备份时间实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的
[*]对应用影响很大
[*]通常加一个读锁
2.2.逻辑备份
备份的是建表、建库、插入等操纵所执行SQL语句（DDL DML DCL），实用于中小型数据库，服从相对较低。
2.3.物理和逻辑备份的区别
逻辑备份
物理备份
备份方式
备份数据库建表、建库、插入sql语句
备份数据库物理文件
优点
备份文件相对较小,只备份表中的数据与结构
恢复速率比较快
缺点
恢复速率较慢(需要重建索引,存储过程等)
备份文件相对较大(备份表空间,包罗数据与索引)
对业务影响
I/O负载加大
I/O负载加大
代表工具
mysqldump
ibbackup、xtrabackup，mysqlbackup
3.物理备份方式
3.1.完备备份
每次对数据进行完备的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。
优点：备份与恢复操纵简朴方便，恢复时一次恢复到位，恢复速率快
缺点：占用空间大，备份速率慢
3.2.增量备份
每次备份上一次备份到现在产生的新数据
只有那些在上次完全备份大概增量备份后被修改的文件才会被备份。以上次完备备份或上次的增量备份的时间为时间点，仅备份这之间的数据变革.
特点：因而备份的数据量小，占用空间小，备份速率快。但恢复时，需要从上一次的完备备份起按备份时间次序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。
3.3.差异备份
只备份跟完备备份不一样的
备份那些自从第一次次完全备份之后被修改过的全部文件，备份的时间出发点是从第一次的完备备份起，且以后每次备份都是和第一次完备备份进行比较（注意是第一次，不是上一次），备份自第一次完备备份以来全部的修改过的文件。备份数据量会越来越大。
特点：占用空间比增量备份大，比完备备份小，恢复时仅需要恢复第一个完备版本和最后一次的差异版本，恢复速率介于完备备份和增量备份之间。
4.xtrabackup安装
1.安装最新rpm包
# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
2.启用存储库
# percona-release enable-only tools release
3.安装percona-xtrabackup
# yum install -y percona-xtrabackup-24 适用于mysql5.7
# yum install -y percona-xtrabackup-80 适用于mysql8.0
4.查看版本
# innobackupex -v
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --log_bin=/var/log/mysql-bin/mylog --server-id=1
innobackupex version 2.4.29 Linux (x86_64) (revision id: 2e6c0951) 5.物理备份流程
5.1完全备份
创建备份目录
# mkdir -p /xtrabackup/full
在备份之前，先进入数据库存储一些数据
# mysql -uroot -pAmo@2501 mysql> create databases tdb;
mysql> use tdbl
mysql> create table t1(id int,name (varchar));
mysql> insert into t1 values(1,'amo'); #备份数据
#innobackupex --user=root --password='Amo@2501' /xtrabackup/full
##可以进入/xtrabackup/full/中查看是否有备份数据产生 #恢复数据
1.停止数据库
# systemctl stop mysqld
2.清理环境
# rm -rf /var/lib/mysql/* //删除所有数据
# rm -rf /var/log/mysqld.log
# rm -rf /var/log/mysql-
3.重演回滚
# innobackupex --apply-log/xtrabackup/full/2025-03-28_16-26-35/
4.恢复数据
# innobackupex --copy-log /xtrabackup/full/2025-03-28_16-26-35/
5.修改权限 ！！！！注意一定要修改权限
# chown -R mysql.mysql /var/lib/mysql/
6.启动数据库
# systemctl start mysqld
7.查看数据库是否有了原来的信息 5.2增量备份
1.进行第一次完备备份
创建备份目录
# mkdir -p /xtrabackup/full
在备份之前，先进入数据库存储一些数据
# mysql -uroot -pAmo@2501#备份数据
#innobackupex --user=root --password='Amo@2501' /xtrabackup/full
##可以进入/xtrabackup/full/中查看是否有备份数据产生 2.第一次增量备份
先在数据库中插入新的数据
# insert into tdb.t1 values(2,'tom');
备份增量数据
# innoxtrabackupex-uroot -p'Amo@2501' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2025-03-28_16-26-35/
##--incremental-basedir 是基于哪个备份做增量备份，此处是上一次的备份数据2025-03-28_16-26-35
#可检查 /xtrabackup/full中是否有新的增量备份 3.第二次增量备份
先在数据库中插入新的数据
# insert into tdb.t1 values(3,'alice');
备份增量数据
# innoxtrabackupex-uroot -p'Amo@2501' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2025-03-28_16-31-13/
##--incremental-basedir 是基于哪个备份做增量备份，此处是上一次的备份数据2025-03-28_16-31-13
#可检查 /xtrabackup/full中是否有新的增量备份 4.数据恢复
#恢复数据
1.停止数据库
# systemctl stop mysqld
2.清理环境
# rm -rf /var/lib/mysql/* //删除所有数据
# rm -rf /var/log/mysqld.log
# rm -rf /var/log/mysql-
3.依次重演回滚
第一次
# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/
第二次
# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/ --incremental-dir=/xtrabackup/full/2025-03-28_16-31-13/
# --incremental-dir 增量备份数据
第三次
# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/ --incremental-dir=/xtrabackup/full/2025-03-28_16-32-30/
4.恢复数据
# innobackupex --copy-log /xtrabackup/full/2025-03-28_16-26-35/
5.修改权限 ！！！！注意一定要修改权限
# chown -R mysql.mysql /var/lib/mysql/
6.启动数据库
# systemctl start mysqld
7.查看数据库是否有了原来的信息 5.3差异备份
跟增量备份的差异就是，差异备份都是基于第一次完备备份的基础上进行新数据的备份，以是恢复数据的时间只需要回滚最后一次备份的数据到第一次完备的备份数据上即可。
1.进行第一次完备备份
创建备份目录
# mkdir -p /xtrabackup/full
在备份之前，先进入数据库存储一些数据
# mysql -uroot -pAmo@2501#备份数据
#innobackupex --user=root --password='Amo@2501' /xtrabackup/full
##可以进入/xtrabackup/full/中查看是否有备份数据产生 2.第一次差异备份
先在数据库中插入新的数据
# insert into tdb.t1 values(2,'tom');
备份增量数据
# innoxtrabackupex-uroot -p'Amo@2501' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2025-03-28_16-26-35/
##--incremental-basedir 是基于哪个备份做差异备份，此处是第一次的备份数据2025-03-28_16-26-35
#可检查 /xtrabackup/full中是否有新的增量备份 3.第二次差异备份
先在数据库中插入新的数据
# insert into tdb.t1 values(3,'alice');
备份增量数据
# innoxtrabackupex-uroot -p'Amo@2501' --incremental /xtrabackup/full/ --incremental-basedir=/xtrabackup/full/2025-03-28_16-26-35/
##--incremental-basedir 是基于哪个备份做差异备份，此处是第一次的备份数据2025-03-28_16-26-35
#可检查 /xtrabackup/full中是否有新的增量备份 4.数据恢复
#恢复数据
1.停止数据库
# systemctl stop mysqld
2.清理环境
# rm -rf /var/lib/mysql/* //删除所有数据
# rm -rf /var/log/mysqld.log
# rm -rf /var/log/mysql-
3.依次重演回滚
第一次
# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/
第二次
# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/ --incremental-dir=/xtrabackup/full/2025-03-28_16-32-30/
# --incremental-dir 增量备份数据
4.恢复数据
# innobackupex --copy-log /xtrabackup/full/2025-03-28_16-26-35/
5.修改权限 ！！！！注意一定要修改权限
# chown -R mysql.mysql /var/lib/mysql/
6.启动数据库
# systemctl start mysqld
7.查看数据库是否有了原来的信息 6.mysqldump逻辑备份流程
6.1库的数据和结构
备份库的数据和结构
mysqldump -uroot -pAmo@2501 tdb > /home/mysql_back/tdb.sql #备份库结构加数据 备份的位置为自定义指定
恢复库的数据和结构
#注意：为保持一致性，恢复前停止数据库对外的服务，停止binlog:set sql_log_bin=0;
恢复库
mysql> create database tdb1;#这里我测试可以不创建库 然后直接恢复库会自动生成一个库位tdb1
mysql> source /home/mysql_back/tdb.sql;#第一种方法
# mysql -uroot -pAmo@2501 tdb1 < /home/mysql_back/tdb.sql#第二种方法 6.2库的结构
只备份库的结构
mysqldump -uroot -pAmo@2501 -d tdb > /home/mysql_back/tdb.sql #备份库的结构备份的位置自定义指定
恢复库结构 #默认创建了一个库 这里为tdb1
恢复库
mysql> create database tdb1;#这里我测试可以不创建库 然后直接恢复库会自动生成一个库位tdb1
mysql> source /home/mysql_back/tdb.sql;#第一种方法
# mysql -uroot -pAmo@2501-D tdb1 < /home/mysql_back/tdb.sql#第二种方法 6.3表的数据和结构
备份表的数据和结构
mysqldump -uroot -pAmo@2501 tdb t1 > /home/mysql_back/tdb_t1.sql #备份表的结构加数据 备份的位置自定义指定
恢复表的数据和结构
#注意：为保持一致性，恢复前停止数据库对外的服务，停止binlog:set sql_log_bin=0;
mysql> create database tdb1;
mysql> source /home/mysql_back/tdb_t1.sql;#第一种方法
# mysql -uroot -pAmo@2501 tdb1 < /home/mysql_back/tdb_t1.sql#第二种方法 6.4表的结构
只备份表的结构
mysqldump -uroot -pAmo@2501 -d tdb > /home/mysql_back/tdb.sql #备份库的结构备份的位置自定义指定
恢复表结构 #默认创建了一个库 这里为tdb1
恢复表
mysql> create database tdb1;
mysql> source /home/mysql_back/tdb_t1.sql;#第一种方法
# mysql -uroot -pAmo@2501-D tdb1 < /home/mysql_back/tdb_t1.sql#第二种方法 6.5数据的导入导出
注意：表的导出和导入只备份表内记载，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
mysql> show variables like "secure_file_priv";----查询导入导出的目录。
默认在 /var/lib/mysql-files中
可以自己设置导入导出的目录
# vim /etc/my.cnf
下
secure_file_priv=/home/sql-data #后面为自己创建的目录 比如我这里创建的目录为/home/sql-data
赋予权限
# chown -R mysql.mysql /home/sql-data
重启mysql
# systemctl restart mysqld
导出数据
mysql> select * from tdb.t1 into outfile '/sql/t1.bak';
导入数据 #这里默认已经把tdb库中t1中的数据已经清除 只有表结构
mysql> load data infile '/home/sql-data/t1.bak' into table t1;
#如果要重新创建新的表进行导入，首先需要备份原来表的结构，然后首先恢复结构到新表，再导入数据！！！！！！ 7.binlog恢复数据
####开启binlog日记
# vim /etc/my.cnf
log-bin=/var/log/mylog-bin/mylog #指定binlog位置 ，但是创建后记得更改所属主和所属组
server-id=1
重启mysql服务
systemctl restart mysqld
mysql> flush logs; #刷新binlog日志会截断产生新的日志文件
mysql> create table tdb1;#创建一个库
根据binlog中想要恢复的SQL语句的起始位置和结束位置进行恢复
[root@mysql-server ~# mysqlbinlog mylog.000001
# mysql -uroot -p'qf123'
mysql> drop database tdb1; #将这个库删除
恢复数据
# cd /var/log/mysql-bin/
# mysqlbinlog --start-position 219 --stop-position 313 mylog.000001 | mysql -uroot -pAmo@2501 二.主从复制
1.binlog方式
#默认两台电脑都已经安装mysql-5.7.44版本，以及防火墙以及SElinux已经关闭
两台电脑都需要配置hosts解析
#这里默认为我自己的master和slave的ip
vim /etc/hosts #两台都需要添加
192.168.242.141 mysql-master
192.168.242.137 mysql-slave 1.1master配置
#编辑主配置文件-------开启binlog功能以及指定server-id
log-bin=/var/log/mysql-bin/mylog #这个是binlog日志的位置，可以自己在my.cnf中设置
#但是记得赋予权限 chown -R mysql.mysql /var/log/mysql-bin/
server-id =1
登录进入MySQL
mysql> grant replication slave on *.* to 'repl'@'192.168.242.%' identified by 'amo@123';#创建主从同步的用户 并指定权限和IP
mysql> show master status\G #查看binlog日志状态 后面需要使用下面的数据 https://i-blog.csdnimg.cn/img_convert/7d571cdd2f1ab8c224cd31504a8faa83.png
1.2slave配置
#编辑主配置文件-------指定server-id
server-id=2 #记得重启mysql服务 登录进入MySQL-启动同步
mysql> \e #编辑同步信息
CHANGE MASTER TO
MASTER_HOST='mysql-master', #主节点域名或者IP
MASTER_USER='repl',#创建的同步用户
MASTER_PASSWORD='amo@123',#用户密码
MASTER_LOG_FILE='mylog.000010',#binlog日志名
MASTER_LOG_POS=817;#开始的位置
-> ;
mysql> start slave; #启动同步
mysql> show slave status\G #查看是否同步（同步信息） https://i-blog.csdnimg.cn/img_convert/bf3958b91e28a4dbbb8b02989f11fa1d.png
然后你可以通过在master节点进行增删改的操纵，来查看从节点是否同时出现！
1.3错误处置惩罚
如果同步效果不正常，需要重新设置
从节点：
stop slave; #停止同步
reset slave; #清除主节点信息
主节点：
reset master; #清除binlog日志
再来到从节点：
重新\e进行配置 1.4binlog模式一主二从
##以上是一主一从，如果要利用binlog模式实现一主二从主从复制
1.配置新的slave2的DNS解析（上面的slave此处成为slave1）。
2.按照slave1的步调配置slave2。
3.在slave2上启动主从复制模式，并且查看主从复制状态。
4.slave2是无法直接通过不到master之前增删改的sql语句，以是如果slave2想同步全部的信息，在启动主从复制模式之前，需要对master进行数据备份，然后利用scp将备份传到slave2，恢复数据之后，再启动主从复制模式。
2.M-S架构GTID基于事务ID的主从复制
##默认全部呆板都是乐成安装mysql-5.7.44，并且关闭防火墙和SElinux
##hosts都已经配置，如果不知道hosts需要配置什么，可以查看上面binlog模式中的配置hosts的操纵，也可以不进行配置，只需要后面配置slave的时间填写master的IP即可
2.1master操纵
# vim /etc/my.cnf #在下添加如下内容
server-id=1 #定义server id master必写
log-bin = mylog #开启binlog日志，master必写；没有定义位置，会放到数据存放目录
gtid_mode = ON #开启gtid
enforce_gtid_consistency=1 #强制gtid
# systemctl restart mysqld #修改配置文件，记得重启mysql！！！！！！！！！！
登陆进入mysql创建主从同步用户
mysql> grant replication slave on *.* to 'repl'@'192.168.242.%' identified by 'amo@123';
mysql> flush privileges;#刷新权限 2.2slave操纵
#这里如果是想利用两个从节点，配置的操纵都是一样的
# vim /etc/my.cnf#添加如下配置
server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE #设置master连接信息的存储方式
relay-log-info-repository=TABLE #设置relay log的存储方式
# systemctl restart mysqld#修改配置文件记得重启mysql
登录进入mysql配置主从复制 启动slave角色
mysql> \e
change master to
master_host='master1', #master的IP地址，建议配置DNS解析使用域名
master_user='授权用户', #主服务上面创建的主从复制同步用户
master_password='授权密码',
master_auto_position=1;
-> ;
mysql> start slave; #启动slave角色
mysql> show slave status\G#查看状态，验证sql和IO是不是yes。 2.3GTID模式一主二从
##以上是一主一从，如果要利用GTID模式实现一主二从主从复制
1.配置新的slave2的DNS解析（上面的slave此处成为slave1）。
2.按照slave1的步调配置slave2。
3.在slave2上启动主从复制模式，并且查看主从复制状态。
4.GTID模式差异于binlog模式的地方在，GTID会主动确定binlog日记的位置，主动同步master之前已经存在的数据
三、主从分离
##默认配置
一主二从主从复制正常运行，四台呆板都配置了DNS，防火墙和SElinux都默认关闭
1部署环境
安装JDK和Mycat
2修改Mycat配置文件
MyCAT 目前主要通过配置文件的方式来定义逻辑库和相关配置:
/usr/local/mycat/conf/server.xml #定义用户以及系统相关变量，如端口等。其中用户信息是前端应用程序连接 mycat 的用户信息。
/usr/local/mycat/conf/schema.xml #定义逻辑库，表、分片节点等内容。 2.1配置server.xml
# vim /usr/local/mycat/conf/server.xml
#########################################
这一部分是可以直接注释或者删除的，因为没有设置对应的逻辑库会导致启动失败
<user name="root" defaultAccount="true">
<property name="password">Qf@12345!</property>
<property name="schemas">testdb</property>
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
#########################################
</user>
<user name="amomt"><!-- 注意这里都是逻辑用户以及逻辑库 -->
<property name="password">amo@2501</property>
<property name="schemas">db1</property>
<property name="readOnly">true</property>
</user> https://i-blog.csdnimg.cn/img_convert/0a190ee89d8a35b666552a9f26b7da3d.png
2.2配置schema.xml
# vim /usr/local/mycat/conf/schema.xml
<schema name="db1" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" >
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="mysql-master" url="mysql-master:3306" user="amomt" password="amo@2501">
<!-- can have multi read hosts -->
<readHost host="mysql-slave1" url="mysql-slave1:3306" user="amomt" password="amo@2501" />
<readHost host="mysql-slave2" url="mysql-slave2:3306" user="amomt" password="amo@2501" />
</writeHost>
</dataHost>
#####
balance 属性
负载均衡类型,目前的取值有 3 种:
balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
balance="1", 全部的 readHost 与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. https://i-blog.csdnimg.cn/img_convert/9da38f859bc6c305937f56dfa61e0625.png
2.3调解JVM 配置wrapper.conf文件
# vim /usr/local/mycat/conf/wrapper.conf
wrapper.startup.timeout=300 //超时时间300秒
wrapper.ping.timeout=120 2.4创建真实用户并授权
master呆板上
#登录进入MySQL创建用户并授权
mysql> grant all on db1.* to amomt@'%' identified by 'amo@2501';
mysql> flush privileges; 2.5启动mycat
# /usr/local/mycat/bin/mycat start# 后面如果是stop就是停止mycat
Starting Mycat-server...#需要稍微等一会儿
# jps
6744 WrapperSimpleApp
6954 Jps #查看Java是否乐成启动
# netstat -ntlp | grep java https://i-blog.csdnimg.cn/img_convert/c48ce6778d57c44805117d91ccd921c0.png
2.6测试Mycat
可以额外开一台呆板，也可以利用当前master大概slave呆板来进行测试mycat
#比如我这里把slave1当成客户端来测试Mycat
# mysql -uamomt -pamo@2501 -P 8066 -h mysql-mycat
https://i-blog.csdnimg.cn/img_convert/4b3bf9430dba1ea35a2e9f766494557c.png
如果大家喜好可以点一个小小的关注，后续持续更新云盘算相关技术
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]