mysql如何打开表的常规_Mysql常规维护操作

一、数据库状态查询

1.1、查看进程详情

show processlist;

show full processlist;

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.00sec)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.00sec)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.dumpzcat 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)

#实现排除函数functionmysql_ignoreTable(){for table in${ignoreTableArray[@]}dos="--ignore-table="${database}.${table}" "options=${options}${s}done#${options}拼接排除的表

#echo${options}

mysqldump-u${dbuser} -p${dbpasswd} ${database} ${options} >${database}.sql

}

mysql_ignoreTable