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'@'%'

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

—权限列表如下

六、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;