MySQL: 常用命令

1、查看慢查询超时时间值
use performance_schema;
select * from variables_by_thread where variable_name = 'long_query_time';

2、查看MySQL当前线程
show processlist;

3、创建wayne用户，设置密码，允许所有网段访问
use performance_schema;
1. create user 'wayne'@'%' identified by '123';
drop user 'wayne'@'%';
2. create user 'wayne'@'192.168.32.%' identified by '123';
drop user 'wayne'@'192.168.32.%';
3. show grants;  查看用户权限
show grants for 'wayne'@'%';  查看'wayne'@'%'用户权限
4. grant select,update,insert,delete on test.* to 'wayne'@'%';  赋予'wayne'@'%'用户对test库下所有tables增删改查的权限
5. alter user 'wayne'@'%' identified by '456';  修改用户密码
6. grant create,index on test.* to 'wayne'@'%';  给用户添加create、index权限
7. revoke create,index on test.* to 'wayne'@'%'; 收回用户的create、index权限
revoke all on test.* to 'wayne'@'%'; 收回用户的所有权限
flush privileges;  
8. grant create,index on test.* to 'wayne'@'%' with grant aption; 给wayne用户赋予可以赋予其他用户权限的权限
9. alter user 'wayne'@'%' with max_user_connections 1; 用户最大连接数限制
10. update mysq.user set Host='%'  where User='root' and Host='0.0.0.0'; 设置所有用户可以使用root用户访问数据库

4、MySQL日志设置开启

1. show variables like '%log%'; 查看慢查询配置；
2. set global slow_query_log='ON'; 
3. set global slow_query_log_file='/usr/local/mysql/data/slow_query.log';
4. set global long_query_time=1;

修改my.cnf配置文件后，重启MySQL服务
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow_query.log
long_query_time = 1

5、修改数据库、表的字符集

show VARIABLES like 'character%';
alter database dbt3_s1 character set utf8;
ALTER TABLE lineitem DEFAULT CHARACTER SET utf8;

6、创建DB及表

CREATE DATABASE db_test;
USE db_test;
CREATE TABLE IF NOT EXISTS `tb_test_1`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `SetID` INT NOT NULL,
   `Table_ID` INT NOT NULL,
   `c_name` VARCHAR(100) NOT NULL,
   `t_name` VARCHAR(100) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;