mysql和shell命令

SELECT * FROM t   INTO OUTFILE '/tmp/t.csv'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
load data local infile '/tmp/t.csv'  into table t
fields terminated by '\t' 

mysql -uroot -p -S /data/db/mysql3306/mysql3306.sock
mysqldump -ucdc -p -h172.31.0.1 -P4000 --no-data   db1 > db1.sql

导出完整的insert
mysqldump -ucdc -p -h172.31.0.1 -P4000 --complete-insert   db1 t1 > t1.sql
mysqldump -h 172.31.0.1 -P4000 -u dba_root -p --all-databases --no-data --skip-lock-tables > all.sql
nohup mysqldump -h 172.31.0.1 -P4000 -u dba_root -p --no-data --skip-lock-tables --databases db1 db2  >all.sql

#导出账号密码及权限
mysqldump -uroot -proot mysql user db tables_priv -t --skip-extended-insert > /tmp/user_info.sql
show master status; 
SHOW STATS_HEALTHY where table_name='t1';
show binary logs;
purge binarylogs to 'xx';
SHOW TABLE STATUS LIKE 'tab';
SHOW GRANTS FOR user1@'%';
GRANT all ON *.* TO user1@'%'  
ALTER TABLE   db1.t1   CONVERT TO CHARACTER SET utf8mb4     COLLATE utf8mb4_unicode_ci;

SELECT TIDB_PARSE_TSO(457031496499200001);
SELECT conv(concat(bin(unix_timestamp('2025-04-01 00:00:00') * 1000),'000000000000000001'),2,10);

select from_unixtime(1743436800000/1000) ;
select unix_timestamp('2025-04-01 00:00:00') * 1000

pt-archiver \
--source h=***.mysql.rds.aliyuncs.com,D=crm,t=t1,u=username,p=password,A=utf8mb4 \
--dest h=***.mysql.rds.aliyuncs.com,D=testdb,t=t1,u=username,p=password,A=utf8mb4 \
--where "create_time < date_sub(now(), interval 3 month)" \
--limit 1000 --commit-each --bulk-delete --bulk-insert --progress 10000 --run-time 600s --sleep 1

set password for root@localhost = password('123'); 
ALTER USER 'root'@'localhost' IDENTIFIED BY '123'
select instance,user,count(*) from INFORMATION_SCHEMA.cluster_processlist group by 1,2 order by 1

SELECT * FROM INFORMATION_SCHEMA.TIKV_REGION_STATUS limit 99
select query, query_time, stats from information_schema.slow_query where is_internal = false and stats like '%pseudo%';

select VARIABLE_NAME, VARIABLE_VALUE from mysql.tidb where VARIABLE_NAME like "tikv_gc%";
update mysql.tidb set VARIABLE_VALUE='10m0s' where VARIABLE_NAME='tikv_gc_life_time';

select count(*) from mysql.gc_delete_range_done;
select TABLE_SCHEMA, TABLE_NAME,TIDB_ROW_ID_SHARDING_INFO from INFORMATION_SCHEMA.tables where TIDB_ROW_ID_SHARDING_INFO ='PK_AUTO_RANDOM_BITS=5'
select * from INFORMATION_SCHEMA. TIKV_STORE_STATUS

tiup cluster edit-config  dev-cluster
tiup ctl:v6.5.7 pd -i -u http://172.31.0.1:2379
region key --format=encode 't\200\000\000\000\000\000\000\026_i\200\000\000\000\000\000\000\001\004\006-ub-8\000\036\003\200\000\000\000\000\010=\352'
select * from INFORMATION_SCHEMA.TIKV_REGION_STATUS where region_id=331949 limit 20;
./tikv-ctl --host 172.31.0.1:20160 region-properties -r 331949

SELECT  TIMESTAMPDIFF(YEAR, '2000-05-01', CURDATE()) 

WITH RECURSIVE cte(a,b) AS (SELECT DATE '2024-01-01', DATE '2024-01-31'
 UNION SELECT TIMESTAMPADD(MONTH, 1, a) ,LAST_DAY(TIMESTAMPADD(MONTH, 1, a ) )from cte where a<'2024-12-01'
)
SELECT * FROM cte;

tidb使用 MPP 模式
set @@session.tidb_allow_mpp=1;
set @@session.tidb_enforce_mpp=1;

使用 MPP 模式访问分区表，需要先开启动态裁剪模式，需要analyze表
ALTER table test.employees SET tiflash replica 1;
SET tidb_partition_prune_mode=dynamic;

analyze table  test.employees;
https://docs.pingcap.com/zh/tidb/stable/statistics/#%E6%8E%A7%E5%88%B6-analyze-%E5%B9%B6%E5%8F%91%E5%BA%A6

du -h ./ | sort -rh | head -n 20
find ./ -type f -exec du -sh {} + | sort -rh | head -n 20

gcloud auth activate-service-account --key-file credentials.json
gcloud auth list

journalctl -u kibana
vim /etc/sysconfig/iptables
systemctl restart iptables.service 
iptables -nvL
netstat -ntlp

curl ifconfig.me
find -mtime +3|egrep '[0-9].log'|xargs rm -f
ln -s /data/kafka/logs   /usr/local/kafka_2.13-3.6.1/
tar -zcvf ./xxx.tar.gz  ./

# sed 打印 5~10 行内容
sed -n '5,10p' test.txt
sed -n '/x/ {N;p}' a.txt      ## 匹配x的行及其下一行
sed -n '/\/\*T!\[auto_rand\] AUTO_RANDOM(5) \*\//{x;p};h' xx.sql   #匹配上一行
sed -n '/\/\*T!\[auto_rand\] AUTO_RANDOM(5) \*\//'p  xx.sql |wc -l

sed -i 's/\/\*T!\[auto_rand\] AUTO_RANDOM(5) \*\//AUTO_RANDOM(5)/g' all.sql

# awk 打印 5~10 行内容
awk 'NR==5,NR==10'  test.txt
awk '/AUTO_RANDOM\(5\)/{print a $0}{a=$0 ORS}'  xx.sql   #匹配行和上一行
awk '/CREATE TABLE/{p=3}p-->0'  all.sql   #匹配 打印3行
awk -F' ' '{print $3,$4}'  xx.log    |sort -rn|uniq -c

result = "及格" if number >=60 else "不及格"
result =  number >=60?  "及格" : "不及格"

mvn clean package -Dmaven.test.skip=true
npm install -g @vue/cli   --prefix   /Desktop/
javascript:void(function(){document.title=prompt('新标签页名称')}())