云计算篇-Linux下MySQL的入门使用-主从复制/分离-持续更新_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包[root@localhost ~]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm2.启用存储库[root@localhost ~]# percona-release enable-only tools release3.安装percona-xtrabackup[root@localhost localhost~]# yum install -y percona-xtrabackup-24 适用于mysql5.7[root@localhost ~]# yum install -y percona-xtrabackup-80 适用于mysql8.04.查看版本[root@localhost ~]# innobackupex -vxtrabackup: 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完全备份
创建备份目录[root@localhost ~]# mkdir -p /xtrabackup/full 在备份之前，先进入数据库存储一些数据[root@localhost ~]# mysql -uroot -pAmo@2501mysql> create databases tdb;mysql> use tdblmysql> create table t1(id int,name (varchar));mysql> insert into t1 values(1,\'amo\');#备份数据[root@localhost ~]# innobackupex --user=root --password=\'Amo@2501\' /xtrabackup/full##可以进入/xtrabackup/full/中查看是否有备份数据产生#恢复数据1.停止数据库[root@localhost ~]# systemctl stop mysqld2.清理环境[root@localhost ~]# rm -rf /var/lib/mysql/*//删除所有数据[root@localhost ~]# rm -rf /var/log/mysqld.log[root@localhost ~]# rm -rf /var/log/mysql-3.重演回滚[root@localhost ~]# innobackupex --apply-log /xtrabackup/full/2025-03-28_16-26-35/4.恢复数据[root@localhost ~]# innobackupex --copy-log /xtrabackup/full/2025-03-28_16-26-35/5.修改权限 ！！！！注意一定要修改权限[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql/6.启动数据库[root@localhost ~]# systemctl start mysqld7.查看数据库是否有了原来的信息5.2增量备份
1.进行第一次完整备份
创建备份目录[root@localhost ~]# mkdir -p /xtrabackup/full 在备份之前，先进入数据库存储一些数据[root@localhost ~]# mysql -uroot -pAmo@2501#备份数据[root@localhost ~]# innobackupex --user=root --password=\'Amo@2501\' /xtrabackup/full##可以进入/xtrabackup/full/中查看是否有备份数据产生2.第一次增量备份
先在数据库中插入新的数据[root@localhost ~]# insert into tdb.t1 values(2,\'tom\');备份增量数据[root@localhost ~]# 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.第二次增量备份
先在数据库中插入新的数据[root@localhost ~]# insert into tdb.t1 values(3,\'alice\');备份增量数据[root@localhost ~]# 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.停止数据库[root@localhost ~]# systemctl stop mysqld2.清理环境[root@localhost ~]# rm -rf /var/lib/mysql/*//删除所有数据[root@localhost ~]# rm -rf /var/log/mysqld.log[root@localhost ~]# rm -rf /var/log/mysql-3.依次重演回滚第一次[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/第二次[root@localhost ~]# 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 增量备份数据第三次[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/ --incremental-dir=/xtrabackup/full/2025-03-28_16-32-30/4.恢复数据[root@localhost ~]# innobackupex --copy-log /xtrabackup/full/2025-03-28_16-26-35/5.修改权限 ！！！！注意一定要修改权限[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql/6.启动数据库[root@localhost ~]# systemctl start mysqld7.查看数据库是否有了原来的信息5.3差异备份
跟增量备份的差别就是，差异备份都是基于第一次完整备份的基础上进行新数据的备份，所以恢复数据的时候只需要回滚最后一次备份的数据到第一次完整的备份数据上即可。
1.进行第一次完整备份
创建备份目录[root@localhost ~]# mkdir -p /xtrabackup/full 在备份之前，先进入数据库存储一些数据[root@localhost ~]# mysql -uroot -pAmo@2501#备份数据[root@localhost ~]# innobackupex --user=root --password=\'Amo@2501\' /xtrabackup/full##可以进入/xtrabackup/full/中查看是否有备份数据产生2.第一次差异备份
先在数据库中插入新的数据[root@localhost ~]# insert into tdb.t1 values(2,\'tom\');备份增量数据[root@localhost ~]# 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.第二次差异备份
先在数据库中插入新的数据[root@localhost ~]# insert into tdb.t1 values(3,\'alice\');备份增量数据[root@localhost ~]# 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.停止数据库[root@localhost ~]# systemctl stop mysqld2.清理环境[root@localhost ~]# rm -rf /var/lib/mysql/*//删除所有数据[root@localhost ~]# rm -rf /var/log/mysqld.log[root@localhost ~]# rm -rf /var/log/mysql-3.依次重演回滚第一次[root@localhost ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-28_16-26-35/第二次[root@localhost ~]# 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.恢复数据[root@localhost ~]# innobackupex --copy-log /xtrabackup/full/2025-03-28_16-26-35/5.修改权限 ！！！！注意一定要修改权限[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql/6.启动数据库[root@localhost ~]# systemctl start mysqld7.查看数据库是否有了原来的信息6.mysqldump逻辑备份流程
6.1库的数据和结构
备份库的数据和结构mysqldump -uroot -pAmo@2501 tdb > /home/mysql_back/tdb.sql #备份库结构加数据 备份的位置为自定义指定恢复库的数据和结构 #注意：为保持一致性，恢复前停止数据库对外的服务，停止binlog:set sql_log_bin=0;恢复库mysql> create database tdb1;#这里我测试可以不创建库 然后直接恢复库会自动生成一个库位tdb1mysql> source /home/mysql_back/tdb.sql;#第一种方法[root@mysql-server ~]# 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;#这里我测试可以不创建库 然后直接恢复库会自动生成一个库位tdb1mysql> source /home/mysql_back/tdb.sql;#第一种方法[root@mysql-server ~]# 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;#第一种方法[root@mysql-server ~]# 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;#第一种方法[root@mysql-server ~]# 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中可以自己设置导入导出的目录[root@mysql-server ~]# vim /etc/my.cnf[mysqld]下secure_file_priv=/home/sql-data #后面为自己创建的目录 比如我这里创建的目录为/home/sql-data赋予权限[root@mysql-server ~]# chown -R mysql.mysql /home/sql-data重启mysql[root@mysql-server ~]# 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日志
[root@mysql-server ~]# vim /etc/my.cnflog-bin=/var/log/mylog-bin/mylog #指定binlog位置 ，但是创建后记得更改所属主和所属组server-id=1重启mysql服务systemctl restart mysqldmysql> flush logs; #刷新binlog日志会截断产生新的日志文件mysql> create table tdb1; #创建一个库根据binlog中想要恢复的SQL语句的起始位置和结束位置进行恢复[root@mysql-server ~# mysqlbinlog mylog.000001[root@mysql-server ~]# mysql -uroot -p\'qf123\'mysql> drop database tdb1; #将这个库删除恢复数据[root@mysql-server ~]# cd /var/log/mysql-bin/[root@mysql-server ~]# mysqlbinlog --start-position 219 --stop-position 313 mylog.000001 | mysql -uroot -pAmo@2501二.主从复制
1.binlog方式
#默认两台电脑都已经安装mysql-5.7.44版本，以及防火墙以及SElinux已经关闭
两台电脑都需要配置hosts解析
#这里默认为我自己的master和slave的ipvim /etc/hosts #两台都需要添加192.168.242.141 mysql-master192.168.242.137 mysql-slave1.1master配置
#编辑主配置文件-------开启binlog功能以及指定server-id[mysqld]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\';#创建主从同步的用户 并指定权限和IPmysql> show master status\\G #查看binlog日志状态 后面需要使用下面的数据
1.2slave配置
#编辑主配置文件-------指定server-id[mysqld]server-id=2 #记得重启mysql服务登录进入MySQL-启动同步
mysql> \\e #编辑同步信息CHANGE MASTER TOMASTER_HOST=\'mysql-master\', #主节点域名或者IPMASTER_USER=\'repl\',#创建的同步用户MASTER_PASSWORD=\'amo@123\',#用户密码MASTER_LOG_FILE=\'mylog.000010\',#binlog日志名MASTER_LOG_POS=817;#开始的位置 -> ;mysql> start slave; #启动同步mysql> show slave status\\G #查看是否同步（同步信息）
然后你可以通过在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操作
[root@mysql-master ~]# vim /etc/my.cnf #在[mysqld]下添加如下内容server-id=1 #定义server id master必写 log-bin = mylog #开启binlog日志，master必写；没有定义位置，会放到数据存放目录gtid_mode = ON #开启gtidenforce_gtid_consistency=1 #强制gtid[root@mysql-master ~]# systemctl restart mysqld #修改配置文件，记得重启mysql！！！！！！！！！！登陆进入mysql创建主从同步用户mysql> grant replication slave on *.* to \'repl\'@\'192.168.242.%\' identified by \'amo@123\';mysql> flush privileges;#刷新权限2.2slave操作
#这里如果是想使用两个从节点，配置的操作都是一样的
[root@mysql-slave ~]# vim /etc/my.cnf #添加如下配置server-id=2gtid_mode = ONenforce_gtid_consistency=1master-info-repository=TABLE #设置master连接信息的存储方式relay-log-info-repository=TABLE #设置relay log的存储方式[root@mysql-slave ~]# systemctl restart mysqld #修改配置文件记得重启mysql登录进入mysql配置主从复制 启动slave角色mysql> \\echange master tomaster_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
[root@mysql-mycat ~]# vim /usr/local/mycat/conf/server.xml #########################################这一部分是可以直接注释或者删除的，因为没有设置对应的逻辑库会导致启动失败 Qf@12345! testdb
######################################### amo@2501 db1 true
2.2配置schema.xml
[root@mysql-mycat ~]# vim /usr/local/mycat/conf/schema.xml select user() #####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.
2.3调整JVM 配置wrapper.conf文件
[root@mysql-mycat ~]# vim /usr/local/mycat/conf/wrapper.conf wrapper.startup.timeout=300 //超时时间300秒 wrapper.ping.timeout=1202.4创建真实用户并授权
master机器上
#登录进入MySQL创建用户并授权mysql> grant all on db1.* to amomt@\'%\' identified by \'amo@2501\';mysql> flush privileges;2.5启动mycat
[root@mysql-mycat ~]# /usr/local/mycat/bin/mycat start # 后面如果是stop就是停止mycatStarting Mycat-server... #需要稍微等一会儿[root@mysql-mycat ~]# jps6744 WrapperSimpleApp6954 Jps#查看Java是否成功启动
[root@mysql-mycat ~]# netstat -ntlp | grep java
2.6测试Mycat
可以额外开一台机器，也可以使用当前master或者slave机器来进行测试mycat
#比如我这里把slave1当成客户端来测试Mycat
[root@mysql-slave ~]# mysql -uamomt -pamo@2501 -P 8066 -h mysql-mycat
