文章目录
一、mysql数据库的日志
mysql的日志默认保存位置为/usr/local/mysql/data
1.1mysql日志类型和作用
- redo(重做日志)
- 达到事务一致性,每次重启之后会重做
- 作用:
确保日志的持久性,防止在发生故障,脏页未写入磁盘,重启数据库会进行redo log执行重做,达到事务一致性
- undo(回滚日志)
- 作用:
保证数据的原子性,记录事务发生之前的一个版本,用于回滚,innodb事务可重复读和读取已提交 ,隔离级别就是通过mvcc+undo实现
- errorlog 错误日志
- 作用:
Mysql本身启动,停止,运行期间发生的错误信息
- slow query log 慢查询日志
- 作用:
记录执行时间过长的sql,时间阈值(10s)可以配置,只记录执行成功
另一个作用:在于提醒优化
- bin log 二进制日志
- 作用:用于主从复制,实现主从同步记录的内容是:数据库中执行的sql语句
- relay log 中继日志
- 作用:用于数据库主从同步,将主库发来的bin log保存在本地,然后从库进行回放
7.general log 普通日志
- 作用:记录数据库的操作明细,默认关闭,开启后会降低数据库性能
1.2mysql配置文件
[mysqld]
##错误日志,用来记录当MySQL启动、停止或运行时发生的错误信息,默认已开启
log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名
##通用查询日志,用来记录MySQL的所有连接和语句,默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
##二进制日志(binlog),用来记录所有更新了数据或者已经潜在更新了数据的语句,记录了数据的更改,可用于数据恢复,默认已开启
log-bin=mysql-bin
或
log_bin=mysql-bin
##中继日志
一般情况下它在Mysql主从同步(复制)、读写分离集群的从节点开启。主节点一般不需要这个日志
##慢查询日志,用来记录所有执行时间超过long_query_time秒的语句,可以找到哪些查询语句执行时间长,以便提醒优化,默认是关闭的
s1ow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #设置超过5秒执行的语句被记录,缺省时为10秒
[root@localhost ~]# systemctl restart mysqld //重启
1.3 查询日志
- 查看通用查询日志是否开启
mysql> show variables like 'general%';
- 查看二进制日志是否开启
mysql> show variables like 'log_bin%';
- 查看慢查询日志功能是否开启
mysql> show variables like 'slow%';
- 查看慢查询时间设置
mysql> show variables like 'long_query_time%';
- 在数据库中设置开启慢查询的方法
mysql> set global slow_query_log=ON;
注意:variables表示like表示模糊查询
- 二进制日志开启后,重启mysql会在目录中查看到二进制日志
[root@localhost bin]# cd /usr/local/mysql/data
[root@localhost data]# ls
[root@localhost data]# cat mysql-bin.index
①重启mysql的时候会更新索引文件,用于记录新的更新语句
②索引二进制日志
mysql-bin.index //二进制日志文件的索引
二、数据库备份与恢复实验
实验环境
use school;
create table if not exists info (
id int(4) not null auto_increment,
name varchar(10) not null,
age char(10) not null,
hobby varchar(50),
primary key (id)); //创建表info
insert into info values(1,'user1',20,'running');
insert into info values(2,'user2',30,'singing');
2.1物理冷备份与恢复
- 压缩备份
[root@localhost data]# systemctl stop mysqld //关闭数据库
[root@localhost data]# yum -y install xz //安装以xz的格式
[root@localhost data]# tar Jcvf /opt/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/ //压缩备份
mv /usr/local/mysql/data/ /opt/
[root@localhost opt]# systemctl start mysqld //开启数据库
[root@localhost mysql]# mysql -uroot -p //登录数据库
mysql> show databases;
mysql> drop database school;
mysql> show databases;
[root@localhost opt]# cd /usr/local/mysql/data/
[root@localhost data]# ls
[root@localhost data]# cd ..
[root@localhost mysql]# ls
- 解压恢复
[root@localhost mysql]# tar Jxvf /opt/mysql_all_2021-07-19.tar.xz -C /usr/local/mysql/data/ //解压恢复
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./
2.2mysqldump备份(温备份)
(1)完全备份一个或多个完整的库 (包括其中所有的表)
- 在命令行对表的内容进行操作
[root@localhost data]# mysql -u root -pabc123 -e 'show databases;'
mysqldump -u root -p[密码] --databases 库名1 [库名2] ... > /备份路径/备份文件名.sql #导出的就是数据库脚本文件
- 备份一个库
[root@localhost data]# mysqldump -u root -pabc123 --databases school > /opt/school.sql
- 备份mysql与 kgc两个库
[root@localhost data]# mysqldump -u root -pabc123 --databases mysql school > /opt/mysql-school.sql
(2)完全备份MySQL服务器中的所有库
格式
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
mysqldump -u root -pabc123 --all-databases > /opt/all.sql
(3)完全备份指定库中的部分表
格式
mysqldump -u root -p[密码] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
[root@localhost opt]# mysqldump -u root -pabc123 school info > /opt/info.sql
[root@localhost opt]# mysqldump -u root -pabc123 [-d] school info > /opt/info.sql
//使用“-d”选项,说明只保存数据库的表结构
//不使用“-d”选项,说明表数据也进行备份
//作为一个表结构模版
(4)查看备份文件
[root@localhost opt]# grep -v "^--" /opt/school.sql | grep -v "^/" | grep -v "^$"
2.3恢复数据库
- 使用dump导出的文件,可使用导入的方法,source命令和mysql命令
- 使用source恢复数据库的基本步骤
登录到MySQL数据库,执行source备份SQL脚本的路径
mysql> show databases; //查看数据库信息
mysql> drop database school;删除数据库school
mysql> show databases;
mysql> source /opt/school.sql; //恢复数据库信息
3.使用musql命令
- 无须登录mysql数据库
- 使用-e删除数据表
- 执行mysql备份SQL脚本的路径
配置方法一 - 创建备份(对表进行备份)
[root@localhost opt]# mysqldump -uroot -pabc123 school info >/opt/info.sql //对iNfo表备份
[root@localhost opt]# mysql -uroot -pabc123 //登录数据库查看
- 恢复数据表
mysql> source /opt/info.sql
mysql> select * from info; 查询所有字段
mysql> show tables; 查看表信息
配置方法二
[root@localhost opt]# mysqldump -uroot -pabc123 school info >/opt/school.info.sql
[root@localhost opt]# mysql -uroot -pabc123 -e 'drop table school.info;'
[root@localhost opt]# mysql -uroot -pabc123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
mysql -uroot -pabc123 -e 'show tables from school;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
2.4 mysqldump归纳
① 当备份时加 --databases ,表示针对于school库
#备份命令
mysqldump -uroot -p123123 --databases school > /opt/school_01.sql 备份库后
#恢复命令过程为:
mysql -uroot -p123123
drop database school;
exit
mysql -uroot -p123123 < /opt/ky11_01.sql
② 当备份时不加 --databases,表示针对school库下的所有表
#备份命令
mysqldump -uroot -p123123 school > /opt/ky11_all.sql
#恢复过程:
mysql -uroot -p123123
drop database school;
create database school;
exit
mysql -uroot -p123123 school < /opt/school_02.sql
四、mysql增量备份与恢复
1.一般恢复
将所有备份的二进制日志内容全部恢复
2.基于位置恢复
- 数据库在某一时间点可能既有错误的操作也有正确的操作
- 可以基于精准的位置跳过错误的操作
- 发生错误节点之前的一个节点,上一次正确操作的位置点停止
3.基于时间点恢复
- 跳过某个发生错误的时间点实现数据恢复
- 在错误时间点停止,在下一个正确时间点开始
五、MySQL增量备份实验
5.1开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED #可选,指定二进制日志(binlog)的记录格式为MIXED(混合输入)
server-id = 1 #可加可不加该命令
#二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
① STATEMENT(基于SQL语句):
每一条涉及到被修改的sql 都会记录在binlog中
缺点:日志量过大,如sleep()函数,last_insert_id()>,以及user-defined fuctions(udf)、主从复制等架构记录日志时会出现问题
② ROW(基于行)
只记录变动的记录,不记录sql的上下文环境
缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大
create table info
insert into info values(1,'zhangsan');
insert into info values(2,'lisi');
delete id=zhangsan
③ MIXED 推荐使用
一般的语句使用statement,函数使用ROW方式存储。