当前位置：首页 > article >正文

mysql的备份和还原

article 2024/12/27 3:19:46

一、事务控制语句

1.控制语句

begin

显示开始一个事务。

commit

提交事务，所有的修改都是永久性的。

rollback

对事务进行回滚，返回到上一次的操作，只能回滚正在进行未提交的修改。

savepoint

在事物当中创建一个回滚点，回滚到指定的位置，一个事务当中可以有多个回滚点。

savepoint 自定义名称

savepoint s1;

rollback to s1;

savepoint 在事务当中是一个临时的标记，在事务执行过程中的一个回滚点，可以有多个回滚点，是按照顺序执行的，前面的回滚点一旦执行，后续的回滚点就会全部失效。

mysql默认是自动提交模式，这意味着每个 sql 语句都会立即提交到数据库。

#关闭自动提交

SET autocommit = 0;

2.行锁和死锁

行锁

innodb通过给索引加锁来实现行级的锁定。

如果没有索引，会使用隐藏的聚簇索引来实现锁定。

如果A事务对索引字段进行操作，就会触发聚簇索引，锁定整行，B事务对这个行进行的操作就会被锁住。

死锁

两个事务互相等待对方的资源，形成了一个环路导致的。

模拟死锁

在终端mysql里面和navicat客户端同时操作

#navicat建表，做id的主键索引和name的普通索引

create table if not exists student(
id int(5) PRIMARY KEY,
name char(8),
age int(3),
sex char(2)
);

CREATE INDEX name_index on student (name);
insert into student values(1,'小明',18,'男');
insert into student values(2,'小红',19,'女');
insert into student values(3,'小刚',19,'男');
insert into student values(4,'小绿',17,'女');
insert into student values(5,'小黑',20,'男');

#终端操作：

begin;

delete from student where id=5; #此时id=5的数据行已经因为主键索引锁住了

#navicat操作：

begin;

select * from student where id =1 for update;

#for update:设置一个排他锁，在当前事务未提交前，禁止事务的写入和更新操作。

#终端操作：

delete from student where id=5; #冲突了，无法执行

#navicat操作：

update student set name='abc' where id=5; #也有行锁

commit;

#终端操作：

commit;

#死锁发生会自动选择一个事务当中的语句进行执行，直接终止其中的一个事务，回滚另外一个事务，以解除死锁。

如何避免死锁的发生

按照固定的顺序对表和行进行访问
大事务拆成小事务，业务允许的情况下
调整事务的默认隔离级别，如果业务允许，隔离级别越低越好。
要给表添加合适的索引（不是经常需要访问的字段最好不要设置索引）

二、mysql的备份和还原*

1.数据库备份的分类

（1）物理备份

冷备份：关闭数据库进行备份，全量备份

（2）逻辑备份

热备份：数据库可以正常运行，不影响数据库的操作，在这个条件下可以实现数据的备份和恢复。

2.备份策略

（1）全量备份

cd /usr/local/mysql/data

#把mysql的data目录打包，打包的格式.xz，保存到指定目录下

tar -Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data

tar Jxvf mysql_all_2024-12-20.tar.xz

（2）增量备份

冷备份只能进行全量备份，逻辑备份既可以实现全量，也可以实现增量。

增量备份：备份指定的库、表，或者指定时间、位置节点的数据等。

3.mysqldump的备份和恢复*

mysqldump是针对库和表的全量和增量，热备份。

把备份的库和表以文件的形式导出，再从导出的文件进行恢复。

先备份再恢复

对库进行备份

mysqldump -u root -p123456 --databases test1 > /opt/test1.sql #单个库

mysqldump -u root -p123456 --databases test1 test2 > /opt/test2.sql #多个库用空格隔开

#在navicat里面删除库

mysql -u root -p123456 < /opt/test1.sql #重新导入

mysql -u root -p123456 < /opt/test2.sql #重新导入

对库里的表进行备份

mysqldump -u root -p123456 库名表名 > /opt/*.sql

mysql -u root -p123456 库名 < /opt/*.sql #重新导入，可以导入到任意库

不登录数据库也可以执行查看，在终端外面用命令行操作

4.二进制日志（数据）的备份和恢复*

（1）开启二进制日志功能

vim /etc/my.cnf

log-bin=mysql-bin

#开启二进制日志的功能，二进制文件的名称mysgl-bin.0000001

binlog_format=MIXED

systemctl restart mysql

#二进制日志不能用cat查看

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001

二进制日志的记录格式

ROW 基于行

记录每一行的数据，准确，但是恢复效率低。

STATEMENT 基于sql语句

按照顺序对sql语句进行记录，高并发的情况下，sql的记录顺序可能会出错，可能会导致数据的记录有偏差，虽然恢复的效率比较高

MIXED 混合

正常情况下使用STATEMENT，高并发时自动切换到ROW的记录方式。

（2）日志断点

mysqladmin -u root -p123456 flush-logs #刷新二进制日志

#会多出来了一个mysql-bin.000002

#刷新之后前面的二进制日志文件不会再写入数据了

（3）恢复

#如下图创建一张名为student的表，插入5行数据，并删除前3行数据

#在终端里刷新日志

mysqladmin -u root -p123456 flush-logs

#所有操作就都保存在了mysql-bin.0000002里面

#此时删除student表

mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p123456

#注意当前文件夹路径为/usr/local/mysql/data下。如果不是该路径，就直接写绝对路径

（4）基于位置的恢复

从一个节点到最后；从开始到一个节点；从一个节点到一个节点

#student表如上

#calss表

create table if not exists calss(
id int(5),
name char(8),
age int(3)
);
insert into calss values(1,'小美',18);
insert into calss values(1,'小壮',17);

#刷新日志之后删除student表和calss表

mysqlbinlog --no-defaults --start-position='6438' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p123456 #从指定位置开始恢复数据，一直恢复到文件的结尾

mysqlbinlog --no-defaults --stop-position='5012' mysql-bin.000003 | mysql -u root -p123456 #从开始恢复到指定位置

mysqlbinlog --no-defaults --start-position='3510' --stop-position='4031' mysql-bin.000003 | mysql -u root -p123456 #从一个节点恢复到指定节点的位置

（5）基于时间点的恢复

#和基于位置的恢复差不多

mysqlbinlog --no-defaults --start-datetime='2024-12-20 13:47:17' mysql-bin.000003 | mysql -u root -p123456

mysqlbinlog --no-defaults --stop-datetime='2024-12-20 14:45:13' mysql-bin.000003 | mysql -u root -p123456

mysqlbinlog --no-defaults --start-datetime='2024-12-20 13:43:12' --stop-datetime='2024-12-20 14:48:26' mysql-bin.000003 | mysql -u root -p123456

5.开启mysql其他日志

vim /etc/my.cnf

error-log=ON

log-error=/usr/local/mysql/data/mysql-error.log #记录mysql在启动，停止或者是运行时错误的日志

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

#开启mysql在使用过程中的记录日志

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

long_query_time=5

#开启慢查询日志，用来记录所有查询的时间超过long_query_time=5的记录，查询的执行时间超过5秒就会记录

#不注明，默认是10s

systemctl restart mysql