mysql数据库权限问题,及解决方案

mysql数据库权限难题,及缓解方案

开首用mysql 数据库直接设置后用root
用户,就足以在指令行登入,也得以由此远程操作,自从mysql被oracle收购后,发现权限只怕比原先更为细致了,安装遇到Win
7 x6四,mysql 版本Server version: 5.伍.3二 MySQL Community Server (GPL)
,安装过后,开掘只可以从本地登入,而不可能通过远程访问,后来在英特网找材料,开采说把mysql
数据库中root 用户的host改为% 并且实施
FLUSH PRIVILEGES;
试行过后,果然开采能够中距离登六了,不过地面用localhost登入的时候,大概用12七.0.0.一登入的是却又开采登入不了,查询数据mysql中user的权柄发掘root
用户有四个3个linux系统下的,那几个能够不思考,剩下多少个同样用户名的权位不一致,而且3个有密码,另二个无密码,经过测试

UPDATE USER SET select_priv=’Y’
,Insert_priv=’Y’,Update_priv=’Y’,Delete_priv=’Y’,Create_priv=’Y’,Drop_priv=’Y’
,Reload_priv=’Y’,Shutdown_priv=’Y’,Process_priv=’Y’,File_priv=’Y’,Grant_priv=’Y’,References_priv=’Y’,
Index_priv=’Y’,alter_priv=’Y’,show_db_priv=’Y’,super_priv=’Y’,Create_tmp_table_priv=’Y’,Lock_tables_priv=’Y’,
Execute_priv=’Y’,Repl_slave_priv=’Y’,Repl_client_priv=’Y’,create_view_priv=’Y’,Show_view_priv=’Y’,create_routine_priv=’Y’,
Alter_routine_priv=’Y’,Create_user_priv=’Y’,event_priv=’Y’,trigger_priv=’Y’,Create_tablespace_priv=’Y’
WHERE HOST=’localhost’ AND USER=’root’

UPDATE USER SET PASSWORD=’*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B’
WHERE HOST=’localhost’ AND USER=’root’

FLUSH PRIVILEGES;
发觉能够用locahost登入了,至此对mysql数据的权限进一步深入的精通。

以前用mysql
数据库直接设置后用root
用户,就足以在指令行登陆,也得以通过远程操作,自从mysql被oracl…

mysql误删root用户苏醒措施

 装完数据库清理一些暗许账号的时候一点都不小心把root删除了,flush privileges
之后的新 root 忘了grant任何权力,查看mysqld选项里面有个
−−skip-grant-tables

 

代码如下:

#/usr/libexec/mysqld –verbos –help

 

mysql5.伍手册说明如下

 

代码如下:

–skip-grant-tables

 

This option causes the server to start without using the privilege
system at all, which gives anyone with access to the server unrestricted
access to all databases. You can cause a running server to start using
the grant tables again by executing mysqladmin flush-privileges or
mysqladmin reload command from a system shell, or by issuing a MySQL
FLUSH PRIVILEGES statement after connecting to the server. This option
also suppresses loading of plugins, user-defined functions (UDFs), and
scheduled events. To cause plugins to be loaded anyway, use the
–plugin-load option.

–skip-grant-tables is unavailable if MySQL was configured with the
–disable-grant-options option. See Section 2.10.2, “Typical configure
Options”.

 

mysqld_safe是Unix/Linux系统下的MySQL服务器的2个开发银行脚本。那一个本子扩展了有些安全特点,会在开发银行MySQL服务器以往继续监督其运维状态,并在出现错误的时候再度开动服务器。后台运转mysql

 

代码如下:

#mysqld_safe –skip-grant-tables &

 

假使未有root账户就增多三个

 

代码如下:

INSERT INTO user SET
User=’root’,Host=’localhost’,ssl_cipher=”,x509_issuer=”,x509_subject=”;

 

直接输入mysql连接并增多权限,这时候是无法选拔grant命令的,只好用update

 

代码如下:

UPDATE user SET
Select_priv=’Y’,Insert_priv=’Y’,Update_priv=’Y’,Delete_priv=’Y’,Create_priv=’Y’,Drop_priv=’Y’,Reload_priv=’Y’,Shutdown_priv=’Y’,Process_priv=’Y’,File_priv=’Y’,Grant_priv=’Y’,References_priv=’Y’,Index_priv=’Y’,Alter_priv=’Y’,Show_db_priv=’Y’,Super_priv=’Y’,Create_tmp_table_priv=’Y’,Lock_tables_priv=’Y’,Execute_priv=’Y’,Repl_slave_priv=’Y’,Repl_client_priv=’Y’,Create_view_priv=’Y’,Show_view_priv=’Y’,Create_routine_priv=’Y’,Alter_routine_priv=’Y’,
Create_user_priv=’Y’,Event_priv=’Y’,Trigger_priv=’Y’,Create_tablespace_priv=’Y’,authentication_string=”
WHERE User=’root’;

 

只顾本人用的是mysql是5.5本子,或者操作进程中sql语句或任什么地方方有例外,语句推行实现之后供给flush
privileges ,还大概要重复登6才行。

装完数据库清理一些默许账号的时候非常大心把root删除了,flush privileges
之后的新 root 忘了grant任何权力,查看mys…

mysql误删root用户复苏措施,mysql误删root用户

装完数据库清理一些暗中认可账号的时候十分的大心把root删除了,flush privileges
之后的新 root 忘了grant任何权力,查看mysqld选项里面有个
−−skip-grant-tables

复制代码 代码如下:

#/usr/libexec/mysqld –verbos –help

mysql伍.伍手册表达如下

复制代码 代码如下:

–skip-grant-tables

This option causes the server to start without using the privilege
system at all, which gives anyone with access to the server unrestricted
access to all databases. You can cause a running server to start using
the grant tables again by executing mysqladmin flush-privileges or
mysqladmin reload command from a system shell, or by issuing a MySQL
FLUSH PRIVILEGES statement after connecting to the server. This option
also suppresses loading of plugins, user-defined functions (UDFs), and
scheduled events. To cause plugins to be loaded anyway, use the
–plugin-load option.

–skip-grant-tables is unavailable if MySQL was configured with the
–disable-grant-options option. See Section 2.10.2, “Typical configure
Options”.

mysqld_safe是Unix/Linux系统下的MySQL服务器的1个初叶脚本。那些剧本扩张了一些安全特点,会在起步MySQL服务器今后继续监督其运作状态,并在出现错误的时候再度开动服务器。后台运维mysql

复制代码 代码如下:

#mysqld_safe –skip-grant-tables &

借使没有root账户就增加三个

复制代码 代码如下:

INSERT INTO user SET
User=’root’,Host=’localhost’,ssl_cipher=”,x509_issuer=”,x509_subject=”;

直接输入mysql连接并增添权限,那时候是无法动用grant命令的,只好用update

复制代码 代码如下:

UPDATE user SET
Select_priv=’Y’,Insert_priv=’Y’,Update_priv=’Y’,Delete_priv=’Y’,Create_priv=’Y’,Drop_priv=’Y’,Reload_priv=’Y’,Shutdown_priv=’Y’,Process_priv=’Y’,File_priv=’Y’,Grant_priv=’Y’,References_priv=’Y’,Index_priv=’Y’,Alter_priv=’Y’,Show_db_priv=’Y’,Super_priv=’Y’,Create_tmp_table_priv=’Y’,Lock_tables_priv=’Y’,Execute_priv=’Y’,Repl_slave_priv=’Y’,Repl_client_priv=’Y’,Create_view_priv=’Y’,Show_view_priv=’Y’,Create_routine_priv=’Y’,Alter_routine_priv=’Y’,
Create_user_priv=’Y’,Event_priv=’Y’,Trigger_priv=’Y’,Create_tablespace_priv=’Y’,authentication_string=”
WHERE User=’root’;

只顾本身用的是mysql是5.5版本,大概操作进程中sql语句或其余地点有差别,语句推行实现之后供给flush
privileges ,还或者要重复登入才行。

装完数据库清理一些私下认可账号的时候十分大心把root删除了,flush privileges
之后的新 root 忘了grant任…