当前位置：首页 > 其他 > 正文内容

MySQL用户权限及备份

lonely4周前 (06-01)其他82 
[root@template ~]# hostnamectl set-hostname mysql			#修改主机名
[root@mysql ~]# nmcli connection modify ens160 ipv4.method manual ipv4.addresses 192.168.8.60/24 ipv4.gateway 192.168.8.254 ipv4.dns 192.168.8.254 connection.autoconnect yes 				 #配置入网参数
[root@mysql ~]# nmcli connection up ens160 				#激活网卡
[root@template ~]# hostnamectl set-hostname backup			#修改主机名
[root@backup ~]# nmcli connection modify ens160 ipv4.method manual ipv4.addresses 192.168.8.61/24 ipv4.gateway 192.168.8.254 ipv4.dns 192.168.8.254 connection.autoconnect yes 					  #配置入网参数
[root@backup ~]# nmcli connection up ens160 				#激活网卡

安装MySQL

mysql主机操作

[root@mysql ~]# dnf -y install mysql-server mysql
[root@mysql ~]# systemctl   start    mysqld     		#启动服务
[root@mysql ~]# systemctl   enable   mysqld    			#将服务设置为开机自启
[root@mysql ~]# ss  -utnlp  |  grep  :3306  			#查看服务信息
[root@mysql ~]#mysqladmin -uroot  password '123qqq...A'	#修改mysql的root用户的密码

软件相关的目录与文件等

文件说明
主配置文件/etc/my.cnf.d/mysql-server.cnf
数据库目录/var/lib/mysql
端口号3306
进程名mysqld
传输协议TCP
进程所有者mysql
进程所属组mysql
错误日志文件/var/log/mysql/mysqld.log

用户授权

  • 创建用户

    • CREATE USER 用户名@'主机' IDENTIFIED BY '密码';

  • 授权

    • GRANT 权限列表 ON 库名.表名 TO 用户名@'主机';

mysql> CREATE USER root@'%' IDENTIFIED BY '123qqq...A';		#创建root@'%'用户
mysql> GRANT ALL ON *.* to root@'%' WITH GRANT OPTION;		#授予所有权限
mysql> FLUSH PRIVILEGES;									#刷新授权表

使用SQLyog连接MySQL




创建库tedugame（SQLyog中执行）

CREATE DATABASE tedugame;					#创建tedugame库
SHOW TABLES FROM tedugame;					#查看tedugame库中有哪些表格

创建tedugame.submarine表

  • 包含字段

    • id字段：INT类型

    • name字段：CHAR()类型，10个宽度

    • level字段：INT类型

    • zone字段：VARCHAR类型，50个宽度

CREATE TABLE tedugame.submarine(id INT,NAME CHAR(10),LEVEL INT,
zone VARCHAR(50)
);
SELECT * FROM  tedugame.submarine;				#查询表中数据为空

写入数据

INSERT INTO tedugame.submarine VALUES(1,"mark",1,"Beijing"),
(2,"sam",2,"Shanghai"),
(3,"PaoPao",2,"Shanghai"),
(4,"Lucy",3,"Chengdu");
SELECT * FROM  tedugame.submarine;				#查询表中数据为空

创建表tedugame.ylgy

  • 包含字段

    • id字段：INT类型

    • name字段：CHAR()类型，10个宽度

    • goldcoin：INT类型

CREATE TABLE tedugame.ylgy(id INT,NAME CHAR(10),
goldcoin INT);
SHOW TABLES FROM tedugame;

写入数据

INSERT INTO tedugame.ylgy VALUES (1,"jack",10000),
(2,"sam",10540),
(3,"lucy",88888),
(4,"mark",66666);
SELECT * FROM tedugame.ylgy;					#查询数据

授权用户

  • 授权用户tom，能够对tedugame库里的所有表拥有所有权限

  • 允许tom用户在网络中所有主机登录（%）

  • tom用户的密码为：123qqq...A

CREATE USER tom@'%' IDENTIFIED BY '123qqq...A';			#创建用户及密码
GRANT ALL PRIVILEGES ON tedugame.* TO 'tom'@'%';  		#授权tom用户
FLUSH PRIVILEGES;										#刷新权限列表

测试验证

  • SQLyog工具新建连接，使用tom用户登录验证


SHOW DATABASES;										#查看所有库#只能看到information_schema库，此库占用内存，无需授权#还能看到tedugame库
SELECT * FROM tedugame.submarine;				#查询tedugame.submarine表数据
SELECT * FROM tedugame.ylgy;					#查询tedugame.ylgy表数据

向tedugame.submarine表写入数据

INSERT INTO tedugame.submarine VALUES (5,'tom',2,'Beijing');

修改tedugame.submarine表中的数据，name为tom的用户zone字段改为'Shanghai'

UPDATE tedugame.submarine SET zone='Shanghai' WHERE NAME='tom';

删除名字为tom的整行

DELETE FROM tedugame.submarine WHERE NAME='tom';SELECT * FROM tedugame.submarine;					#查看tedugame.submarine数据

授权用户

  • 授权用户wwc，能够对tedugame.submarine拥有查询（SELECT）和写入（INSERT）权限

  • 允许wwc用户在网络中所有主机登录（%）

  • wwc用户的密码为：123qqq...A

使用root@'%'连接数据库

CREATE USER wwc@'%' IDENTIFIED BY '123qqq...A';				#创建用户
GRANT SELECT,INSERT ON tedugame.submarine to wwc@'%';		#授权
FLUSH PRIVILEGES;											#刷新授权表
SHOW GRANTS FOR wwc@'%';									#查看授权

验证权限，使用wwc@'%'连接数据库

SHOW TABLES FROM tedugame;								#查看表
SELECT * FROM tedugame.submarine;						#验证查询表记录权限，成功
INSERT INTO tedugame.submarine VALUES(6,'wwc',6,'Beijing');	#验证写入表记录权限，成功
DELETE FROM tedugame.sunbmarine							#验证删除表记录权限，失败

撤销权限

  • REVOKE 权限列表E ON 库名.表名 FROM 用户@'主机';

root@'%'用户操作，撤销wwc@'%'用户对tedgame.subarine表的插入（INSERT）权限

REVOKE INSERT ON tedugame.submarine FROM wwc@'%';

测试验证

使用wwc@'%'连接数据库

INSERT INTO tedugame.submarine VALUES(7,'dcc',6,Shanghai');		#验证写入表记录权限，失败

MySQL备份

1715150261890

完全备份

  • 冷备：使用cp命令或者tar等命令都可以进行冷备

使用tar命令将数据库目录/var/lib/mysql/*进行备份

[root@mysql ~]# systemctl stop mysqld
[root@mysql ~]# tar -zcPf /opt/mysql.tar.gz /var/lib/mysql		#备份失败
[root@mysql ~]# rm -rf /var/lib/mysql							#模拟数据丢失
[root@mysql ~]# tar -xPf /opt/mysql.tar.gz						#还原数据
[root@mysql ~]# systemctl start mysqld							#启动mysqld服务
使用SQLyog可以正常连接

  • 逻辑备份

    • mysqldump -h主机 -u用户 -p'密码' [-B 选项] > /路径/xx.sql

    • -A，--all-databases：备份所有数据

    • -B：备份单个库或者多个库

    • 备份单张表时，库名、表名使用空格隔开

备份单张表

  • 备份tedugame.ylgy表至/opt/ylgy.sql

 ~]# mysqldump -hlocalhost -uroot -p'123qqq...A'  tedugame ylgy > /opt/ylgy.sql

  • 模拟数据丢失，删除tedugame.ylgy表

[root@mysql ~]# mysql -uroot -p123qqq...Amysql> USE tedugame;								#切换至tedugame库
mysql> SHOW TABLES ;								#查看表
mysql> DROP TABLE tedugame.ylgy;					#删除tedugame.ylgy表
mysql> SHOW TABLES ;								#确认tedugame.ylgy表被删除

+--------------------+
| Tables_in_tedugame |
+--------------------+
| submarine          |
+--------------------+

mysql> EXIT;										#退出数据库

  • 使用/opt/ylgy.sql文件还原

[root@mysql ~]# mysql -hlocalhost -uroot -p'123qqq...A' tedugame < /opt/ylgy.sql
[root@mysql ~]# mysql -uroot -p123qqq...Amysql> USE tedugame;mysql> SHOW TABLES ;

+--------------------+
| Tables_in_tedugame |
+--------------------+
| submarine          |
| ylgy               |
+--------------------+

2 rows in set (0.00 sec)mysql> SELECT * FROM tedugame.ylgy;					#查看数据已还原

+------+------+----------+
| id   | name | goldcoin |
+------+------+----------+
|    1 | jack |    10000 |
|    2 | sam  |    10540 |
|    3 | lucy |    88888 |
|    4 | mark |    66666 |
+------+------+----------+

4 rows in set (0.00 sec)

备份单个库

  • 备份tedugame库

~]# mysqldump -hlocalhost -uroot -p123qqq...A -B tedugame > /opt/tedugame.sql

  • 模拟数据丢失，删除tedugame库

[root@mysql ~]# mysql -uroot -p123qqq...Amysql> DROP DATABASE tedugame;mysql> SHOW DATABASES;									

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> EXIT;

  • 使用/opt/tedugame.sql还原tedugame库

[root@mysql ~]# mysql -uroot -p123qqq...A < /opt/tedugame.sql
[root@mysql ~]# mysql -uroot -p123qqq...A 
mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tedugame           |
+--------------------+

mysql> SHOW TABLES FROM tedugame;

+--------------------+
| Tables_in_tedugame |
+--------------------+
| submarine          |
| ylgy               |
+--------------------+

mysql> SELECT * FROM tedugame.submarine;

+------+--------+-------+-----------+
| id   | name   | level | zone      |
+------+--------+-------+-----------+
|    1 | mark   |     1 | Beijing   |
|    2 | sam    |     2 | Shanghai  |
|    3 | PaoPao |     2 | Shanghai  |
|    4 | Lucy   |     3 | Chengdu   |
|    5 | jack   |     4 | Chongqing |
|    6 | wwc    |     6 | Beijing   |
+------+--------+-------+-----------+

6 rows in set (0.00 sec)

mysql> SELECT * FROM tedugame.ylgy;

+------+------+----------+
| id   | name | goldcoin |
+------+------+----------+
|    1 | jack |    10000 |
|    2 | sam  |    10540 |
|    3 | lucy |    88888 |
|    4 | mark |    66666 |
+------+------+----------+

4 rows in set (0.00 sec)

备份所有库

  • 备份所有数据（所有库，所有表）

mysql主机操作

[root@mysql ~]# mysqldump -hlocalhost -uroot -p123qqq...A -A > /opt/all.sql
[root@mysql ~]# scp /opt/all.sql root@192.168.8.61:/opt/	#将备份文件拷贝至backup主机

backup主机操作

[root@backup ~]# dnf -y install mysql-server mysql
[root@backup ~]# systemctl start mysqld
[root@backup ~]# systemctl enable mysqld
[root@backup ~]# ss -nutlp | grep :3306
[root@backup ~]# mysql < /opt/all.sql					#使用备份文件还原（此刻还没有密码）
[root@backup ~]# systemctl restart mysqld 				#重启服务
[root@backup ~]# mysql -hlocalhost -uroot -p123qqq...A	#连接测试
mysql> SHOW DATABASES ;									#数据已还原

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tedugame           |
+--------------------+

5 rows in set (0.00 sec)mysql> SHOW TABLES FROM tedugame;						#查看tedugame库中的表

+--------------------+
| Tables_in_tedugame |
+--------------------+
| submarine          |
| ylgy               |
+--------------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM tedugame.submarine;				#查看tedugame.submarine中的数据

+------+--------+-------+-----------+
| id   | name   | level | zone      |
+------+--------+-------+-----------+
|    1 | mark   |     1 | Beijing   |
|    2 | sam    |     2 | Shanghai  |
|    3 | PaoPao |     2 | Shanghai  |
|    4 | Lucy   |     3 | Chengdu   |
|    5 | jack   |     4 | Chongqing |
|    6 | wwc    |     6 | Beijing   |
+------+--------+-------+-----------+

6 rows in set (0.01 sec)

mysql> SELECT * FROM tedugame.ylgy;						#查看tedugame.ylgy中的数据

+------+------+----------+
| id   | name | goldcoin |
+------+------+----------+
|    1 | jack |    10000 |
|    2 | sam  |    10540 |
|    3 | lucy |    88888 |
|    4 | mark |    66666 |
+------+------+----------+

4 rows in set (0.01 sec)

xtrabackup完全备份与恢复

  • xtrabackup一款强大的在线热备份工具

  • 备份过程中不锁库表，适合生产环境

  • 由专业组织Percona提供（改进MySQL分支）

主机IP地址配额
mysql192.168.8.602G内存1CPU
backup192.168.8.612G内存1CPU
[root@mysql ~]# dnf -y install lrzsz

将2024-AI大模型Java全链路工程师环境资料/第四模块/percona-xtrabackup-80-8.0.32-25.1.el8.x86_64.rpm上传至虚拟机mysql的/root

mysql主机操作，安装percona-xtrabackup（libev是依赖包）

[root@mysql ~]# dnf -y install libev
[root@mysql ~]# dnf -y localinstall  percona-xtrabackup-80-8.0.32-25.1.el8.x86_64.rpm

完全备份

  • 命令格式

    • xtrabackup --backup --user=用户名 --password=密码 --databases="库名"

      --target-dir=备份文件的存储目录

  • 如有报错请执行：OPTIMIZE TABLE 库名.表名;

在mysql主机将所有数据库进行备份

[root@mysql ~]# mkdir /db_all
[root@mysql ~]# xtrabackup --backup --user=root --password="123qqq...A"  \
--target-dir=/db_all

将备份目录拷贝至backup

[root@mysql ~]# scp -r /db_all root@192.168.8.61:/

完全恢复

步骤如下

1、systemctl stop mysqld #停止数据库服务

2、rm -rf /var/lib/mysql/* #清空数据库目录

3、xtrabackup --prepare --target-dir=/备份目录 #准备恢复数据

4、xtrabackup --copy-back --target-dir=/备份目录 #恢复数据

5、chown -R mysql:mysql /var/lib/mysql #修改所有者和组

6、systemctl start mysqld #启动服务

在backup主机安装软件包

[root@mysql ~]# dnf -y install lrzsz

将2024-AI大模型Java全链路工程师环境资料/第四模块/percona-xtrabackup-80-8.0.32-25.1.el8.x86_64.rpm上传至虚拟机backup的/root

[root@backup ~]# dnf -y install libev
[root@backup ~]# dnf -y install percona-xtrabackup-80-8.0.32-25.1.el8.x86_64.rpm

恢复数据

[root@backup ~]# systemctl stop mysqld
[root@backup ~]# rm -rf /var/lib/mysql/*
[root@backup ~]# xtrabackup --prepare --target-dir=/db_all		#准备恢复数据
[root@backup ~]# xtrabackup --copy-back --target-dir=/db_all	#恢复数据
[root@backup ~]# chown -R mysql:mysql /var/lib/mysql

启动服务验证

[root@backup ~]# systemctl restart mysqld
[root@backup ~]# mysql -uroot -p123qqq...Amysql> SHOW DATABASES;								#数据已回复

增量备份

  • 增量备份：备份上次备份后，新产生的数据。

    • 增量备份时，必须先有一次备份,通常是完全备份

    • 例如：周一完全备份 ， 周二~周日增量备份

1715155248752

  • 增量备份格式

    • xtrabackup --backup --user=用户名 --password=密码 --target-dir=/增量备份目录 \

      --incremental-basedir=/上一次备份目录

mysql主机新增数据

mysql> CREATE DATABASE game;						#创建库
mysql> CREATE TABLE game.t1(id INT,name CHAR(10));	#创建表
mysql> INSERT INTO game.t1 VALUES(1, "zhangsan");	#插入数据

mysql增量备份

[root@mysql ~]# mkdir /db_firstinc							#创建备份目录
[root@mysql ~]# xtrabackup --backup  --user=root --password='123qqq...A' \
--target-dir=/db_firstinc --incremental-basedir=/db_all			#增量备份
[root@mysql ~]# scp -r /db_firstinc/ root@192.168.8.61:/	#将增量备份拷贝至备份服务器

增量恢复

  1. xtrabackup --prepare --apply-log-only --target-dir=目标目录

    #将最新的全量备份恢复到指定的目录

  2. xtrabackup --prepare --apply-log-only --target-dir=目标目录 --incremental-dir=增量备份目录1 #指的是第一个增量备份文件所在的目录

  3. systemctl stop mysqld

  4. rm -rf /var/lib/mysql/*

  5. xtrabackup --prepare --target-dir=目标目录 #准备恢复数据

  6. xtrabackup --copy-back --target-dir=目标目录 #恢复数据

  7. chown -R mysql:mysql /var/lib/mysql/

  8. systemctl start mysqld

backup主机恢复操作

#将最新的全量备份恢复到指定的目录
[root@backup ~]# xtrabackup --prepare --apply-log-only --target-dir=/db_all   
#将增量备份数据恢复到指定的目录#--incremental-dir=/db_firstinc指的是第一个增量备份文件所在的目录 
[root@backup ~]# xtrabackup --prepare --apply-log-only --target-dir=/db_all \ --incremental-dir=/db_firstinc
[root@backup ~]# systemctl  stop mysqld
[root@backup ~]# rm  -rf  /var/lib/mysql/*
[root@backup ~]# xtrabackup --prepare --target-dir=/db_all	 #准备恢复数据
[root@backup ~]# xtrabackup --copy-back --target-dir=/db_all #恢复数据
[root@backup ~]# chown -R  mysql:mysql  /var/lib/mysql/
[root@backup ~]# systemctl  start mysqld

验证

[root@backup ~]# mysql -hlocalhost -uroot -p123qqq...Amysql> SHOW DATABASES ;								#查看库，多出了game

+--------------------+
| Database           |
+--------------------+
| game               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tedugame           |
+--------------------+
6 rows in set (0.00 sec)mysql> SHOW TABLES FROM game;

+----------------+
| Tables_in_game |
+----------------+
| t1             |
+----------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM game.t1;

+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)

总结

  • 掌握MySQL备份策略

    • 完全备份

    • 增量备份

    • 差异备份

  • xtrabackup 完全备份与恢复

    • 掌握原理及命令

  • xtrabackup 增量备份与恢复

    • 掌握增量备份流程与应用场景

  • 掌握恢复流程

ChatGPT扩展

  • 帮我写一个shell脚本，且完成每周星期四02:00完成全量备份



扫描二维码推送至手机访问。

版权声明：本文由复制者发布，如需转载请注明出处。

本文链接：https://www.copyer.cn/post/44.html

分享给朋友：

相关文章

CentOS安装Tomcat

CentOS安装Tomcat

一、确保已安装JAVA二、下载Tomcat压缩包上传到服务器中三、1. 解压压缩包tar -zxv -f apache-tomcat-9.0.30.tar.gz 记得将版本号修改成自己的重新命名mv...

手机wifi连上Fiddler后无网络的问题解决办法

手机wifi连上Fiddler后无网络的问题解决办法

一 、1、关闭防火墙、增加8888端口监听：2、点击防火墙和网络保护：　3、点击使用中的防火墙，关掉：4、点击Fiddler右上角FiddlerScript：5、找到OnBeforeRequest函数...

数据库联表查询

数据库联表查询

一、Joinjoin的固定语法搭配是join(要连接的表)on，用于连接查询，而通常我们所使用的where是等值查询。多表连接分为：内连接、外连接，而外连接又包括左外连接与右外连接和全外连接。总结对比...

宝塔面板非https站点使用https访问跳转其他网站内容的处理办法

宝塔面板非https站点使用https访问跳转其他网站内容的处理办法

宝塔面板非https站点使用https访问跳转其他网站内容的处理办法进入宝塔后台->网站->（指定未启用ssl的网站）设置->配置文件进行修改其实说白了就是修改nginx或者Apac...

mysql中把两列数据对调

mysql中把两列数据对调

方法一：使用临时变量第一步：创建临时变量首先我们需要创建2个临时变量来保存2列数据的值。例如：SET @tmp1 = (SELECT col1 FRO...

docker更换国内安装源

docker更换国内安装源

修改或新建文件 /etc/docker/daemon.json{   "registry-mirrors": [   ...

发表评论

访客

看不清，换一张

◎欢迎参与讨论，请在这里发表您的看法和观点。

Copyright copyer.cn Some Rights Reserved. 皖ICP备17004337号-10

Powered By Z-BlogPHP. Theme by TOYEAN.

分享：

支付宝

微信