Mysql 常用操作

http://blog.csdn.net/zyz511919766/article/details/22083651

http://www.cnblogs.com/fslnet/p/3143344.html

Mysql 查看连接数   查看Mysql数据库文件位置 mysql清空表内数据

1、Mysql 查看连接数

命令： show processlist;
如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
show processlist;

可以通过获取到的相关进程ID,可以使用kill 进程ID,命令终止相关SQL语句的运行.

只列出前100条，如果想全列出请使用

show full processlist;

 

2、数据库连接池应用中数据库服务器断开超时连接的问题

查看文件/etc/my.cnf，查询有关超时配置的参数：

mysql> show variables like '%timeout'; 

mysql> show variables like '%timeout';

我们可以在属性组mysqld下面修改如下两个参数：
interactive_timeout、wait_timeout

MySQL数据库服务器配置的连接超时时间默认是8小时，如果修改的超时时间足够长的话，就不会出现连接断开的问题。但是，如果有很多应用都在使用数据库连接池，大量的数据库连接资源一直被占用，严重的话可能使数据库服务器宕机，而且，也会使一些攻击者伪造大量请求，使数据库服务器负荷过载而宕机，从而影响应用处理业务。 

3、Mysql清空表内数据那语法

TRUNCATE TABLE 表名
 

4、LINUX查看Mysql数据库文件位置

find / -name "mysql" -print
一般来说mysql是放在/usr/local/mysql/下的。
然后在其bin目录下有个mysql_config文件，vi：
ldata='/usr/local/mysql/var'

5、更改MySQL数据库的数据库名

方法1：

 use information_schema;
mysql> select table_name from tables where table_schema='数据库名1';

mysql> rename table 数据库名1.ttt to 数据库名2.ttt;

方法2：

数据库默认是采用的INNODB存储引擎，不能直接修改数据库的名称，所以现转换成MyISAM存储引擎在修改表名在转换成INNODB存储引擎就好了

show processlist; ＃这一步主要查看有没有其他进程连接，要保证没有其他程序操作数据库。

mysql> alter table t1 engine=MyISAM;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> exit

进入mysql 数据库文件夹位置,修改数据库文件夹名

[root@mysqludf var]# mv 源数据库名称  目标数据库名;

mysql> use目标数据库名;

Database changed

mysql> alter table t1 engine=INNODB;

注意，在改名之前必须现转换存储引擎，否则会报错，你想换的这个名称就换不成了，只能换另外一个名称了。 

6、配置 Mysql 允许远程连接

切换到 mysql DB 

mysql> USE mysql;  

添加一个新的 root 用户, 密码为空, 只允许 192.168.1.100 连接 

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' IDENTIFIED BY '' WITH GRANT OPTION;  

添加一个新的 root 用户, 密码为helloworld, 允许 任何主机连接 

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'helloworld' WITH GRANT OPTION; 

查看现有用户,密码及允许连接的主机 

mysql> SELECT User, Password, Host FROM user;

7、MySQL备份和恢复 
使用mysqldump命令导出数据库
导出整个数据库的语法格式
mysqldump -u root -p news > news.sql   (输入后会让你输入进入MySQL的密码)


只导出表的结构,用mysqldump的-d选项


mysqldump -uroot -p -d 数据库名 > 文件名.sql

如果想导出表 test1，test2，test3 的 表结构,加-d就可以了

mysqldump -uroot -p -d 数据库名 test1 test2 test3 > createtab.sql


如果想导出表 test1，test2，test3 的 表结构和数据?

只要在导入的时候不要加-d,就可以同时导入表结构和数据

 mysqldump -uroot -p 数据库名  表名1,表名2  >文件名.sql

导出存储过程的代码

默认情况下，mysqldump并不会导出数据库的存储过程和函数，如果数据库上创建了存储过程且备份是需要备份存储过程，那就需要用参数 -R 来指定，

mysqldump -uroot -p -hlocalhost -P3306 -n -d -t -R DBName > procedure_name.sql

-d 表示--no-create-db, -n表示--no-data, -t表示--no-create-info, -R表示导出function和procedure。

还原
方法一： 
建立你要还原的数据库，输入"create database 数据库名；"
切换到刚建立的数据库，输入"use 数据库名；"，回车；
导入数据，输入"source 数据库名.sql；"，回车，开始导入，
再次出现"mysql>"并且没有提示错误即还原成功。

方法二： 

[root@localhost mysql]# mysql -u root -p 数据库名<数据库名.sql，输入密码即可。

如何查看mysql表 ; 查看数据库show databases ;使用数据库use databases; 查看表 show tables: 看表结构describe table; 

8、更改MySQL数据库目录位置
 
MySQL默认的数据文件存储目录为/var/lib/mysql。假如要把MySQL目录移到/home/data下需要进行下面几步：
　　1、home目录下建立data目录
　　cd /home
　　mkdir data
　　2、把MySQL服务进程停掉：
　　mysqladmin -u root -p shutdown
　　3、把/var/lib/mysql整个目录移到/home/data
　　mv /var/lib/mysql　/home/data/
　　这样就把MySQL的数据文件移动到了/home/data/mysql下
　　4、找到my.cnf配置文件
　　如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/下找到*.cnf文件，拷贝其中一个到/etc/并改名为my.cnf)中。命令如下：
　　[root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf
　　5、编辑MySQL的配置文件/etc/my.cnf
　　为保证MySQL能够正常工作，需要指明mysql.sock文件的产生位置。修改

socket=/var/lib/mysql/mysql.sock一行中等号右边的值为：/home/mysql/mysql.sock 。操作如下：
　　vi　 my.cnf　　　 (用vi工具编辑my.cnf文件，找到下列数据修改之)
　[mysql d]
socket=/var/lib/mysql .sock
[mysql ]
socket=/tmp/mysql .sock
　　6、修改MySQL启动脚本/etc/init.d/mysql
　　最后，需要修改MySQL启动脚本/etc/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径：/home/data/mysql。
　　[root@test1 etc]# vi　/etc/init.d/mysql
　　#datadir=/var/lib/mysql　　　　(注释此行)
　　datadir=/home/data/mysql　　 (加上此行)
　　7、重新启动MySQL服务
　　/etc/init.d/mysql　start
　　或用reboot命令重启Linux
　　如果工作正常移动就成功了，否则对照前面的7步再检查一下。
　　还要注意目录的属主和权限。
 代码:
[root@sample ~]# chown -R mysql:mysql /home/data/mysql/　 ← 改变数据库的归属为mysql

[root@sample ~]# chmod 700 /home/data/mysql/test/　 ← 改变数据库目录属性为700

[root@sample ~]# chmod 660 /home/data/mysql/test/*　 ← 改变数据库中数据的属性为660

 9、mysql错误:Table XXX is marked as crashed and should be repaired

myisamchk -f xxxx/*.MYI

mysqlcheck -aro xxxx -uusername -ppassword

% myisamchk --recover --quick /path/to/tblName
% myisamchk --recover /path/to/tblName
% myisamchk --safe-recover /path/to/tblName

第一种是最快的，用来修复最普通的问题；而最后一种是最慢的，用来修复一些其它方法所不能修复的问题    

10、mysql 用户管理和权限设置

用户管理
mysql>use mysql;
查看
mysql> select host,user,password from user ;
创建
mysql> create user  zx_root   IDENTIFIED by 'xxxxx';   //identified by 会将纯文本密码加密作为散列值存储
修改
mysql>rename   user  feng  to   newuser；//mysql 5之后可以使用，之前需要使用update 更新user表
删除
mysql>drop user newuser;   //mysql5之前删除用户时必须先使用revoke 删除用户权限，然后删除用户，mysql5之后drop 命令可以删除用户的同时删除用户的相关权限
更改密码
mysql> set password for zx_root =password('xxxxxx');
 mysql> update  mysql.user  set  password=password('xxxx')  where user='otheruser'
查看用户权限
mysql> show grants for zx_root;
赋予权限
mysql> grant select on dmc_db.*  to zx_root;
回收权限
mysql> revoke  select on dmc_db.*  from  zx_root;  //如果权限不存在会报错
 
上面的命令也可使用多个权限同时赋予和回收，权限之间使用逗号分隔
mysql> grant select，update，delete  ，insert  on dmc_db.*  to  zx_root;
如果想立即看到结果使用
flush  privileges ;
命令更新 
 
设置权限时必须给出一下信息
1，要授予的权限
2，被授予访问权限的数据库或表
3，用户名
grant和revoke可以在几个层次上控制访问权限
1，整个服务器，使用 grant ALL  和revoke  ALL
2，整个数据库，使用on  database.*
3，特点表，使用on  database.table
4，特定的列
5，特定的存储过程
 
user表中host列的值的意义
%              匹配所有主机
localhost    localhost不会被解析成IP地址，直接通过UNIXsocket连接
127.0.0.1      会通过TCP/IP协议连接，并且只能在本机访问；
::1                 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
 
 
grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。


grant select on testdb.* to common_user@’%’


grant insert on testdb.* to common_user@’%’


grant update on testdb.* to common_user@’%’


grant delete on testdb.* to common_user@’%’


或者，用一条 MySQL 命令来替代：


grant select, insert, update, delete on testdb.* to common_user@’%’