MySQL整理 - ACGIST

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 "%log%"
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

绿色版MySQL`mysql-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;

SELECT
	SUM(TABLE_ROWS) AS ROWSIZE, 
	CONCAT(ROUND(SUM(DATA_LENGTH / 1024 / 1024), 2), 'MB') AS DATASIZE,
	CONCAT(ROUND(SUM(INDEX_LENGTH / 1024 / 1024), 2), 'MB') AS INDEXSIZE
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_SCHEMA = 'database' AND TABLE_NAME = 'table';

锁

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

bind_address

如果这个没有配置连接数据库时需要指定IP地址