This article includes the following:
Anonymous Access Test: Modify the root password: Create a new user: Limit user rights to tables and columns: A user assigns the permissions you have to another: Cancel user rights: Delete a user: Use mysqldump back up a database: use MySQLDUMP back up one or more tables: Recover a database from the backup file:
Anonymous Access Test:
C: /> mysql -h localhost testwelcome to the mysql monitor. ComMMands end with; or /g.your mysql connection ID is 6 to Server Version: 4.0.20a-debug
Type 'help;' or '/ h' for help. Type '/ c' to clear the buffer.
Mysql> show tables; -------------- | TABLES_IN_TEST | -------------- | Tab1 | - -------------- 1 ROW IN Set (0.00 sec)
Modify root password: c: /> mysql -u rootwelcome to the mysql monitor. Commands end with; or /g.your mysql connection ID is 7 to Server version: 4.0.20a-debugtype 'help;' or '/ h' for . help Type '/ c' to clear the buffer.mysql> update user set password = password ( '0000') where user = root; ERROR 1046: No Database Selectedmysql> create database mydata; Query OK, 1 row affected (0.00 sec ) mysql> use mydata; Database changedmysql> update user set password = password ( '0000') where user = root; ERROR 1146: Table 'mydata.user' does not existmysql> use mysql; Database changedmysql> update user set password = Password ('0000') where user = root; error 1054: Unknown column 'root' in 'Where clause'mysql> Update user set password = password (' 0000 ') where user =' root '; Query Ok, 2 Rows Affected (0.00 sec) Rows Match: 2 Changed: 2 Warnings: 0c: /> mysqladmin -u root statusuptime: 1045 Threads: 1 Questions: 32 Slow Queries: 0 Opens: 9 Flush Tables: 1 Open Tables: 2 queries per second AVG: 0.031 Memory IN Use: 8332k Max Memory US Ed: 8372kc: /> mysql -u rootwelcome to the mysql monitor. Commands end with; or /g.your mysql connection ID is 9 to server version: 4.0.20a-debugtype 'help;' or '/ h' for help. Type '/ c' to clear the buffer.mysql> exitbyec: /> mysql -u rootwelcome to the mysql monitor. Commands endh; or /g.your mysql connection ID is 10 to Server Version: 4.0.20a-debugtype 'Help 'or' / c 'to clear the buffer.mysql> EXITBYEC: /> mysqladmin -u root reloadc: /> mysql -u rooterror 1045: Access Denied for user:' root @
LocalHost '(Using Password: No) c: /> mysql -u root -penter password: **** Welcome to the mysql monitor. Commands endh; or /g.your mysql connection ID is 17 to Server Version: 4.0. 20A-DebugType 'Help;' or '/ h' for Help. Type '/ c' to Clear the Buffer.mysql> Create a new user: mysql> show database; ---------- | Database | ---------- | MyData || mysql || TEST | ---------- 3 ROWS IN Set (0.00 sec) mysql> Use mydataDatabase changeMysql> CREATE Table Tab1 (COL1 INT (4) Not Null Primary Key Auto_Increme -> Col2 Char (10) Not Null, -> Col3 Char (20) Not Null; Query Ok, 0 Rows Affected (0.05 sec) mysql> Grant All on MyData . * to Bitan@127.0.0.1 Identified by 'Bitan'; Query OK, 0 Rows Affected (0.02 sec) mysql> Use mysql; Database ChangedMysql> show tables; ---------------------------------------------------------- - | Tables_IN_MYSQL | ----------------- | Column_priv || DB || func || Host || Tables_PRIV || user | ----- ------------ 6 rows in set (0.00 sec) mysql> select host, name, password From User; Error 1054: Unknown Column 'Name' in 'Field List'mysql> SELECT HOST, USER, Password from User; --------- ------- - ---------------- | Host | User | Password | ---------- ------- --- ------------- | localhost | root | || Build | root | || Localhost | | || Build | || Localhost | Bitan | 000864941A82A3C6 | ---------------------- ----- ------- ------------------ 5 ROWS IN Set (0.00 sec) Limit user rights to tables and columns: MySQL > Grant SELECT ON MyData.tab1 to Tab1 @
Localhost Identified by 'Tab1' ->; Query OK, 0 Rows Affected (0.02 Sec) MySQL> EXITBYEC: /> MYSQL -U Tab1 -p Tab1enter Password: **** Error 1044: Access Denied for User: 'Tab1 @ localhost 'to database' tab1'c: /> mysql -u tab1 -p mydataenter password: **** Welcome to the mysql monitor. commands end with; or /g.Your mysql connection ID is 11 to Server Version: 4.0.20a -debugtype 'help;' or '/ h' for help. type '/ c' to clear the buffer.mysql> select * from tab1; ---- ------- - ----------- | COL1 | Col2 | Col3 | ------ ------- ------------ | 1 | AAAAA | AAAAAAAAAA || 2 | BBBBB | BBBBBBBBBBBB | ------ ------- ------------ 2 ROWS IN SET ( 0.00 sec) Mysql> INSERT TAB1 (col2, col3) Values ('bbbbb', 'bbbbbbbbbbbbb); error 1142: Insert Command Denied to user:' Tab1 @ localhost 'for table' tab1'mysql> EXITBYEC: /> mysql - u Rootwelcome to the mysql monitor. commands end with; or /g.your mysql connection ID is 12 to server version: 4.0.20a-debugtype 'help;' or '/ h' for help. type '/ c' t o clear the buffer.mysql> use mysqlDatabase changedmysql> grant insert on mydata.tab1 to tab1 @ localhost ->; Query OK, 0 rows affected (0.01 sec) mysql> exitByeC: /> mysql -u tab1 -p mydataEnter password: * *** Welcome to the mysql monitor. Commands end with; or /g.your mysql connection ID is 13 to server version: 4.0.20a-debugtype 'help;' or '/ h' for help. Type '/ c' to Clear the buffer.mysql> select * from tab1 ->; ------ ----- -------------- | col1 | col2 | col3 | ------
------- -------------- | 1 | AAAA | AAAAAAAAA || 2 | BBBBB | BBBBBBBBBBB | ---- ----- - -------------- 2 Rows in set (0.00 sec) mysql> INSERT TAB1 (col2, col3) Values ('bbbbb', 'bbbbbbbbbbbbb'); Query OK, 1 Row Affected (0.00 sec) mysql> select * from tab1; ------ ------- ------------ | col1 | col2 | col3 | ------ ------- -------------- | 1 | AAAA | AAAAAAAAAAAA || 2 | BBBBB | BBBBBBBBBBBB || 3 | BBBBB | bbbbbbbbbbb | ---- ------- -------------- 3 Rows in set (0.00 sec) mysql> Update Tab1 set col2 = ' cccc ', col3 =' cccccccccccc 'where col1 = 3 ->; ERROR 1142: update command denied to user:' tab1 @ localhost 'for table' tab1'mysql> exitByeC: /> mysql -u root mysqlWelcome to the MySQL monitor. Commands end with; or / g.Your mysql connection ID is 14 to server version: 4.0.20a-debugtype 'help;' or '/ h' for help. Type '/ c' to clear the buffer.mysql> grant Update COL2) on mydata.tab1 to Tab1 @ localhost; query ok, 0 rows affected (0.02 sec) mysql> exitbyec: /> mysql -u tab1 -p mydataenter password: **** Welcome to the mysql monitor. Commands end with; or /g.your mysql connection ID is 15 to Server version: 4.0.20a-debugtype 'help;' or '/ h' for help. TYPE '/ C' to CLEAR THE BUFFER.MYSQL> Update Tab1 Set Col2 = 'CCCC', Col3 = 'cccccccccccccc' where colcccccccccc 'where colcccccccccc' where colcccccccccc 'WHERE COL1 = 3 -> Error 1143: Update Command Denied To User:' Tab1 @ localhost 'for column' Col3 'in table' Tab1'mysql> Update Tab1 set col2 = 'cccc' where col1 = 3 ->;
Query OK, 1 ROW Affected (0.00 sec) rows match: 1 Changed: 1 Warnings: 0mysql> Select * from tab1; ------ ------- -------- ------ | COL1 | Col2 | Col3 | ------ ------- -------------- | 1 | AAAAA | Aaaaaaaaaaa || 2 | BBBBB | BBBBBBBBBBBB || 3 | CCCC | BBBBBBBBBBBBBBB | ------ ----- ------------ 3 Rows IN Set (0.00 sec) mysql> EXITBYEC: /> mysql -u root mysqlwelcome to the mysql monitor. commands end with; or /g.your mysql connection ID is 16 to Server version: 4.0.20a-debugtype 'help;' or ' / h 'for help. type' / c 'to clear the buffer.mysql> grant update. Tab1 to Tab1 @ localhost; query ok, 0 rows affected (0.00 sec) mysql> exitbyec: /> mysql -u tab1 - P mydataenter password: **** Welcome to the mysql monitor. Commands end with; or /g.Your mysql connection ID is 17 to server version: 4.0.20a-debugtype 'help;' or '/ h' for help. Type '/ c' to clear the buffer.mysql> Update Tab1 set col2 = 'cccc', col3 = 'cccccccccc' where col1 = 3 ->; query ok, 1 row affected (0.00 sec Rows Match: 1 Changed: 1 WARNINGS: 0MYSQL> SELECT * from Tab1; ------ ------- ---------- | col1 | col2 | col3 | ------ ------- ------------ | 1 | AAAAA | AAAAAAAAAA || 2 | BBBBB | BBBBBBBBBBBB | | 3 | CCCC | CCCCCCCCCCC | ------ ------- ------------ 3 ROWS IN Set (0.00 sec) mysql> A user Assign the permissions you have to another user: c: /> mysql -u root mysqlwelcome to the mysql monitor. Commands endh; or /g.Your mysql connection ID is 26 to server version: 4.0.20a-debugtype ' Help; 'or' / h '
For help. type '/ c' to clear the buffer.mysql> grant select on mydata. * to user6 @ localhost iDentified by 'x' with grant option; query ok, 0 rows affected (0.00 sec) mysql> Grant Insert On MyData . * to user7 @ localhost iDentified by 'x' with grant option; query ok, 0 rows affected (0.00 sec) mysql> EXITBYEC: /> mysql -u user6 -penter password: * Welcome to the mysql monitor. Commands end with; OR /G.Your mysql connection: 4.0.20a-debugtype 'help;' or '/ h' for help. type '/ c' to clear the buffer.mysql> grant select on mydata. * To user7 @ localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant update on mydata * to user8 @ localhost identified by 'x'; ERROR 1044:. Access denied for user: 'user6 @ localhost' to database 'mydata' mysql> grant update on mydata * to user7 @ localhost; ERROR 1044:. Access denied for user: 'user6 @ localhost' to database 'mydata'mysql> grant all on * * to user7 @ localhost; ERROR 1045:. Access denied for User: 'User6 @ localhost' (useing password: yes) mysql> g Rant All on MyData. * to user7 @ localhost; error 1044: Access Denied for user: 'user6 @ localhost' to database 'mydata'mysql> Cancel user rights: mysql> grant select, insert, update on myData.tab1 to user6 @ localhost identified by 'x'; Query OK, 0 rows affected (0.06 sec) mysql> revoke update on mydata.tab1 from user6 @ localhost; Query OK, 0 rows affected (0.03 sec) mysql> revoke insert on mydata.tab1 from user6 @localhost; query ok, 0 rows affected (0.00 sec) mysql> revoke all on mydata.tab1 from user6 @ localhost;
Query OK, 0 ROWS Affected> EXITBYEC: /> mysql -u user6 -penter password: * Welcome to the mysql monitor. Commands end with; or /g.your mysql connection ID is 55 To Server Version: 4.0 .20a-debugtype 'help;' or '/ h' for help. Type '/ c' to clear the buffer; error 1044: Access Denied for user: 'user6 @ localhost' to database 'MyData'mysql > EXITBYEC: /> mysql -u user6 -p mydata; enter password: * error 1044: Access Denied for user: 'user6 @ localhost' to database 'mydata;' Deleting a user: c: /> mysql -u root mysqlwelcome to THE mysql monitor. Commands end with; or /g.your mysql connection ID is 62 to server version: 4.0.20a-debugtype 'help;' or '/ h' for help. type '/ c' to clear the buffer.mysql > SELECT HOST, User from User; ----------- -------------- | Host | User | ------ ----- ---------------- |% | User2localhost || Build | || Build | root || Localhost | || LocalHost | root || LocalHost | User1 || Localhost | User3 || Localhost | User4 || Localhost | User5 || Localhost | User6 || LocalHost | User7 | ----------- --------- ----- 11 rows in set (0.02 sec) mysql> delete from user where user = 'user7'; query ok, 1 row affected (0.00 sec) mysql> delete from user where user = 'user6'; query OK , 1 row affected (0.00 sec) mysql> delete from user where user = 'user5'; query ok, 1 row affected (0.00 sec) mysql> delete from user where user = 'user4'; query ok, 1 row affed (0.00 Sec) MySQL>
Delete from user where user = 'user3'; query ok, 1 row affected (0.00 sec) mysql> delete from user where user = 'user2'; query ok, 0 rows affected (0.00 sec) mysql> SELECT HOST, User from User ; --------- -------------- | Host | User | ----------- - --------------- |% | User2localhost || Build | || Build | root || Localhost | || Localhost | root || LocalHost | User1 | ----- ------ ---------------- 6 ROWS IN Set (0.00 sec) mysql> Use mysqldump backup a database: mysql> grant all on myData.tab1 to user2 @localhost identified by 'x'; Query OK, 0 rows affected (0.00 sec) mysql> exitByeC: /> mysqldump -u user2 mydata> ./mydata.sqlmysqldump: Got error: 1045: Access denied for user: 'user2 @ localhost (USING Password: /> mysqldump -u user2 -p mydata.sqlenter password: * c: /> mysql -u root mydatawelcome to the mysql monitor. Commands end with; or / g.Your MySQL Connection ID IS 68 TO Server Vers ION: 4.0.20A-DebugType 'Help;' or '/ h' for help. Type '/ c' to clear the buffer.mysql> Create Table Tab2 (Col1 Int Not Null Primary Key Auto_Increment, -> Col2 Char (20) NOT NULL); Query OK, 0 ROWS Affected (0.03 Sec) MySQL> EXITBYEC: /> mysqldump -u user2 -p mydata> ./mydata.sqlenter password: * c: /> mysql -u root mysqlwelcome to the mysql monitor. Commands end with; or /g.your mysql connection ID is 70 to server version: 4.0.20a-debugtype 'help;' or '/ h' for help. Type '/ c' to clear the buffer.mysql> grant all on MyData.tab2 to user2 @ localhost;
Query OK, 0 ROWS Affected (0.00 sec) mysql> exitbyec: /> mysqldump -u user2 -p mydata> ./mydata.sqlenter password: * c: /> c: /mydata.sql: - mysql dump 9.11-- - Host: Localhost Database: MyData - -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------- Server Version 4.0.20a-debug ---- Table Structure for Table `Tab1` - Create Table Tab1 (Col1 Int (4) Not Null Auto_Increment, Col2 CHAR (10) Not null default ', col3 char (20) Not null default', primary key (col1)) type = myisam; ---- Dumping Data for Table `Tab1` - Insert Into Tab1 Values (1 , 'aaaaaaaaaaaaa'); INSERT INTO TAB1 VALUES (2, 'bbbbB'); Insert Into Tab1 Values (3, 'CCCC', 'cccccccccccccc "; Insert Into Tab1 Values (4,' CCCCC ',' ccccccccccc '; ---- Table Structure for Table `Tab2` - Create Table Tab2 (Col1 Int (11) Not Null Auto_Increment, Col2 Char (20) Not Null Default', Primary Key (col1) ) Type = myisam; ---- Dumping Data for Table `Tab2`-- Use mysqldump backup one or more tables: c: /> mysqldump -u user2 mydata tab1 -p> ./mydata.sqlen Ter Password: * c: /> mysqldump -u user2 mydata tab1 tab2 -p> ./mydata.sqlenter password: * c: /> Restore a database from backup file: MySQL> Drop Table Tab1, Tab2; Query Ok, 0 ROWS Affected (0.00 sec) mysql> show tables; EMPTY SET (0.00 sec) mysql> exitbyec: /> mysql -u root mydata <./mydata.sqlc: Commands End with; OR / G.Your MySQL Connection ID is 91 TO Server Version: 4.0.20a-debugtype 'Help;' or '/ h' for help. Type '/ c' to clear the buffer.mysql> show tables; - ---------------
