数据库04，用户授权及备份

mac2022-06-30 19

一、用户授权

1.1 什么是用户授权

　　在数据库服务器上添加新的连接用户，添加用户时可以设置用户权限和连接用户密码，默认数据库管理员root用户是有授权操作的

1.2 用户授权的命令格式

　　grant 授权列表 on 库名 to 用户名@"客户端地址" identified by "密码" //授权用户密码

　　with grant option ；//有授权权限，可选项（加上这一句意味着新的用户也具有授权新用户的能力）

　　#################################################################################

　　授权列表：

　　all 所有权限

　　usage //无权限

　　select update insert //个别权限

select，update（字段1，字段2……） //指定字段

　　库名：

　　*.* //所有库下所有表

　　库名.* //指定库下的所有表

　　库名.表名 //指定库下的指定表

　　用户名：

　　授权时自定义，要有标识性

　　存储在MySQL库的user表里

　　客户端地址：

　　— % //所有主机

　　—192.168.4.% //网段内的所有主机

　　—192.168.4.1 //1台主机

　　—localhost //数据库服务器本机

1.3授权例子

　　服务机本机上进行授权：

mysql> grant all on *.* to admin@"192.168.142.129" identified by "123qqq...A" -> ; Query OK, 0 rows affected (1.10 sec)

　　客户机上进行查看：（客户机上要查看是否有mysql 命令 which mysql 查看，若没有，安装mariadb 软件包）

[leilei@localhost ~]$ mysql -h192.168.142.140 -uadmin -p"123qqq...A"

这时在客户机上可以看到服务及上的数据库：

1.4相关命令

　　查找添加用户

　　select user（）；　　查看已授权的用户，显示登录用户名和客户端地址，只是查看当前主机上被授权的用户是谁，无法查询所有被授权的用户

mysql> select user(); ###客户端上 +-----------------------+ | user() | +-----------------------+ | admin@192.168.142.129 | +-----------------------+

mysql> select user();####数据库服务机上+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec

　　show grants；用户显示自身访问权限，也只是在自身登录到的数据库下查看自身拥有的权限

mysql> show grants;#####服务机上查看 +----------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

mysql> show grants;######客户机上查看+-----------------------------------------------------------------------------------------------------------------------------+| Grants for admin@192.168.142.129 |+-----------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.142.129' IDENTIFIED BY PASSWORD '*F19C699342FA5C91EBCF8E0182FB71470EB2AF30' |

show grants for 用户名@"客户端地址"　　管理员查看已有授权用户权限（要知道授权用户名字和客户端地址）

mysql> show grants for admin@192.168.142.129; +-----------------------------------------------------------------------------------------------------------------------------+ | Grants for admin@192.168.142.129 | +-----------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'admin'@'192.168.142.129' IDENTIFIED BY PASSWORD '*F19C699342FA5C91EBCF8E0182FB71470EB2AF30' |

set password=password("密码");　　授权用户登录后修改连接密码

mysql> set password=password("123"); Query OK, 0 rows affected (0.04 sec)

set password for 用户@客户端地址 =password("密码");　　管理员修改授权用户的登录密码

mysql> set password for admin@192.168.142.129=password("123"); Query OK, 0 rows affected (0.00 sec)

drop user 用户名@客户端地址；　　管理员权限下删除授权用户

######################################################################################################################

1.5授权库MySQL

　　show databases；

　　每一个数据库里都有一个mysql库

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | abc123 | | db3 | | mysql | | performance_schema | +--------------------+

在mysql库下，查看表：

　　1.user　　记录已有的授权用户和权限

user表下的部分字段

mysql> desc user; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | |

根据字段查看权限

　　2.db 　　记录已有授权用户对数据库的访问权限

　　mysql> grant all on abc123.* to mydb@localhost identified by "123"; ####增加一条本机的用户权限

　　grant all on abc123.* to admin@192.168.142.129 identified by "123";###再添加一条客户端的授权

mysql> select user,host,db from db;#########可以看到用户admin和mydb 所在的地址及对数据库具体的权限，而上两行的%就表示对所有都有权限+-------+-----------------+---------+| user | host | db |+-------+-----------------+---------+| | % | test || | % | test\_% || admin | 192.168.142.129 | abc123 || mydb | localhost | abc123 |+-------+-----------------+---------+

看到db 表中的权限后，就可以用update来修改相关权限

#flush priviles 可以来刷新修改权限后的数据库

　　3.tables_priv　　记录已有授权用户对表的访问权限（没有特殊对表的权限时，表内数据为空）

解读：　　客户端192.168.142.129 的admin用户对abc123下的T3表有insert，update，还有授予权限grant的权限，其中timestamp 为自定义的授权时间，columns_priv为字段权限

　　4.columns_priv 　　记录已有用户对字段的权限

mysql> grant update(name,class),insert on abc123.T3 to admin@192.168.142.129 identified by "123" with grant option; Query OK, 0 rows affected (0.07 sec) mysql> select *from columns_priv; +-----------------+--------+-------+------------+-------------+---------------------+-------------+ | Host | Db | User | Table_name | Column_name | Timestamp | Column_priv | +-----------------+--------+-------+------------+-------------+---------------------+-------------+ | 192.168.142.129 | abc123 | admin | T3 | name | 0000-00-00 00:00:00 | Update | | 192.168.142.129 | abc123 | admin | T3 | class | 0000-00-00 00:00:00 | Update | +-----------------+--------+-------+------------+-------------+---------------------+-------------+ 2 rows in set (0.00 sec)

1.6撤销授权 revoke 权限 on 库名.表名 from 用户@"客户端地址";

####这里撤销的只是权限，但是不管怎么撤销，授权用户依然可以登录数据库，只有drop user 用户名@"客户端地址"；才可以删除授权用户

1.7删除授权用户

drop user 用户名@"客户端地址"

1.8 管理root密码

　1）恢复root密码

　　—root密码忘了怎么办

　　1.停止MySQL服务程序

　　systemctl stop mysqld.service

　　2.跳过授权表启动MySQL服务程序

　　这一步主要利用mysqld的 --skip-grant-tables选项

　　修改my.cnf配置，在 [mysqld] 下添加 skip_grant_tables=1启动设置：

　　之后启动数据库服务：systemctl start mysqld.service

　　直接以 mysql命令进入数据库

　　3.修改root密码

　　在user表下修改用户密码：

mysql> update mysql.user set authentication_string=password("123") where user="root" and host="localhost"; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0

　　之后刷新一下数据率：flush privileges；

　　（通过执行“FLUSH PRIVILEGES;”可使授权表立即生效，对于正常运行的MySQL服务，也可以用上述方法来修改密码，不用重启服务。本例中因为是恢复密码，最好重启MySQL服务程序，所以上述“FLUSH PRIVILEGES;”操作可跳过。）

　　exit退出数据库

　　在/etc/my.cnf下把刚设置的skip_grant_tables=1 注释掉

　　重新启动一下服务

4.以正常方式重启MySQL服务程序

这时再以 mysql -hlocalhost -uroot -p"密码"登录即可

[root@localhost ~]# mysql -hlocalhost -uroot -p123 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.45 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

这时查看一下mysql 库下的user表下的密码验证字段

mysql> select authentication_string from mysql.user where user="root" and host="localhost"\G; *************************** 1. row *************************** authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 1 row in set (0.00 sec)

2）重置数据库密码：

方法一：在shell脚本命令行重置密码

[root@dbsvr1 ~]# mysqladmin -uroot -p password 'A…qqq321' Enter password: //验证原来的密码

方法二：以管理员身份登入数据库后修改grant

mysql> GRANT all ON *.* TO root@localhost IDENTIFIED BY '1234567';

3）方法3，以root登入mysql> 后，使用UPDATE更新相应的表记录

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('1234567') -> WHERE user='root' AND host='localhost'; //重设root的密码 Query OK, 0 rows affected, 1 warning (0.00 sec)

4）数据库下使用set

mysql> SET PASSWORD FOR root@localhost=PASSWORD('1234567');

如果有报错，>mysql 下使用刷新命令

2.数据备份与恢复

　　2.1数据备份目的

　　当数据丢失或损坏时可以使用备份恢复

　　2.2 数据备份的方式

　　1) 物理备份：cp tar scp

　　恢复：在另一台主机上获得备份的数据，放于/var/lib/mysql下（把数据库数据文件夹给备份下来）

　　2）逻辑备份：（使用软件自带的备份程序做备份，执行备份命令时，会根据已有的数据生成对应的命令，把命令存放到指定的备份文件里

　　　　　　　　　　恢复时执行备份文件里的命令恢复）

　　　　　　　　　mysqldump 备份命令

　　　　　　　　　mysql　　恢复命令

2.3 数据备份的策略

　　完全备份

　　　　—备份所有数据

　　增量备份

　　　　—备份上次的备份后，所有新产生的数据（每一次都和上一次相比，备份多出来的数据）

　　　差异备份：

　　　　—备份完全备份后，所有新产生的数据（每一次都与完全备份相比，备份与完全备份不同的一部分，某些数据可能会重复备份）

2.4 完全备份

　　命令：mysqldump -uroot -p“密码” 库名 > 目录/XXX.sql

　　备份时的库名表示方式

　　— --all-databases 或-A //所有库

　　—数据库名　　//单个库

　　—数据库名表名　　　　//单张表

　　—-B 数据库1 数据库2 //多个库

　　无论是备份还是恢复，都要验证用户权限

2.5 完全恢复

　　如果是只备份了一个库，那么需要在数据库中建立这个库的库名，然后使用mysql -uroot -p密码库名 < 目录/xxx.sql

　　若是使用 -B，那么就无需建立库名，备份和恢复命令里会自动处理

3. 增量备份：

　　使用mysql服务的binlog日志实现数据的增量备份与恢复

　3.1binlog日志的使用

　　　　3.1.1binlog 日志介绍

　　　　　　1）也叫做二进制文件

　　　　　　2）MySQL服务日志文件的一种

　　　　　　3）记录处查询之外的所有sql命令

　　　　　　4）可用于数据的备份与恢复

　　　　　　5)配置MySQL主从同步的必要条件

　　　　3.1.2启用日志

#######################################################################################################

　　　　配置项　　　　　　　　　　　用途

　　　　server_id=数字　　　　　　　　指定id值（1-255，即启动服务的主机名）

　　　　log-bin=目录名/文件名　　　　启用日志

　　　　max_binlog_size=数值m　　　　指定日志文件的容量（当超过这个容量时，就开始另起一个日志文件默认大小为1G）

#######################################################################################################

　　　　启动：在配置文件中[mysqld]下中指定id值和启动

[mysqld] server_id=140 log_bin

然后重启一下mysqld 服务

查看是否已经启动，可以登入数据率查看：

命令：show master status可以查看当前使用的日志文件名称

或者可以在/var/lib/mysql 下查看到日志文件

mysql> system ls /var/lib/mysql abc db3 ib_logfile1 mysqld-bin.000002 performance_schema abc123 ibdata1 mysql mysqld-bin.index auto.cnf ib_logfile0 mysqld-bin.000001 mysql.sock

其中mysqld-bin.XXXXXX为日志文件，mysqld-bin.index 为所有日志文件的索引，也就是说所有的日志文件的名字都存放在里面

可以查看一下这个索引：这里的文件名都是按照主机名-bin-编号命名，位置也是默认在数据库文件下

mysql> system cat /var/lib/mysql/mysqld-bin.index ./mysqld-bin.000001 ./mysqld-bin.000002

自定义日志文件的位置和名称：log-bin=XXX/XXX（不要建在root下就好）

要事先建立好文件夹，并赋予mysql 用户权限

　　　　3.1.3手动创建新的binlog日志文件

　　　　　　1）systemctl reatart mysqld 只要重启一下数据库服务就会生成一个新的日志文件

　　　　　　2）flush logs；刷新一次生成一个新的日志文件

　　　　　　3）命令行下 mysql -uroot -p密码 -e "flush logs"　　(其实和 2）一个意思，只是一个在命令行下，一个在服务中)，-e 是选项

　　　　　　4）mysqldump --flush-logs （在备份数据库后新建一个日志）

　　　　　　（一般后三种用的较多）

　　　　3.1.4删除已有的binlog日志

　　　　　　1）删除指定编号前的日志文件

　　　　　　　　purge master logs to "binlog文件名"

　　　　　　2）删除所有的binlog日志文件，重建新日志（删除所有日志后，会再建一个新的）

　　　　　　reset master

　　　　3.1.5查看日志内容

　　　　1）查看内容

　　　　mysqlbinlog /日志文件所在地址可查看（在没有改变文件格式之前是乱码）

[root@localhost ~]# mysqlbinlog /var/lib/mqsql/mysqld-bin.000001; ####在命令行下执行 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; mysqlbinlog: File '/var/lib/mqsql/mysqld-bin.000001' not found (Errcode: 2 - No such file or directory) DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost ~]#

　　　　2）如何区分记录的多条命令

　　　　　　1.偏移量：position

show master status；其中的position 为日志结点，若结点数值不变，那么就是数据库没有更新，日志没有改变

　　　　　　--start-position=数字　　起始偏移量

　　　　　　--stop-position =数字　　结束偏移量

　　　　　　2.时间结点：

　　　　　　　--start-datetime ="yyyy-mm-dd hh:mm:ss" 　　起始时间

　　　　　　　--stop-datetime ="yyyy-mm-dd hh:mm:ss"　　结束时间

　　　　　　（只要是改变数据的命令就会有对应的偏移量和时间结点）

　3）查看日志当前格式：日志文件转化成我们能看懂的格式，就可以观察到变化的格式命令　　

　　　　　日志文件的三种格式： statement 报表模式

　　　　　　　　　　　　　　　row 　　　　　行模式

　　　　　　　　　　　　　　　mixed　　　　混合模式

show variables like "binlog_format" 可以查看当前文件格式，format为变量

要将格式改为混合模式

步骤：在配置文件中更改，改完需重启服务

　　　　/etc/my.cnf

　　　　[mysqld]

　　　　binlog_format="MIXED"

　　　然后使用mysqlbinlog 来查看

　4）使用日志进行恢复/（这是在需要备份的客户端操作）

　　mysqlbinlog /日志目录 | mysql -uroot -p密码

（将日志文件拷给备份的主机，然后把读取的日志内容管道给数据库做处理）

若选择性读取日志文件则 mysqlbinlog --start-position=开始偏移量 --stop-position=结束偏移量 /目录文件 | mysql -uroot -p密码

如何找偏移量：

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysqld-bin.000003 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191001 8:29:28 server id 129 end_log_pos 120 CRC32 0xd69d903f Start: binlog v 4, server v 5.6.45-log created 191001 8:29:28 at startup ROLLBACK/*!*/; BINLOG ' 6J2SXQ+BAAAAdAAAAHgAAAAAAAQANS42LjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADonZJdEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAT+Q ndY= '/*!*/; # at 120 #191001 8:35:37 server id 129 end_log_pos 197 CRC32 0x3ddc78ef Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1569890137/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 197 #191001 8:35:37 server id 129 end_log_pos 320 CRC32 0xb108bd32 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1569890137/*!*/; insert into abc123.T3 values("rt","2",23,"game",89) ###如找这一条插入命令的开始与结束偏移量，往上找#开始的为开始偏移量 /*!*/; # at 320 ###320 为输完insert 命令后的偏移量，这一条命令还没有执行 #191001 8:35:37 server id 129 end_log_pos 351 CRC32 0x85bec906 Xid = 10 COMMIT/*!*/;###commit 为提交，即回车后提交 # at 351 ### 这一条命令为用户按完回车后正式插入的偏移量 #191001 8:56:57 server id 129 end_log_pos 374 CRC32 0x301e7b58 Stop DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@localhost ~]#

故这一条插入命令的偏移量为 197-351

转载于:https://www.cnblogs.com/zhanglei97/p/11610473.html

相关资源：JAVA上百实例源码以及开源项目

最新回复(0)