mysql操作合集

 : jank    :   : 2038    : 2017-03-25 11:47  mysql

  1. mysql数据库下存储的都是权限表,重要的有user表、db表、host表还有tables_priv表、columns_priv、proc_priv表  

  2. 1、user表:有用户列、权限列、安全列、资源控制列。  

  3. 1)、用户列包括:host、user、password表示主机、用户名、密码。  

  4. 2)、权限列包括select_priv、insert_priv等以priv结尾的字段。决定了用户的权限。这些字段值只有y和n,y表示该权限可以用到所有数据库上;n表示该权限不能用到所有数据库上。可以用grant语句为用户赋予一些权限,也可以用update语句更新。如:grant_priv字段表示是否拥有grant权限;shutdown_priv字段表示是否拥有停止mysql服务的权限;super_priv字段表示是否拥有超级权限;execute_priv字段表示是否拥有execute权限,拥有该权限可以执行存储过程和函数。  

  5. 3)、安全列:分别是ssl_type、ssl_cipher、x509_issuer、x509_subject。ssl用于加密;x509标准可以用来标识用户。通常标准的发行版不支持ssl,读者可以使用show variables like 'have_openssl'语句查看是否具有ssl功能。如果have_openssl的取值为disabled,则不支持ssl加密功能。  

  6. 4)、资源控制列:max_questions、max_updates分别规定每小时可以允许执行多少次查询和更新、max_connections规定每小时可以建立多少连接;max_user_connctions规定单个用户可以同时具有的连接数。默认值都为0表示没有限制。  

  7.   

  8. 2、db表和host表  

  9. db表中存储了某个用户对一个数据库的权限。db表比较常用,而host表很少会用到。有用户列和权限列。  

  10. 1)、分别是host、db、user。分别表示主机名、数据库名和用户名。host表的用户列有两个字段,分别是host和db,分别表示主机名和数据库名。  

  11. 2)、host表是db表的扩展。如果db表中找不到host字段的值,就需要到host表中去寻找。但是host表很少用到,通常db表的设置已经满足要求了。  

  12. 3)、权限列:db表相比host表多了create_routine_priv字段和alter_routine_priv字段。决定用户是否具有创建和修改存储过程的权限。user表中的权限是针对所有数据库。如果user表中select_priv字段值为y,那么该用户可以查询所有数据库的表;如果为某用户只设置查询test表的权限,那么user表的select_priv字段的取值为n。而这个这个权限则记录在db表中取值是y。用户先根据user表的内容获取权限然后根据db表内容获取权限。  

  13.   

  14. 3、tables_priv表和columns_priv表  

  15. tables_priv表可以对单个表进行权限设置,columns_priv表可以对单个数据列进行权限设置。  

  16. 1)、tables_priv表包含8个字段分别是host、db、user、table_name、table_priv、column_priv、tiemstamp和grantor。前4个字段分别表示主机名、数据库名、用户名和表名。权限包括selecct、insert、update、delete、create、drop、grant、references、index、alter。column_priv权限包括select、insert、update、references。timestamp表示修改权限的时间。grantor表示权限是谁设置的。  

  17. 2)、columns_priv表包括7个字段,分别是host、db、user、table_name、column_name、column_priv、tiemstamp。这里多出了column_name字段,表示可以对哪些数据列进行操作。  

  18. 注:数据库先判断user表中值是否为y,如果user表值是y,就不需要检查后面的表。如果user表值为n,则依次检查db表、tables_priv表和columns_priv表。  

  19.   

  20. 4、procs_priv表  

  21. 此表可以对存储过程和存储函数进行权限设置。包含8个字段分别是host、db、user、routine_name、routine_type、proc_priv、timestamp和grantor。routine_name字段表示存储过程或函数的名称。routine_type字段表示类型。该字段有两个取值分别是function和procedure。proc_priv字段表示拥有的权限,分别为3类,分别是execute、alter routine和grant。timestamp字段存储更新的时间;grantor字段存储权限是谁设置的。  

  22.   

  23. 权限管理  

  24. 权限名称  对应user表中的列  权限范围  

  25. create  create_priv  数据库、表或索引  

  26. drop  drop_priv  数据库或表  

  27. grant option  grant_priv  数据库、表、存储过程或函数  

  28. references  references_priv  数据库或表  

  29. alter  alter_priv  修改表  

  30. delete  delete_priv  删除表  

  31. index  index_priv  用索引查询表  

  32. insert  insert_priv  插入表  

  33. select  select_priv  查询表  

  34. update  update_priv  更新表  

  35. create view  create_view_priv  创建视图  

  36. show view  show_view_priv  查看视图  

  37. alter routine  alter_routine_priv  修改存储过程或存储函数  

  38. create routine  create_routine_priv  创建存储过程或存储函数  

  39. execute routine  execute_priv  执行存储过程或存储函数  

  40. file  file_priv  加载服务器主机上的文件  

  41. create temporary tables  create_tmp_table_priv  创建临时表  

  42. lock tables  lock_tables_priv  锁定表  

  43. create user  create_user_priv  创建用户  

  44. process  process_priv  服务器管理  

  45. reload  reload_priv  重新加载权限表  

  46. replication client  repl_client_priv  服务器管理  

  47. replication slave  repl_slave_priv  服务器管理  

  48. show databases  show_db_priv  查看数据库  

  49. shutdown  shutdown_priv  关闭服务器  

  50. super  super_priv  超级权限  

  51.   

  52. 用户管理  

  53. 新建普通用户  

  54. 可以用create user语句、mysql.user表中添加用户、还可以用grant语句新建用户  

  55. 1、create user语句:  

  56. 格式:create user user1 [identified by [password] 'password'][,user2 [identified by [password]'password']]...  

  57. 例:create user 'test1'@'localhost' identified by 'test1';//如果密码是普通字符串就不用加password参数  

  58. 2、insert语句来新建普通用户,直接将用户信息添加到mysql.user表  

  59. 格式:insert into mysql.user(host,user,password) values('hostname','username',PASSWORD('password'));  

  60. 注:要有对该表的insert权限才行。一般只要添加host、user、password这三个字段就行,其它字段取值为默认值。如果某个字段没有默认值。这个语句将不能执行。要将没有默认值的字段也设置值。通常ssl_cipher、x509_issuer、x509_subject没有默认值,所以插入时要设置值。  

  61. 格式:insert into mysql.user(host,user,password,ssl_cipher,x509_issuer,x509_subject) values('hostname','username',PASSWORD('password'),'','','');  

  62. 执行完后用flush privileges;使用户生效。但是执行flush命令需要reload权限。  

  63. 3、grant语句来新建普通用户  

  64. 格式:grant priv_type [(column_list)] on database.table to user [identified by [password]'password'][,user[identified by [password]'password']...[WITH with_option[with_option]...]  

  65. 注:WITH后面可以带有一个或多个参数,如:  

  66. grant option:被授权的用户可以将这些权限赋予给别的用户;这个特性一般用不到。实际中,数据库权限最好由 DBA 来统一管理。  

  67. max_queries_per_hour count:设置每个小时可以允许执行count次查询;  

  68. max_updates_per_hour count:设置每个小时可以允许执行count次更新;  

  69. max_connections_per_hour count:设置每个小时可以建立count连接;  

  70. max_user_connections count:设置单个用户可以同时具有的count个连接数;  

  71. 例:grant select on testdb.* to dba@localhost with grant option;  

  72. mysql> grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名1@用户地址 identified by '连接口令',用户名2@用户地址 identified by '连接口令';  

  73. 权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。  

  74. 当权限1,权限2,…权限n被all privileges或者all代替,表示赋予用户全部权限。  --注:privileges可以省略  

  75. 当数据库名称.表名称被*.*代替,表示赋予用户操作服务器上所有数据库所有表的权限。  

  76. 用户地址可以是localhost,也可以是ip地址、机器名字、域名。也可以用"%"表示从任何地址连接。  

  77. 如:mysql>grant select,insert,update,delete on *.* to 'user1'@'%' Identified by "123"  

  78. 给来自所有地址的用户user1分配可对所有数据库所有表进行select,insert,update,delete,create,drop等操作的权限,并设定口令为123。  

  79. 注:grant可以修改用户的密码和权限。  

  80.   

  81. 删除普通用户  

  82. 1)、删除普通用户要有drop user权限格式:drop user user1[,user2]...;  

  83. 注:user参数是由用户名和主机名组成。  

  84. 例:drop user 'test2'@'localhost';  

  85. 2)、delete语句将用户信息从mysql.user表中删除  

  86. delete from mysql.user where host='hostname' and user='username';//因为host和user两个字段都是主键,所以要两个字段才能确定一条记录。  

  87.   

  88. 修改密码  

  89. 1、root用户修改自己密码  

  90. a、用mysqladmin命令修改密码,格式:mysqladmin -u username -p password "new_password";  

  91. 注:这里的password为关键字不是指旧密码而且新密码必须用双引号括起来。  

  92. b、用update语句如:update mysql.user set password=password("new_password") where user="root" and host="localhost";  

  93. c、set语句,格式:set password=password("new_password");  

  94. 2、root用户修改普通用户密码  

  95. a、set password for 'username'@'hostname'=password("new_password");  

  96. b、用update语句如:update mysql.user set password=password("new_password") where user="root" and host="localhost";  

  97. c、grant语句,格式:grant select on *.* to 'test3'@'localhost' identified by 'mytest3';  

  98. 3、普通用户修改密码  

  99. 1、set password=password('new_password');注:普通用户不能用mysqladmin命令,因为没有super权限  

  100.   

  101. root用户密码丢失解决办法  

  102. 1)、使用--skip-grant-tables选项使服务器停止权限判断,任何用户都可以访问数据库。该选项跟在mysql服务命令后面,  

  103.   windows中用mysqld或mysqld-nt启动mysql服务,也可以用net start mysql启动mysql服务。如:  

  104. msyqld --skip-grant-tables或mysqld-nt --skip-grant-tables或net start mysql --skip-grant-tables  

  105.   linux系统中用mysqld_safe启动mysql服务,也可以用/etc/init.d/mysql启动服务。如:  

  106. mysqld_safe --skip-grant-tables user=mysql或/etc/init.d/mysql start --mysqld --skip-grant-tables  

  107. 2)、用上面的方法停止权限判断后用update语句来修改密码:  

  108. 先用命令:mysql -u root登录  

  109. 然后改密码update mysql.user set password=password('root') where user='root' and host='localhost';  

  110. 最后flush privileges;  

  111. 注:只能用update更新user表,不能用set语句  

  112.   

  113. 4、刷新系统权限表  

  114. mysql>flush privileges;  注意,授权后必须FLUSH PRIVILEGES;否则无法立即生效。  

  115. 5、查看 MySQL 用户权限  

  116. 查看当前用户(自己)权限:  

  117. show grants;  

  118. 查看其他 MySQL 用户权限:  

  119. 1)、select * from mysql.user;  

  120. 2)、show grants for 'username'@'hostname';//可以查看权限的语句  

  121. 6、收回授予权限  

  122. revoke priv_type [(column_list)]...on database.table from user [,user]...  

  123. 注:column_list参数表示权限作用于哪些列上,没有该参数时作用于整个表上;user由用户名和主机名构成;  

  124. 收回全部权限  

  125. 格式:revoke all privileges,grant option from user[,user]...  

  126. 例:revoke update on *.* from 'test5'@'localhost';  


   

备案编号:赣ICP备15011386号

联系方式:qq:1150662577    邮箱:1150662577@qq.com