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';