mysql工作中常用权限操作命令总结

权限命令总结

grant 权限名(所有的权限用all) on 库名(*全部).表名(*全部) to '用户名'@'%'(%表示所有的IP，可以只些一个IP) identified by "密码";
2）授权所有主机： grant all privileges on *.* to 'testUser'@'%' identified by '123456' with grant option; flush privileges;

3）授权指定主机： grant all privileges on *.* to 'testUser'@'192.168.22.250' identified by 'password' with grant option; flush privileges;

4）授权本地主机： grant all privileges on *.* to root@localhost identified by 'password' with grant option; flush privileges;

5）授权指定数据库： grant all privileges on testDB.* to 'testUser'@localhost identified by 'password' with grant option; flush privileges;

6）授权指定操作权限： grant select, insert, update, delete, create, drop on testDB.* to testUser@localhost identified by 'password' with grant option; flush privileges;

7)  进mysql库查看host为%的数据是否添加： use mysql; select host, user, password, Delete_priv, Grant_priv, Execute_priv from user;

8)  只读权限dump数据库： mysqldump -h 172.192.1.12 -uroot -p123456 --single-transaction your_db > your_db_bk.sql

9)  通过MySQL端口远程连接mysql： mysql -h 122.128.10.114 -P 31206 -uroot -pyg123456 // -P mysql在/etc/mysql/my.cnf 配置文件配置的端口，-p 密码

实际场景应用

我们的zabbix数据库出现下面的问题

这里的问题是我们的root没有访问此数据库的权限，当时mysql用的是zabbix用户
MySQL: 查看zabbix用户的权限

我们添加root的相关权限

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option; flush privileges;