mysql备份种类
要备份的内容：
数据
二进制日志, InnoDB事务日志
代码(存储过程、存储函数、触发器、事件调度器)
服务器配置文件三种备份类型：
全部备份：完全备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。实际应用中就是用一盘磁带对整个系统进行完全备份，包括其中的系统和所有数据。这种备份方式最大的好处就是只要用一盘磁带，就可以恢复丢失的数据。因此大大加快了系统或数据的恢复时间。
增量备份：增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象是进行全备份后所产生的增加和修改的文件;第二次增量备份的对象是进行第一次增量备份后所产生的增加和修改的文件，如此类推。
差异备份：差异备份与增量备份的区别在于它们备份的参考点不同：前者的参考点是上一次完全备份、差异备份或增量备份，后者的参考点是上一次完全备份。
组合应用的示例：
重要数据库：每天全备
一般的数据库：
① 完全备份与差异备份：以每周数据备份计划为例，我们可以在星期一进行完全备份，
其他时间每天进行差异备份。如果在星期五数据被破坏了，则只需要还原星期一完全的备份和星期四的差异备份。这种策略备份数据需要较多的时间，但还原数据使用较少的时间。
② 完全备份与增量备份：以每周数据备份为例，在星期一进行完全备份，其他时间每天进行增量备份。如果在星期五数据被破坏了，则你需要还原星期一正常的备份和从星期二至星期五的所有增量备份。这种策略备份数据需要较少的时间，但还原数据使用较多的时间。三种备份方式：
热备份：一般用于保证服务正常不间断运行，数据库的读写操作均不是受影响，用两台机器作为服务机器，一台用于实际数据库操作应用,另外一台实时的从前者中获取数据以保持数据一致.如果当前的数据库当机了,备份的数据库立马取代当前的数据库继续提供服务。跟MySQL的主从里的一主一从一样，主数据库宕机之后，从数据库就会取代主数据库
温备份：对表进行锁定，数据库的读操作可以执行, 但是不能执行写操作，然后备份，但对当前数据库的操作会产生影响。
冷备份：停止数据库，数据库不能进行读写操作, 即数据库要下线后备份
MySQL中进行不同方式的备份还要考虑存储引擎是否支持
MyISAM
热备 ×
温备 √
冷备 √
InnoDB
热备 √
温备 √
冷备 √两种备份
物理备份
在数据目录下，数据库为目录，表为文件，直接打包，物理备份一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
逻辑备份
逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)，例如使用mysql的内置命令（mysqldump）记录具体的sql语句进行导出备份与还原
一、直接在数据目录下（一般为/var/lib/mysql）寻找数据库进行备份（属于冷备份）
将node01的MySQL数据库所有数据备份到node02的数据库服务器上，并能够确保备份数据在node02服务器上的可用性
# 查看node1 信息
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use student;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| job |
| t_student |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from job;
+----+-----------+-----+
| id | name | sex |
+----+-----------+-----+
| 1 | 谭咏麟 | 男 |
| 2 | 张国荣 | 男 |
| 3 | 叶倩文 | 女 |
+----+-----------+-----+
3 rows in set (0.01 sec)
mysql> select * from t_student;
+----+--------+
| id | name |
+----+--------+
| 1 | tom |
| 2 | lisi |
| 3 | jerry |
| 4 | haha |
| 6 | meimei |
+----+--------+
5 rows in set (0.01 sec)
# 打包数据库备份文件
## 停止mysqld服务
[root@node1 ~]# systemctl stop mysqld
# 进入存放数据的目录进行打包
[root@node1 ~]# cd /opt/data/
[root@node1 data]# ls
auto.cnf client-cert.pem ibdata1 mysql node1.err public_key.pem student
ca-key.pem client-key.pem ib_logfile0 mysql-bin.000001 performance_schema server-cert.pem sys
ca.pem ib_buffer_pool ib_logfile1 mysql-bin.index private_key.pem server-key.pem
# 打包存放到/opt/下面
[root@node1 data]# tar zcf /opt/all-$(date '+%Y-%m-%d:%H-%M-%S').tar.gz *
[root@node1 data]# cd ..
[root@node1 opt]# ls
all-2022-07-03:20-56-35.tar.gz data
# 使用scp传到node2
[root@node1 ~]# scp /opt/all-2022-07-03:20-56-35.tar.gz root@192.168.229.130:/opt/
The authenticity of host '192.168.229.130 (192.168.229.130)' can't be established.
ECDSA key fingerprint is SHA256:mntQBTppC7e+5Uh8MyZHFW3FuKZzpoS46G0j2C+O8U4.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.229.130' (ECDSA) to the list of known hosts.
root@192.168.229.130's password:
all-2022-07-03:20-56-35.tar.gz 100% 1779KB 61.0MB/s 00:00二、使用mysql内置命令进行数据表备份（不建议）
将 student 表导入到 wocao 表
导出
格式：select * from 表名 into outfile "路径文件名";
select * from student into outfile "/var/lib/mysql/student.txt";
保存的目录一定要有mysql用户权限
导入
load data infile "路径文件名" into table 表名;
create table wocao(id int,name char(20));
load data infile "/var/lib/mysql/student.txt" into table wocao;
select * from wocao;
三：mysqldump备份
mysqldump命令已经在第一部分mysql数据库运维基础理论中提到过，这里只写操作过程
实验
我这里192.168.1.10端为mysql1，192.168.1.11端为mysql2
确保两端都开启了数据库
10端操作
我的数据库名字为ceshi，表名字为student
create database ceshi charset=utf8;
show create database ceshi
insert into students values ('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),('008', '小乔', '女', '河南', '15', '3班', null),('009', '百里守约', '男', '湖南', '21', '1班', ''),('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');
备份我的ceshi
创建备份目录到/mybak目录并检查，名字为 库名.sql
mkdir /mybak
mysqldump -uroot -p123 ceshi > /mybak/ceshi.sql
cd /mybak/;ls
将数据库文件导入到11端
scp /mybak/ceshi.sql 192.168.1.11:/root
11端操作
mysql -uroot -p123
导入
mysql -uroot -p123 < ceshi.sql
mysql -uroot -p123
show databases;
use ceshi
show tables;
select * from students;
这里有一个mysqldump备份还原脚本
#!/bin/bash
read -p "输入用户名:" name
read -s -p "输入密码:" pass
echo
read -p "导出(1) 导入(2):" num
if [ $num -eq 1 ];then
read -p "请选择备份方式(1:库 2:表):" choice1
if [ $choice1 -eq 1 ];then
read -p "选择库备份方式(1：全部备份 2：选择备份):" choice2
if [ $choice2 -eq 1 ];then
mysqldump -u$name -p$pass -A >all.sql-$(date +"%Y-%m-%d")
elif [ $choice2 -eq 2 ];then
mysql -u$name -p$pass -e "show databases;"
read -p '选择需要备份的库' database_name
mysqldump -u$name -p$pass $database_name > $database_name.sql-$(date +"%Y-%m-%d")
else
echo "输入正确格式！"
fi
elif [ $choice1 -eq 2 ];then
mysql -u$name -p$pass -e "show databases;"
read -p '选择库:' database_name2
mysql -u$name -p$pass -e "use $database_name2;show tables;"
read -p '选择表:' table_name
mysqldump -u$name -p$pass $database_name2 $table_name >$table_name-table.sql-$(date +"%Y-%m-%d")
else
echo "请输入备份方式的正确格式(1:库 2:表)"
fi
elif [ $num -eq 2 ];then
read -p "选择导入方式(1:所有库 2:选择库文件):" type
if [ $type -eq 2 ];then
read -p "输入导入的sql文件" sql_file
read -p "输入创建的库名" base_name
mysql -u$name -p$pass -e "drop database if exists $base_name;create database $base_name;"
mysql -u$name -p$pass $base_name < $sql_file
elif [ $type -eq 1 ];then
read -p "选择库文件" sql_file2
mysql -u$name -p$pass < $sql_file2
fi
else
echo "请选择(1：备份 2：导入)"
fimysql-binlog日志备份：
简介
主从复制的优势：
1：如果主库出现问题，可以切换到从库上提供服务
2：从库提供读操作，可以减轻主库IO压力
3：可在从库上做数据备份，避免备份期间影响主库性能
如果出现主从不同步，可能的原因是
1：网络的延迟
2：主库性能好，写速度快，从库进行复制负载过高
3：版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面不支持该功能
4：异步复制导致的数据不一致性
主从复制方式
异步
主库执行完成一个事务后，立即将结果返回给客户端，并不关心客户端是否已经接受并处理
同步
主库执行完成一个事务后，且所有的从库都执行该事务并返回给客户端
半同步
主库执行完成一个事务后，等待至少一个从库接收到，并写入relay log中才能返回给客户端
二进制日志格式：语句、行、混合模式
基于语句的复制：就是从库(slave)基于产生变化的SQL语句从主库(master)进行复制。在MySQL5.1.4版本之前是binlog和复制唯一支持的模式，也是MySQL5.5中默认的格式。
基于行的复制：基于行的复制不复制SQL语句，而是将插入，删除或更新操作的各行进行复制。master的binlog记录的是各个表中行的变化。5.7.7之后默认是行模式复制
基于混合模式的复制：它是根据事件的类型实时的改变binlog的格式。当设置为混合模式时，默认为基于语句的格式，但在特定的情况下它会自动的转变为基于行的模式。
REPLICATION
是MySQL内建的，本身自带的，二进制日志保存的内容：DML:SQL操作语句（update，insert，delete）
REPLICATION的原理简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作
1、Fail Over 故障切换
2、Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份
3、High Performance高性能，可以多台slave,实现读写分离
REPLICATION常见方案：
一主多从
一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中在Master上<多台同步影响IO>
M-S-S（级联）
使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates
M-S-S 减少主压力M1进行写，M2进行同步复制，但是会导致数据延迟更加大，需要在m2中存储引擎改为blackhole，只进行日志分发，不进行执行语句操作，既增加同步速度又减少了IO压力
互为主从（M-M）
每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性
开启server-id区分binlog日志中的SQL语句是否该执行，所以并不会出现循环复制
M-M-M
一台监控，监控三台机器互相做对方的master
多主一从
好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多
获取二进制日志流程
二进制日志（log-bin日志）
所有对数据库状态更改的操作（create、drop、update等）
操作以及原理详解
事先操作：
(1)主库通过grant replcation slave on 库.表 进行授权，从库设置change master to master_user接收主库授权
(2)开启主库二进制日志功能
数据同步过程：
(1)master将event通过sync_binlog写入到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）
(2)当从节点连接主节点时，主节点会为其创建一个 log dump 线程，用于发送和读取 Binlog 的内容。在读取 Binlog 中的操作时，log dump 线程会对主节点上的 Binlog 加锁。主节点会为自己的每一个从节点创建一个 log dump 线程。
(3)从节点 I/O 线程接收到主节点的 log dump 进程发来的更新之后，保存在本地 relay-log（中继日志）中
(4) SQL 线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
sync_binlog
通过sync_binlog将sql语句写入binlog日志
show variables like "sync_binlog";
如果关闭，mysql不控制二进制日志写入磁盘，由操作系统进行控制，异步复制，延迟binlog日志写入磁盘，减少IO占用，但是数据安全性减低
设置方式：set global sync_binlog=0;
开启二进制日志
存放日志目录
vim /etc/my.cnf
启动二进制日志
log-bin=mysql-bin
重启服务检查日志
systemctl restart mariadb
cd /var/lib/mysql;ls
这个文件就是二进制日志
主从备份实验
主从备份
这里我使用192.168.1.10（主），192.168.1.11（从）
两端同时下载mariadb
yum -y install mariadb mariadb-server
修改配置文件，开启binlog日志
vim /etc/my.cnf
开启mariadb并设定开机自启
systemctl start mariadb;systemctl enable mariadb
主服务器授权，从服务器保存授权信息
10端
登录：
mysql
授权给11端
grant replication slave on *.* to slave@'192.168.1.%' identified by '123';
授权给1.0网段，所有此网段的IP都可以以slave用户，密码为123进行登录
flush privileges;
查看授权信息
show master status;
11端
登录
mysql
转换为slave身份并确认授权
change master to master_user='slave',master_password='123',master_host='192.168.1.10',master_log_file='mysql-bin.000001',master_log_pos=532;
最后的两位参数master_log_file和master_log_pos需要查看上一步骤主服务器master状态信息填写
开启从服务器slave模式并检测
start slave;
show slave status\G
IO，SQL这两个参数都是yes即证明同步成功
若不是两个yes，注意查看下面此处位置，会有报错信息
例如：
查看/var/lib/mysql目录下多出一个master.info，查看并核实内容
cd /var/lib/mysql/;ls
cat master.info
测试
主服务器创建一个数据库，查看从服务器是否也同步
10端
show databases;
create database abc;
drop database abc;
11端
查看同步即可
从库如果出现宕机，在数据文件中输出mysqlbinlog localhost-relay-bin.000001,然后输出查看进行到哪个节点了，对比主库二进制日志进行数据恢复
生产环境中一般要加入从库，先把主库宕掉，然后把主库数据复制到从库中，再配置主从复制
常见报错
从机没开启bin-log日志
主主备份（继续上个实验）
10、11相互为主
10、11配置文件增加一条内容
两台主机重启服务
systemctl restart mariadb
11给10授权，10接受授权
两端登录：mysql
11授权
grant replication slave on . to slave@'192.168.1.%' identified by '123';
show master status;
10接受
change master to master_user='slave',master_password='123',master_host='192.168.1.11',master_log_file='mysql-bin.000004',master_log_pos=393;
开启slave
start slave;
show slave status\G
检测方式：
10端创建一个数据库，11端同步，11端删除这个数据库，10端同步
一主多从
再开一台192.168.1.12服务器
先把10的slave关闭，让10成为主，其他两台都为从
slave stop;
12服务器端操作
yum -y install mariadb mariadb-server
vim /etc/my.cnf
log-bin=mysql-bin
server-id=12
systemctl start mariadb
mysql
查看10端状态
show master status;
12端根据master端继续操作
change master to master_user='slave',master_password='123',master_host='192.168.1.10',master_log_file='mysql-bin.000004',master_log_pos=330;
start slave;
show slave status\G
测试：10端创建一个库，查看是否同步到12端
多主一从(多源复制)
简介
优势：可以节约成本，可以作为多个主库的备用从库
缺点：
用于做OLAP（联机分析处理）和OLTP（联机数据分析）
共用从库会导致数据混乱
难以维护
目前设置10为主，11为主，12为从
三台机子同时下载mariadb并配置文件
yum -y install mariadb mariadb-server
10端
vim /etc/my.cnf
log-bin=mysql-bin
server-id=10
11端
vim /etc/my.cnf
log-bin=mysql-bin
server-id=11
12端
vim /etc/my.cnf
10，11端授权
10端
systemctl start mariadb;systemctl enable mariadb
mysql
grant replication slave on *.* to slave@'192.168.1.12' identified by '123';
show master status;
11端
systemctl start mariadb;systemctl enable mariadb
mysql
grant replication slave on *.* to slave@'192.168.1.12' identified by '123';
show master status;
12初始化授权
mysql_install_db --datadir=/var/lib/mysqla --user=mysql
mysql_install_db --datadir=/var/lib/mysqlb --user=mysql
检查授权是否成功
cd /var/lib/;ls
设置mysqla,mysqlb目录及以下文件的属主为mysql(防止出现权限问题)
chown -R mysql /var/lib/mysqla/
chown -R mysql /var/lib/mysqlb/
启动从服务器线程
mysqld_multi --defaults-file=/etc/my.cnf start 10
mysqld_multi --defaults-file=/etc/my.cnf start 11
查看端口号
netstat -anpt
可以看到3306和3307端口号都开启
新建一个会话框各自登录各自的服务器
mysql -uroot -P 3306 -S /var/lib/mysqla/mysql.sock
mysql -uroot -P 3307 -S /var/lib/mysqla/mysql.sock
两个线程各自接受各自的授权
让第一个db（3306开启的mysqla）接受10，让第二个db（3306开启的mysqlb）接受11
第一个db
change master to master_user='slave',master_password='123',master_host='192.168.1.10',master_log_file='mysql-bin.000003',master_log_pos=394;
start slave;第二个
change master to master_user='slave',master_password='123',master_host='192.168.1.11',master_log_file='mysql-bin.000003',master_log_pos=394;
start slave;如图
测试
两台主服务器创建，从服务器查看
10端创建abcd，11端创建bcde。12端不同的db中都进行同步
主从复制架构原理
一些问题
中继日志默认是有SQL线程管理，执行完自动会清理怎么办？（如MHA）
如果不想自动清理，设置relay_log_purge=0关闭
主从复制有延迟的原因
默认是异步复制
一主多从中，从库会增加主库的IO和网络压力，为什么？
主库需要发送更多的二进制日志，会增加主库的IO压力
解决方式：配置M-M-S级联模式
如何增加一台从库
全量备份之前锁表只读（防止主库再次进行持续写入造成的数据不一致）
flush tables with read lock;
添加从库，进行全量备份
主库解锁：unlock tables;
发布者：LJH，转发请注明出处：https://www.ljh.cool/6407.html
