总结一些 MySQL 的常用命令
客户端
- Navicat 可视化工具
mysql-workbench MySQL 官方的可视化开发和管理平台，ubuntu 安装命令
snap connect mysql-workbench-community
snap connect mysql-workbench-community:password-manager-service
snap connect mysql-workbench-community:ssh-keys
常用命令介绍
Where
where 执行顺序是从左往右执行的，在数据量多的时候要考虑条件的先后顺序，应遵守一个原则：排除最多的条件放在第一个，即按照过滤数据由大到小排序 where- where 中 AND 的执行优先级高于 OR，即在 AND、OR 同时出现时，优先执行 AND 语句，再执行 OR 语句
ALTER
-- 更改数据库字符集
ALTER DATABASE [database_name]
CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
-- 更改表字符集
ALTER TABLE [table_name]
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Change a column
ALTER TABLE [table_name]
CHANGE [column_name] [column_name] VARCHAR(255)
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 调整表字段顺序
ALTER TABLE [table_name]
CHANGE COLUMN `x1` `x1` LONGTEXT NULL DEFAULT NULL AFTER `x2`;
- 配置字符集
- ALTER TABLE 的锁
- MySQL 5.6 之前的版本，整个 DDL 过程的就是全程锁表
- MySQL 5.6 之后的版本，新增
ONLINE DDL 的功能，大大减少锁表时间
-
- 原始表加写锁
-
- 按照原始表和执行语句的定义，重新定义一个空的临时表，并申请 rowlog 的空间
-
- 拷贝原表数据到临时表，此时的表数据修改操作（增删改）都会存放在 rowlog 中。此时该表客户端可以进行操作的
-
- 原始表数据全部拷贝完成后，会将 rowlog 中的改动全部同步到临时表，这个过程客户端是不能操作的
-
- 当原始表中的所有记录都被 Copy 临时表，并且 Copy 期间客户端的所有增删改操作都同步到临时表。再将临时表命名为原始表表名
- 耗时经验值：
- 16 核 32G 500w 数据 ALTER TABLE 加字段时，不加 default 65s，加 default 220s，锁表时间占后 50%左右
optimize table
delete from table_name 之后，表会产生大量的碎片空间，使用 optimize table 可以释放该空间碎片
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...
- 一般输出 Table does not support optimize, doing recreate + analyze instead，等价于 `alter table <table_name> ENGINE=InnoDB`
optimize table 后 .ibd 文件的空间与 mysqldump 的大小基本持平- 900w 数据，20G 表空间，耗时 650s，锁部分时间很短，基本不影响表的插入
- 参考
DELETE
DELETE 语句的锁定行为主要包括两个方面
表级锁(Table Locking) 不添加限定条件时产生（不推荐）行级锁(Row Locking) 添加限定条件，如 where、limit 等时出现，推荐删除数据时指定
rename 重命名表
# rename
rename table <old-table-name> to <new-table-name>;
# aalter
alter table <old-table-name> rename as <new-table-name>;
- MySQL 5.7+ 开始提供
json_extract 函数
- 格式
$.字段名 可以用来查询对应的 valuejson_extract('字段', '$.key') json 对象json_extract('字段', '$[数组下标].key') json 数组
JSON_EXTRACT(json_doc, path[, path] …)
# 创建数据库
create database test;
use test
# 创建表
CREATE TABLE `json_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`value` json DEFAULT NULL COMMENT 'json value',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 表结构
mysql> desc json_table;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| value | json | YES | | NULL | |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show create table json_table;
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| json_table | CREATE TABLE `json_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
`value` json DEFAULT NULL COMMENT 'json value',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 插入数据
insert into `json_table` values (1, '{"name": "xie", "age": 18}');
insert into `json_table` values (2, '{"name": "xianbin", "site": "https://www.xiexianbin.cn"}');
# 查询所有字段
mysql> select * from json_table;
+----+----------------------------------------------------------+
| id | value |
+----+----------------------------------------------------------+
| 1 | {"age": 18, "name": "xie"} |
| 2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
2 rows in set (0.00 sec)
# 简单示例
mysql> select json_extract('{"name":"xianbin","age":"18"}', "$.age");
+-------------------------------------------------------+
| json_extract('{"name":"xianbin","age":"18"}',"$.age") |
+-------------------------------------------------------+
| "18" |
+-------------------------------------------------------+
1 row in set (0.00 sec)
# 查询 json 的 name 字段
mysql> select json_extract(`value`, '$.name') from `json_table`;
+---------------------------------+
| json_extract(`value`, '$.name') |
+---------------------------------+
| "xie" |
| "xianbin" |
+---------------------------------+
2 rows in set (0.00 sec)
# 默认值为 NULL
mysql> select json_extract(`value`, '$.name') as `name`, json_extract(`value`, '$.site') as `site` from `json_table`;
+-----------+-----------------------------+
| name | site |
+-----------+-----------------------------+
| "xie" | NULL |
| "xianbin" | "https://www.xiexianbin.cn" |
+-----------+-----------------------------+
2 rows in set (0.00 sec)
# 数组示例
mysql> insert into `json_table` values (3, '[{"name": "xiexianbin", "site": "https://www.xiexianbin.cn"}]');
# 查询数据的内容
mysql> select json_extract(`value`, '$[0].name') from `json_table` where id = 3;
+------------------------------------+
| json_extract(`value`, '$[0].name') |
+------------------------------------+
| "xiexianbin" |
+------------------------------------+
1 row in set (0.00 sec)
# json 查询条件
mysql> select * from `json_table` where json_extract(`value`, '$.name') = 'xianbin';
+----+----------------------------------------------------------+
| id | value |
+----+----------------------------------------------------------+
| 2 | {"name": "xianbin", "site": "https://www.xiexianbin.cn"} |
+----+----------------------------------------------------------+
1 row in set (0.00 sec)
RETURNING
- 生效版本
- MySQL 5.7 20210330 及以上
- MySQL 8.0 20220330 及以上
- 使用
DELETE...RETURNING 语句返回前镜像数据INSERT/REPLACE...RETURNING 返回后镜像数据
USE INDEX
使用 USE INDEX 指示查询优化器使用查询的命名索引列表
SELECT select_list
FROM <table_name>
USE INDEX(<index1, index2, ...>)
-- IGNORE
-- FORCE INDEX
WHERE <condition>;
查看索引
SHOW INDEXES FROM <table_name>;
show processlist
show processlist;
# 等价于
select * from information_schema.processlist where User='UserName';
# 生成杀死进程的命令
select concat('kill ',ID,';') from information_schema.processlist where User='UserName';
# 监控统计每个用户的访问
select User,count(*) as cnt from information_schema.processlist group by user;
show processlist; 查看当前所有数据库连接的 session 状态，非 root 用户仅能看到自己正在运行的线程（也可以配置权限）
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 28 | Waiting on empty queue | NULL |
| 10 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)
说明：
Id 线程的唯一标识，可以通过 kill <id> 命令杀掉线程
- 本质为
information_schema.processlist 表的主键
User 启动该线程的用户Host 发送请求的客户端的 IP 和 端口号db 当前执行的命令的数据库上
Command 此刻该线程正在执行的命令，参考
- `Binlog Dump` This is a thread on a replication source for sending binary log contents to a replica.
- `Change user` The thread is executing a change user operation.
- `Close stmt` The thread is closing a prepared statement.
- `Connect` Used by replication receiver threads connected to the source, and by replication worker threads.
- `Connect Out` A replica is connecting to its source.
- `Create DB` The thread is executing a create database operation.
- `Daemon` This thread is internal to the server, not a thread that services a client connection.
- `Debug` The thread is generating debugging information.
- `Delayed insert` The thread is a delayed insert handler.
- `Drop DB` The thread is executing a drop database operation.
- `Error`
- `Execute` The thread is executing a prepared statement.
- `Fetch` The thread is fetching the results from executing a prepared statement.
- `Field List` The thread is retrieving information for table columns.
- `Init DB` The thread is selecting a default database.
- `Kill` The thread is killing another thread.
- `Long Data` The thread is retrieving long data in the result of executing a prepared statement.
- `Ping` The thread is handling a server ping request.
- `Prepare` The thread is preparing a prepared statement.
- `Processlist` The thread is producing information about server threads.
- `Query` Employed for user clients while executing queries by single-threaded replication applier threads, as well as by the replication coordinator thread.
- `Quit` The thread is terminating.
- `Refresh` The thread is flushing table, logs, or caches, or resetting status variable or replication server information.
- `Register Slave` The thread is registering a replica server.
- `Reset stmt` The thread is resetting a prepared statement.
- `Set option` The thread is setting or resetting a client statement execution option.
- `Shutdown` The thread is shutting down the server.
- `Sleep` The thread is waiting for the client to send a new statement to it.
- `Statistics` The thread is producing server status information.
- `Time` Unused.
Time 该线程运行到该状态的时间State 线程运行 Command 当前的状态，参考
- `After create`
- `altering table`
- `Analyzing`
- `checking permissions`
- `Checking table`
- `cleaning up`
- `closing tables`
- `committing alter table to storage engine`
- `converting HEAP to ondisk`
- `copy to tmp table`
- `Copying to group table`
- `Copying to tmp table`
- `Copying to tmp table on disk`
- `Creating index`
- `Creating sort index`
- `creating table`
- `Creating tmp table`
- `deleting from main table`
- `deleting from reference tables`
- `discard_or_import_tablespace`
- `end`
- `executing`
- `Execution of init_command`
- `freeing items`
- `FULLTEXT initialization`
- `init`
- `Killed`
- `Locking system tables`
- `logging slow query`
- `login`
- `manage keys`
- `Opening system tables`
- `Opening tables`
- `optimizing`
- `preparing`
- `preparing for alter table`
- `Purging old relay logs`
- `query end`
- `Receiving from client`
- `Removing duplicates`
- `removing tmp table`
- `rename`
- `rename result table`
- `Reopen tables`
- `Repair by sorting`
- `Repair done`
- `Repair with keycache`
- `Rolling back`
- `Saving state`
- `Searching rows for update`
- `Sending data`
- `Sending to client`
- `setup`
- `Sorting for group`
- `Sorting for order`
- `Sorting index`
- `Sorting result`
- `starting`
- `statistics`
- `System lock`
- `update`
- `Updating`
- `updating main table`
- `updating reference tables`
- `User lock`
- `User sleep`
- `Waiting for commit lock`
- `waiting for handler commit`
- `Waiting for tables`
- `Waiting for table flush`
- `Waiting for lock_type lock`
- `Waiting on cond`
- `Writing to net`
Info 一般记录的是线程执行的语句
- 默认只显示前 100 个字符，查看全部信息可使用
SHOW FULL PROCESSLIST\G;
- 从数据库查询（等价的命令）
select * from information_schema.processlist;
select * from information_schema.processlist where User='UserName';
# 生成 kill 的命令
select concat('kill ',ID,';') from information_schema.processlist where User='UserName';
# 统计用户的连接数量
select User, count(*) as cnt from information_schema.processlist group by user;
免密码登录
方法一
[root@xiexianbin_cn ~]# cat .my.cnf
[client]
user=root
password=pass
方法二
mysql -uroot -S /var/lib/mysql/mysql.sock
用户
GRANT 语法
# 赋权
GRANT privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION
# 写入、刷新权限
FLUSH PRIVILEGES;
# 查询
show grants for <user>;
- 权限列表
ALL 所有权限，ALL PRIVILEGES 同义词ALTER 修改表和索引CREATE 创建数据库和表DELETE 删除表中已有的记录DROP 抛弃(删除)数据库和表INDEX 创建或抛弃索引INSERT 向表中插入新行REFERENCE 未用SELECT 检索表中的记录UPDATE 修改现存表记录FILE 读或写服务器上的文件PROCESS 查看服务器中执行的线程信息或杀死线程RELOAD 重载授权表或清空日志、主机缓存或表缓存SHUTDOWN 关闭服务器USAGE 特殊的 无权限 权限
创建用户
GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;
GRANT ALL ON test.* TO 'root'@'%' IDENTIFIED BY 'test';
GRANT ALL ON test.* TO 'root'@'localhost' IDENTIFIED BY 'test' WITH GRANT OPTION;
FLUSH PRIVILEGES;
GRANT SElECT ON test.* TO 'root'@'${ip}' IDENTIFIED BY "test";
FLUSH PRIVILEGES;
# 权限回收
REVOKE all ON *.* FROM 'root'@'localhost';
用户权限
show grants for itat@‘%';
数据库与表
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
创建数据库
CREATE DATABASE IF NOT EXISTS data1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
mysql> create database date2;
Query OK, 1 row affected (0.01 sec)
mysql -uroot -proot -Dmysql -e "select host,user from user";
for name in a b c; do
mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_t DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_o DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
done
mysql -uroot -proot -e "show databases"
删除数据库
drop database data1;
创建表
CREATE TABLE training(
id INT(11) PRIMARY KEY,
col1 VARCHAR(20) NOT NULL,
START DATE);
查看表创建命令
mysql> show create table t_config \G
*************************** 1. row ***************************
Table: t_config
Create Table: CREATE TABLE `t_config` (
`id` int(11) NOT NULL,
`col2` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
其他
查看 mysql 服务器数据文件
show variables like '%datadir%';
查看 mysql 版本
show variables like '%version%';
查看系统变量控制二进制日志自动删除的天数
show variables like 'expire_log%';
set global expire_logs_days=7;
select @@expire_logs_days;
查看 MySQL 支持的存储引擎
show engines;
查看 innodb 的状态
show engine innodb status;
表拆分示例
create table hpc_job_table_2021_1231 like hpc_job_table;
insert into hpc_job_table_2021_1231 select * from hpc_job_table where time_end < "2021-12-31";
delete from hpc_job_table where time_end < "2021-12-31";
查看 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
where table_schema='mysql'
order by data_length desc, index_length desc;
select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
from tables
where table_schema='passport' and table_name='tb_user_info';
innochecksum
innochecksum 是一个用于校验 innodb 表空间文件完整性的工具
- innnchecksum 使用时必须关闭 mysqld 进程，否则会在使用的时候提示
Unable to lock file 的错误
- 使用场景：mysqld 进程异常退出，或服务器宕机时用于快速检查表空间文件的完整性
使用
innochecksum --count <table>.ibd # ibd 文件中共有多少个page
innochecksum --page-type-summary sbtest1.ibd
innochecksum --page-type-dump=/tmp/<table>.log sbtest1.ibd
备份账号信息
SELECT
CONCAT(
'create user \'',
user,
'\'@\'',
Host,
'\''
' IDENTIFIED BY PASSWORD \'',
authentication_string,
'\';'
) AS CreateUserQuery
FROM
mysql.`user`
WHERE
`User` NOT IN (
'mysql.session',
'mysql.sys'
);
#!/bin/bash
#Function export user privileges
pwd=root
expgrants()
{
mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
mysql -u'root' -p${pwd} $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}
expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql
#执行脚本后结果
-- Grants for read@%
GRANT SELECT ON *.* TO 'read'@'%';
-- Grants for root@%
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
-- Grants for test@%
GRANT USAGE ON *.* TO 'test'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON `test_db`.* TO 'test'@'%';
-- Grants for test_user@%
GRANT USAGE ON *.* TO 'test_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.`t1` TO 'test_user'@'%';
-- Grants for mysql.session@localhost
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';
-- Grants for mysql.sys@localhost
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
禁止库写入
# 对库生效
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
# 对表生效
LOCK TABLES table_name READ;