MySQL常用运维命令

最新推荐文章于 2022-08-01 12:31:29 发布

匿名者X

最新推荐文章于 2022-08-01 12:31:29 发布

阅读量492

点赞数

分类专栏： mysql 数据库

本文链接：https://blog.csdn.net/Ronin_88/article/details/107520962

版权

数据库同时被 2 个专栏收录

16 篇文章 2 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

一 . 数据库启停和访问

1 .优雅关闭mysql

-- 方式一
mysqladmin -uroot -p123 shutdown
-- 方式二
/etc/init.d/mysqld stop

2. 启动MySQL

nohup /home/mysql/mysql/bin/mysqld_safe &

二. 长连接和慢查询

1 . 获取长连接的用户连接

select left(host,if(locate(':',host),locate(':',host),length(host)+1)-1) as host_short,
       GROUP_CONCAT(distinct USER) as users,
       count(*)
from information_schema.`PROCESSLIST`
group by host_short
order by count(*),host_short;

2 . 手动KILL掉运行时间超过200s的所有数据库连接

select CONCAT('KILL ',id,';') from information_schema.`PROCESSLIST`
where user<>'root' and Command='Query' and db='db_name' and time > 200 into outfile '/data/mysql/a.txt';

3. 记录运行时间超过120s的查询

mysql -uroot -p -e "show processlist"|grep "Query"|grep "select"|egrep -V "root|Sleep|Locked|INSERT|DELETE|UPDATE"|gawk '{if(strtonum($6)>120){print $0;}}'|grep db_name > /tmp/long_running_process.lst

三 . 体系架构

1 . 查看哪些表是MyISAM引擎

select table_schema,table_name,engine from information_schema.tables where engine = 'MyISAM'

2. Innodb Bufferpool

使用情况

SELECT POOL_ID,POOL_SIZE，FREE_BUFFERS,DATABASE_PAGES FROM INNODB_BUFFER_POOL_STATS\G；

四 . 数据库对象

1 . 索引

force index

mysql> explain select * from t where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra                             |
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
|  1 | SIMPLE     | t     | range | a,b           | b   | 5       | NULL | 50128 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
1 row in set (0.00 sec)

mysql> explain select * from t force index(a)  where (a between 1 and 1000)  and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra                                             |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE     | t     | range | a             | a   | 5       | NULL |  999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+

2 . 查询无主键的表

select distinct TABLE_SCHEMA,TABLE_NAME from information_schema.tables t
where TABLE_SCHEMA = 'cshs_cz_test'
and not exists (select 1 from information_schema.KEY_COLUMN_USAGE k
where k.CONSTRAINT_NAME='PRIMARY' and k.TABLE_SCHEMA = 'cshs_cz_test'
and t.TABLE_SCHEMA=k.TABLE_SCHEMA and t.TABLE_NAME=k.TABLE_NAME);

3 . 表

数据库真是大小

-- SQL
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables;
-- 物理文件
cd $datadir
ls -l|grep ^d|awk '{print $NF}'|xargs du -sk|awk '{SUM += $1} END {print SUM/1000/1000"G"}'

空间大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
       concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB 
from tables where table_schema='test' and table_name='t1';

某个库的表大小排列

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) as data_MB FROM TABLES WHERE TABLE_SCHEMA='employees' order by round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) desc;

实例下所有表大小排序

select table_schema,table_name,round(DATA_LENGTH/1024/1024,2) as size_MB from information_schema.tables order by 3 desc limit 50;

4 . 存储过程

查询有哪些存储过程

select db,name from mysql.proc where type='PROCEDURE';

5 . 函数

查询有哪些函数

select db,name from mysql.proc where type='FUNCTION';

6 . 触发器

7 . 主外键

查找所有没有定义主键的表

select table_schema,table_name from information_schema.tables 
where (table_schema,table_name) not in(
    select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'    
)
and table_schema not in (
    'sys','mysql','information_schema','performance_schema'
);

删除test数据库中所有外键

SELECT
 CONCAT(
  "ALTER TABLE ",
  A.TABLE_SCHEMA,
  ".",
  A.TABLE_NAME,
  " DROP FOREIGN KEY ",
  A.CONSTRAINT_NAME,
  ";" 
) 
FROM
 (
 SELECT
  C.TABLE_SCHEMA,
  C.REFERENCED_TABLE_NAME,
  C.REFERENCED_COLUMN_NAME,
  C.TABLE_NAME AS TABLE_NAME,
  C.COLUMN_NAME AS COLUMN_NAME,
  C.CONSTRAINT_NAME,
  R.UPDATE_RULE,
  R.DELETE_RULE 
 FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
  JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME 
  AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
  AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME 
 WHERE
  C.TABLE_SCHEMA = 'test' 
 AND C.REFERENCED_TABLE_NAME IS NOT NULL 
 ) A;

test库的外键备份

SELECT
 CONCAT(
  "ALTER TABLE ",
  A.TABLE_SCHEMA,
  ".",
  A.TABLE_NAME,
  " ADD CONSTRAINT ",
  A.CONSTRAINT_NAME,
  " FOREIGN KEY (",
  A.COLUMN_NAME,
  ") references ",
  A.TABLE_SCHEMA,
  ".",
  A.REFERENCED_TABLE_NAME,
  "(",
  A.REFERENCED_COLUMN_NAME,
  ")",
  ";" 
) 
FROM
 (
 SELECT
  C.TABLE_SCHEMA,
  C.REFERENCED_TABLE_NAME,
  C.REFERENCED_COLUMN_NAME,
  C.TABLE_NAME AS TABLE_NAME,
  C.COLUMN_NAME AS COLUMN_NAME,
  C.CONSTRAINT_NAME,
  R.UPDATE_RULE,
  R.DELETE_RULE 
 FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
  JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME 
  AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
  AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME 
 WHERE
  C.TABLE_SCHEMA = 'test'
 AND C.REFERENCED_TABLE_NAME IS NOT NULL 
 ) A;

查询主外键对应关系

SELECT
 C.TABLE_SCHEMA,
 C.REFERENCED_TABLE_NAME,
 C.REFERENCED_COLUMN_NAME,
 C.TABLE_NAME AS TABLE_NAME,
 C.COLUMN_NAME AS COLUMN_NAME,
 C.CONSTRAINT_NAME,
 R.UPDATE_RULE,
 R.DELETE_RULE 
FROM
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
 JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME 
 AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
 AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME 
WHERE
 C.TABLE_SCHEMA = 'vcc' 
 AND C.REFERENCED_TABLE_NAME IS NOT NULL;

生成增加外键语句

SELECT
 CONCAT(
  "ALTER TABLE ",
  A.TABLE_SCHEMA,
  ".",
  A.TABLE_NAME,
  " ADD FOREIGN KEY ",
  A.CONSTRAINT_NAME,
  "(",
  A.COLUMN_NAME,
  ") references ",
  A.TABLE_SCHEMA,
  ".",
  A.REFERENCED_TABLE_NAME,
  "(",
  A.REFERENCED_COLUMN_NAME,
  ")",
  ";" 
) 
FROM
 (
 SELECT
  C.TABLE_SCHEMA,
  C.REFERENCED_TABLE_NAME,
  C.REFERENCED_COLUMN_NAME,
  C.TABLE_NAME AS TABLE_NAME,
  C.COLUMN_NAME AS COLUMN_NAME,
  C.CONSTRAINT_NAME,
  R.UPDATE_RULE,
  R.DELETE_RULE 
 FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
  JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME 
  AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
  AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME 
 WHERE
  C.TABLE_SCHEMA = 'vcc' 
 AND C.REFERENCED_TABLE_NAME IS NOT NULL 
 ) A;

五 . 备份和恢复

1 .备份

1 . 备份整库

mysqldump -uroot -p --all-database --routines --triggers --events --hex-blob > /tmp/all.sql

2 . 备份单库test

mysqldump -uroot -pbcrdb \
    --single-transaction \
    --master-data=2 \
    --routines --triggers --events --hex-blob \
    --databases test \
   > /tmp/`date +%Y%m%d`.sql

3 . 备份单库test下的表emp

mysqldump -uroot -p test emp > /tmp/emp.sql
mysql -uroot -p -e "select * from test.t1" > /tmp/t1.dmp (推荐）

4 . 备份单库test下的表emp和dept

mysqldump -uroot -p test emp dept > /tmp/emp_dept.sql

5 . 使用分隔符导出

mysqldump -uroot -p -T /var/lib/mysql-files/ galeratest t1 --fields-terminated-by ','

注：如果secure_auth=ON,则可使用secure_file_priv定义的路径来导出文件，这里定义的是/var/lib/mysql-files；
-T必须配合--fields-terminated-by使用，指定路径；
经测试，/var/lib/mysql-files初始权限750，会报如下错，最小可行权限是753（必须有w和x权限），chmod修改后成功

mysqldump: Got error: 1: Can't create/write to file '/var/lib/mysql-files/t1.txt' (Errcode: 13 - Permission denied) when executing 'SELECT INTO OUTFILE'

 select * into outfile '/var/lib/mysql-files/t1.txt'
             fields terminated by ',' optionally enclosed by '"'
             lines terminated by '\n'
           from galeratest.t1;   (推荐）

6 . 导入

mysql -uroot -p test < /tmp/test.sql （针对非分隔符导出的sql文件）
load data infile '/var/lib/mysql-files/t1.txt' into table galeratest.t1 fields terminated by ',' optionally enclosed by '"' lines terminated by '\n'; (针对分隔符导出的文件）

7 . 查看具体sql

mysqldump -uroot -p123 test > /opt/mysql_bak.sql
egrep -v "#|\*|--|^$" /opt/mysql_bak.sql

2 .从备份文件中恢复单表

导出表结构

#方法1:
sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q' testdb.sql > t1.ddl
#方法2，动态调整扩展范围：
grep -i 'CREATE TABLE `t1`' testdb.sql -a6

导出表数据

grep -i 'INSERT INTO `t1`' testdb.sql -a6 > t1.data

恢复表

mysql -uroot -p testdb < t1.ddl
mysql -uroot -p testdb < t1.data

六 .隔离级别、锁和Latch

1 . 在线修改隔离级别

Syntax:
SET [GLOBAL | SESSION] TRANSACTION
   transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic:
   ISOLATION LEVEL level
 | READ WRITE
 | READ ONLY

level:
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE

方法一：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

方法二：修改/etc/bcrdb/my.cnf

[mysqld]
transaction-isolation = READ-COMMITTED

2 . 查看锁等语句

SELECT
 r.trx_id waiting_trx_id,
 r.trx_mysql_thread_id waiting_thread,
 r.trx_query waiting_query,
 b.trx_id blocking_trx_id,
 b.trx_mysql_thread_id blocking_thread,
 b.trx_query blocking_query 
FROM
 information_schema.innodb_lock_waits w
 INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
 INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

show OPEN TABLES where In_use > 0;   --In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁

七 .日志

1 .binlog

show binlog events; --只查看第一个binlog文件的内容
show binlog events in 'mysql-bin.000002'; --查看指定binlog文件的内容
show binary logs;  --获取binlog文件列表
show master status； --查看当前正在写入的binlog文件
flush logs； --刷新二进制日志
set sql_log_bin=0; --临时关闭sql 写日志

八 . 高可用

1 . 主从复制

1. change master to...
CHANGE MASTER TO MASTER_HOST = '10.154.6.38', MASTER_USER = 'repl', MASTER_PASSWORD = 'repl', MASTER_PORT = 3307, MASTER_AUTO_POSITION = 1; 
2. 跳过gtid解决同步出错
stop slave;
set gtid_next='uuid:sqno'(sqno 可根据报错中的end_postion去master节点的binlog文件里寻找验证）
begin;commit;
seg gtid_next='automatic';
start slave;
3. 普通主从模式跳过报错
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;

九 .功能实现

1 . 行转列

例子1
+------+------+-------+------+
| id   | name | level | dept |
+------+------+-------+------+
|    1 | a   | 1     | dev |
|    2 | b   | 1     | dev |
|    3 | c   | 2     | dev |
|    4 | d   | 1     | prod |
|    5 | e   | 1     | prod |
|    6 | f   | 2     | prod |
|    7 | g   | 3     | prod |
|    8 | h   | 1     | test |
|    9 | i   | 2     | test |
+------+------+-------+------+

根据dept分组，分组后根据行数决定字段个数，比如dev有3行，输出字段为dept,name1,name2,name3

select t1.dept,
max(case t1.rank when 1 then t1.name end) 'name1',
max(case t1.rank when 1 then t1.level end) 'level1',
max(case t1.rank when 2 then IFNULL(t1.name,0) end) 'name2',
max(case t1.rank when 3 then t1.name end) 'name3',
max(case t1.rank when 4 then t1.name end) 'name4'
from (select a.id,a.name,a.level,if(@dept=a.dept,@rank:=@rank+1,@rank:=1) as rank,(@dept:=a.dept) dept from t1 a,(select @rank:=0,@dept:=NULL) b order by a.dept,a.id) t1
group by t1.dept;

+------+-------+-------+-------+-------+
| dept | name1 | name2 | name3 | name4 |
+------+-------+-------+-------+-------+
| dev | a     | b     | c     | NULL |
| prod | d     | e     | f     | g     |
| test | h     | i     | NULL | NULL |
+------+-------+-------+-------+-------+
3 rows in set (0.01 sec)
例子2 将5个日期转化成5个新的字段，每个字段下对应其C2字段的值
create table t0(c1 int,c2 int,c3 varchar(20));
insert into t0 values (1,11,'20190501'),(1,12,'20190601'),(1,13,'20190701'),(1,14,'20190801'),(1,15,'20190901');
insert into t0 values (2,21,'20190501'),(2,22,'20190601'),(2,23,'20190701'),(2,24,'20190801'),(2,25,'20190901');
insert into t0 values (3,31,'20190501'),(3,32,'20190601'),(3,33,'20190701'),(3,34,'20190801'),(3,35,'20190901');
insert into t0 values (4,41,'20190501'),(4,42,'20190601'),(4,43,'20190701'),(4,44,'20190801'),(4,45,'20190901');
insert into t0 values (5,51,'20190501'),(5,52,'20190601'),(5,53,'20190701'),(5,54,'20190801'),(5,55,'20190901');
insert into t0 values (6,61,'20190501'),(6,62,'20190601'),(6,63,'20190701'),(6,64,'20190801'),(6,65,'20190901');
insert into t0 values (7,71,'20190501'),(7,72,'20190601'),(7,73,'20190701'),(7,74,'20190801'),(7,75,'20190901');


select t1.c1,
max(case t1.rank when 1 then t1.c2 end) 'date1',
max(case t1.rank when 2 then t1.c2 end) 'date2',
max(case t1.rank when 3 then t1.c2 end) 'date3',
max(case t1.rank when 4 then t1.c2 end) 'date4',
max(case t1.rank when 5 then t1.c2 end) 'date5'
from (
select a.c1,a.c2,if(@c1=a.c1,@rank:=@rank+1,@rank:=1) as rank,(@c1:=a.c1) aa
from t0 a,(select @rank:=0,@c1:=NULL) b
where a.c3 in ('20190501','20190601','2019-05-20 00:00:00.000','20190701','20190801','20190901')
order by a.c1,a.c3) t1
group by t1.c1

+------+-------+-------+-------+-------+-------+
| c1   | date1 | date2 | date3 | date4 | date5 |
+------+-------+-------+-------+-------+-------+
|   1 |   11 |   12 |   13 |   14 |   15 |
|   2 |   21 |   22 |   23 |   24 |   25 |
|   3 |   31 |   32 |   33 |   34 |   35 |
|   4 |   41 |   42 |   43 |   44 |   45 |
|   5 |   51 |   52 |   53 |   54 |   55 |
|   6 |   61 |   62 |   63 |   64 |   65 |
|   7 |   71 |   72 |   73 |   74 |   75 |
+------+-------+-------+-------+-------+-------+

2 . 批量生成测试数据

insert into f select 1,1;
insert into f select a.id + b.a,a.id from f a join (select max(id) as a from f b) b on 1=1; -- 重复执行

3 . 实现rownum()行号功能

select @rownum:=@rownum+1 as rownum,lv,sex,age2 from TL,(select @rownum:=0) t where sex is not null;

匿名者X

关注

0
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL常用运维命令

一 .数据库启停和访问1 .优雅关闭mysql-- 方式一mysqladmin -uroot -p123 shutdown-- 方式二/etc/init.d/mysqld stop2. 启动MySQLnohup /home/mysql/mysql/bin/mysqld_safe &二. 长连接和慢查询1 . 获取长连接的用户连接select left(host,if(locate(':',host),locate(':',host),length(host.
复制链接

扫一扫