MySQL重要目录
数据库目录  /var/lib/mysql/
配置文件   /usr/share/mysql
命令目录    /usr/bin(mysqladmin mysqldump等命令)
启动脚本   /etc/rc.d/init.d/mysqld
 
# service mysqld restart     启动mysql服务
停止 mysqld：          [ 确定 ]
启动 mysqld：           [ 确定 ]
 
# netstat -nat   检查mysql3306状态
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp00 0.0.0.0:3306 0.0.0.0:* LISTEN
********************************************************************
[local@localhost ~]$ mysqladmin ping   检查mysq活动状态
mysqld is alive
[local@localhost ~]$ mysqladmin status   返回服务器状态
Uptime: 3214  Threads: 2  Questions: 258  Slow queries: 0  Opens: 28  Flush tables: 1  Open tables: 22  Queries per second avg: 0.080
[local@localhost ~]$ mysqladmin -uroot -p123456 shutdown      关闭服务器
[local@localhost ~]$ mysqladmin stat
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!
service mysqld start
 
[root@localhost ~]# mysqladmin -uroot -p123456 version    显示服务器 版本
mysqladmin  Ver 8.41 Distrib 4.1.20, for redhat-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          4.1.20
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 min 9 sec
Threads: 1  Questions: 5  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 5  Queries per second avg: 0.072

 添加mysql管理员密码
格式：mysqladmin -u root password 'new-password'
mysqladmin -u root password 123456        为root用户添加密码，默认安装未设置密码
 
mysql添加新用户
格式：grant 权限 on 数据库.*  to  用户名@登录主机 identified  by "密码"
grant select,insert,update,delete on *.*  to  abcd@'%'  identified by '654321';
grant all on web.* to www@'%.meloafire.com' identifeid by 'abcd';
abc@'%'=所有网络
abc@'localhost'=本地登录
abc@'192.168.0.10'=指定IP登录
abc@'192.168.0.%'= 192.168.0/254网络
 
mysql撤销用户权限
格式： revoke 权限 on 数据库 from 用户名@local;
revoke insert,delete on db1.info from abc@localhost;
revoke all on web.* from www@'%.meloadfire.com';
 
mysql删除用户
>mysql -u root -p123456
> 密码
mysql>Delete FROM user Where User="abc" and Host="localhost";
mysql>flush privileges;
 
mysql>use mysql       
Database changed
mysql> select host,user,password from user;   查看mysql用户
+-----------------------+------+------------------+
| host                  | user | password         |
+-----------------------+------+------------------+
| localhost             | root | 565491d704013245 |
| localhost.localdomain | root |                  |
| localhost.localdomain |      |                  |
| localhost             |      |                  |
+-----------------------+------+------------------+
4 rows in set (0.00 sec)

mysql> show processlist;      查看连接服务器用户线程
+----+------+-----------+-------+---------+------+-------+------------------+
| Id | User | Host      | db    | Command | Time | State | Info             |
+----+------+-----------+-------+---------+------+-------+------------------+
|  7 | root | localhost | mysql | Query   |    0 | NULL  | show processlist |
+----+------+-----------+-------+---------+------+-------+------------------+
1 row in set (0.01 sec)
mysql> kill 7;    取消用户线程
Query OK, 0 rows affected (0.00 sec)
 
mysql数据库备份恢复
mysqldump database --user= --password=    显示数据库内容
mysqldump -uuser -ppassword database table1 table2 >file.txt  备份多个表
mysqldump -uuser -ppassword database > file.txt   备份单一数据库
mysqldump -uuser -ppassword -B database1 database2 >file.txt  备份多个数据库
mysqldump --all-databases -uuser -ppassword >file.txt 备份所有数据库
mysql>source file.txt   恢复数据库

引用:http://linuxinfo.blog.51cto.com/412748/107827

本文永久链接： http://www.zzxj.net/blog/fxs_2008/archive/2010/06/25/173.html

发表于 @ 2010年06月25日 |评论(loading... )|收藏