mysql学习

1、时间处理

-- bigint datetime互换
select 1554047999 as bit10,
(from_unixtime(1554047999))as datatime10,
1556609724438 as bit13,
(from_unixtime(1556609724438/1000))as datatime13,
now(),
now(4),
(unix_timestamp(now())) as unixtime10,
(unix_timestamp(now(4))) as unixtime13

-- 当前日期
select DATE_SUB(curdate(),INTERVAL 0 DAY) ;
-- 明天 
select DATE_SUB(curdate(),INTERVAL -1 DAY) ;
-- 昨天
select DATE_SUB(curdate(),INTERVAL 1 DAY) ;
-- 前一个小时
select date_sub(now(), interval 1 hour);
-- 后一个小时
select date_sub(now(), interval -1 hour);
-- 前30分钟
select date_add(now(),interval -30 minute)
-- 后20分钟
select date_add(now(),interval 30 minute)

2、查看数据容量

mysql查看各数据库数据容量和索引容量

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema;

mysql查看各用户容量排行前20表

select table_name as '数据库',
table_rows as '记录数',
data_length as '数据容量(MB)',
index_length as '索引容量(MB)'
from
(select
concat(table_schema,'.',table_name) as table_name,
sum(table_rows) as table_rows,
sum(truncate(data_length/1024/1024, 2)) as data_length,
sum(truncate(index_length/1024/1024, 2)) as index_length
from information_schema.tables
where table_schema='athena'
group by table_name) t
order by t.data_length desc limit 20;

删除表的所有索引

SELECT distinct  case when NON_UNIQUE=0 then  concat('alter table artemis_bak202105101.',TABLE_NAME ,' modify ',COLUMN_name,' int ;' 
																	,'alter table artemis_bak202105101.',TABLE_NAME ,' drop primary key;')
  when NON_UNIQUE=1 then   concat('drop index ',INDEX_NAME,' on artemis_bak202105101.',TABLE_NAME,';') END
  FROM INFORMATION_SCHEMA.STATISTICS t 
where  t.TABLE_NAME ='out_order_container_details'
 and TABLE_SCHEMA='artemis'
order by NON_UNIQUE desc ;

-- 删除unique
ALTER TABLE artemis_bak202105101.out_order DROP INDEX unique_out_order;

2、Linux查看
首先查看数据文件存放的Linux路径

 show variables like '%datadir%';

到该目录下查看数据文件大小

du *.ibd |sort -rn

3、锁表

select * from information_schema.innodb_trx ;

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;
kill 738178711 

-- 查询线程
 SELECT * from information_schema.processlist WHERE id = 738178711;
 show full processlist;

-- 终极方法
SELECT * from information_schema.`PROCESSLIST` WHERE Time > 1000 AND USER = 'wonguser' ORDER BY TIME desc;

kill 740097562

mysql行转列

SELECT 
  distinct   SUBSTRING_INDEX(SUBSTRING_INDEX(A.COL,',',help_topic_id+1),',',-1) AS num 
FROM 
    (SELECT sort_type4 COL from t_shop_merge_order_strategy_detail ) A join 
    mysql.help_topic b WHERE 
    b.help_topic_id < LENGTH(A.COL)-LENGTH(REPLACE(A.COL,',',''))+1 

查看binlog日志

/usr/bin/mysqlbinlog  --no-defaults --database=wms --base64-output=decode-rows -v /usr/local/geekplus/data/mysql/mysql-bin.001706 >/home/readonly/mysql-bin.0017061.log

/usr/bin/mysqlbinlog  --base64-output=decode-rows -v /usr/local/geekplus/data/mysql/mysql-bin.001706 >/home/readonly/mysql-bin.0017061.log

将关键信息输出：
grep -r -C20 '你要的关键信息'  mysql-bin.0017061.log >log1.sql

grep -r -C30 '你要的关键信息' mysql-bin.0017061.log;



或者将binlog下载到本地
到cmd运行，到mysql路径 C:\Program Files\MySQL\MySQL Server 8.0\bin，执行：
mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime="2021-03-16 11:00:00" --stop-datetime="2021-03-16 15:00:00" C:\Users\DELL\Downloads\Demo_Monitor_Python-master\mysql-bin-2.000316    D:\mysqlbinlog

mysql恢复备份数据

scp -P 22 readonly@172.16.19.14:/usr/local/geekplus/backup/mysql_data/dikanong_yanjiao/2021-05-20_04-00-01.tar.bz2 /usr/local/geekplus/backup/tmp

1 scp 到仿真后解压 tar -jxvf 2021-05-20_04-00-01.tar.bz2
2  准备恢复 innobackupex --apply-log /usr/local/geekplus/backup/tmp/2021-05-20_04-00-01
3  恢复     innobackupex --defaults-file=/etc/my.cnf  --move-back /usr/local/geekplus/backup/tmp/2021-05-20_04-00-01

重启mysql service mysqld restart
或者      service mysqld stop 然后 service mysqld start
如果起不来，检查/etc/my.cnf的datedir对应路径文件的所有者，如果是 root root 要改成mysql mysql 

恢复完后，可能需要改密码：
sed -i '/skip-grant-tables/d' /etc/my.cnf
sed -i '/skip/a skip-grant-tables' /etc/my.cnf
systemctl restart mysqld
以上步骤将改成无密码配置
然后登录mysql，用下面sql修改密码
flush privileges;
alter user 'root' @ '%' identified by 'root';