东莞网站建站公司,网站备案密码怎么找回,河南智能seo快速排名软件,网站开发攻克时间文章目录 前言一、关闭mysql服务1.mha节点,关闭MHA高可用2.主节点#xff0c;摘掉vip#xff0c;停掉mysql服务3.从节点#xff0c;停掉mysql服务 二、启动mysql1.启动数据库顺序2.主节点#xff0c;登陆数据库检查主库状态,将主库改成读写状态3.从节点启动配置数据库… 文章目录 前言一、关闭mysql服务1.mha节点,关闭MHA高可用2.主节点摘掉vip停掉mysql服务3.从节点停掉mysql服务 二、启动mysql1.启动数据库顺序2.主节点登陆数据库检查主库状态,将主库改成读写状态3.从节点启动配置数据库检查从库状态启动从库检查主从同步状态4.主节点配置VIP挂载VIP 三、查看数据库字符集和排序规则并创建数据库四、查看表字段的注释信息五、修改表字段对应的长度六、清理binlog七、查看活跃的session八、查看会话的历史查询记录九、线程内存使用统计十、mysqldump备份表十一、mysqldump备份单库十二、查看正在进行中的事务十三、查看lock阻塞信息十四、查看阻塞者并解除锁十五、获取表中未使用索引信息十六、获取冗余索引十七、跳过GTID十八、binlog解析十九、gh-ost在线表结构变更 DDL二十、xtrabackup物理备份二十一、慢查询日志解析top10性能sql查询二十二、查询从库状态二十三、查看某数据库中的所有表二十四、查看库大小二十五、查看表大小二十六、查看表有多少条数据量二十七、根据表名找对应的库名二十八、查看表的数据并将结果导出为csv格式二十九、导出数据为csv格式并携带表头三十、查看mysql最近执行的语句三十一、查看mysql正在执行的sql三十二、查看mysql设置的连接数三十三、查看mysql设置的读权限三十四、创建用户并授权三十五、修改存储引擎三十六、查看默认存储引擎三十七、查看存储引擎设置 前言
本篇文章主要整理了以下在MySQL5.7环境中常用的一些命令,希望对大家有用。 一、关闭mysql服务
别轻易停数据库
关闭MHA高可用---主库摘掉VIP---关闭主库---关闭从库1.mha节点,关闭MHA高可用
systemctl stop daemontools.service2.主节点摘掉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.sockrootlocalhost: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_onlyoff;
set global super_read_onlyoff;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 commandsleep and commandBinlog Dump GTID order by time;八、查看会话的历史查询记录
mysql SELECTb.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_nameUPTIME) - a.TIMER_START * 1e-12 SECOND) AS start_time,DATE_SUB(NOW(), INTERVAL (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE variable_nameUPTIME) - a.TIMER_START * 1e-12 SECOND) AS TIMER_END
FROMperformance_schema.events_statements_history a
JOINperformance_schema.threads b USING (thread_id)
JOINinformation_schema.processlist c ON b.processlist_id c.id
WHEREc.id 284 #这个id使用show processlist命令查询即可
ORDER BYa.EVENT_ID;
九、线程内存使用统计
mysql SELECTmt.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
FROMperformance_schema.memory_summary_by_thread_by_event_name mt
INNER JOINperformance_schema.threads t ON mt.thread_id t.thread_id
WHEREt.processlist_db IS NOT NULLAND t.name thread/sql/one_connection
GROUP BYmt.thread_id
ORDER BYcurrent_allocated_size_m;十、mysqldump备份表
[rootpython3 tmp]# /export/servers/app/mysql-5.7.42/bin/mysqldump -uroot -p -S \
/export/servers/data/my3306/run/mysqld.sock --default-character-setutf8 --opt --hex-blob --skip-tz-utc \--add-drop-databaseFALSE --add-drop-tableFALSE --single-transaction --set-gtid-purgedOFF \--log-errorsbtest1.full.sql.date %Y%m%d_%H%M%S.err \dbtest --tables sbtest1 sbtest1.full.sql.date %Y%m%d_%H%M%S 2sbtest1.full.sql.date %Y%m%d_%H%M%S.log参数解释
--default-character-setutf8 指定备份的字符集
--opt 启用一组优化选项包括 --quick、--add-drop-table、--add-locks、--create-options、--disable-keys 和 --extended-insert。这是一个常用的选项组合用于提高备份速度并确保备份数据的一致性
--hex-blob 以十六进制格式备份 BLOB 类型的字段。适用于二进制数据以避免数据丢失或损坏
--skip-tz-utc 备份时不会在备份文件的最前几行添加SET TIME_ZONE00:00
--add-drop-databaseFALSE 不在备份中添加 DROP DATABASE 语句。如果数据库已经存在备份将不会包含删除现有数据库的语句
--add-drop-tableFALSE 不在备份中添加 DROP TABLE 语句。备份将不会包含删除现有表的语句。
--single-transaction 在一个事务中进行备份这样可以在备份期间保持数据一致性。适用于使用事务的存储引擎例如 InnoDB
--set-gtid-purgedOFF 开启了GTID功能的数据库,备份时需要添加该参数 。不设置 GTID全局事务标识的被清除信息。适用于某些情况下需要禁用 GTID 记录
-R 备份存储过程(必须使用超管用户备份)
--tables 指定表名十一、mysqldump备份单库
[rootpython3 tmp]# dbnamevisualdata
[rootpython3 tmp]# /export/servers/app/mysql-5.7.22/bin/mysqldump -uroot -S \/export/servers/data/my${port}/run/mysqld.sock -p --default-character-setutf8 --opt --hex-blob --skip-tz-utc \--add-drop-databaseFALSE --add-drop-tableFALSE --single-transaction --set-gtid-purgedOFF \--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
FROMinformation_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 BYr.trx_id;十五、获取表中未使用索引信息
mysql select * from sys.schema_unused_indexes a where a.object_schemadbtest 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-outputDECODE-ROWS -vv mysql_bin.000008 mysql_bin.000008.log十九、gh-ost在线表结构变更 DDL
gh-ost --userroot --password****** --host10.241.241.224 --port3306 --databaseorder --tableorderinfo \
--alter add key ind_account (trans_account) --allow-on-master --initially-drop-old-table \
--initially-drop-ghost-table --allow-nullable-unique-key --max-loadThreads_connected1500 \
--critical-loadThreads_connected4000 --chunk-size200000 --max-lag-millis1500 --initially-drop-socket-file \
--verbose --execute二十、xtrabackup物理备份
innobackupex --defaults-file${mycnfdir} --user$username --password$passwd --lock-ddl-per-table --slave-info ${backup_path} --streamtar | gzip - ${backup_path}/full_${current_day}bak.tar.gz--lock-per-table: 在备份期间对每个表进行DDL锁定,确保表结构的一致性
--slave-info: 包括备份中的主从信息
--streamtar: 将备份数据流式传输到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_schemaupper(foreigntrade_db);
#foreigntrade_db 替换为为库名二十四、查看库大小
mysql use information_schema
mysql select sum(data_length)/1024/1024/1024 from tables where table_schemaupper(foreigntrade_db);
#foreigntrade_db 替换为为库名
#该语句出来是GB单位,如果要为MB,则为/1024/1024二十五、查看表大小
mysql use information_schema
mysql select sum(data_length)/1024/1024/1024 from tables where table_schemaupper(foreigntrade_db) and table_namefor_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_rowsindex_length)/1024,KB)as size from tables where table_schemaschool and table_namestudent;二十六、查看表有多少条数据量
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_namegeneral_log; #输出为on为打开,反之为关闭
mysql set global general_logon;可以设置打开
然后确认一下默认的log产生在哪里
mysql show variables where Variable_namegeneral_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_XZSPJ192.168.65.11 identified by Vs$SJROuNPxUHyN;三十五、修改存储引擎
mysql alter table 表名 engineinnodb;三十六、查看默认存储引擎
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 |
-----------------------------------------------------------------------------------------------------------------------------
