MySQL整理

0

CentOS安装

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum localinstall mysql57-community-release-el7-11.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
yum install mysql-community-server
systemctl start mysqld
systemctl enable mysqld
# 查看密码
cat /var/log/mysqld.log | grep password
# 修改密码等级
set global validate_password_policy=LOW
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'acgist';
# 远程登陆
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'acgist' WITH GRANT OPTION;

修改密码后生效

flush privileges

查询MySQL引擎

show engines

查询MySQL参数

# 可以使用like查询:show variables like "ft%"
show variables

重建索引

REPAIR TABLE table_name QUICK

查询某个表的索引

SHOW INDEX FROM table_name

时间消耗查询

set profiling = 1;show profiles

查询包含某个字段的所有表

SELECT * FROM information_schema.columns WHERE column_name='field'

语句优化查询

explain select * from table_name

绿色版MySQLmysql-5.7.13-winx64.zip在Window上面安装命令

# 初始化生成data文件夹,以前使用mysqld install的
mysqld --install
# 安装服务:随机密码
mysqld --initialize
# 安装服务:没有密码
mysqld --initialize-insecure
net start mysql

分组查询不重复

select * from tb group by field1 having count(distinct field2) > 1;

GROUP BY替换DISTINCT

碎片整理

optimize table table_name

修改引擎

alter table table_name engine=MyISAM;

查询表的状态(大小、引擎等等)

show table status;

共享锁
排它锁
MyISAM不支持行锁
insert锁表,update索引锁行,否者锁表。
select默认不加锁,可以通过lock in share modefor update加锁。