If you ever had to deal with MySQL in the past, I'm sure you had to spend lots of time searching on Google (as I did..).

To save you some searching, I'll post some commands I "learned while doing".

  • I'll assume all you know about MySQL is "mysql -u root -p"

The above means:

  1. You are logged-in to a MySQL Box as "root".
  2. You know the MySQL root's password (which might be different from the root local user...).
  3. There is at least 1 database with a couple of tables you can safely play with.

Let's get dirty, let's say you have one MySQL table as follows:

  • Table name "login" - Table Structure (aka schema) as follows:
Field Type Null Key Default Extra
id tinyint(3) NO PRI NULL auto_increment
password varchar(150) YES NULL
username varchar(150) YES NULL
  • Table name "login" - Table Content as follows:
id password username
1 pantera phil

 

 

select * from TABLE-NAME;

Select lists a table content.

On a MySQL prompt, type:

mysql> select * from login;

 

delete from TABLE-NAME where id=1;

delete wipes rows based on some condition (always pay attention b/c there's no such thing as a "Recycle Bin").

Example:

mysql> delete from login where id=1;

 

insert into TABLE-NAME (id, password, username) VALUES (1, "usr", "pwd");

insert adds data into rows.

Example:

mysql> insert into login (id, password, username) VALUES (1, "metallica", "lars");

 

describe TABLE-NAME;

describe shows you the schema (the columns definitions).

Example:

mysql> describe login;

Output:

Field Type Null Key Default Extra
id tinyint(3) NO PRI NULL auto_increment
password varchar(150) YES NULL
username varchar(150) YES NULL

 

ALTER TABLE TABLE-NAME ADD COLUMN new-col-name bool;

Alter allows you to add (& remove) new column(s):

Example:

mysql> ALTER TABLE login ADD COLUMN status bool;

 

select * from mysql.user;

When you run a select against the mysql.user, you will get a list of all the authorised MySQL Users.

Example:

select * from mysql.user;

 

SHOW GRANTS;

show grants ;-)

not THAT Grant's...

Show GRANTS reports your [logged-in] user's permissions.

Example:

mysql> SHOW GRANTS;

Output:

+---------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]|
+---------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '1e5515b01d57bf15' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

SHOW GRANTS FOR phil;

When you wish to know a PARTICULAR User Permissions.

mysql> SHOW GRANTS FOR phil;
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for phil@%|
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, FILE, SHOW VIEW ON *.* TO 'phil'@'%' IDENTIFIED BY PASSWORD '794c6f050dff6713' |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

GRANT ALTER ON  `db2\_db`.* TO 'lars'@'%';

Grant can also be used by itself To assign (additional) permissions to a particular user.

Example:

mysql> GRANT ALTER ON  `db2\_db`.* TO 'lars'@'%';
Query OK, 0 rows affected (0.03 sec)

 

REVOKE ALTER ON  `db2\_db`.* TO 'lars'@'%';

Revoke is the opposite of Grant and it is used to deny permissions to a user.

Example:

mysql> REVOKE ALTER ON  `db2\_db`.* TO 'lars'@'%';
Query OK, 0 rows affected (0.00 sec)

Senior Professional Network and Computer Systems Engineer during work hours and father when home.

Andrea strives to deliver outstanding customer service and heaps of love towards his family.

In this Ad-sponsored space, Andrea shares his quest for "ultimate" IT knowledge, meticulously brought to you in an easy to read format.

Rate this post

Leave a Reply

Your email address will not be published. Required fields are marked *

Connect with Facebook