mysql常用操作命令收集

浏览数：25 / 时间：2015年06月12日

查看连接数

show processlist; 也可以查information_schema.processlist表

查看定义/默认的参数

show variables like ‘%参数名字%‘;

取消用户权限：

revoke SELECT, EXECUTE, SHOW VIEW ON `tsdp`.* from ‘tsdp_ro‘@‘10.58.%.%‘ ;

更改参数

set session 参数名字=值;

set global 参数名字=值;

查看状态值

show global status;

show status like ‘%参数名字%‘;

查看同步状态

show slave status\G 做主从同步 change master to.....

创建用户/赋予权限

create user test@‘111.111.111.111‘ identified by ‘123456‘;

grant all on clus.* to test@‘111.111.111.111‘;

查看触发器

use clus;

show triggers;

show create trigger triggername; 可以在information_schema.trigger差看

查看存储过程

use clus;

show procedure status;

show create procedure procedurename;

查看event

use clus;

show events;

show create event eventname;

建库

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

添加字段

alter table tablename add column name varchar(10);

添加自增主键字段

alter table tablename add column `ID` int primary key auto_increment;

修改字段类型

alter table tablename modify column name varchar(10);

删除字段

alter table tablename drop clumn name;

索引

alter table tablename add index index_name (字段);

alter table tablename drop index index_name;

create index index_name on tablename(字段);

drop index index_name on tablename;

主键

alter table tablename add primary key (`ID`) auto_increment;

1.alter table tablename modify column `ID` int

2.alter table tablename drop primary key (`ID`);

锁库备份

/usr/local/mysql/bin/mysqldump -uroot -p‘vxxx‘ -h127.0.0.1 -P3306 --databases 库名 --lock-all-tables --master-data > axx_20130607.sql 不锁就去掉--lock-all-tables --master-data

错误链接过多，mysqld中断主机所有请求，恢复可请求状态

mysqladmin flush-hosts

查看用户资源限制情况

select Host,User,Password,max_questions,max_updates,max_connections,max_user_connections from mysql.user;

转换Myisam引擎到Innodb

alter table your_table_name engine=‘Innodb‘;

查看是否有使用Myisam引擎的表结构

select * from information_schema.tables where TABLE_SCHEMA not in (‘mysql‘,‘information_schema‘,‘performance_schema‘) and engine != ‘InnoDB‘;

导出表结构

mysqldump -u*** -p -d your_database_name > database_structure.sql

查看user权限

show grants for root@‘127.0.0.1‘;

为一个用户修改权限

UPDATE mysql.user SET Grant_priv=‘Y‘, Super_priv=‘Y‘ WHERE User=‘root‘;

FLUSH PRIVILEGES;

mcluster限制检查sql

SELECT DISTINCT
       CONCAT(t.table_schema,‘.‘,t.table_name) as tbl,
       t.engine,
       IF(ISNULL(c.constraint_name),‘NOPK‘,‘‘) AS nopk,
       IF(s.index_type = ‘FULLTEXT‘,‘FULLTEXT‘,‘‘) as ftidx,
       IF(s.index_type = ‘SPATIAL‘,‘SPATIAL‘,‘‘) as gisidx
  FROM information_schema.tables AS t
  LEFT JOIN information_schema.key_column_usage AS c
    ON (t.table_schema = c.constraint_schema AND t.table_name = c.table_name
        AND c.constraint_name = ‘PRIMARY‘)
  LEFT JOIN information_schema.statistics AS s
    ON (t.table_schema = s.table_schema AND t.table_name = s.table_name
        AND s.index_type IN (‘FULLTEXT‘,‘SPATIAL‘))
  WHERE t.table_schema NOT IN (‘information_schema‘,‘performance_schema‘,‘mysql‘)
    AND t.table_type = ‘BASE TABLE‘
    AND (t.engine <> ‘InnoDB‘ OR c.constraint_name IS NULL OR s.index_type IN (‘FULLTEXT‘,‘SPATIAL‘))
  ORDER BY t.table_schema,t.table_name;

源数据库mysql特性统计

select * from mysql.func;
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = ‘win4‘;
select * from mysql.event;
select * from information_schema.EVENTS where EVENT_SCHEMA = ‘win4‘;
select * from mysql.proc;
select * from information_schema.VIEWS where TABLE_SCHEMA = ‘win4‘;
select * from information_schema.TRIGGERS where TRIGGER_SCHEMA = ‘win4‘;
select * from information_schema.tables where table_schema= ‘win4‘ and engine != ‘InnoDB‘;
select * from information_schema.STATISTICS where table_schema = ‘win4‘ and INDEX_TYPE in (‘FULLTEXT‘,‘SPATIAL‘);
select t.table_schema,t.TABLE_NAME,c.CONSTRAINT_TYPE from information_schema.tables t left join information_schema.TABLE_CONSTRAINTS c on t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME and c.CONSTRAINT_TYPE = ‘PRIMARY KEY‘ where t.TABLE_SCHEMA = ‘win4‘ and c.CONSTRAINT_TYPE is null;