运维对数据库的一些要求（安装，部署，权限，mysqldump，xtrabackup的备份和还原）

发布时间：2023年12月20日

一、安装部署

1.mysql rpm下载：https://downloads.mysql.com/archives/community/
2.卸载linux自带mariadb
通过以下代码可以查看mariadb： rpm -qa | grep mariadb
卸载mariadb：yum remove mariadb-libs-5.5.52-1.el7.x86_64 -y
再次查看是否卸载成功：rpm -qa | grep mariadb

创建mysql目录到/usr/local目录下：mkdir mysql
上传安装包到/usr/local/mysql中，进行解压：
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

按照依赖关系依次安装rpm包依赖关系依次common→libs→client→server

按照顺序依次输入下列命令
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
yum install -y net-tools
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

安装成功后输入mysql --version查看
mysql数据库yum源安装
数据库5.7版本 wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

二、数据库的权限分配

1.密码策略

MySQL5.7为root用户随机生成了一个密码，打印在error_log中，关于error_log的位置，如果安装的是RPM包，则默认是 /var/log/mysqld.log。
查看系统随机生成的密码：

grep 'password' /var/log/mysqld.log

查看数据库当前密码策略：

show VARIABLES like "%password%";

官方文档策略定义：

Policy	Tests Performed
0 or LOW	Length
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary file

更改密码策略的方法：
方法1:临时修改

更改密码策略为LOW，改为LOW或0
mysql> set global validate_password.policy=low;
Query OK, 0 rows affected (0.01 sec)
更改密码长度
mysql> SET GLOBAL validate_password_length=0;
Query OK, 0 rows affected (0.00 sec)
mysql>alter user ‘root’@‘localhost’ identified by ‘你自己得mysql密码’;

方法2：修改配置文件
在 /etc/my.cnf 配置文件中增加：

[mysqld]
validate_password=off

2.MySQL中授权(grant)和撤销授权(revoke）

create和grant结合使用

mysql> create user test@'%' identified by 'redhat';
mysql> create database test;

grant 权限列表 on 库名.表名 to 用户名@'客户端主机'
mysql> grant select, insert, update, delete on test.* to test@'%';
mysql> show grants for test@'%';   查看为test用户授予的权限
更改权限设置后需要刷新权限：FLUSH PRIVILEGES
查看用户权限： help SHOW GRANTS;

回收权限：

回收test用户的插入权限
mysql> revoke insert on test.* from test@'%';

删除用户：DROP USER username

三、数据库的备份还原

数据库在丢失或者损坏的时候需要还原。

1、mysqldump备份数据库

mysqldump备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）
（MyISAM是温备份，InnoDB是热备份）

mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级
时相对比较合适，这也是最常用的备份方法。
备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于
重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线
上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave
机器上做备份。

show create table Student; ---------------查看建表的信息

全量备份所有数据库:
mysqldump -uroot -predhat --all-databases > /mysqlfile/2023-11-20.sql
还原所有数据库
mysql -uroot -predhat < /mysqlfile/2023-11-20.sql

全量备份指定数据库:
mysqldump -uroot -predhat it > /mysqlfile/11.sql
还原的时候不用指定数据库，因为有建表sql：
mysql -uroot -predhat  < /mysqlfile/2.sql

mysql> show variables like ‘%log%’; 查看二进制日志开启没有，需要开启，否则不能执行增量更新
–增量备份：开启binlog，记录增删改的操作

mysqldump全量备份后需要刷新日志来记录增量备份的数据日志,在数据库出现问题后，恢复数据之前也需要刷新日志。
方法1：

[root@localhost ~]# mysqldump -uroot -predhat -F -B it > /mysqlfile/1.sql
参数 -F   在备份开始时,刷新一个新binlog日志
参数 -B   指定数据库
方法2：
mysqladmin -uroot -predhat flush-logs
方法3：
mysql> flush logs

InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性

常用:
mysqldump -uroot -p --default-character-set=utf8 --single-transaction -F -B
school > backup.sql

mysql> show master status; 查看当前数据库使用日志文件
mysql> show binlog events in ‘binlog.000007’; 查看日志的事件

一切准备就绪：
先创建数据库和表，并写入数据；全量备份；插入数据；删除数据库；全量备份恢复；增量备份恢复。
mysqlbinlog增量恢复方式

mysqlbinlog /var/lib/mysql/binlog.000013

基于时间点恢复
1）指定开始时间到结束时间
myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ --stopdatetime=’2014-10-45 03:10:46’-r time.sql
2）指定开始时间到文件结束
myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ -d esen -r
time.sql
3）从文件开头到指定结束时间
myslbinlog mysqlbin.000008 --stop-datetime=’2014-10-45 03:10:46’ -d esen -r
time.sql
基于位置点的增量恢复
1）指定开始位置到结束位置
myslbinlog mysqlbin.000008 --start-position=510 --stop-position=1312 -r pos.sql
2）指定开始位置到文件结束
myslbinlog mysqlbin.000008 --start-position=510 -r pos.sql
3）从文件开始位置到指定结束位置
myslbinlog mysqlbin.000008 --stop-position=1312 -r pos.sql

认识mysql的日志类型：

日志类型	记入文件中的信息类型
错误日志	记录启动、运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录所有更改数据的语句。主要用于复制和即时点恢复
慢日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

2、Percona XtraBackup备份数据库

yum源安装：网站： https://repo.percona.com/yum/

Percona XtraBackup 下载网站：https://repo.percona.com/yum/
https://www.percona.com/downloads
官方下载太慢

1、Percona XtraBackup的介绍

无论是 24x7 高负载服务器还是低事务量服务器环境中，Percona XtraBackup 旨在实现无缝备份过程，而不会中断生产中服务器的性能环境。Percona XtraBackup （PXB）是一个 100% 开源备份解决方案，为希望从 MySQL 的全面、响应迅速且经济灵活的数据库支持中受益的组织提供商业支持。

Percona XtraBackup 是世界上唯一的开源免费 MySQL 热备份为 InnoDB 和 XtraDB 执行非阻塞备份的软件数据库。使用 Percona XtraBackup，您可以获得以下优势：

快速可靠地完成备份
备份期间不间断的事务处理
节省磁盘空间和网络带宽
自动备份验证
由于恢复时间更快，正常运行时间更长

xtrabakackup有3个工具，分别是
xtrabakup、
innobakupex、

xbstream 通过流数据功能，可将备份内容打包并传给管道后的压缩工具进行压缩
xtrabackup 支持InnoDB，XtraDB
innobakupex 支持Myisam,InnoDB、XtraaDB
xbstream：以专有格式压缩 xtrabackup 输出的信息

支持的存储引擎：
Percona XtraBackup 可以备份 MySQL 8.0 服务器以及 Percona Server for MySQL with XtraDB、Percona Server for MySQL 8.0 和 Percona XtraDB Cluster 8.0 上的 InnoDB、XtraDB、MyISAM 和 MyRocks 表中的数据。
局限性：

1、Percona XtraBackup 8.0 目前不可能同时支持 8.0 之前的版本
2、Percona XtraBackup 2.4 支持 MySQL 和 Percona Server for MySQL 5.6 和 5.7 数据库
3、Percona XtraBackup 8.1 不支持对数据库进行备份 在 MySQL 8.1 之前的版本中创建，Percona Server for MySQL 或 Percona XtraDB 集群。

2、Percona XtraBackup安装

用阿里或者清华大学percona源yum安装Percona XtraBackup==
事例：我用的redhat9安装这个软件
写源仓库：
cat <>/etc/yum.repos.d/percona.repo
[percona]
name = Percona
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0

[epel]
name=epelrepo
baseurl=https://mirrors.aliyun.com/epel/$releasever/$basearch
gpgcheck=0
enable=1
eof

[root@manged ~]# yum list all| grep xtraback -i    查看xtraback有哪些软件包
报错
Errors during downloading metadata for repository 'epel':
  - Status code: 404 for https://mirrors.aliyun.com/epel/9/x86_64/repodata/repomd.xml (IP: 118.112.22.214)
错误：为仓库 'epel' 下载元数据失败 : Cannot download repomd.xml: Cannot download repodata/repomd.xml: All mirrors were tried

去epel官网：https://docs.fedoraproject.org/en-US/epel/ 下载额外的软件包。

dnf install
https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm

如果安装失败，就下载下来rpm安装：

[root@manged ~]# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-9.noarch.rpm
[root@manged ~]# rpm -ivh epel-release-latest-9.noarch.rpm 
可以[root@manged ~]# yum repolist   查看

[root@manged ~]# yum list all| grep xtraback -i   好了耶
Repository epel is listed more than once in the configuration
holland-xtrabackup.noarch                                                                1.2.10-1.el9                                   epel               
percona-xtrabackup-24.x86_64                                                             2.4.28-1.el9                                   percona            
percona-xtrabackup-24-debuginfo.x86_64                                                   2.4.28-1.el9                                   percona            
percona-xtrabackup-24-debugsource.x86_64                                                 2.4.28-1.el9                                   percona            
percona-xtrabackup-80.x86_64                                                             8.0.35-30.1.el9                                percona            
percona-xtrabackup-80-debuginfo.x86_64                                                   8.0.35-30.1.el9                                percona            
percona-xtrabackup-80-debugsource.x86_64                                                 8.0.35-30.1.el9                                percona            
percona-xtrabackup-81.x86_64                                                             8.1.0-1.1.el9                                  percona            
percona-xtrabackup-81-debuginfo.x86_64                                                   8.1.0-1.1.el9

[root@manged ~]# yum install percona-xtrabackup-80
安装对应数据库版本的，速度快多了。

3、Percona XtraBackup8.0的使用

常用选项:  
   --host     指定主机
   --user     指定用户名
   --password    指定密码
   --port     指定端口
   --databases     指定数据库
   --incremental    创建增量备份
   --incremental-basedir   指定包含完全备份的目录
   --incremental-dir      指定包含增量备份的目录   
   --apply-log        对备份进行预处理操作 
   --defaults-file	  指定默认配置文件，默认是读取/etc/my.cf

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
–redo-only 不回滚未提交事务
–copy-back 恢复备份目录

1.全库备份和还原

一、全库备份
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup -uroot -predhat -H localhost -P 3306

-p 后面是mysql的密码 -H 后面是主机，支持远程备份，-P是端口

   如果出现如下问题是在没有创建/data/backup/目录
2023-12-13T18:05:19.164770+08:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-12-13T18:05:19.257242+08:00 0 [ERROR] [MY-011825] [Xtrabackup] cannot mkdir: 2 /data/backup/

出现的这个没有权限问题：

2023-12-13T11:40:23.815725-05:00 0 [ERROR] [MY-011825] [Xtrabackup] failed to execute query 'LOCK INSTANCE FOR BACKUP' : 1227 (42000) 
Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation

解决方法：

GRANT BACKUP_ADMIN ON *.* TO 'root'@'localhost';  
FLUSH PRIVILEGES;

二、数据库恢复
删掉数据库：

先停掉数据库systemctl stop mysqld
rm -rf  /var/lib/mysql/*         mysql这个目录的所有者，所属组都是mysql   
这个目录删除之后，重启数据库就是一个全新的数据库

启动数据库，这时是一个全新的数据库。

备份之前要删除/var/lib/mysql/* 的文件，并且先停掉数据库，还原的时候数据库dead状态
先备份备份集：
备份后的数据并不处于一致性状态，为了可以用来恢复，需要使用-prepare准备备份集
[root@redhat ~]# xtrabackup --prepare --target-dir=/data/backup 准备数据备份集
[root@redhat ~]# xtrabackup --copy-back --target-dir=/data/backup 备份
然后chown mysql:mysql /var/lib/mysql/ -R
再重启数据库就完成了。非常完美！

注意：好像数据库版本不一样，还原的时候会出错

2.增量备份和还原

解释：target-dir=/data/inc1中的inc1目录不用自己创建，--incremental-basedir=/data/backup增备份是在全备的基础上做得，这个参数是填全备的路径，全备和增备的目录要分开，不能放在一起。

增备份2：
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/inc2 --incremental-basedir=/data/inc1 -uroot -predhat -H localhost -P 3306

第二天也做了增量备份 ，incremental-basedir=/data/inc1，这里的参数是参照增备1里备份的，所以这个参数是填增备1的路径。

备份好后会在/data出现3个目录，一个是全备的数据，还有个增备1的数据，还有个增备2的数据。

恢复数据：
准备还原命令：
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/

xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup --incremental-dir=/data/inc1/

xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup --incremental-dir=/data/inc2/

以上命令就是把增量备份合并到完全备份的日志里，到时候直接还原完全备份的目录数据就行

注意：最好一次把增量备份恢复到全部不要加 --apply-log-only，不然会出现问题，说你没有准备好。

恢复数据到目录：
停止数据库并清空数据库目录
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/backup 恢复
chown mysql:mysql /var/lib/mysql/ -R 把文件的所有文件属组改成mysql

重启数据库，所有的数据都在，实验完成！👌
遇到问题：
当最后一次增备加了 --apply-log-only，还原的时候会报这个错误：

2023-12-15T08:03:29.481391-05:00 0 [Note] [MY-011825] [Xtrabackup] cd to /datas/backup/
2023-12-15T08:03:29.481464-05:00 0 [ERROR] [MY-011825] [Xtrabackup] The target is not fully prepared. Please prepare it without option --apply-log-only

解决方法：
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/datas/backup/
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/datas/backup
解释：也就是在做一次全备份，并且全备的时候删除--apply-log-only，再进行还原，这时候就成功了。

3.差异备份和还原

先进行全备：
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup -uroot -predhat -H localhost -P 3306
增备份1：
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/inc1 --incremental-basedir=/data/backup -uroot -predhat -H localhost -P 3306
增备份2：
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/inc2 --incremental-basedir=/data/backup -uroot -predhat -H localhost -P 3306

解释：差异备份跟增量备份其实差不多，只不过增量备份每次的参照物是前一个，而差异备份的参照物就是全备

恢复数据：
准备还原命令：
xtrabackup --defaults-file=/etc/my.cnf --prepare --apply-log-only --target-dir=/data/backup/

xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup --incremental-dir=/data/inc2/

解释：准备还原只需要第一次备份和最后一次备份就行

4.差异备份和增量备份的区别

在这里插入图片描述
总结一下，感觉差异备份更好用。差异备份都是基于全备备份的，而且还原的时候只需要准备全备和最后一次增量备份就行。

5.压缩备份和还原

xtrabackup --defaults-file=/etc/my.cnf --backup --compress --target-dir=/data/backup/compressed/ -uroot -predhat -H localhost -P3306
解释：compress就是压缩的命令，因为数据库的备份的数据比较大，占用的空间也比较多，而压缩就能减少文件大小。

xtrabackup --defaults-file=/etc/my.cnf --backup --compress-threads=4 --target-dir=/data/backup/compress/ -uroot -predhat -H localhost -P3306
如果要加速压缩，可以使用--compress-threads=4 ，使用四个线程同时进行压缩

解压缩和还原：
xtrabackup --defaults-file=/etc/my.cnf --decompress --target-dir=/data/backup/compressed/
解释:解压命令 --decompress，使用这个压缩的备份集在准备备份之前需要解压，解压工具是qpress（报错就安装这个），解压的原来的文件不会被删除，可以使用 --remove-original选项清除。

xtrabackup --prepare --target-dir=/data/backup/compressed/ 准备数据集
xtrabackup --copy-back --target-dir=/data/backup/compressed 还原

解释：跟全备一样的还原
chown mysql:mysql /var/lib/mysql/ -R 把文件的所有文件属组改成mysql
重启数据库，所有的数据都在，压缩备份实验完成！👌

文章来源:https://blog.csdn.net/qq_62331653/article/details/134505768
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：chenni525@qq.com进行投诉反馈，一经查实，立即删除！