MySQLåç§æéï¼å
±27个ï¼ï¼ä»¥ä¸æä½é½æ¯ä»¥root身份ç»éè¿è¡grantææï¼ä»¥p1@localhost身份ç»éæ§è¡åç§å½ä»¤ãï¼1.usageè¿æ¥ï¼ç»éï¼æéï¼å»ºç«ä¸ä¸ªç¨æ·ï¼å°±ä¼èªå¨æäºå
¶usageæéï¼é»è®¤æäºï¼ãmysql>grantusageon*.*toâp1â²@âlocalhostâidentifiedbyâ123â²;该æéåªè½ç¨äºæ°æ®åºç»éï¼ä¸è½æ§è¡ä»»ä½æä½ï¼ä¸usageæéä¸è½è¢«åæ¶ï¼ä¹å³REVOKEç¨æ·å¹¶ä¸è½å é¤ç¨æ·ã2.selectå¿
é¡»æselectçæéï¼æå¯ä»¥ä½¿ç¨selecttablemysql>grantselectonpyt.*toâp1â²@âlocalhostâ;mysql>select*fromshop;3.createå¿
é¡»æcreateçæéï¼æå¯ä»¥ä½¿ç¨createtablemysql>grantcreateonpyt.*toâp1â²@âlocalhostâ;4.createroutineå¿
é¡»å
·æcreateroutineçæéï¼æå¯ä»¥ä½¿ç¨{create|alter|drop}{procedure|function}mysql>grantcreateroutineonpyt.*toâp1â²@âlocalhostâ;å½æäºcreateroutineæ¶ï¼èªå¨æäºEXECUTE,ALTERROUTINEæéç»å®çå建è
ï¼mysql>showgrantsforâp1â²@âlocalhostâ;+âââââââââââââââââââââââââ+Grantsforp1@localhost+âââââââââââââââââââââââââ+|GRANTUSAGEON*.*TOâp1â²@âlocalhostâIDENTIFIEDBYPASSWORDâ*23AE809DDACAF96AF0FD78ED04B6A265E05AA257â²||GRANTSELECT,CREATE,CREATEROUTINEON`pyt`.*TOâp1â²@âlocalhostâ||GRANTEXECUTE,ALTERROUTINEONPROCEDURE`pyt`.`pro_shop1`TOâp1â²@âlocalhostâ|+ââââââââââââââââââââââââââââ-+5.createtemporarytables(注æè¿éæ¯tablesï¼ä¸æ¯table)å¿
é¡»æcreatetemporarytablesçæéï¼æå¯ä»¥ä½¿ç¨createtemporarytables.mysql>grantcreatetemporarytablesonpyt.*toâp1â²@âlocalhostâ;[mysql@mydev~]$mysql-hlocalhost-up1-ppytmysql>createtemporarytablett1(idint);6.createviewå¿
é¡»æcreateviewçæéï¼æå¯ä»¥ä½¿ç¨createviewmysql>grantcreateviewonpyt.*toâp1â²@âlocalhostâ;mysql>createviewv_shopasselectpricefromshop;7.createuserè¦ä½¿ç¨CREATEUSERï¼å¿
é¡»æ¥æmysqlæ°æ®åºçå
¨å±CREATEUSERæéï¼ææ¥æINSERTæéãmysql>grantcreateuseron*.*toâp1â²@âlocalhostâ;æï¼mysql>grantinserton*.*top1@localhost;8.insertå¿
é¡»æinsertçæéï¼æå¯ä»¥ä½¿ç¨insertintoâ¦..valuesâ¦.9.alterå¿
é¡»æalterçæéï¼æå¯ä»¥ä½¿ç¨altertablealtertableshopmodifydealerchar(15);10.alterroutineå¿
é¡»å
·æalterroutineçæéï¼æå¯ä»¥ä½¿ç¨{alter|drop}{procedure|function}mysql>grantalterroutineonpyt.*toâp1â²@âlocalhostâ;mysql>dropprocedurepro_shop;QueryOK,0rowsaffected(0.00sec)mysql>revokealterroutineonpyt.*fromâp1â²@âlocalhostâ;[mysql@mydev~]$mysql-hlocalhost-up1-ppytmysql>dropprocedurepro_shop;ERROR1370(42000):alterroutinecommanddeniedtouserâp1â²@âlocalhostâforroutineâpyt.pro_shopâ11.updateå¿
é¡»æupdateçæéï¼æå¯ä»¥ä½¿ç¨updatetablemysql>updateshopsetprice=3.5wherearticle=0001anddealer=âA';12.deleteå¿
é¡»ædeleteçæéï¼æå¯ä»¥ä½¿ç¨deletefromâ¦.whereâ¦.(å é¤è¡¨ä¸çè®°å½)13.dropå¿
é¡»ædropçæéï¼æå¯ä»¥ä½¿ç¨dropdatabasedb_name;droptabletab_name;dropviewvi_name;dropindexin_name;14.showdatabaseéè¿showdatabaseåªè½çå°ä½ æ¥æçæäºæéçæ°æ®åºï¼é¤éä½ æ¥æå
¨å±SHOWDATABASESæéã对äºp1@localhostç¨æ·æ¥è¯´ï¼æ²¡æ对mysqlæ°æ®åºçæéï¼æ以以æ¤èº«ä»½ç»éæ¥è¯¢æ¶ï¼æ æ³çå°mysqlæ°æ®åºï¼mysql>showdatabases;+âââââââ+|Database|+âââââââ+|information_schema||pyt||test|+âââââââ+15.showviewå¿
é¡»æ¥æshowviewæéï¼æè½æ§è¡showcreateviewãmysql>grantshowviewonpyt.*top1@localhost;mysql>showcreateviewv_shop;16.indexå¿
é¡»æ¥æindexæéï¼æè½æ§è¡[create|drop]indexmysql>grantindexonpyt.*top1@localhost;mysql>createindexix_shoponshop(article);mysql>dropindexix_shoponshop;17.excuteæ§è¡åå¨çFunctions,Proceduresmysql>callpro_shop1(0001,@a)ï¼+âââ+|article|+âââ+|0001||0001|+âââ+mysql>select@a;+ââ+|@a|+ââ+|2|+ââ+18.locktableså¿
é¡»æ¥ælocktablesæéï¼æå¯ä»¥ä½¿ç¨locktablesmysql>grantlocktablesonpyt.*top1@localhost;mysql>locktablesa1read;mysql>unlocktables;19.referencesæäºREFERENCESæéï¼ç¨æ·å°±å¯ä»¥å°å
¶å®è¡¨çä¸ä¸ªå段ä½ä¸ºæä¸ä¸ªè¡¨çå¤é®çº¦æã20.reloadå¿
é¡»æ¥æreloadæéï¼æå¯ä»¥æ§è¡flush[tables|logs|privileges]mysql>grantreloadonpyt.*top1@localhost;ERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGESmysql>grantreloadon*.*toâp1â²@âlocalhostâ;QueryOK,0rowsaffected(0.00sec)mysql>flushtables;21.replicationclientæ¥ææ¤æéå¯ä»¥æ¥è¯¢masterserverãslaveserverç¶æãmysql>showmasterstatus;ERROR1227(42000):Accessdenied;youneedtheSUPER,REPLICATIONCLIENTprivilegeforthisoperationmysql>grantReplicationclienton*.*top1@localhost;æï¼mysql>grantsuperon*.*top1@localhost;mysql>showmasterstatus;+ââââââ+âââ-+âââââ+ââââââ+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+ââââââ+âââ-+âââââ+ââââââ+|mysql-bin.000006|2111|||+ââââââ+âââ-+âââââ+ââââââ+mysql>showslavestatus;22.replicationslaveæ¥ææ¤æéå¯ä»¥æ¥çä»æå¡å¨ï¼ä»ä¸»æå¡å¨è¯»åäºè¿å¶æ¥å¿ãmysql>showslavehosts;ERROR1227(42000):Accessdenied;youneedtheREPLICATIONSLAVEprivilegeforthisoperationmysql>showbinlogevents;ERROR1227(42000):Accessdenied;youneedtheREPLICATIONSLAVEprivilegeforthisoperationmysql>grantreplicationslaveon*.*top1@localhost;mysql>showslavehosts;Emptyset(0.00sec)mysql>showbinlogevents;+âââââ+ââ-+âââââ-+ââââ+ââââ-+âââââ+|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|+âââââ+ââ-+âââââ+ââââ+ââââ-+âââââ+|mysql-bin.000005|4|Format_desc|1|98|Serverver:5.0.77-log,Binlogver:4||mysql-bin.000005|98|Query|1|197|use`mysql`;createtablea1(iint)engine=myisam|â¦â¦â¦â¦â¦â¦â¦â¦â¦â¦â¦â¦â¦â¦23.Shutdownå
³éMySQLï¼[mysql@mydev~]$mysqladminshutdownéæ°è¿æ¥ï¼[mysql@mydev~]$mysqlERROR2002(HY000):CanâtconnecttolocalMySQLserverthroughsocketâ/tmp/mysql.sockâ(2)[mysql@mydev~]$cd/u01/mysql/bin[mysql@mydevbin]$./mysqld_safe&[mysql@mydevbin]$mysql24.grantoptionæ¥ægrantoptionï¼å°±å¯ä»¥å°èªå·±æ¥æçæéæäºå
¶ä»ç¨æ·ï¼ä»
éäºèªå·±å·²ç»æ¥æçæéï¼mysql>grantGrantoptiononpyt.*top1@localhost;mysql>grantselectonpyt.*top2@localhost;25.fileæ¥æfileæéæå¯ä»¥æ§è¡select..intooutfileåloaddatainfileâ¦æä½ï¼ä½æ¯ä¸è¦æfile,process,superæéæäºç®¡çå以å¤çè´¦å·ï¼è¿æ ·åå¨ä¸¥éçå®å
¨éæ£ãmysql>grantfileon*.*top1@localhost;mysql>loaddatainfileâ/home/mysql/pet.txtâintotablepet;26.superè¿ä¸ªæéå
许ç¨æ·ç»æ¢ä»»ä½æ¥è¯¢ï¼ä¿®æ¹å
¨å±åéçSETè¯å¥ï¼ä½¿ç¨CHANGEMASTERï¼PURGEMASTERLOGSãmysql>grantsuperon*.*top1@localhost;mysql>purgemasterlogsbeforeâmysql-bin.000006â²;27.processéè¿è¿ä¸ªæéï¼ç¨æ·å¯ä»¥æ§è¡SHOWPROCESSLISTåKILLå½ä»¤ãé»è®¤æ
åµä¸ï¼æ¯ä¸ªç¨æ·é½å¯ä»¥æ§è¡SHOWPROCESSLISTå½ä»¤ï¼ä½æ¯åªè½æ¥è¯¢æ¬ç¨æ·çè¿ç¨ãmysql>showprocesslist;+â-+ââ+ââââ+ââ+âââ+ââ+ââ-+ââââââ+|Id|User|Host|db|Command|Time|State|Info|+â-+ââ+ââââ+ââ+âââ+ââ+ââ-+ââââââ+|12|p1|localhost|pyt|Query|0|NULL|showprocesslist|+â-+ââ+ââââ+ââ+âââ+ââ+ââ-+ââââââ+å¦å¤ï¼ç®¡çæéï¼å¦superï¼processï¼fileçï¼ä¸è½å¤æå®æ个æ°æ®åºï¼onåé¢å¿
é¡»è·*.*mysql>grantsuperonpyt.*top1@localhost;ERROR1221(HY000):IncorrectusageofDBGRANTandGLOBALPRIVILEGESmysql>grantsuperon*.*top1@localhost;QueryOK,0rowsaffected(0.01sec)
温馨提示:内容为网友见解,仅供参考