ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • 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

    댓글 0

Designed by Tistory.