一 . 数据库启停和访问
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;