- 授权用户登录
grant all privileges on *.* to '用户名'@'ip地址' identified by '登录密码' with grant option;
- 查看当前连接数
SELECT SUBSTRING_INDEX(host,':',1) as ip,COUNT(*) FROM information_schema.`PROCESSLIST` GROUP BY ip
-
查看最大连接数:
show variables like '%max_connections%'
-
临时设置最大连接数等于1000:
set GLOBAL max_connections=1000
-
停止数据库:
systemctl stop mariadb.service
-
启动数据库:
systemctl start mariadb.service
-
设置开机启动:
systemctl enable mariadb.service
-
临时设置连接缓存数等于16:
set global thread_cache_size=16
-
线程数查看:
show global status like 'Thread%'
-
查询某个数据库的所有表的记录数
USE information_schema;
SELECT table_name,table_rows FROM TABLES
WHERE TABLE_SCHEMA = '表明'
ORDER BY table_rows DESC;
- 替换某个字段部分内容
update 表名 set 字段名=REPLACE (字段名,’需要替换的字符串’,’替换的内容’);
- limit关键字分页查询
select * from tablename limit 5; --返回前5行
select * from tablename limit 0,5; --同上
select * from tablename limit 5,10; --返回6-15行(从5开始,查10个)