MySQL常用sql语句记录

1，创建用户及赋权

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
 
-- 赋予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
 
-- 赋予特定表的某些权限
GRANT SELECT, INSERT ON table_name TO 'username'@'localhost';
 
-- 更改密码
ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
 
-- 删除用户
DROP USER 'username'@'localhost';

--查看用户权限
SHOW GRANTS FOR 'username'@'localhost';

--查看用户创建语句
SHOW GRANTS FOR 'username'@'localhost';

查看数据库大小

select table_schema,round((sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024),2)  from information_schema.tables group by table_schema;

修改tmpdir

创建目录
mkdir -p /root/mysql_tmp
chmod 777 /root/mysql_tmp
修改my.cnf
tmpdir= /root/mysql_tmp
重启数据库服务

修改连接数

最大连接数
show variables like '%max_connection%';
当前连接数
show status like 'Threads_connected%'; 
修改最大连接数到500（临时）
set global max_connections=500;
永久修改，my.cnf添加
max_connections=1000

告警日志检查

cat /var/log/mysqld.log |grep -E "Error|Failed"

数据库监控

将输出内容保存到文本文件，如收集2分钟的一个报告，每次间隔30秒

mysql> tee diag.out;
mysql> CALL sys.diagnostics(120, 30, 'current');
mysql> notee;

用mysql命令导出为HTML格式如
mysql -h127.0.0.1 -P3306 -uroot -p -H -e "CALL sys.diagnostics(1, 1, 'current')" > /tmp/report_$(date +"%Y-%m-%d_%H-%M").html


sys.diagnostics 该存储过程有三个重要参数，in_max_runtime、in_interval、in_auto_config。

in_max_runtime： 总共最大收集时间，单位秒，null 为默认值60秒；
in_interval：快照间的间隔时间，单位秒，null为默认30秒；
in_auto_config： Performance Schema的选项分析current\medium\full, 使用的选项指标越全，对MySQL服务的性能影响越大，FULL的影响最大。

小小检查

#!/bin/bash
#su - mysql
./bin/mysql -S /u01/data/tmp/mysql.sock -uroot -p'111111' <<EOF
select "查询当前mysql的版本" from dual;
select @@version;
select "显示所有数据库名的命令" from dual;
show databases;
select "显示最近的警告详情" from dual;
show warnings;
select "设置多少个缓冲池" from dual;
show variables like 'innodb_buffer_pool_instances';
select "查看缓冲池的大小" from dual;
show variables like 'innodb_buffer_pool_size';
select "查看锁的信息" from dual;
select * from innodb_lock_waits;
select "查看连接数" from dual;
SELECT  count(*)-1 FROM  information_schema.processlist;
select "查看bin-log位置" from dual;
show variables like '%log_bin%';
select "查看数据目录位置" from dual;
show variables like '%datadir%';
select "查看软件目录位置" from dual;
show variables like '%basedir%';
select "查看从库日志传输" from dual;
show slave status\G;
select "完成" from dual;
EOF
exit