Mysql常规维护操作

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‘) AS‘Total‘FROM 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