mysql5.7常用操作命令手册

前言

本篇文章主要整理了以下在MySQL5.7环境中常用的一些命令,希望对大家有用。

一、关闭mysql服务

别轻易停数据库

关闭MHA高可用--->主库摘掉VIP--->关闭主库--->关闭从库

1.mha节点,关闭MHA高可用

systemctl stop daemontools.service

2.主节点，摘掉vip，停掉mysql服务

ifconfig bond0:0 down
/etc/init.d/mysql3306 stop（需要数据库root密码）
ps -ef|grep mysqld   检查确认数据库是否关闭

3.从节点，停掉mysql服务

/etc/init.d/mysql3306 stop（需要数据库root密码）
ps -ef|grep mysqld   检查确认数据库是否关闭

二、启动mysql

1.启动数据库顺序

首先启动主库---->启动从库---->挂载VIP--->启动MHA高可用

2.主节点，登陆数据库检查主库状态,将主库改成读写状态

/etc/init.d/mysql3306 start
/export/servers/app/mysql-5.7.42/bin/mysql -uroot  -p -S /export/servers/data/my3306/run/mysqld.sock

root@localhost:mysqld.sock : (none) 09:05:14> show slave status\G;
Empty set (0.00 sec)  主机点显示的信息为空

ERROR: 
No query specified
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)
set global read_only=off;
set global super_read_only=off;

3.从节点启动配置数据库，检查从库状态，启动从库，检查主从同步状态

/etc/init.d/mysql3306 start
/export/servers/app/mysql-5.7.22/bin/mysql -uroot  -p -S /export/servers/data/my3306/run/mysqld.sock
start slave;
show slave status\G;

4.主节点配置VIP，挂载VIP

/sbin/ifconfig 网卡名称 vip地址 netmask 255.255.255.0 && /sbin/arping -c 3 -s vip地址 -I bond0 网关地址

三、查看数据库字符集和排序规则并创建数据库

mysql> SELECT 
    SCHEMA_NAME, 
    DEFAULT_CHARACTER_SET_NAME, 
    DEFAULT_COLLATION_NAME 
FROM 
    information_schema.SCHEMATA 
WHERE 
SCHEMA_NAME = 'bank_data';  #替换为需要查看的库名

mysql> create database bank_data character set utf8 collate utf8_general_ci;  #根据查找出来的信息创建对应的库

四、查看表字段的注释信息

mysql > show  create table  表名 \G; 

五、修改表字段对应的长度

mysql > ALTER TABLE `表名` MODIFY COLUMN `字段名` varchar(500)修改后的长度   NOT NULL DEFAULT '' COMMENT '通道名称'; 

如果修改之前查出来该字段有对应的注释信息和不为空、默认值,则再修改时照常添加上

六、清理binlog

mysql >PURGE BINARY LOGS BEFORE '2021-10-12 17:00:00'; #删除指定日期之前的binlog
mysql > show master logs;
#删除mysql-bin.000006之前的binlog日志  
mysql >  purge binary logs to 'mysql-bin.000006';   

七、查看活跃的session

然后输出kill sql

mysql > select concat("kill ",id,";"),id,user,host,db,state,command,time,left(info,50) from information_schema.processlist where command<>'sleep' and command<>'Binlog Dump GTID' order by time;

八、查看会话的历史查询记录

mysql > SELECT
    b.processlist_id,
    c.db,
    c.user,
    a.sql_text,
    c.command,
    c.time,
    c.state,
    DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE variable_name='UPTIME') - a.TIMER_START * 1e-12 SECOND) AS start_time,
    DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE variable_name='UPTIME') - a.TIMER_START * 1e-12 SECOND) AS TIMER_END
FROM
    performance_schema.events_statements_history a
JOIN
    performance_schema.threads b USING (thread_id)
JOIN
    information_schema.processlist c ON b.processlist_id = c.id
WHERE
    c.id = 284 #这个id使用show processlist命令查询即可
ORDER BY
    a.EVENT_ID;

九、线程内存使用统计

mysql > SELECT
   mt.thread_id AS thread_id,
   SUM(mt.current_count_used) AS current_count_used,
   CAST(ROUND(SUM(mt.current_number_of_bytes_used) / 1024 / 1024) AS SIGNED) AS current_allocated_size_m,
   MAX(mt.current_number_of_bytes_used) AS current_max_alloc,
   t.name,
   t.processlist_db,
   t.processlist_info,
   t.processlist_command,
   t.processlist_host,
   t.processlist_time,
   t.processlist_user,
   t.processlist_id,
   t.processlist_state,
   t.parent_thread_id,
   t.thread_os_id
FROM
   performance_schema.memory_summary_by_thread_by_event_name mt
INNER JOIN
   performance_schema.threads t ON mt.thread_id = t.thread_id
WHERE
   t.processlist_db IS NOT NULL
   AND t.name = 'thread/sql/one_connection'
GROUP BY
   mt.thread_id
ORDER BY
   current_allocated_size_m;

十、mysqldump备份表

[root@python3 tmp]# /export/servers/app/mysql-5.7.42/bin/mysqldump  -uroot  -p -S \
/export/servers/data/my3306/run/mysqld.sock --default-character-set=utf8 --opt  --hex-blob  --skip-tz-utc \
 --add-drop-database=FALSE --add-drop-table=FALSE --single-transaction  --set-gtid-purged=OFF \
 --log-error=sbtest1.full.sql.`date +%Y%m%d_%H%M%S`.err  \
 dbtest --tables sbtest1 > sbtest1.full.sql.`date +%Y%m%d_%H%M%S` 2>sbtest1.full.sql.`date +%Y%m%d_%H%M%S`.log

参数解释
--default-character-set=utf8  指定备份的字符集
--opt 启用一组优化选项，包括 --quick、--add-drop-table、--add-locks、--create-options、--disable-keys 和 --extended-insert。这是一个常用的选项组合，用于提高备份速度并确保备份数据的一致性
--hex-blob  以十六进制格式备份 BLOB 类型的字段。适用于二进制数据，以避免数据丢失或损坏
--skip-tz-utc 备份时不会在备份文件的最前几行添加SET TIME_ZONE='+00:00'
--add-drop-database=FALSE 不在备份中添加 DROP DATABASE 语句。如果数据库已经存在，备份将不会包含删除现有数据库的语句
--add-drop-table=FALSE 不在备份中添加 DROP TABLE 语句。备份将不会包含删除现有表的语句。
--single-transaction 在一个事务中进行备份，这样可以在备份期间保持数据一致性。适用于使用事务的存储引擎（例如 InnoDB）
--set-gtid-purged=OFF  开启了GTID功能的数据库,备份时需要添加该参数 。不设置 GTID（全局事务标识）的被清除信息。适用于某些情况下需要禁用 GTID 记录
-R 备份存储过程(必须使用超管用户备份)
--tables 指定表名

十一、mysqldump备份单库

[root@python3 tmp]# dbname=visualdata
[root@python3 tmp]# /export/servers/app/mysql-5.7.22/bin/mysqldump  -uroot -S \
 /export/servers/data/my${port}/run/mysqld.sock -p  --default-character-set=utf8   --opt  --hex-blob  --skip-tz-utc \
  --add-drop-database=FALSE  --add-drop-table=FALSE --single-transaction  --set-gtid-purged=OFF \
     --log-error=${dbname}.full.sql.`date +%Y%m%d_%H%M%S`.err  \
     ${dbname}   > ${dbname}.full.sql.`date +%Y%m%d_%H%M%S` 2>${dbname}.full.sql.`date +%Y%m%d_%H%M%S`.log

十二、查看正在进行中的事务

mysql >SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
Kill  查出来的trx_mysql_thread_id  即可杀掉当前的大事务

十三、查看lock阻塞信息

mysql > show engine innodb status\G;
mysql > select * from sys.innodb_lock_waits; #用于获取 InnoDB 存储引擎中关于锁等待的详细信息。这个视图显示了哪些事务正在等待其他事务持有的锁，包括事务 ID、被等待的锁以及相关的资源。这对于诊断和分析数据库中的锁竞争问题非常有帮助。
mysql > select * from information_schema.INNODB_LOCKS;  #查看当前锁定的事务
mysql > select * from information_schema.INNODB_LOCK_WAITS; #查看当前等锁的事务
mysql > select * from information_schema.INNODB_TRX order by trx_started desc; #查看进行中的事务并按事务开始时间倒序排序

十四、查看阻塞者并解除锁

mysql > SELECT current_timestamp,
   pw.user as pw_user,pw.db  as pw_db,substring_index(pw.host,':',1)  as pw_host,pw.COMMAND as pw_command,pw.state as pw_state,pw.INFO  as pw_sqltext,pw.id  as pw_processid,pw.TIME as pw_time,
   `r`.`trx_wait_started` AS `waiting_started`,
   timediff( now(), `r`.`trx_wait_started` ) AS `waiting_age`,
   timestampdiff( SECOND, `r`.`trx_wait_started`, now() ) AS `waiting_age_secs`,
   `rl`.`lock_table` AS `waiting_locked_table`,
   `rl`.`lock_index` AS `waiting_locked_index`,
   `rl`.`lock_type` AS `waiting_locked_type`,
   `rl`.`lock_data` AS `waiting_locked_data`,
   `rl`.`lock_mode` AS `waiting_lock_mode`,
   `r`.`trx_mysql_thread_id` AS `waiting_pid`,
   r.trx_isolation_level AS waiting_isolation_level,
   `r`.`trx_id` AS `waiting_trx_id`,
   `r`.`trx_started` AS `waiting_trx_started`,
   timediff(now(), `r`.`trx_started`) AS `waiting_trx_age`,
   `r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,
   `r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,
   `r`.`trx_query` AS `waiting_query`,
   `rl`.`lock_id` AS `waiting_lock_id`,
   `b`.`trx_id` AS `blocking_trx_id`,
   `b`.`trx_mysql_thread_id` AS `blocking_pid`,
   b.trx_isolation_level AS block_isolation_level,
   `b`.`trx_query` AS `blocking_query`,
   `bl`.`lock_id` AS `blocking_lock_id`,
   `bl`.`lock_mode` AS `blocking_lock_mode`,
   `b`.`trx_started` AS `blocking_trx_started`,
   timediff(now(), `b`.`trx_started`) AS `blocking_trx_age`,
   `b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,
   `b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,
   concat(  'KILL ', `b`.`trx_mysql_thread_id`, ';' ) AS `kill_sql`,
pb.user as pb_user,pb.db  as pb_db,substring_index(pb.host,':',1)  as pb_host,pb.COMMAND as pb_command,pb.state as pb_state,pb.INFO  as pb_sqltext,pb.id  as pb_processid,pw.TIME as pw_time
FROM
   `information_schema`.`innodb_lock_waits` `w`
JOIN `information_schema`.`innodb_trx` `b` ON `b`.`trx_id` = `w`.`blocking_trx_id`
JOIN `information_schema`.`innodb_trx` `r` ON `r`.`trx_id` = `w`.`requesting_trx_id`
JOIN `information_schema`.`innodb_locks` `bl` ON `bl`.`lock_id` = `w`.`blocking_lock_id`
JOIN `information_schema`.`innodb_locks` `rl` ON `rl`.`lock_id` = `w`.`requested_lock_id`
JOIN `information_schema`.`processlist` `pw` ON `pw`.`id` = `r`.`trx_mysql_thread_id`
JOIN `information_schema`.`processlist` `pb` ON `pb`.`id` = `b`.`trx_mysql_thread_id`
ORDER BY
   `r`.`trx_id`;

十五、获取表中未使用索引信息

mysql> select * from sys.schema_unused_indexes a  where a.object_schema='dbtest'  and a.object_name  in ('sbtest2') ORDER BY a.object_name;
+---------------+-------------+------------+
| object_schema | object_name | index_name |
+---------------+-------------+------------+
| dbtest        | sbtest2     | k_2        |
+---------------+-------------+------------+
1 row in set (0.00 sec)

十六、获取冗余索引

mysql > select a.table_schema,a.table_name,a.redundant_index_name,a.redundant_index_columns,a.dominant_index_name,a.dominant_index_columns from sys.schema_redundant_indexes a where table_schema != 'performance_schema';

十七、跳过GTID

mysql > SET @@SESSION.GTID_NEXT= 'ece7b132-33c5-11e8-bfd6-e8611f23c126:256649553';
mysql > begin;
mysql > commit;
mysql > SET @@SESSION.GTID_NEXT= 'AUTOMATIC';

十八、binlog解析

mysqlbinlog --base64-output=DECODE-ROWS -vv mysql_bin.000008 > mysql_bin.000008.log

十九、gh-ost在线表结构变更 DDL

gh-ost --user="root" --password="******" --host=10.241.241.224 --port=3306 --database="order" --table="orderinfo" \
--alter=" add key ind_account (trans_account) "   --allow-on-master --initially-drop-old-table \
--initially-drop-ghost-table --allow-nullable-unique-key   --max-load=Threads_connected=1500 \
--critical-load=Threads_connected=4000 --chunk-size=200000  --max-lag-millis=1500 --initially-drop-socket-file  \
--verbose --execute

二十、xtrabackup物理备份

innobackupex --defaults-file=${mycnfdir} --user=$username --password=$passwd --lock-ddl-per-table --slave-info ${backup_path} --stream=tar | gzip - > ${backup_path}/full_${current_day}bak.tar.gz

--lock-per-table: 在备份期间对每个表进行DDL锁定,确保表结构的一致性
--slave-info: 包括备份中的主从信息
--stream=tar: 将备份数据流式传输到tar文件中
|gzip - >  ${backup_path}/full_${current_day}bak.tar.gz :通过管道将tar文件的输出压缩为gzip格式的文件，并将文件保存到.tar.gz中
-R: 备份的同时也备份存储过程(前提是必须使用超管用户来执行备份指令,且数据库开启了存储过程)

二十一、慢查询日志解析top10性能sql查询

pt-query-digest --type slowlog --since "2021-05-22 17:01:00" --until "2021-05-22 17:05:00"  slow.log > slow.log.txt

二十二、查询从库状态

mysql >show slave status\G;

二十三、查看某数据库中的所有表

mysql >use information_schema
mysql > select * from tables where table_schema=upper('foreigntrade_db');
#foreigntrade_db 替换为为库名

二十四、查看库大小

mysql > use information_schema
mysql > select sum(data_length)/1024/1024/1024 from tables where table_schema=upper('foreigntrade_db');
#foreigntrade_db 替换为为库名
#该语句出来是GB单位,如果要为MB,则为/1024/1024

二十五、查看表大小

mysql > use information_schema
mysql > select sum(data_length)/1024/1024/1024 from tables where table_schema=upper('foreigntrade_db') and table_name='for_access_manifest';
#首先进入到information_schema库中
#注意替换foreigntrade_db库名和for_access_manifest表名
#该语句出来是GB单位,如果要为MB,则为/1024/1024
#upper()函数对数据库名进行了转换，将其转换为大写形式,如果库名本身是小写,则此处不需要该函数

mysql > SELECT CONCAT(SUM(data_length)/1024/1024, ' MB') AS size FROM information_schema.tables WHERE table_schema = upper('foreigntrade_db') AND table_name = 'for_access_manifest';
#将获得一个名为'size'的结果列，其中包含数据大小以MB为单位的字符串。例如，结果可能类似于"10.5 MB"。
 
#该语句也能查表空间大小,结果以KB显示
mysql > select table_name,concat((avg_row_length*table_rows+index_length)/1024,'KB')as size from tables where table_schema='school' and table_name='student';

二十六、查看表有多少条数据量

mysql > select count(*) from 库名.表名;

二十七、根据表名找对应的库名

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME = '已知的表名';

二十八、查看表的数据并将结果导出为csv格式

mysql > SELECT column1, column2, ... INTO OUTFILE '/tmp/back.csv' FROM your_table  WHERE your_conditions;

#一般指定导出目录为tmp目录,就不会报错‘权限不足问题’

二十九、导出数据为csv格式并携带表头

mysql > SELECT 'id', 'deleted', 'created_time', 'modified_time', 'created_user' #必须是表中的字段和下方是select保持一致
UNION ALL
SELECT id, deleted, created_time, modified_time, created_user
from  b_incident
WHERE 
    incident_state = 'incident_done'
INTO OUTFILE '/tmp/incident.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

三十、查看mysql最近执行的语句

先查看一下二进制的记录日志是否打开
mysql > show variables where Variable_name="general_log";  #输出为on为打开,反之为关闭 
mysql > set global general_log=on;可以设置打开
然后确认一下默认的log产生在哪里
mysql > show variables where Variable_name="general_log_file"; #查看
然后去查出来的指定位置去查看即可

三十一、查看mysql正在执行的sql

mysql > select * from information_schema.processlist where info is not null;
可查看正在执行sql的ID、用户、主机、数据库库名、命令(增删改查)、执行时间、状态、执行的sql
可以查找出来一直长时间挂着是事务，通过ID进行kill 操作

三十二、查看mysql设置的连接数

mysql > show variables like '%conn%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 2000            |
| max_connections                               | 2080            |
| max_user_connections                          | 1000            |
| performance_schema_session_connect_attrs_size | 512             |
+-----------------------------------------------+-----------------+

三十三、查看mysql设置的读权限

mysql > show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+

三十四、创建用户并授权

mysql > grant select,update,insert,delete on yz_work.*  to 'pull_data_XZSPJ'@'192.168.65.11' identified by 'Vs$SJ&ROuNPxUHyN';

三十五、修改存储引擎

mysql > alter table 表名 engine=innodb;

三十六、查看默认存储引擎

mysql >select @@default_storage_engine;

三十七、查看存储引擎设置

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+