MySQL用户权限及备份
[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备份
完全备份
冷备:使用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地址 | 配额 |
---|---|---|
mysql | 192.168.8.60 | 2G内存1CPU |
backup | 192.168.8.61 | 2G内存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; #数据已回复
增量备份
增量备份:备份上次备份后,新产生的数据。
增量备份时,必须先有一次备份,通常是完全备份
例如:周一完全备份 , 周二~周日增量备份
增量备份格式
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:/ #将增量备份拷贝至备份服务器
增量恢复
xtrabackup --prepare --apply-log-only --target-dir=目标目录
#将最新的全量备份恢复到指定的目录
xtrabackup --prepare --apply-log-only --target-dir=目标目录 --incremental-dir=增量备份目录1 #指的是第一个增量备份文件所在的目录
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --prepare --target-dir=目标目录 #准备恢复数据
xtrabackup --copy-back --target-dir=目标目录 #恢复数据
chown -R mysql:mysql /var/lib/mysql/
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完成全量备份