MySQL权限管理和MySQL备份

五、MySQL权限管理

·设置密码安全级别

# root 根用户 (超级管理员)、用户信息表：mysql.user

# 刷新权限

flush privileges;

# 安装并激活密码规则插件

install plugin validate_password soname 'validate_password.so';

# 查看密码强度

show variables like 'validate_password%';

# 设置密码强度为低，但是密码至少8位

set global validate_password_policy = 0;或者

set global validate_password_policy = low;

·创建新用户

create user 用户名@地址 identified by [password] 密码(字符串) ;

create user ‘tom’@’localhost’ identified by ‘12345678’;

create user ‘lisi’@’%’ identified by ‘lisi@123’;

# mysql中的用户由两部分构成

username 例如：root

host      例如：localhost（本机）、192.168.221.20、192.168.221.%、%

# 修改用户名

rename user old_user to new_user;

eg：rename user tom to tom2;

# 删除用户

drop user 用户名;       eg：drop user tom2;

# 查看用户/查询mysql.user表

select user,host from mysql.user;

·修改密码

# mysql5.X版本修改密码

# 设置管理员密码(mysql8.0之后删除了该语法)

set password = password ( '12345678' );

# 为指定用户设置密码

set password for 用户名 = password ( '密码' );

eg：set password for 'tom'@'%' = password ( '12345678' );

# mysql8.0之后版本修改密码

alter user '用户'@'地址' identified by '密码';

eg：alter user 'tom'@'%' identified by '12345678';

# 刷新权限

flush privileges;

·给用户设置权限

格式：grant 权限列表 on 库名.表名 to ‘用户’@’地址’;

# 赋予tom所有权限

grant all privileges on *.* to "tom" @"%";

# 赋予tom插入数据、查询数据的权限

grant insert,delete,update,select on *.* to "tom" @"%";

# 查看用户的权限

show grants;

# 查看指定某用户的权限

show grants for 用户名;

·删除权限

# 撤销用户的某个权限

revoke 权限列表 on 库名.表名 from 用户名;

# 撤销用户的所有权限

revoke all privileges,grant option on 库名.表名 from 用户名;

eg：revoke all privileges on *.* from ‘tom’@’localhost’;

---

·最小权限与角色分工

1、只给需要的权限（最小权限原则）

错误做法：grant all on *.* to 'user'@'%'（给用户所有权限）

正确做法：grant select, insert on app_db.users to 'web_app'@'%'（只允许查询和插入用户表）

比喻：就像员工只能打开自己办公区域的门，不能进财务室。

2、按角色分配权限（角色化管理）

创建角色：create role 'developer', 'readonly'

分配权限：grant select on app_db.* to 'readonly'

关联用户：grant 'developer' to 'team_member'@'%'

比喻：不同职位（如开发、测试、财务）有不同的钥匙串。

---权限列表如下

权限名称	权限说明
all [privileges]	设置除grant option（授予）之外的所有简单权限
alter	允许使用 alter table
alter routine	更改或取消已存储的子程序
create	允许使用 create table
create routine	创建已存储的子程序
create temporary tables	允许使用 create temporary table
create user	允许使用 create user、drop user、rename user 和 revoke all privileges
create view	允许使用 create view
delete	允许使用 delete，删除表中内容的权限
drop	允许使用 drop table
execute	允许用户运行已存储的子程序
file	允许使用 select...into outfile 和 load data infile
index	允许使用 create index 和 drop index
insert	允许使用 insert，在表中添加内容的权限
lock tables	允许对拥有 select 权限的表使用 lock tables
process	允许使用 show full processlist
references	未被实施
reload	允许使用 flush
replication client	允许用户询问从属服务器或主服务器的地址
replication slave	用于复制型从属服务器（从主服务器中读取二进制日志事件）
select		允许使用 select，查询表内容的权限
show databases	显示所有数据库
show view	允许使用 show create view
shutdown	允许使用 mysqladmin shutdown
super	允许使用 change master、kill、purge master logs 和 set global 语句，mysqladmin debug 命令；允许连接（一次），即使已达到 max_connections
update		允许使用 update，修改表内容的权限
usage	“无权限” 的同义词
grant option	允许授予权限

六、MySQL备份

·为了防止数据丢失、方便数据迁移，建议定期备份数据库中的数据。

·方式：

·逻辑备份：将数据库中库和表转换为SQL语句，生成.sql文件。还原数据时只需要运行该SQL语句。适合中小型规模的数据，30GB以内的数据。数据量过大时，转换效率慢。

·物理备份：将数据库的data目录，直接复制备份。

     ·冷备份：关闭MySQL-Server后进行的备份。

     ·热备份：使用专业的数据备份根据，在服务运行中备份。

     ·全量备份：每次备份都备份所有的数据。

     ·增量备份（差异备份）：只备份新增的数据或修改的数据。

使用MySQLdump工具进行备份（逻辑备份）

mysqldump 是MySQL自带的备份命令

用来备份数据库或者进行数据库的迁移，包含创建表、插入表、SQL 语句等。

语法：

mysqldump [options] db_name

常规使用：(以数据库test为例)

# 备份test数据库

mysqldump -uroot -proot123 test > test.sql（不加目录就是当前目录）

# 在其他主机远程备份

mysqldump -uroot -proot123 -h192.168.8.20 test > test.sql

# 还原

mysql -uroot -proot123 test<test.sql

# 恢复所有数据

mysql -uroot -proot123 < all.sql

# 或者登录数据库后，在库中执行还原

use test;

source /root/test.sql

---

物理备份

冷备份：

# 备份

systemctl stop mysql

mkdir -p /bak/20250811/

cp -r /usr/local/mysql/data /bak/20250811/data

systemctl start mysql

# 还原

systemctl stop mysql

rm -rf /usr/local/mysql/data

cp -r /bak/20250811/data /usr/local/mysql/

chown mysql.mysql -R /usr/local/mysql/data

systemctl start mysql

热备份

·Xtrabackup：percona免费的数据热备工具，用来替代Hotbackup。

·Hotbackup：percona收费版数据热备工具。

一、CentOS7 安装Xtrabackup 8.0和Ubuntu22.04 安装Xtrabackup 8.0

Centos7：

# 安装Percona yum存储库

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 启用Percona Server 8.0存储库

percona-release enable-only tools release

percona-release setup ps80

# 安装依赖环境

yum -y install  libev perl-DBD-mysql perl-Digest-MD5 epel-release

# 安装

yum -y install percona-xtrabackup-80-8.0.12

# 安装成功后测试版本

xtrabackup --version

#如果成功安装，则会显示Xtrabackup的版本信息。

Ubuntu：

# 添加percona的软件仓库

sudo add-apt-repository 'deb http://repo.percona.com/apt focal main'

# 回车

# 添加percona的公钥

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

# 安装xtrabackup-80

sudo apt update

sudo apt install percona-xtrabackup-80

# 若安装过程中出现如下错误信息

The following packages have unmet dependencies:

percona-xtrabackup-80 : Depends: libssl1.1 (>= 1.1.1) but it is not installable

# 则下载libssl1.1安装一下

wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/libssl1.1_1.1.1f-1ubuntu2_amd64.deb

sudo dpkg -i libssl1.1_1.1.1f-1ubuntu2_amd64.deb

# 之后继续安装percona-xtrabackup-80

sudo apt install -y percona-xtrabackup-80

# 若安装过程中出现如下错误信息

The following packages have unmet dependencies:

 percona-xtrabackup-24 : Depends: libdbd-mysql-perl but it is not going to be installed

                         Depends: libcurl4-openssl-dev but it is not going to be installed

                         Depends: libev4 (>= 1:4.04) but it is not going to be installed

 percona-xtrabackup-80 : Depends: libdbd-mysql-perl but it is not going to be installed

                         Depends: libcurl4-openssl-dev but it is not going to be installed

                         Depends: libev4 (>= 1:4.04) but it is not going to be installed

                         Depends: libssl1.1 (>= 1.1.1) but it is not installable

                         Conflicts: percona-xtrabackup-24 but 2.4.28-1.jammy is to be installed

E: Unmet dependencies. Try 'apt --fix-broken install' with no packages (or specify a solution).

# 则根据提示执行相应的命令修复

sudo apt --fix-broken install

# 查看安装版本

sudo xtrabackup --version

二、全量备份

# 在备份服务器上创建一个目录，用于存储备份文件。

mkdir /bak/

# 运行以下命令来执行完整备份：（全量备份，如果远程备份需要添加参数--host=IP，删除--socket参数）

xtrabackup  --defaults-file=/etc/my.cnf  \

            --user=root  \

            --password=root123 \

            --port=3306  \

            --socket=/tmp/mysql.sock \

            --datadir=/usr/local/mysql/data \

            --backup \

            --target-dir=/bak/full

# 这将备份MySQL数据目录（/usr/local/mysql/data）到指定的目录（/bak/full ）中。

三、全量还原

# 首先停止MySQL服务，并确保MySQL数据目录为空。

systemctl stop mysqld 或者 /etc/init.d/mysqld stop

rm -rf /usr/local/mysql/data/*

# 进行恢复：

## a.合并数据文件和日志文件，确保数据完整性。

xtrabackup --prepare \

           --datadir=/usr/local/mysql/data \

           --target-dir=/bak/full

## b.全量备份的恢复

xtrabackup  --copy-back \

            --datadir=/usr/local/mysql/data \

            --target-dir=/bak/full

## c.修复所有者和权限

chown -R mysql:mysql /usr/local/mysql/data

## d.启动MySQL

systemctl start mysqld或者：/etc/init.d/mysqld start

## e.登录测试

mysql -uroot -proot123

show databases;

四、增量备份

# 增量备份

mkdir /bak/{inc1,inc2}

# 第一次增量备份

xtrabackup  --defaults-file=/etc/my.cnf  \

            --user=root  \

            --password=root123 \

            --port=3306  \

            --socket=/tmp/mysql.sock \

            --datadir=/usr/local/mysql/data \

            --backup \

            --target-dir=/bak/inc1 \

            --incremental-basedir=/bak/full

# 第二次增量备份

xtrabackup  --defaults-file=/etc/my.cnf  \

            --user=root \

            --password=root123 \

            --port=3306  \

            --socket=/tmp/mysql.sock \

            --datadir=/usr/local/mysql/data \

            --backup \

            --target-dir=/bak/inc2 \

            --incremental-basedir=/bak/inc1

增量还原

# 首先停止MySQL服务，并确保MySQL数据目录为空。

systemctl stop mysqld 或者 /etc/init.d/mysqld stop

rm -rf /usr/local/mysql/data/*

# 进行恢复：(增量备份的恢复,需要恢复增量备份到完全备份)

## a.准备全备(预处理，此选项--apply--log-only 阻止回滚未完成的事务)

xtrabackup --prepare --apply-log-only --target-dir=/bak/full

## b.将第一次增备添加到全备中

xtrabackup --prepare --apply-log-only --target-dir=/bak/full --incremental-dir=/bak/inc1

## c.将第二次增备添加到全备中

xtrabackup --prepare --apply-log-only --target-dir=/bak/full --incremental-dir=/bak/inc2

## d.将合并后的完备再一次准备复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动

(1)合并数据文件和日志文件，确保数据完整性。

xtrabackup --prepare \

           --datadir=/usr/local/mysql/data \

           --target-dir=/bak/full

(2)全备的恢复

xtrabackup  --copy-back \

            --datadir=/usr/local/mysql/data \

            --target-dir=/bak/full

# e.修复所有者和权限

chown -R mysql:mysql /usr/local/mysql/data

# f.启动MySQL

systemctl start mysqld或者 /etc/init.d/mysqld start

# g.登录测试

mysql -uroot -proot123

show databases;