MySQL常用命令

 

• 允许root远程连接，并开放所有权限(新建一个用户也是使用这个命令)：
  允许root从所有机器远程连接：

  grant all privileges on *.* to 'root'@'%' identified by 'password';
  

  允许IP为192.168.11*的机器：

  grant all privileges on *.* to 'root'@'192.168.11%' identified by '123456';
  

  在5.0.x中, privileges可以省去不写(4.x中未验证过), 比如允许trsids从IP为192.168.11*的机器远程连接trsidsv3数据库, 并在该数据库上有所有权限:

  grant all on trsidsv3.* to 'trsids'@'192.168.11%' identified by 'trs123';
  

• 列出用户的权限
  • 列出当前连接用户的权限: show grants
  • 列出用户trs在localhost上的权限: show grants for 'trs'@'localhost';

• 列出当前所有连接

  show full processlist;
  

• 如何知道当前运行的数据目录和启动目录

  mysql> show variables like "%dir%";
  +----------------------------+----------------------------------+
  | Variable_name              | Value                            |
  +----------------------------+----------------------------------+
  | basedir                    | /usr/local/                      |
  | character_sets_dir         | /usr/local/share/mysql/charsets/ |
  | datadir                    | /usr/local/var/                  |
  | innodb_data_home_dir       |                                  |
  | innodb_log_arch_dir        |                                  |
  | innodb_log_group_home_dir  | ./                               |
  | innodb_max_dirty_pages_pct | 90                               |
  | slave_load_tmpdir          | /tmp/                            |
  | tmpdir                     | /tmp/                            |
  +----------------------------+----------------------------------+
  9 rows in set (0.00 sec)
  

• 一台机器上安装有多台运行的mysql访问方式
  1. ps -ef | grep mysql

  [root@RHEL4-32 ~]# ps -ef | grep mysql
  root      3485     1  0 May31 ?        00:00:00 /bin/sh /var/eyou/mysql/bin/mysqld_safe --defaults-file=/var/eyou/mysql/var/my.cnf --user=root
  
  root      3512  3485  0 May31 ?        00:00:00 /var/eyou/mysql/libexec/mysqld --defaults-file=/var/eyou/mysql/var/my.cnf --basedir=/var/eyou/mysql 
  --datadir=/var/eyou/mysql/var --user=root --pid-file=/var/eyou/mysql/var/RHEL4-32.pid --skip-locking --port=3306 --socket=/tmp/mysql.sock
  
  root      3916     1  0 May31 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my-medium.cnf --user=root
  
  root      4511  3916  0 May31 ?        00:00:02 /usr/local/libexec/mysqld --defaults-file=/usr/local/mysql/my-medium.cnf --basedir=/usr/local 
  --datadir=/usr/local/var --user=root --pid-file=/usr/local/var/RHEL4-32.pid --skip-external-locking --port=3356 --socket=/tmp/mysql2.sock
  
  root     22162 22125  0 00:15 pts/3    00:00:00 grep mysql
  

  2. 如果选择端口为3356的mysql，则输入如下命令

  mysql -h192.9.200.170 -S/tmp/mysql2.sock -uroot