MySQL常用sql语句记录

置顶软茸兔

已于 2024-03-20 18:00:34 修改

阅读量397

点赞数 9

分类专栏： Mysql 文章标签： mysql sql

于 2024-01-08 12:50:25 首次发布

本文链接：https://blog.csdn.net/Lizi_TT/article/details/135454480

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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

小小检查

#!/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