mysql常用脚本

持续更新

mysql查看事务

SELECT
    a.trx_id,
    a.trx_state,
    a.trx_started,
    a.trx_query,
    a.trx_weight,
    a.trx_lock_memory_bytes,
    b.ID,
    b.USER,
    b.DB,
    b.COMMAND,
    b.TIME,
    b.STATE,
    b.INFO,
    c.PROCESSLIST_USER,
    c.PROCESSLIST_HOST,
    c.PROCESSLIST_DB,
    d.SQL_TEXT
FROM
    information_schema.INNODB_TRX a
LEFT JOIN
    information_schema.PROCESSLIST b
ON
    a.trx_mysql_thread_id = b.id
LEFT JOIN
    PERFORMANCE_SCHEMA.threads c
ON
    b.id = c.PROCESSLIST_ID
LEFT JOIN
    PERFORMANCE_SCHEMA.events_statements_current d
ON
    d.THREAD_ID = c.THREAD_ID;

mysql开窗函数实现

求每个部门收入最高的员工

SELECT
    *
FROM
    (
        SELECT
            IF(@v_department_id=c.department_id,@rn:=@rn+1,@rn:=1) AS rn,
            @v_department_id:=                                        c.department_id,
            c.department_id,
            c.name,
            c.salary
        FROM
            (
                SELECT
                    b.DEPARTMENT_ID,
                    b.name,
                    SUM(b.SALARY) SALARY
                FROM
                    department a,
                    salary b
                WHERE
                    a.id = b.department_id
                GROUP BY
                    b.DEPARTMENT_ID,
                    b.name) c, (select @v_department_id:=-1) d
        ORDER BY
            c.department_id,
            c.salary DESC) d
WHERE
    d.rn=1;
//试验数据
CREATE TABLE department (id int, name varchar(100)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE salary (department_id int, name varchar(100), salary int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO department (id, name) VALUES (1, '霸气的部门');
INSERT INTO department (id, name) VALUES (2, '牛皮的部门');
INSERT INTO department (id, name) VALUES (3, '最拽的部门');
INSERT INTO salary (department_id, name, salary) VALUES (1, 'Superman', 1000);
INSERT INTO salary (department_id, name, salary) VALUES (1, 'Superman', 2000);
INSERT INTO salary (department_id, name, salary) VALUES (1, 'spiderman', 3500);
INSERT INTO salary (department_id, name, salary) VALUES (1, 'hulk', 6000);
INSERT INTO salary (department_id, name, salary) VALUES (2, 'Jack', 5000);
INSERT INTO salary (department_id, name, salary) VALUES (2, 'Rose', 3000);
INSERT INTO salary (department_id, name, salary) VALUES (2, 'Tom', 4000);
INSERT INTO salary (department_id, name, salary) VALUES (3, 'Jerry', 2000);
INSERT INTO salary (department_id, name, salary) VALUES (3, 'Harry', 3000);

mysql查看配置参数

show variables like '%buffer%';

mysql查看当前正在使用的表

show open tables;

mysql查看引擎状态（包含最后一次死锁原因记录）

show engine innodb status

mysql查看当前进程

show processlist;（进程对应的sql可能显示不全）
show full processlist;（进程对应的sql可以显示全，内容可能会贼多）

mysql查看执行计划

explain select * from tableName;
desc select * from tableName;

导出数据，忽略某个表

mysqldump -u 用户名 -p 数据库名 \
  --ignore-table=数据库名.表名1 \
  --ignore-table=数据库名.表名2 \
  > /path/to/backup.sql

mysql导出结构不包含数据

// 如果不指定表则代表导出整个库，表可以指定多个
mysqldump --skip-lock-table --no-data -hip -uuser -ppwd -Pport dbname tableName > dump.sql

mysql导出数据不包含结构

mysqldump --skip-lock-table --no-create-info --no-create-db -hip -uuser -ppwd -Pport dbname tableName > dump.sql

mysql查看建表语句

show create table filter_monitor

mysql创建、删除、查看索引

// 创建
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE table_name ADD UNIQUE (column_list);
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
CREATE INDEX index_name ON table_name (column_list);
CREATE UNIQUE INDEX index_name ON table_name (column_list);
CREATE TABLE `pack_group` (
  PRIMARY KEY (`id`),
  UNIQUE `idx_shop_beehive_packable_time` (`shop_id`,`beehive_id`,`packable_time`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分组状态表';
// 删除
DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;
// 查看
show index from tblname;
show keys from tblname;

mysql查看表碎片

如果索引的Cardinality属性与count(distinct字段）差别很大，说明碎片很多，需要整理，不然执行可能会很慢

select count(distinct columnName) from tableName;
show index from tableName;

mysql innodb引擎数据碎片整理

// 查看表的磁盘碎片，如果很多，则进行收集整理
alter table tableName engine=innodb;
analyze table tableName;(不会立即整理，会后台慢慢整理，不会阻塞当前会话）。注意：磁盘空间不会释放，即：myTable.ibd数据文件依然很大，数据可恢复（因为数据文件实际没有清理删除）。使用optimize命令会释放ibd文件空间，即磁盘上真实的物理删除。
optimize table tableName;(立即整理，阻塞当前会话，不可终止，如果终止可能会导致表数据文件损坏）

mysql 更改字段

alter table tableName modify `columnName` tinyint NOT NULL COMMENT '注释', modify ...;
ALTER TABLE tableName CHANGE oldColumnName newColumnName CHAR(32) NOT NULL DEFAULT '123';

mysql查看端口与版本号

show global variables like 'port';
show global variables like 'version';

mysql查看事务隔离级别

select @@global.tx_isolation;

清理binlog

# 指定某个binlog之前的日志删除
purge binary logs to 'binlog.000068';
# 按照时间删除binlog
purge binary logs before '2023-06-16 00:00:00';

重启服务

sudo /usr/local/mysql/support-files/mysql.server start
sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server restart

修改root密码

# 正常模式下
ALTER USER 'root'@'localhost' IDENTIFIED BY '哈哈';
# skip-grant-tables 模式下报错
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
# 先执行
update user set authentication_string='' where user='root';
flush privileges;
# 停止mysql服务，取消skip-grant-tables配置，启动mysql服务后，root可以无密码登陆
# root登陆后修改root密码
ALTER USER 'root'@'%' IDENTIFIED BY 'haha';
flush privileges;

查看锁

SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM performance_schema.transactions;

查看等待事件

SELECT * FROM performance_schema.events_waits_current;