MySQL commands

Computer/linux 2010.02.17 23:53
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

more


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"

* Check the selected database
     mysql> select database();
+------------+
| database() |
+------------+
| aaa        |
+------------+

* Show what tables in the database aaa
     mysql> show tables;
+------------------------------+
| Tables_in_aaa                |
+------------------------------+
| analysis                     |
| beam                         |
| bf_test                      |
| cerenkov                     |
| compton_data                 |
| compton_run                  |
| cut                          |
| cut_data                     |
| high_voltage                 |
| high_voltage_file            |
| high_voltage_reading         |
| high_voltage_setting         |
| measurement_type             |
| monitor                      |
| monitor_calibration          |
| monitor_calibration_data     |
| pmt                          |
| polarized_source             |
| polarized_source_measurement |
| polarized_source_monitor     |
| qtor                         |
| qtor_measurement             |
| qtor_monitor                 |
| run                          |
| seed                         |
| slope                        |
| slope_type                   |
| summary_ba                   |
| summary_by                   |
| summary_da_calc              |
| summary_da_correct           |
| summary_dy_calc              |
| summary_dy_correct           |
| target                       |
| target_measurement           |
| target_monitor               |
+------------------------------+

* See what are in the run table in the "aaa" database

    mysql> describe run;
+------------+-----------------------------------+------+-----+---------+----------------+
| Field      | Type                              | Null | Key | Default | Extra          |
+------------+-----------------------------------+------+-----+---------+----------------+
| run_id     | int(10) unsigned                  | NO   | PRI | NULL    | auto_increment |
| run_number | int(10) unsigned                  | NO   |     | NULL    |                |
| run_type   | enum('good','bad','junk','spiky') | YES  |     | NULL    |                |
| start_time | datetime                          | YES  |     | NULL    |                |
| end_time   | datetime                          | YES  |     | NULL    |                |
| n_mps      | int(10) unsigned                  | NO   |     | 0       |                |
| n_qrt      | int(10) unsigned                  | NO   |     | 0       |                |
+------------+-----------------------------------+------+-----+---------+----------------+





'Computer > linux' 카테고리의 다른 글

Java Plugin for a customized firefox  (0) 2010.02.18
MySQL commands  (0) 2010.02.17
Debian Squeeze (testing) Installation Log (progress)  (0) 2010.02.05
install MySQL++ on RHEL 5.3 and Debian Lenny  (0) 2010.01.27
posted by citadel