当前位置:Gxlcms > 数据库问题 > Mysql常规维护操作

Mysql常规维护操作

时间:2021-07-01 10:21:17 帮助过:15人阅读

1.2、数据库连接数
当前连接数
show status like %threads_%;
最大连接数
show variables like %max_connections%;
已使用连接数
show global status like Max_used_connections;

二、表锁状态查询

2.1、表锁

查看有多少线程使用某张表,name_locked表示表名是否被锁

show OPEN TABLES where In_use > 0;
show open tables from db_name;
2.2、锁状态

mysql的锁有表锁和行锁,myisam最小锁为表锁,innodb最小锁为行锁,可以通过以下命令获取锁定次数、锁定造成其他线程等待次数,以及锁定等待时间信息。

show status like %lock%;
2.3、查询表PROCESSLIST,查看各SQL执行状态
use information_schema;
select * from PROCESSLIST  where DB=database_name limit 10;
2.4、解锁方式
show processlist; kill id;
unlock tables;

三、数据库引擎状态查询

3.1、查看innodb状态
show engine innodb status\G;

四、MySQL数据库参考配置查询

4.1、超时参数
show variables like %timeout%;
4.2、慢日志参数
show variables like %slow%;

慢日志分析工具—mysqldumpslow

# -s:排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序;
#                             ac , at , al , ar 表示相应的倒叙;
# -t:返回前面多少条的数据;
# -g:包含什么,大小写不敏感的;
mysqldumpslow -s r -t 10  /slowquery.log     #slow记录最多的10个语句
mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log     #按照时间排序前10中含有"left join"

日志分析工具—pt-query-digest

pt-query-digest slow.logs
SQL耗时语句
1)打开分析工具
set @@prifileing=1; 或者 SET profiling=1;
2)执行一条语句
select * from userinfo limit 1;
3)查看sql性能
mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.06216700 | SELECT DATABASE()               |
|        2 | 0.04255600 | select * from user_info limit 1 |
|        3 | 0.00148150 | select * from userinfo limit 1  |
+----------+------------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)
4)根据Query_ID 查看某个查询的详细时间耗费
mysql> show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000191 |
| checking permissions | 0.000068 |
| Opening tables       | 0.000458 |
| init                 | 0.000054 |
| System lock          | 0.000032 |
| optimizing           | 0.000028 |
| statistics           | 0.000059 |
| preparing            | 0.000056 |
| executing            | 0.000035 |
| Sending data         | 0.000207 |
| end                  | 0.000079 |
| query end            | 0.000045 |
| closing tables       | 0.000059 |
| freeing items        | 0.000066 |
| cleaning up          | 0.000048 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
5)查看cpu io情况
mysql> show profile block io, cpu for query 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000191 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000068 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000458 | 0.002999 |   0.000000 |            0 |             0 |
| init                 | 0.000054 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000028 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000059 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000056 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000035 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000207 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000079 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000045 | 0.000000 |   0.001000 |            0 |             0 |
| closing tables       | 0.000059 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000066 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000048 | 0.001000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

五、查看表的索引

5.1、查看table_name有索引信息
show index from table_name;
5.2、创建索引
CREATE INDEX index_name ON table_name (column_key);

六、数据库操作

6.1、查看db_name的每个表大小
SELECT CONCAT(table_schema,.,table_name) AS Table Name, CONCAT(ROUND(table_rows/1000000,4),M) AS Number of Rows, CONCAT(ROUND(data_length/(1024*1024*1024),4),G) AS Data Size, CONCAT(ROUND(index_length/(1024*1024*1024),4),G) AS Index Size, CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),G) ASTotalFROM information_schema.TABLES WHERE table_schema LIKE db_name ORDER BY Total;
6.2、导出表内容
查看数据库允许导出的目录
show variables like %secure%;
导出sql格式
mysql -uroot -p 123456 db_name -e "SELECT id,name from users INTO OUTFILE ‘/tmp/filename.sql‘"
导出csv格式
SELECT * FROM passwd INTO OUTFILE /tmp/runoob.txt FIELDS TERMINATED BY , ENCLOSED BY " LINES TERMINATED BY \r\n;
6.3、查看字符编码
show variables like %char%;
6.4、数据库备份
#全库备份
mysqldump -uroot  dbname -p | gzip > dbname20190101.gz
#排除某些表备份
mysqldump -uroot  dbname -p --ignore-table=dbname.tablename1 --ignore-table=dbname.tablename2 > dbname-20190731.sql
#全库还原
gunzip -c dbname20190101.gz | mysql -uroot -p dbname
#导出指定表
mysqldump -uroot  dbname -p --tables table_name1 table_name2 table_name3 | gzip > dbname20190101.gz
#导出结构
mysqldump -d dbname -uroot -p > dbname.sql
#导出特定表结构
mysqldump -d -u someuser -p mydatabase
mysqldump --no-data -u someuser -p mydatabase
#多张表结构
mysqldump -uroot -p -d dbname $(mysql -uroot -p -D dbname -Bse "SHOW TABLES LIKE ‘tbl_flow%‘") > dbname-table.sql
mysqldump -uroot -d dbname -p --tables table1 table2 table3
#部分表还原
sed -n -e /CREATE TABLE.*`mytable`/,/CREATE TABLE/p mysql.dump > mytable.dump
zcat database-2013-03-03-weekly.sql.gz | sed -n -e /CREATE TABLE.*interesting_table/,/CREATE TABLE/p > interesting_table.sql

#备份脚本

#!/bin/bash
database="dbname"
dbuser="dbuser"
dbpasswd="dbpw"
options=""
#定义排除表的数组
ignoreTableArray=(table1 table2)
#实现排除函数
function mysql_ignoreTable(){
        for table in  ${ignoreTableArray[@]}
        do
                s="--ignore-table="${database}.${table}" "
                options=${options}${s}
        done
        #${options}拼接排除的表
#       echo ${options}
        mysqldump -u${dbuser} -p${dbpasswd} ${database} ${options} >${database}.sql
}
mysql_ignoreTable

 

Mysql常规维护操作

标签:index   排除   sam   col   order   time   data   set   like   

人气教程排行