【mysql】常用命令

一 系统mysql用户密码查询

1、在工程目录如/usr/local/httpd/下的*.php中查找类似有db.inf的文件

以php为例。

2、在代码文件中确认有数据库连接的的功能实现

例如：

$dbconf   = parse_ini_file('/usr/local/httpd/conf/db.inf');
$link = mysql_connect($dbconf['db_address'],$dbconf['db_username'],$dbconf['db_password']);
mysql_select_db('username',$link);

3、cat /usr/local/httpd/conf/db.inf可看到用户名密码，例如：

db_address=localhost
db_username=user01
db_password=pass
db_name=dbname01

4、登录验证：

/usr/local/mysql/bin/mysql -u user01 -ppass

二 不知道mysql root密码的情况下再增加一个超级用户办法【简要说明】

1 杀死原有进程

kill -9 mysql进程

2 启动mysql无权限表模式

/usr/local/mysql/bin/mysqld_safe --datadir=/mysql_data --user=mysql --pid-file=/mysql_data/mysql.pid --skip-grant-tables &

3 不需要口令即可登录mysql：

/usr/local/mysql/bin/mysql

以上无权限表模式启动后可能要稍等片刻，才能执行/usr/local/mysql/bin/mysql
否则会报ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

4 新建名为admin的超级用户

mysql> GRANT ALL PRIVILEGES ON . TO admin@localhost IDENTIFIED BY ‘xxxxxx’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO admin@localhost IDENTIFIED BY ‘xxxxxx’ WITH GRANT OPTION;无法执行，
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

mysql> insert into mysql.user(Host,User,Password) values(“%”,“admin”,password(“xxxxxx”));
Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> flush privileges; <-------------------------------------------重要
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON . TO admin@localhost IDENTIFIED BY ‘xxxxxx’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON . TO admin@“%” IDENTIFIED BY ‘xxxxxx’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

$ /usr/local/mysql/bin/mysql -uadmin -pxxxxx;
mysql> use mysql
Database changed
mysql> select * from user;

三 查看数据库概况

1 数据库版本

select VERSION()
或
mysql --version
或简写为：
mysql -V
或

2 查看变量情况

mysql> show variables like ‘%char%’;
±-------------------------±---------------------------------+
| Variable_name | Value |
±-------------------------±---------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
±-------------------------±---------------------------------+
8 rows in set (0.00 sec)

3 查看用户情况

mysql> select * from user;

四 授权

1 授权查询和更新权限

GRANT SELECT ON db01.table01 TO ‘user01’@‘%’ IDENTIFIED BY ‘password’;
GRANT UPDATE ON db01.table01 TO ‘user01’@‘%’ IDENTIFIED BY ‘password’;