当前连接数 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.logsSQL耗时语句
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/hdhcms.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
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did118020