mysql相关知识点

mysql

sql相关

• mysql按照某个范围查询
  select count(*) from {表} where request_time BETWEEN ‘2023-11-03 00:00:00’ and ‘2023-11-10 00:00:00’;

排查问题相关

• 查所有库下 mysql 表空间 和 索引大小

select table_schema as '数据库',  table_name as '表名',  table_rows as '记录数',  truncate(data_length/1024/1024, 2) as '数据容量(MB)',  truncate(index_length/1024/1024, 2) as '索引容量(MB)'  from information_schema.tables order by data_length desc, index_length desc;

• 查询当前执行时间长的sql （前10个）

select * from performance_schema.processlist order by TIME desc limit 10;

• 统计连接IP

select SUBSTRING_INDEX(host,':',1) as ip, count(*) from performance_schema.processlist group by ip;

• 查看执行sql 是否使用 索引 扫描行数等

explain {select SQL}

参数相关

• 常用参数

show status;    # mysql 统计信息
show variables; # mysql 参数信息

#查看是否开启binlog
show global variables like 'log_bin';

# 查看最大连接数
show variables like '%max_connect%';

# 查看当前连接数
show status like 'Threads_connected';

# 查看当前运行线程数
show status like 'Threads_Running';

# 查看慢查询参数
show variables like '%query%';

# 查看从库状态
show slave status

# 修改参数
# 修改慢查询时长
set long_query_time=8;
# 部分参数不能在mysql中修改需要 手动在 my.cnf 中添加参数 重启

常用方法

• mysql 8.0 以后重置 修改用户密码

# 1. 置空密码
update mysql.user set authentication_string='' where user='root';
# 2. 修改密码
alter user 'root'@'localhost' identified by 'greedisgood.1'
alter user 'root'@'%' identified by 'greedisgood.1'
# 3. 刷新
FLUSH PRIVILEGES;

• mysql 备份 & 还原

# 备份 所有库
mysqldump -u{用户名} -p{密码}  --all-databases  > /root/db_back/all.sql
# 还原
mysql -u{用户名} -p{密码} < /root/db_back/all.sql
# 备份单表 (不包含索引) 
create table {备份表名} as select * from {需备份表名};  
# 复制空表
create table {备份表名} like {需要备份的表名};
# 备份表 （包含索引）
mysqldump -u{用户名} -p{密码} {数据库名} {表名} > {备份文件}.sql

• mysql 开启binlog

show variables like '%log_bin%';    # 查看是否开启binlog
show binary logs;   # 查看binlog 文件

# mysql binlog
# 编辑 my.cnf
log_bin = NO        # 开启binlog
binlog_format = ROW # binlog记录内容的方式，记录被操作的每一行
log_bin_basename = /var/lib/mysql/binlog    # binlog位置
log_bin_index = /var/lib/mysql/binlog.index # binlog索引文件
max_binlog_size=200M    # 每个binlog 最大大小
# 重启mysql
systemctl restart mysql

• mysql binlog 回放

mysqlbinlog /var/lib/mysql/binlog/mysql-bin.000001 > /root/sql/mysql-bin.000001.sql

• mysql 主从搭建

# 1. 检查主备mysql server-id 是否一致
cat /etc/my.cnf
[mysqld] 
server-id=1    # 两台服务器 server-id需要不一致 **
# 2. 查看当前主备信息 没有配置 没有显示信息
show slave status;
# 3. 登陆**主库**创建备份用户
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpasswd';
# 对创建用户进行 slave角色 授权  
GRANT REPLICATION slave on *.* to 'repl'@'%';
# 查看授权
show grants for 'repl'@'%';
# 刷新权限
flush privileges;
# 4. 获取主节点当前binary log文件名和位置
SHOW MASTER STATUS;
	# logfile= mysql-bin.000001
	# logois=328
# 5.登陆**备库**执行备份
# 停止同步
stop slave;
# 重置同步信息
reset slave;
# 创建同步任务  注意 MASTER_LOG_FILE 和 MASTER_LOG_POS 需要替换为 主库执行 SHOW MASTER STATUS; 显示的内容
CHANGE MASTER TO MASTER_HOST='10.0.0.15' , MASTER_PORT=3306 , MASTER_USER='repl' , MASTER_PASSWORD='youpasswd' , MASTER_LOG_FILE='mysql-bin.000001' , MASTER_LOG_POS=328;
# 开始同步
start slave;

# 查看备份状态
show slave status\G;

不常用操作

• 锁表解锁表

# 读锁定
LOCK TABLES tbl_name READ
# 写锁定
LOCK TABLES tbl_name WRITE
# 查询锁表
show OPEN TABLES where In_use > 0;
# 解锁
UNLOCK TABLES;

• 改变表结构

# 改变表 允许输入字段
ALTER TABLE {表名称} MODIFY id VARCHAR(64);
# 改变表 列名称
ALTER TABLE {表名称} RENAME COLUMN {列名} {改后列名}
查询相关

特性相关

• mysql federated引擎
  代替数据同步方案 实现表级同步

• 说明:在创建表A时 输入 engine=federated connection=‘mysql://用户名:密码@mysql地址:端口号/数据库/数据表B’
  在创建完表后在刚才输入的库中建立相同表结构的表
  后续在向表A 插入数据的同时 A表所在mysql会向B表mysql 对应表写入此数据

• 创表语句

  create table `employee33` 
  (`employee_id` int(11) default null,`department_id` int(11) default null ) 
  engine=federated connection='mysql://root:passwd.1@10.0.0.15:3308/testdb/employee33';
  

但是如果mysql 断连A表无法查询

select * from employee33