2010-02-18 16:42:58

MySQL useful commands

MySQL ROOT

# access MySQL root account to mysql itself
[] mysql -u root -p mysql


# Show GRANT
mysql> show grants;
mysql> show grants for 'user';
mysql> show grants for 'user'@'localhost';
myslq> show grants for user@localhost;


# Set priv_type (GRANT)

types of priv

ALTER      Modify tables with ALTER TABLE
CREATE     Make new database, table, or index
DELETE  Remove rows from tables
DROP    Remove databases or tables
INDEX   Create or remove indexes for tables
INSERT  Add rows to tables
SELECT  Select records from tables
UPDATE  Modify records in tables

GRANT <priv_type> [, priv_type ...] ON <*.*|database.*> TO
      <user name> [IDENTIFIED BY '<password>']
      [, <user name> [IDENTIFIED BY '<password>'] ,...]
      [WITH GRANT OPTION]

mysql> grant select, execute on `qw\_%`.* TO 'user'@'localhost';
mysql> grant select on *.* to 'user'@'localhost';

# change priv_type (REVOKE)

REVOKE <priv_type> ON <*.*|database.*> FROM <user name>
[, <user name>, ...]


For example,

mysql> show grants for 'user'@'localhost';
+-------------------------------------------------------------------+
| Grants for user@localhost                                               
+-------------------------------------------------------------------+
| GRANT INSERT ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD  |
| GRANT SELECT, EXECUTE ON `qw\_%`.* TO 'user'@'localhost'     
| GRANT SELECT ON `qw_test`.* TO 'user'@'localhost'           
+-------------------------------------------------------------------+

mysql> revoke select on qw_test.* from 'user'@'localhost';
mysql> show grants for 'user'@'localhost';
+-------------------------------------------------------------------+
| Grants for user@localhost      
+-------------------------------------------------------------------+
| GRANT INSERT ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD  |
| GRANT SELECT, EXECUTE ON `qw\_%`.* TO 'user'@'localhost'  
+-------------------------------------------------------------------+


# How to create user "bbb" and grant previliages to "bbb"

mysql> create user bbb@localhost identified by '********';
mysql> select host, user from mysql.user;
+-----------+----------+
| host      | user     |
+-----------+----------+
| localhost | bbb      |
| localhost | root     |
+-----------+----------+

mysql> grant select, insert, update on `qw\_%`.* to bbb@localhost;
mysql> show grants for bbb@localhost;
+--------------------------------------------------------------------
| Grants for bbb@localhost                                      
+--------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'bbb'@'localhost' IDENTIFIED BY PASSWORD
| GRANT SELECT, INSERT, UPDATE ON `qw\_%`.* TO 'bbb'@'localhost'  
+--------------------------------------------------------------------

mysql> grant select, insert, update, execute on `qw\_%`.* to bbb@localhost;
mysql> show grants for bbb@localhost;
+---------------------------------------------------------------------
| Grants for bbb@localhost                                        
+---------------------------------------------------------------------
| GRANT USAGE ON *.* TO 'bbb'@'localhost' IDENTIFIED BY PASSWORD
| GRANT SELECT, INSERT, UPDATE, EXECUTE ON `qw\_%`.* TO 'bbb'@'localhost'        
+--------------------------------------------------------------------


# How to remove user "bbb"

mysql> select host, user from mysql.user;
+-----------+----------+
| host      | user     |
+-----------+----------+
| localhost | bbb      |
| localhost | root     |
+-----------+----------+

mysql> show grants for 'bbb'@'localhost';
+-----------------------------------------------------------------
| Grants for bbb@localhost                                          
+------------------------------------------------------------------
| GRANT SELECT, EXECUTE ON `qw\_%`.* TO 'bbb'@'localhost'        
+------------------------------------------------------------------

mysql> revoke select, execute on `qw\_%`.* from 'bbb'@'localhost';

mysql> show grants for 'bbb'@'localhost';
+-------------------------------------------------------------------
| Grants for bbb@localhost                                           
+--------------------------------------------------------------------
| GRANT INSERT ON *.* TO 'bbb'@'localhost' IDENTIFIED BY PASSWORD
+------------------------------------------------------------------

mysql> revoke insert on *.* from 'bbb'@'localhost';
mysql> show grants for 'bbb'@'localhost';
+-----------------------------------------------------------------
| Grants for bbb@localhost                                        
+-----------------------------------------------------------------
| GRANT USAGE ON *.* TO 'bbb'@'localhost' IDENTIFIED BY PASSWORD
+----------------------------------------------------------------

mysql> drop user 'bbb'@'localhost';
mysql> select host,  user from mysql.user;
+-----------+----------+
| host      | user     |
+-----------+----------+
  localhost | root     |
+-----------+----------+


* How to backup "aaa" database by MySQL ROOT

  [] mysqldump  --add-drop-table -u root -p aaa > aaa.sql
  --add-drop-table option : to create aaa.sql that can overwrite
                            an existing database
  backup all databases
  [] mysqldump --add-drop-table --all-databases -u root -p > alldatabases.sql

* How to restore "aaa" database
  [] mysql -u root -p  aaa < aaa.sql

===================================================

MySQL "user"

* How to login "aaa" database by using "bbb" user
  [] mysql -u "bbb" -h localhost -p "aaa"
  or
  [] mysql -u "bbb" -p
     mysql> use "aaa"



Posted by Jeong Han Lee | Permanent link
/\